set DateFirst 5 -- Friday
select
DATEPART(ww, p.[CHECKDATE]) [Week]
, SUM(p.[INSURANCE]) [Insurance]
, p.[CHECKDATE] [CheckDate]
from [PAYROLL] p
where p.[CHECKDATE] >= '01/01/2012'
and p.[CHECKDATE] < '01/01/2013'
and p.[ INSURANCE] <> 0.00
and DATEPART(ww, p.[CHECKDATE]) = 4
group by p.[CHECKDATE]
set DateFirst 7 -- Always be sure to set it back
select dateadd(dd, number, '2012') as StartDT
, dateadd(dd, number+1, '2012') as EndDT
from master..spt_values
where type = 'P'
and dateadd(dd, number, '2012') < '2013'
and datename(dw, dateadd(dd, number, '2012')) = 'Thursday'
ASKER
select dateadd(dd, number-7, '2012') as StartDT
, dateadd(dd, number, '2012') as EndDT
from master..spt_values
where type = 'P'
and dateadd(dd, number, '2012') < '2013'
and datename(dw, dateadd(dd, number, '2012')) = 'Friday'
ASKER
ASKER
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
http://stackoverflow.com/questions/3839923/get-date-of-all-saturdays-in-a-given-year-sql-server
The following query would return you all the Thursdays in 2012:
Open in new window
So maybe you could have your base query for your checks and add a final part to it of something like
and CHECKDATE in (the select above)
You'd need to do a little date manipulation to truncate the dates to midnight of the dates returned only or convert to char with just the dates (which is horrible on indexing), but hopefully that gives you a good starting point.