Count distinct holidays in a Calendar

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 distinct holidays in a Calendar

Author

Topic

spendyala
Starting Member

15 Posts

Posted - 2009-01-07 : 18:22:32

Hi,

I am having tough time figuring out the logic to count the distinct days as holidays from my calendar especially during this holiday season.

I have a calendar table where holidays are posted and i have exclude those holidays from the students absent details.

declare @Calendar table (descrip varchar(50), startdate datetime, EndDate DateTime)

INSERT @Calendar
select 'Christmas/New Years Break' ,'2008-12-25 00:00:00.000', '2009-01-02 00:00:00.000'
union all select 'Winter Break 2008','2008-12-25 00:00:00.000','2009-01-02 00:00:00.000'
union all select '2008 Winter Break', '2008-12-24 00:00:00.000','2008-12-24 00:00:00.000'

select MAX( DateDiff(dd,AdCalendar.StartDate,Case WHEN AdCalendar.EndDate >= getdate() THEN
getdate() ELSE AdCalendar.EndDate END)+1)
from @Calendar adcalendar
where startdate between '2008-12-01' and '2008-12-31'

select MAX( DateDiff(dd,AdCalendar.StartDate,Case WHEN AdCalendar.EndDate >= getdate() THEN
getdate() ELSE AdCalendar.EndDate END)+1)
from @Calendar adcalendar
where startdate between '2008-12-01' and '2008-12-31'

The first one is giving me a count of 19 which is wrong and the second one is giving me 9 which is also wrong, The result should have been 10 including the holiday 2008-12-24.

I appreciate your help.

Thank you,

Sri.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-07 : 18:26:54

I get 9 back from both, but a quick glance at the code and it looks like you posted the same query twice. Could you explain why 9 is incorrect and 10 is correct?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

spendyala
Starting Member

15 Posts

Posted - 2009-01-07 : 18:47:42

Tara,

This is the query that returns 19

select SUM( DateDiff(dd,AdCalendar.StartDate,Case WHEN AdCalendar.EndDate >= getdate() THEN
getdate() ELSE AdCalendar.EndDate END)+1)
from @Calendar adcalendar
where startdate between '2008-12-01' and '2008-12-31'

I am expecting 10 because when i use the MAX Function, it is not counting the 2008-12-24 holiday. So including 12-24-2008 and holidays between 2008-12-25 to 2009-01-02 the holiday count should be 10.

Thank you for the help!!!

Sri

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-07 : 18:49:49

This article might help you understand it: http://www.sqlteam.com/article/datediff-function-demystified

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

spendyala
Starting Member

15 Posts

Posted - 2009-01-07 : 18:54:47

Tara,

Thank you, for the link.

I dont have a problem with the Date Diff function. I am trying to figure the logic to count the distinct days as holidays for a date range when the same day is repeated multiple times in the calendar which i am not able to get using either MAX or SUM.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-07 : 23:06:13

Hi Try This

declare @table table (noofholidays int)
INSERT INTO @table
select DateDiff(dd,AdCalendar.StartDate,Adcalendar.enddate)+1
from @Calendar adcalendar
where startdate between '2008-12-01' and '2008-12-31'
group by adcalendar.startdate,adcalendar.enddate

SELECT SUM(noofholidays) FROM @table

Jai Krishna

spendyala
Starting Member

15 Posts

Posted - 2009-01-09 : 18:24:18

Jai Krishna,

Thank you, the logic worked for me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 03:09:15

quote:
Originally posted by spendyala

Jai Krishna,

Thank you, the logic worked for me.

will break date range will always be same? can there be an overlapping case like

select 'x Break' ,'2008-12-25 00:00:00.000', '2009-01-02 00:00:00.000'
union all select 'y break','2008-12-31 00:00:00.000','2009-01-02 00:00:00.000'

spendyala
Starting Member

15 Posts

Posted - 2009-01-12 : 14:14:56

Hi Visakh,

I was about to post the question with the overlapping dates. Yes, I some times have overlapping dates for the holidays as you mentioned above for the different shifts the students might be in. Could you please help me with this.

Thank you,

Srivalli

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 23:53:04

in that case, how should total calculation be? can you illustrate with some data?

spendyala
Starting Member

15 Posts

Posted - 2009-07-09 : 18:12:44

Hi Vishakh,
I would request your help on this issue. This is the same as mentioned above but have one more criteria added to it. The Calendar table has Shift Information and when the shift is Zero it means that the hoiday applies to all Shifts. In the example that i have given below, the student has holidays for his shift from 7/6/2009 and general holiday from 7/3/2009 to 7/5/2009. With the logic given below I am only able to pick the holidays associated for his shift not the holidays for every shift. Could you please look into and correct it.

CREATE TABLE #ConsDaysViolation (AdEnrollID int, AdShiftID int, LDA DateTime, SyCampusID int)
INSERT #ConsDaysViolation
SELECT 100297,79, '2009-06-30',16

DECLARE @GetDate Datetime
SET @GetDate = GETDATE()

DECLARE @AdCalendar TABLE (StartDate DateTime, EndDate DateTime, Descrip Varchar(100), AdShiftID int, SyCampusGrpID int)
INSERT @AdCalendar
SELECT '2009-07-03 00:00:00.000', '2009-07-05 00:00:00.000','2009 Independence Day',0, 1
UNION ALL SELECT '2009-07-06 00:00:00.000', '2009-07-11 00:00:00.000','2009 Independence Day',79,1

DECLARE @SyCampusList TABLE (SyCampusGrpID int, SyCampusID int)
INSERT @SyCampusList
SELECT 1,16

Select
Adenroll.AdenrollId,
DateDiff(dd,AdCalendar.StartDate,Case WHEN AdCalendar.EndDate >= @GetDate THEN
@GetDate ELSE AdCalendar.EndDate END)+1 as holidays,
adcalendar.startdate,
adcalendar.enddate
From #ConsDaysViolation
JOIN Adenroll (Nolock) ON Adenroll.AdenrollId = #ConsDaysViolation.AdenrollId
JOIN @SyCampusList SyCampusList ON
adenroll.SyCampusId = SyCampusList.SyCampusId
JOIN @AdCalendar AdCalendar ON
adcalendar.SyCampusGrpid= SyCampusList.SyCampusGrpid
and
AdCalendar.StartDate between #ConsDaysViolation.LDA and @GetDate + '23:59:59'
where adcalendar.adshiftid = (case when Exists (SELECT 1 from adcalendar join sycampuslist on
adcalendar.sycampusgrpid = sycampuslist.sycampusgrpid
where adshiftid = adenroll.adshiftid
and sycampuslist.sycampusid = adenroll.sycampusid
and AdCalendar.StartDate between #ConsDaysViolation.LDA and @GetDate + '23:59:59' ) then adenroll.adshiftid
else 0 end )

GROUP BY Adenroll.AdenrollId,adcalendar.startdate,adcalendar.enddate

DROP TABLE #ConsDaysviolation

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources