Menu
I have an sample data like this :
DECLARE @T Table (ID INT, Name VARCHAR(10), DOB DATE)
INSERT INTO @T (ID,Name,DOB) VALUES (1,'Mohan','2016-11-11')
INSERT INTO @T (ID,Name,DOB) VALUES (2,'Raj','2016-11-07')
INSERT INTO @T (ID,Name,DOB) VALUES (3,'Manny','2016-10-30')
INSERT INTO @T (ID,Name,DOB) VALUES (4,'kamal','2016-11-01')
INSERT INTO @T (ID,Name,DOB) VALUES (5,'Raj','2016-11-08')
INSERT INTO @T (ID,Name,DOB) VALUES (6,'Manasa','2016-11-10')
My question is when I run the query on this table on Sunday (i.e 06/11/2016)
For example :
Select Count(*), Cnt
from @T /* how to write logic for missing days */
My output :
Cnt Days
6 0 Days
Same thing when I run it on Thursday (i.e 06/11/2016)
Cnt Days
6 4 Days
How I need to get this one. Every Sunday it will run if it ran on Saturday it should show 6 days and Sunday to Sunday calculation.
Please suggest some way of doing this - I'm unable to move forward
303
To find the number of days between these two dates, you can enter “=B2-B1” (without the quotes into cell B3). Once you hit enter, Excel will automatically calculate the number of days between the two dates entered.
How do I go about calculating the days between two dates? To calculate the number of days between two dates, you need to subtract the start date from the end date. If this crosses several years, you should calculate the number of full years.
Say you have a Due Date field on a form in Access, and you want to show how many days are left until that Due Date arrives. A quick way to do this is to add a new text box to the form, and then use the DateDiff function in that text box to do the calculation.
To get the number of days since Sunday, you can use the DATEPART function to get the day of the week as an integer with Sunday = 1, Saturday = 7. So for this case:
SELECT COUNT(*), DATEPART(WEEKDAY, GETDATE()) - 1
FROM @T
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
