Business day calculations - 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

Analysis Server and Reporting Services (2005)

Business day calculations

Author

Topic

glawsfan
Starting Member

3 Posts

Posted - 2009-07-02 : 10:50:25

I know this subject may have been done to death elsewhere but I am new to SSRS and need to be able to find a solution to this....

I have a table that holds a unique transaction reference and two dates, startdate and enddate. I have a second calendar table that holds all dates between 1990 and 2020 and marks them as 'isweekend' or 'isholiday'.

I need to be able to structure a query in SSRS that selects each transaction reference and returns a sum of 'isweekend' and a sum of 'isholiday'.

I'm unsure of how to join the two tables to return these sums for each transaction?

please help!!!

glawsfan
Starting Member

3 Posts

Posted - 2009-07-02 : 10:53:31

just to be clear the sums of 'isweekend' and 'isholiday' needs to be between the transaction startdate and enddate.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-02 : 12:35:59

assuming isweeked and isholiday are bit fields:-


SELECT t.*,t1.WeekendCount,t1.HolidayCount
FROM YourTable t
CROSS APPLY (SELECT SUM(CASE WHEN isweekend=1 THEN 1 ELSE 0 END) AS WeekendCount,
             SUM(CASE WHEN isholiday=1 THEN 1 ELSE 0 END) AS HolidayCount,
             FROM CalendarTable
             WHERE Date >= t.StartDate
             AND Date <= t.EndDate)t1

glawsfan
Starting Member

3 Posts

Posted - 2009-07-03 : 04:59:00

Thanks, I've not come across cross apply before - looks as though I shall be using it alot from know on!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-05 : 02:31:28

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources