CODEWITHSUNDEEP
sqlsql-servert-sql
mohan111
mohan111

Reputation: 8865

how to get count of days when it not ran on Target date

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

Upvotes: 7

Views: 93

Answers (1)

Beno
Beno

Reputation: 4753

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

Upvotes: 1

Related Questions