| Author |
 Topic  |
|
|
meef
Yak Posting Veteran
90 Posts |
 Posted - 06/21/2012 : 13:06:36
|
Right now I have the following in a stored procedure:
DATEDIFF(DAY, ship_date, p.delivered_date)as [ship_days]
I use this to determine how many days it takes a shipment to reach its destination, but is there any way I can have it ignore weekends? Things aren't delivered on weekends so it adds false days in there. |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/21/2012 : 13:36:31
|
Create a calendar table, populate with dates and a flag to say whether a weekend or not - then join to count
Something like
;with cte as
(
select dte = convert(datetime,'20080101')
union all
select dte = DATEADD(dd,1,dte) from cte where dte < '20201231'
)
insert dim_Calendar
(
dim_Calendar_id,
dte,
WorkingDay = case when datename(dw,dte) in ('Saturday','Sunday') then 0 else 1 end
from cte
option (maxrecursion 0);
select t.orderno, sum(d.workingday)-1
from dim_calendar d
join mytbl t
on d.dte between t.ship_date and t.delivered_date
group by t.OrderNo
==========================================
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. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47115 Posts |
Posted - 06/21/2012 : 17:48:22
|
DATEDIFF(DAY, ship_date, p.delivered_date) - ((DATEDIFF(wk,ship_date, p.delivered_date)-1) * 2)as [ship_days]
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 06/22/2012 : 07:20:11
|
quote:
Originally posted by visakh16
DATEDIFF(DAY, ship_date, p.delivered_date) - ((DATEDIFF(wk,ship_date, p.delivered_date)-1) * 2)as [ship_days]
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
I think I vaguely understand the logic you are trying to implement - count the total number of days, and subtract twice the number of week boundary crossings. But, I am not able to get the right answer when I try - for example this gives me 5 days.DECLARE @ship_date date,
@delivered_date date;
SET @ship_date = '20120101';
SET @delivered_date = '20120104';
SELECT
DATEDIFF(DAY, @ship_date, @delivered_date) -((DATEDIFF(wk, @ship_date, @delivered_date) -1) * 2) AS
[ship_days]
Does it depend on the @@DATEFIRST setting? My @@DATEFIRST is 7. |
|
|
|
subhaoviya
Posting Yak Master
India
119 Posts |
Posted - 06/22/2012 : 07:49:14
|
DECLARE @ship_date date,
@delivered_date date;
SET @ship_date = '20120601';
SET @delivered_date = '20120604';
select
DATEDIFF(DAY, @ship_date, @delivered_date) -(DATEDIFF(wk, @ship_date, @delivered_date)*2)
|
|
|
|
meef
Yak Posting Veteran
90 Posts |
Posted - 06/22/2012 : 08:23:45
|
Thanks guys, the one below seems to work:
quote:
Originally posted by subhaoviya
DECLARE @ship_date date,
@delivered_date date;
SET @ship_date = '20120601';
SET @delivered_date = '20120604';
select
DATEDIFF(DAY, @ship_date, @delivered_date) -(DATEDIFF(wk, @ship_date, @delivered_date)*2)
I have a ship date of 6/14 and a delivery of 6/21 and it's coming up 5 days as it should, not 7. |
Edited by - meef on 06/22/2012 08:24:40 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 06/22/2012 : 08:43:52
|
Thanks subhaoviya, that works. Visakh was almost right, he threw in an extra minus one probably to see if we are really paying attention 
Intuitively, I would have thought that the week boundary would depend on the DATEFIRST setting, but seems like it does not. If I had to bet, I would have bet on it being a bug, but it is a feature! See here: http://www.sqlmag.com/article/sql-server/datediff-with-the-week-part |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/22/2012 : 08:59:03
|
What about bank holidays?
==========================================
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. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 06/22/2012 : 10:56:18
|
quote:
Originally posted by nigelrivett
What about bank holidays?
==========================================
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.
Joe Celko and others have convinced me that if you want to consider anything beyond Saturday & Sunday as holidays, the only sane way is to use a calendar table. http://www.simple-talk.com/sql/t-sql-programming/temporal-data-techniques-in-sql-/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47115 Posts |
Posted - 06/22/2012 : 22:28:03
|
quote:
Originally posted by sunitabeck
Thanks subhaoviya, that works. Visakh was almost right, he threw in an extra minus one probably to see if we are really paying attention
Intuitively, I would have thought that the week boundary would depend on the DATEFIRST setting, but seems like it does not. If I had to bet, I would have bet on it being a bug, but it is a feature! See here: http://www.sqlmag.com/article/sql-server/datediff-with-the-week-part
Oops...seems like I complicated it a bit.. 
Didnt test it properly as I was in between something
Nice catch
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
DATEDIFF question
