"neater" way of counting holidays

Question

I have a neat table of holidays showing date and recurrence if such holiday is recurrent (i.e. New Year)

Now, I need to count the number of holidays between two dates. If it was a simple list of dates without info about recurrence (so i.e. it would show all New Years between 2000-01-01 and 2015-01-01) it would be quite easy, i.e. something like

declare @start_Date Date= '2013-01-02',
@end_date Date ='2014-01-02'



SELECT  COUNT(CE.name) AS holidays_count
FROM dbo.argo_cal_event AS CE INNER JOIN  dbo.argo_cal_event_type AS CET 
ON CE.event_type_gkey = CET.gkey
WHERE (CET.name = 'EXEMPT_DAY') AND (CE.name <> 'Sundays')
AND (CE.occ_start BETWEEN @start_Date AND @end_date) 

But now we have a neat recurrence, so the query above won't count all the New Years, Christmases etc that have been declared as happening "every year starting from".

I COULD create a table with such list, but I've been wondering, is there any other way?

EDIT: Let me precise what I had in mind: I'd like to count the event normally if it event occurs once (I will assume here that user will have to populate all the irregular holidays as i.e. Easter), but when the recurrence <> Once, then get the occurrence start and count the years between that date and final date.

EDIT2: I think I've got it - for the recurrent holidays I can use

SELECT sum (datediff (year, ce.occ_start, @end_date)) as recurrent_holidays
FROM dbo.argo_cal_event AS CE INNER JOIN  dbo.argo_cal_event_type AS CET 
ON CE.event_type_gkey = CET.gkey
 WHERE (CET.name = 'EXEMPT_DAY') and (CE.repeat_interval ='ANNUALLY')

EDIT3: unfortunately this solution doesn't work (or at least getting quite complicated) if I'd like to count between TWO dates, in which one is taken from another table, i.e if I'd like to count recurring holidays between unit.time_in and getddate() :/

Answer 1

The most straightforward way would be to make them all one-time occurrences.

The table isn't going to be that big that you can't add in every Sunday. It's only 52 or 53 entries per year.

If you do it this way now you can do something like,

select count(*) from events where event_date between start_date and end_date

Done.

The main reason to do it this way, though, is that some of your holidays are tricky to calculate. You'll need to be calculating (or looking up) the dates for Good Friday, Easter Monday, and others. They're based on the phase of the moon.

Why not just calculate them all and make your query really easy?

In the end, it matters a lot less how "neat" your data structures are and a lot more on whether your code does what it's supposed to do, how long it takes to do it, and how much effort it takes to get it working.