Pay Period Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pay Period Data

Author

Topic

cemartin
Starting Member

2 Posts

Posted - 2011-02-10 : 12:48:18

need to write a query that finds the previous Sunday and last Saturday date. For example, today is thursday, 2/10, I need the query to find the Sunday date of 1/30 and the Saturday date of 2/5.

Here is what I have thus far but it is only giving me the current date range

select PERSONNUM, PERSONFULLNAME,laborleveldsc7, laborleveldsc4,sum(TIMEINSECONDS) from vp_totals where
PAYCODENAME = 'NWNA TIME-WORKED' AND APPLYDATE >= dateadd(day,1-datepart(dw, getdate()), getdate()) and APPLYDATE <=
dateadd(day,7-datepart(dw, getdate()), getdate()) group by personnum, personfullname, laborleveldsc7, laborleveldsc4

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-10 : 13:20:02

get the idea from the below if it helps

select
dateName(WEEKDAY, getdate())
,(case
when dateName(WEEKDAY, getdate())='Friday' then DATEADD(d,-6,getdate())
when dateName(WEEKDAY, getdate())='Thursday' then DATEADD(d,-5,getdate())
when dateName(WEEKDAY, getdate())='Wednesday' then DATEADD(d,-4,getdate())
when dateName(WEEKDAY, getdate())='Tuesday' then DATEADD(d,-3,getdate())
when dateName(WEEKDAY, getdate())='Monday' then DATEADD(d,-2,getdate())
Else GETDATE()
end) as Saturday
,(case
when dateName(WEEKDAY, getdate())='Friday' then DATEADD(d,-5,getdate())
when dateName(WEEKDAY, getdate())='Thursday' then DATEADD(d,-4,getdate())
when dateName(WEEKDAY, getdate())='Wednesday' then DATEADD(d,-3,getdate())
when dateName(WEEKDAY, getdate())='Tuesday' then DATEADD(d,-2,getdate())
when dateName(WEEKDAY, getdate())='Monday' then DATEADD(d,-1,getdate())
Else GETDATE()
end) as Sunday

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-02-10 : 14:04:31

select
	a.DT,
	StartOfWeekSunday	= dateadd(dd,((datediff(dd,-53684,a.DT)/7)*7)-7,-53684),
	EndOfWeekSaturday	= dateadd(dd,((datediff(dd,-53684,a.DT)/7)*7)-1,-53684)
from
	( -- Test dates
	select	DT = convert(datetime,'20110201')	union all
	select	DT = convert(datetime,'20110202')	union all
	select	DT = convert(datetime,'20110203')	union all
	select	DT = convert(datetime,'20110204')	union all
	select	DT = convert(datetime,'20110205')	union all
	select	DT = convert(datetime,'20110206')	union all
	select	DT = convert(datetime,'20110207')	union all
	select	DT = convert(datetime,'20110208')	union all
	select	DT = convert(datetime,'20110209')	union all
	select	DT = convert(datetime,'20110210')	union all
	select	DT = convert(datetime,'20110211')	union all
	select	DT = convert(datetime,'20110212')	union all
	select	DT = convert(datetime,'20110213')
	) a
order by
	a.DT

Results:

DT                      StartOfWeekSunday       EndOfWeekSaturday 
----------------------- ----------------------- -----------------------
2011-02-01 00:00:00.000 2011-01-23 00:00:00.000 2011-01-29 00:00:00.000
2011-02-02 00:00:00.000 2011-01-23 00:00:00.000 2011-01-29 00:00:00.000
2011-02-03 00:00:00.000 2011-01-23 00:00:00.000 2011-01-29 00:00:00.000
2011-02-04 00:00:00.000 2011-01-23 00:00:00.000 2011-01-29 00:00:00.000
2011-02-05 00:00:00.000 2011-01-23 00:00:00.000 2011-01-29 00:00:00.000
2011-02-06 00:00:00.000 2011-01-30 00:00:00.000 2011-02-05 00:00:00.000
2011-02-07 00:00:00.000 2011-01-30 00:00:00.000 2011-02-05 00:00:00.000
2011-02-08 00:00:00.000 2011-01-30 00:00:00.000 2011-02-05 00:00:00.000
2011-02-09 00:00:00.000 2011-01-30 00:00:00.000 2011-02-05 00:00:00.000
2011-02-10 00:00:00.000 2011-01-30 00:00:00.000 2011-02-05 00:00:00.000
2011-02-11 00:00:00.000 2011-01-30 00:00:00.000 2011-02-05 00:00:00.000
2011-02-12 00:00:00.000 2011-01-30 00:00:00.000 2011-02-05 00:00:00.000
2011-02-13 00:00:00.000 2011-02-06 00:00:00.000 2011-02-12 00:00:00.000