2 week date parameter - 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

General SQL Server Forums

New to SQL Server Programming

2 week date parameter

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-06-19 : 13:09:17

I have this formula that I am using below. Everything is working well, except for the date formula. If I put a set dates in it runs, but I don't want to put set dates in. I want it to pull 2 weeks work of data.

I know how to pull 1 weeks worth, but not two.
(YEAR [Mon1 Date] = YEAR(GETDATE())) AND (DATEPART(wk, [Mon1 Date]) = DATEPART(wk, GETDATE()))

SELECT Distinct 'Payroll' AS Type,
1 AS OrderVal,
Department,
Company,
ManagerName,
Emp_FirstName,
Emp_MiddleName,
Emp_LastName,
Sum(Mon1PHours + Tue1PHours + Wed1PHours + Thur1PHours + Fri1PHours + Mon2PHours + Tue2PHours + Wed2PHours + Thur2PHours + Fri2PHours) as [Hours],
Sum(Mon1PTOhours+ Tue1PTOhours + Wed1PTOhours + Thur1PTOhours + Fri1PTOhours +Mon2PTOhours + Tue2PTOhours + Wed2PTOhours + Thur2PTOhours + Fri2PTOhours)as[PTO Hours]
--(cast(WeekOneMonday as float)+ cast(WeekOneTuesday as float)+ cast(WeekOneWednesday as float) + cast(WeekOneThursday as float)+ cast(WeekOneFriday as float)+ cast(WeektwoMonday as float)+ cast(WeektwoTuesday as float)+
--cast(WeektwoWednesday as float)+ cast(WeektwoThursday+WeektwoFriday as float))) as ([Holiday Hours])

FROM VW_TimeSheetInfo
Where [Mon1 Date] >= '6/8/2009' and [Fri2 Date] <= '6/19/2009'
group by Emp_ID,Department,Company, ManagerName,Emp_FirstName,Emp_MiddleName,Emp_LastName-

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-19 : 13:20:05

just make where condition as


WHERE [Mon1 Date] >=DATEADD(dd,(DATEDIFF(dd,'17530101',GETDATE())/7) * 7,'17530101')-7 and [Fri2 Date] <= DATEADD(dd,(DATEDIFF(dd,'17530101',GETDATE())/7) * 7,'17530101') +4

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-06-19 : 13:28:41

You are awesome, it worked. I understand most of this formula,but what is 17530101?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 13:39:31

It is Monday, 01/01/1753, the earliest date that SQL Server knows.

Jim

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-06-19 : 14:00:46

Thanks Jim.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-20 : 01:48:43

quote:
Originally posted by werhardt

You are awesome, it worked. I understand most of this formula,but what is 17530101?

welcome

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-06-21 : 19:25:49

No one get's paid for the time they work on the weekends?

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 03:06:42

And noone celebrates the Midsummer festivities, or that is a Nordic custom only?

E 12°55'05.63"
N 56°04'39.26"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-06-23 : 22:23:33

If there's beer involved, I'll help you celebrate the Nordic custom. ;-)

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources