CODEWITHSUNDEEP
sqlsql-serverdatabase
Sathesh
Sathesh

Reputation: 496

Sql query to check if the table value exists between range of dates

I am working in SQLServer database. I have the following scenario,

My application runs every weekday of the month, when it runs it puts an entry into the table 'CONTROL' into the 'businessDate' column.

Is there a way to find out what are the days the application did not run for a given month. Is it possible to achieve it in a single query ?

Upvotes: 0

Views: 848

Answers (1)

Stan Shaw
Stan Shaw

Reputation: 3034

You need a calendar table. Then select from that table left joined to your CONTROL table on date, where the month is what you're looking for, and where the control table is null (to show just exclusions). 

DECLARE @Month int = 1 //For January

SELECT BaseDate
FROM CalendarTable cal
LEFT OUTER JOIN ControlTable con
    ON cal.BaseDate = con.businessDate //make sure to cast as a date if it's a datetime
WHERE MONTH(cal.BaseDate) = @Month
AND con.businessDate IS NULL

Upvotes: 1

Related Questions