Count Business Days from 2 Tables

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

Count Business Days from 2 Tables

Author

Topic

TPie9
Yak Posting Veteran

67 Posts

Posted - 2010-02-26 : 16:40:06

I have a workcalendar table that has the following info:
dt---isweekday
1/1/2010---1
1/2/2010---0
1/3/2010---0
1/4/2010---1
1/5/2010---1

Another table has the following
cust---shipped---delivered
ABC---1/1/2010---1/5/2010
DEF---1/3/2010---1/5/2010

I'm trying to figure out how many week days between the delivered date and shipped date. The answer should be 3 week days for cust ABC and 2 week days for cust DEF.

How can I link the 2 tables and count where weekdays = '1'?

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-26 : 17:07:44

This function should do the trick for you.
http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-26 : 20:14:25

you already have a workcalendar, you don't need to use the method provided in the link


select	cust, shipped, delivered, count(nullif(isweekday, 0))
from	order o
	inner join calendar c	on	shipped		<= dt
				and	delivered	>= dt
group by cust, shipped, delivered

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 23:31:07

if sql 2005 or above


SELECT c.columns...,cl.WeekdayCnt
FROM custtable c
CROSS APPLY (SELECT COUNT(*) AS WeekDayCnt
             FROM workcalendar
             WHERE dt BETWEEN c.shipped AND c.delivered
             AND isweekday=1) cl

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-27 : 06:10:15

another way to do it

SELECT c.Cust , COUNT(dt) FROM workcalander w
CROSS JOIN Custtbl c
WHERE dt BETWEEN c.Shipped AND c.Delivered AND isweekday = 1
GROUP BY c.Cust

Vabhav T

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-01 : 01:57:15

[code]SELECT C.*,
(SELECT COUNT(*)
FROM workcalendar
WHERE dt BETWEEN C.shipped AND C.delivered
AND isweekday = 1) AS WeekDayCnt
FROM custtable AS C;
[/code]

TPie9
Yak Posting Veteran

67 Posts

Posted - 2010-03-01 : 10:11:20

Thanks for the help. I got what I need.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-01 : 10:28:26

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources