Queries taking weekends into account

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

Queries taking weekends into account

Author

Topic

BBassic
Starting Member

10 Posts

Posted - 2012-06-14 : 04:32:18

Hi there,

I'm trying to put together a query which shows how often we aren't hitting our product delivery times. I've got the base query in place however it doesn't factor in non-business days such as weekends so it looks as though a large number of orders are being delivered behind schedule.

A colleague of mine pointed me in the direction of the weekday datepart but I'm struggling to put it into practice.

Any help would be greatly appreciated

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 04:34:41

Create a calendar table populated with days and have a working day attribute.
Join to that to count the days. Then yoou can easily include bank holidays and such.

Much easier and safer than trying to do the calculation.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BBassic
Starting Member

10 Posts

Posted - 2012-06-14 : 04:57:23

Fantastic, thank you

Just another quick question. What I've got is an OrderDate column and a CompletedDate column and I'm using the difference between those two datetimes to calculate how many days an order has gone overdue. Using a calendar table I would be able to specify that if an order is placed on a Friday that the two days following should be ignored?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 05:20:28

yes
count(*)-1 from calendar where dte between OrderDate and CompletedDate and workingday = 1

Might be count(*)-1 or count(*) depending on how you calculate days
you will probably want

select o.orderid, count(*)-1
from orders o
join calendar c
on c.dte between o.OrderDate and o.CompletedDate
and c.workingday = 1
group by o.orderid

or maybe

select o.orderid, (select count(*)-1 from calendar c where c.dte between o.OrderDate and o.CompletedDate and c.workingday = 1)
from orders o

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BBassic
Starting Member

10 Posts

Posted - 2012-06-14 : 05:25:32

That's amazing, thank you so much

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 05:36:48

To populate the calendar something like

create table Calendar (dte datetime primary key, workingday int not null, weekend int not null, bankholidayUK int not null)
;with cte as
(
select dte = convert(datetime,'19800101')
union all
select dte = dte + 1 from cte where dte < '21000101'
)
insert Calendar
select dte ,
case when datename(dw,dte) in ('Saturday','Sunday') then 0 else 1 end ,
case when datename(dw,dte) in ('Saturday','Sunday') then 1 else 0 end ,
0
from cte

You will notice that instead of a count(*) and where clause you can use sum(workingday).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources