Turnaround - Datediff - 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

Turnaround - Datediff

Author

Topic

obroie1
Starting Member

6 Posts

Posted - 2008-01-24 : 12:12:47

Hi all,
I have a question about the datediff function. Basically I have two tables. An Export Table with two columns DateSubmitted and PublishDate. So I need the difference between those dates without the weekends. To achive this I created a Calendar table with a column for the date and another one for day of the week to know if the date was monday or saturday etc.

Here is where I have the problem

My Sql query without the calendar table is like this:

Select Date_Submitted, Publish_Date,((DATEDIFF(dd, Date_Submitted, Publish_Date) + 1) )AS Turnaround
from Export

and works

but i need to join the calendar table and will need to aply only to the datediff section

Select Date_Submitted.Export, Publish_Date.Export, DayEmailed.Calendar, DayofWeek.Calendar,((DATEDIFF(dd, Date_Submitted, Publish_Date) + 1) WHERE DateSubmited = DateEmailed and DayofWeek != 'Saturday' OR Sunday??? )AS Turnaround
from Export, Calendar

In the red section is where I have the problem

Please if someone could help me and give some advice on how to execute this query

Thanks & Regards

Eileen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-24 : 13:03:09

What is structure of your calendar table?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-24 : 13:09:47

Id be happy to see someone come up with a more simplified way to do this than this solution:


DECLARE @dates TABLE ( fromdate DATETIME, todate DATETIME )
INSERT @dates ( fromdate, todate )
SELECT '20080101', '20080124' UNION
SELECT '20070515', '20070623'

SELECT d1.fromdate, d1.todate, DATEDIFF(dd, d1.fromdate, d1.todate)-SUM(CASE WHEN d2.Weekdy IS NOT NULL THEN 1 ELSE 0 END) AS Weekdays FROM @dates d1
LEFT OUTER JOIN (
       SELECT Date, DATEPART(WEEKDAY, Date) AS Weekdy FROM (
         SELECT CAST(CAST(CAST('20000101' AS DATETIME) AS INT)+n0+n1+n2+n3+n4+n5+n6 AS DATETIME) AS Date
         FROM (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) AS z0
         CROSS JOIN (SELECT 0 AS n1 UNION SELECT 4 UNION SELECT 8 UNION SELECT 12) AS z1
         CROSS JOIN (SELECT 0 AS n2 UNION SELECT 16 UNION SELECT 32 UNION SELECT 48) AS z2
         CROSS JOIN (SELECT 0 AS n3 UNION SELECT 64 UNION SELECT 128 UNION SELECT 192) AS z3
         CROSS JOIN (SELECT 0 AS n4 UNION SELECT 256 UNION SELECT 512 UNION SELECT 768) AS z4
         CROSS JOIN (SELECT 0 AS n5 UNION SELECT 1024 UNION SELECT 2048 UNION SELECT 3072) AS z5
         CROSS JOIN (SELECT 0 AS n6 UNION SELECT 4096 UNION SELECT 8192 UNION SELECT 12288) AS z6 ) d
     ) d2 ON d2.Date BETWEEN d1.fromdate AND d1.todate AND d2.Weekdy IN ( 1, 7 )
GROUP BY d1.fromdate, d1.todate

obroie1
Starting Member

6 Posts

Posted - 2008-01-24 : 15:01:14

Hi Visakh16, My calendar table has two columns: DayEmailed which contains every single day of the last two years like 01/01/2008 and the next column in DateofWeek, which contains Monday, Tuesday, to Sunday. This way I know that the 01/01/2008 say was Monday.
Thanks for your help

obroie1
Starting Member

6 Posts

Posted - 2008-01-24 : 15:02:20

jdaman, could you please explain me this query??
can i use it as it is?
which variables do I need to change?
Thanks!

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-24 : 15:19:36

quote:
Originally posted by obroie1

jdaman, could you please explain me this query??
can i use it as it is?
which variables do I need to change?
Thanks!

Just replace the variable table with your ExportTable and outer join to the derived table provided using your FromDate and ToDate and include the the DATEDIFF - weekends formula in your select statement.

SELECT et.FromDate, et.ToDate, DATEDIFF(dd, et.FromDate, et.ToDate)-SUM(CASE WHEN d2.Weekdy IS NOT NULL THEN 1 ELSE 0 END) AS Weekdays
FROM [ExportTable] et
LEFT OUTER JOIN (
       SELECT Date, DATEPART(WEEKDAY, Date) AS Weekdy FROM (
         SELECT CAST(CAST(CAST('20000101' AS DATETIME) AS INT)+n0+n1+n2+n3+n4+n5+n6 AS DATETIME) AS Date
         FROM (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) AS z0
         CROSS JOIN (SELECT 0 AS n1 UNION SELECT 4 UNION SELECT 8 UNION SELECT 12) AS z1
         CROSS JOIN (SELECT 0 AS n2 UNION SELECT 16 UNION SELECT 32 UNION SELECT 48) AS z2
         CROSS JOIN (SELECT 0 AS n3 UNION SELECT 64 UNION SELECT 128 UNION SELECT 192) AS z3
         CROSS JOIN (SELECT 0 AS n4 UNION SELECT 256 UNION SELECT 512 UNION SELECT 768) AS z4
         CROSS JOIN (SELECT 0 AS n5 UNION SELECT 1024 UNION SELECT 2048 UNION SELECT 3072) AS z5
         CROSS JOIN (SELECT 0 AS n6 UNION SELECT 4096 UNION SELECT 8192 UNION SELECT 12288) AS z6 ) d
     ) d2 ON d2.Date BETWEEN et.FromDate AND et.ToDate AND d2.Weekdy IN ( 1, 7 )
GROUP BY et.FromDate, et.ToDate

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-24 : 15:36:16

quote:
Originally posted by obroie1

Hi Visakh16, My calendar table has two columns: DayEmailed which contains every single day of the last two years like 01/01/2008 and the next column in DateofWeek, which contains Monday, Tuesday, to Sunday. This way I know that the 01/01/2008 say was Monday.
Thanks for your help

ofroie1,

Since you already have a date table with all necessary dates in it the following code using the same logic as with my previous posts but using your date table should work:

SELECT et.FromDate, et.ToDate, DATEDIFF(dd, et.FromDate, et.ToDate)-SUM(CASE WHEN ct.DayEmailed IS NOT NULL THEN 1 ELSE 0 END) AS Weekdays
FROM [ExportTable] et
LEFT OUTER JOIN [CalenderTable] ct ON ct.DayEmailed BETWEEN et.FromDate AND et.ToDate
                                 AND ct.DateofWeek IN ( 'Saturday', 'Sunday' )
GROUP BY et.FromDate, et.ToDate

obroie1
Starting Member

6 Posts

Posted - 2008-01-25 : 09:08:39

Hi jdaman, Thanks for your reply
I am trying to use the query below, but gives me an error, Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Calendar'. I don;t know why this is as the table is called calendar...Thks a lot for your help

SELECT et.Date_Submitted, et.Publish_Date,
DATEDIFF(dd, et.Date_Submitted, et.Publish_Date)-SUM(CASE WHEN ct.DateEmailed IS NOT NULL THEN 1 ELSE 0 END) AS Weekdays
FROM [TBL_PSC_SA_Export] et
LEFT OUTER JOIN [Calendar] ct ON ct.DateEmailed BETWEEN et.Date_Submitted AND et.Publish_Date
AND ct.DayOfWk IN ( 'Saturday', 'Sunday' )
GROUP BY et.Date_Submitted, et.Publish_Date

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-25 : 10:16:59

Is the Calendar table in the same database as the TBL_PSC_SA_Export table?

obroie1
Starting Member

6 Posts

Posted - 2008-01-25 : 10:28:59

Yes it is..

Thks

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-25 : 10:44:49

Are there multiple Calendar tables in your database with different owners?

Do you get an error when running this:
SELECT TOP 1 * FROM [Calendar]

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-25 : 10:49:43

Also, check to verify that you are running in the correct database.

Add to the top of the query: USING [YourDatabaseName]

obroie1
Starting Member

6 Posts

Posted - 2008-01-25 : 11:09:29

Hi, no there is only one Calendar table...

I get an error when running this as well:
Do you get an error when running this:
SELECT TOP 1 * FROM [Calendar]

First says The command(s) completed successfully. and then when I play it I get
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Calendar'.

Thks

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-25 : 11:16:22

The Calendar table doesnt exists in the database that you are running your query. Check to make sure you are running in the correct database and that the Calendar table exists in that database.

This will tell you if the calendar table exists in the database you are running in:
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Calendar'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources