code help with leap year - 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)

code help with leap year

Author

Topic

webfoo
Starting Member

12 Posts

Posted - 2008-10-20 : 01:55:45

My code in a view is giving me errors. I'm getting information for the last four weeks from today but i keep getting one more week.

See my code below.

SELECT TOP (100) PERCENT AllocatedHrs AS TotalHours, AllocatedHrs - AllocatedHrs AS PastDemand, DATEPART(wk, ActualDueDate) AS TheWeekID,
MachineID
FROM dbo.tblMachineRunTime
WHERE (TaskStatusID = 2) AND (ActualDueDate BETWEEN GETDATE() - 42 AND GETDATE() - 14)
ORDER BY TheWeekID

at the moment i'm getting week 37 - 41. Is my logic incorrect??

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 02:05:16

quote:
Originally posted by webfoo

My code in a view is giving me errors. I'm getting information for the last four weeks from today but i keep getting one more week.

See my code below.

SELECT TOP (100) PERCENT AllocatedHrs AS TotalHours, AllocatedHrs - AllocatedHrs AS PastDemand, DATEPART(wk, ActualDueDate) AS TheWeekID,
MachineID
FROM dbo.tblMachineRunTime
WHERE (TaskStatusID = 2) AND (ActualDueDate BETWEEN GETDATE() - 42 AND GETDATE() - 14)
ORDER BY TheWeekID

at the moment i'm getting week 37 - 41. Is my logic incorrect??

Thanks

here what you're looking at is simply data from 14 days before current day up to 42 days from today. this may not always be getting you full weeks data and will vary based on day in which you're running the code.i think what you want is

SELECT     TOP (100) PERCENT AllocatedHrs AS TotalHours, AllocatedHrs - AllocatedHrs AS PastDemand, DATEPART(wk, ActualDueDate) AS TheWeekID, 
                      MachineID
FROM         dbo.tblMachineRunTime
WHERE     (TaskStatusID = 2) 
AND (ActualDueDate BETWEEN DATEADD(wk,DATEDIFF(wk,0,GETDATE())-4,0) AND DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)) 
ORDER BY TheWeekID

if inclusive of last week or

SELECT     TOP (100) PERCENT AllocatedHrs AS TotalHours, AllocatedHrs - AllocatedHrs AS PastDemand, DATEPART(wk, ActualDueDate) AS TheWeekID, 
                      MachineID
FROM         dbo.tblMachineRunTime
WHERE     (TaskStatusID = 2) 
AND (ActualDueDate BETWEEN DATEADD(wk,DATEDIFF(wk,0,GETDATE())-5,0) AND DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0)) 
ORDER BY TheWeekID

if you dont want last week included

webfoo
Starting Member

12 Posts

Posted - 2008-10-20 : 02:46:33

Thanks so much for that you can't believe how much this has helped me.
I think i missed the boat somewhere how can i get the last four weeks excluding the current week. So if i ran the query today it will get the data for the last four weeks 41/ 40/ 39/ 38.

Can you explain what these values do so i understand.

DATEADD(wk, DATEDIFF(wk, 0, GETDATE())- 6, 0) AND DATEADD(wk, DATEDIFF(wk, 0, GETDATE())0))

Your code is very elegant if i may add.

Once again i appreciate the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 02:54:52

quote:
Originally posted by webfoo

Thanks so much for that you can't believe how much this has helped me.
I think i missed the boat somewhere how can i get the last four weeks excluding the current week. So if i ran the query today it will get the data for the last four weeks 41/ 40/ 39/ 38.

Can you explain what these values do so i understand.

DATEADD(wk, DATEDIFF(wk, 0, GETDATE())- 6, 0) AND DATEADD(wk, DATEDIFF(wk, 0, GETDATE())0))

Your code is very elegant if i may add.

Once again i appreciate the help.

if you exclude last week what you want is this

SELECT     TOP (100) PERCENT AllocatedHrs AS TotalHours, AllocatedHrs - AllocatedHrs AS PastDemand, DATEPART(wk, ActualDueDate) AS TheWeekID, 
                      MachineID
FROM         dbo.tblMachineRunTime
WHERE     (TaskStatusID = 2) 
AND (ActualDueDate BETWEEN DATEADD(wk,DATEDIFF(wk,0,GETDATE())-5,0) AND DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0)) 
ORDER BY TheWeekID

this will give you data starting from previous week starting day backwards up to 4 weeks.

webfoo
Starting Member

12 Posts

Posted - 2008-10-20 : 02:58:22

Thanks for that but it's giving me week 37, 38, 39, 40, 41, 42.

I'm stumped as to why it's not giving me just week 38,39,40, and 41.

Which will exclude this week and get the last 4.

I appreciate your time.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 04:20:02

quote:
Originally posted by webfoo

Thanks for that but it's giving me week 37, 38, 39, 40, 41, 42.

I'm stumped as to why it's not giving me just week 38,39,40, and 41.

Which will exclude this week and get the last 4.

I appreciate your time.

thats becuse its taking first day of week as monday while datefirst settings suggests sunday as first date. try like below and see if it works

SELECT *
from Company_calendar
where Calendar_date between DATEADD(dd,-1,DATEADD(wk,DATEDIFF(wk,0,GETDATE())-5,0)) AND DATEADD(dd,-2,DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0))

webfoo
Starting Member

12 Posts

Posted - 2008-10-20 : 05:34:27

This gave me the exact days four weeks back
22/Sept/2008 - 19/10/2008

(Date BETWEEN DATEADD(dd, 7, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 5, 0)) AND DATEADD(dd, 6, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 1,
0)))

Thanks for all your help.

Much appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 05:45:17

quote:
Originally posted by webfoo

This gave me the exact days four weeks back
22/Sept/2008 - 19/10/2008

(Date BETWEEN DATEADD(dd, 7, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 5, 0)) AND DATEADD(dd, 6, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 1,
0)))

Thanks for all your help.

Much appreciated

welcome

or even this will give you same

SELECT *
from Company_calendar
where Calendar_date between DATEADD(wk,DATEDIFF(wk,0,GETDATE())-4,0) AND DATEADD(dd,-1,DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0))

webfoo
Starting Member

12 Posts

Posted - 2008-10-20 : 06:25:39

Well it didn't work i can't for the life of me figure it out.
I can't believe this is giving me so much grief.
Anymore ideas...i'm just about ready to throw this away.

Sorry but i can't seem to grasp it correctly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 06:33:18

quote:
Originally posted by webfoo

Well it didn't work i can't for the life of me figure it out.
I can't believe this is giving me so much grief.
Anymore ideas...i'm just about ready to throw this away.

Sorry but i can't seem to grasp it correctly.

can you run this and post back the result?

SELECT @@DATEFIRST

webfoo
Starting Member

12 Posts

Posted - 2008-10-20 : 06:39:17

It says 7...is this the root of my problem??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 06:46:15

then this should work for you

SELECT     TOP (100) PERCENT AllocatedHrs AS TotalHours, AllocatedHrs - AllocatedHrs AS PastDemand, DATEPART(wk, ActualDueDate) AS TheWeekID, 
                      MachineID
FROM         dbo.tblMachineRunTime
WHERE     (TaskStatusID = 2) 
AND (ActualDueDate BETWEEN DATEADD(wk,DATEDIFF(wk,0,GETDATE())-4,0) AND DATEADD(dd,-1,DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0))) 
ORDER BY TheWeekID

webfoo
Starting Member

12 Posts

Posted - 2008-10-20 : 06:59:24

7 is that Sunday??
I want it to start at Monday?? How can i set it??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 07:00:53

quote:
Originally posted by webfoo

7 is that Sunday??
I want it to start at Monday?? How can i set it??

SET DATEFIRST 1

will make Monday as starting day

webfoo
Starting Member

12 Posts

Posted - 2008-10-20 : 07:08:25

SET DATEFIRST 1

SELECT TOP (100) PERCENT AllocatedHrs AS TotalHours, AllocatedHrs - AllocatedHrs AS PastDemand, DATEPART(wk, ActualDueDate)
AS TheWeekID, MachineID, ActualDueDate
FROM dbo.tblMachineRunTime
WHERE (TaskStatusID = 2) AND (ActualDueDate BETWEEN DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 4, 0) AND DATEADD(dd, - 1, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)))
ORDER BY ActualDueDate

I'm totally stumped now. Any help you can render would be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 07:15:57

this is what i used and got as result from my table. FYI my datefirst setting is 7.

SELECT date
from Company_calendar
where Calendar_date between DATEADD(wk,DATEDIFF(wk,0,GETDATE())-4,0) AND DATEADD(dd,-1,DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0))


date
-----------
Sep 22 2008
Sep 23 2008
Sep 24 2008
Sep 25 2008
Sep 26 2008
Sep 27 2008
Sep 28 2008
Sep 29 2008
Sep 30 2008
Oct  1 2008
Oct  2 2008
Oct  3 2008
Oct  4 2008
Oct  5 2008
Oct  6 2008
Oct  7 2008
Oct  8 2008
Oct  9 2008
Oct 10 2008
Oct 11 2008
Oct 12 2008
Oct 13 2008
Oct 14 2008
Oct 15 2008
Oct 16 2008
Oct 17 2008
Oct 18 2008
Oct 19 2008

webfoo
Starting Member

12 Posts

Posted - 2008-10-20 : 07:20:35

The week number for 19-Oct-2008 shows up as 43 did i miss something here??

39 22/09/2008 12:00:00 AM
39 23/09/2008 12:00:00 AM
39 24/09/2008 12:00:00 AM
39 25/09/2008 12:00:00 AM
39 26/09/2008 12:00:00 AM
39 27/09/2008 12:00:00 AM
40 28/09/2008 12:00:00 AM
40 29/09/2008 12:00:00 AM
40 30/09/2008 12:00:00 AM
40 01/10/2008 12:00:00 AM
40 02/10/2008 12:00:00 AM
40 03/10/2008 12:00:00 AM
40 04/10/2008 12:00:00 AM
41 05/10/2008 12:00:00 AM
41 06/10/2008 12:00:00 AM
41 07/10/2008 12:00:00 AM
41 08/10/2008 12:00:00 AM
41 09/10/2008 12:00:00 AM
41 10/10/2008 12:00:00 AM
41 11/10/2008 12:00:00 AM
42 12/10/2008 12:00:00 AM
42 13/10/2008 12:00:00 AM
42 14/10/2008 12:00:00 AM
42 15/10/2008 12:00:00 AM
42 16/10/2008 12:00:00 AM
42 17/10/2008 12:00:00 AM
42 18/10/2008 12:00:00 AM
43 19/10/2008 12:00:00 AM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 07:45:31

quote:
Originally posted by webfoo

The week number for 19-Oct-2008 shows up as 43 did i miss something here??

39 22/09/2008 12:00:00 AM
39 23/09/2008 12:00:00 AM
39 24/09/2008 12:00:00 AM
39 25/09/2008 12:00:00 AM
39 26/09/2008 12:00:00 AM
39 27/09/2008 12:00:00 AM
40 28/09/2008 12:00:00 AM
40 29/09/2008 12:00:00 AM
40 30/09/2008 12:00:00 AM
40 01/10/2008 12:00:00 AM
40 02/10/2008 12:00:00 AM
40 03/10/2008 12:00:00 AM
40 04/10/2008 12:00:00 AM
41 05/10/2008 12:00:00 AM
41 06/10/2008 12:00:00 AM
41 07/10/2008 12:00:00 AM
41 08/10/2008 12:00:00 AM
41 09/10/2008 12:00:00 AM
41 10/10/2008 12:00:00 AM
41 11/10/2008 12:00:00 AM
42 12/10/2008 12:00:00 AM
42 13/10/2008 12:00:00 AM
42 14/10/2008 12:00:00 AM
42 15/10/2008 12:00:00 AM
42 16/10/2008 12:00:00 AM
42 17/10/2008 12:00:00 AM
42 18/10/2008 12:00:00 AM
43 19/10/2008 12:00:00 AM

thats what i told earlier. as datefirst setting is 1 it regards sunday as start of week. so 19th Oct will be start of 43rd week.

webfoo
Starting Member

12 Posts

Posted - 2008-10-20 : 07:47:31

I see what you're saying how can i get it to select Monday as the start of the week.

Many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 08:00:36

quote:
Originally posted by webfoo

I see what you're saying how can i get it to select Monday as the start of the week.

Many thanks

thats what i told you to set datefirst to 1.

what does below return?

set datefirst 1
SELECT DISTINCT DATEPART(wk, ActualDueDate)
FROM dbo.tblMachineRunTime
WHERE (TaskStatusID = 2) AND (ActualDueDate BETWEEN DATEADD(wk,DATEDIFF(wk,0,GETDATE())-4,0) AND DATEADD(dd,-1,DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)))
ORDER BY ActualDueDate

webfoo
Starting Member

12 Posts

Posted - 2008-10-20 : 16:24:28

visakh16 i really appreciate your help.
The code returned week 39 - 42 but i can't save it as a view.

I had to add the date filed for it to show up.

set datefirst 1
SELECT DISTINCT DATEPART(wk, ActualDueDate), ActualDueDate
FROM dbo.tblMachineRunTime
WHERE (TaskStatusID = 2) AND (ActualDueDate BETWEEN DATEADD(wk,DATEDIFF(wk,0,GETDATE())-4,0) AND DATEADD(dd,-1,DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)))
ORDER BY ActualDueDate

Thank you

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources