1

I'm having trouble with getting the records for the following

TABLE
id | holiday_From | holiday_To
1  | 2012-01-02   | 2012-01-03
1  | 2012-01-11   | 2012-01-16
1  | 2012-01-08   | 2012-01-22
1  | 2012-01-29   | 2012-01-30
1  | 2012-01-08   | 2012-01-11

I'm trying to get occurrences of holidays for a given month - i.e.

BETWEEN "2012-01-01" AND "2012-01-31"

there is a similar post but im unable to tweak it for my needs

RESULT
day (within range) | count() //number of ppl on holiday
DATE               | 3

for e.g.

SAMPLE OUTPUT
2012-01-02 | 1
2012-01-03 | 1
2012-01-08 | 2
2012-01-09 | 2
2012-01-10 | 2
2012-01-11 | 3
2012-01-12 | 2
2012-01-13 | 2
2012-01-14 | 2
2012-01-15 | 2
2012-01-16 | 2
......

In other words I am trying to get how many times a record is found for a specific date. I.e. how many people are on holiday on the 1st, 2nd 3nd etc.

Not every day is in the TABLE for each month

Any ideas?

p.s. this is what i have already (my shot in the dark)

SELECT h.holiday_From, h.holiday_To, COUNT( * )
FROM holiday h
JOIN holiday ho ON h.holiday_From
BETWEEN DATE( "2012-01-01" )
AND IF( DATE( "2012-01-31" ) , DATE( "2012-01-31" ) , DATE( "2012-01-01" ) )
GROUP BY h.holiday_From, h.holiday_To
Community
  • 1
  • 1
Stevanicus
  • 7,561
  • 9
  • 49
  • 70

2 Answers2

6

Please, do not be scared :))

Based on generate days from date range

select d.everyday, count(*) from (select @rownum:=@rownum+1, date('2012-01-01') + interval (@rownum-1) day everyday from
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(SELECT @rownum:=0) r WHERE @rownum < DAY(LAST_DAY('2012-01-01'))) d, tablename tbl WHERE d.everyday>=tbl.hFrom AND d.everyday<tbl.hTo GROUP BY d.everyday

Result:

2012-01-02  1
2012-01-08  2
2012-01-09  2
2012-01-10  2
2012-01-11  2
2012-01-12  2
2012-01-13  2
2012-01-14  2
2012-01-15  2
2012-01-16  1
2012-01-17  1
2012-01-18  1
2012-01-19  1
2012-01-20  1
2012-01-21  1
2012-01-29  1

ps: I renamed columns to hFrom and hTo

pps: updated variant for the range of dates

select d.everyday, count(*) from (select @rownum:=@rownum+1, date('2012-01-01') + interval (@rownum - 1) day everyday from
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(SELECT @rownum:=0) r WHERE @rownum <= DATEDIFF('2012-01-11','2012-01-01')) d, `test` tbl WHERE d.everyday BETWEEN tbl.hFrom AND tbl.hTo GROUP BY d.everyday

Updated - number 2 was missing from all the unions. it should not significantly affect anything.

Community
  • 1
  • 1
Cheery
  • 16,063
  • 42
  • 57
  • i think someone may be the SQL guru :) thank you very much! It worked. However, with one glitch. It does the dates between perfectly apart from the dateTo. I changed the > & < to <= & >=. – Stevanicus Jan 24 '12 at 19:31
  • @Stevanicus I thought about it, but changed the limits based on your example (last day of the range was not counted) here d.everyday>=tbl.hFrom AND d.everyday – Cheery Jan 24 '12 at 19:35
  • @Cherry, woops thats a mistake in my question. I altered it and posted the solution to include the dateTo in the result. Thanks again! – Stevanicus Jan 24 '12 at 19:36
  • @Cherry... any idea how to change the range? If I set it to date('2012-01-26') and DAY('2012-01-31') I get next months results not the dates within the 26 and the 31. Any ideas? thanks – Stevanicus Jan 24 '12 at 20:46
  • @Stevanicus I wrote it exactly as you asked - "for a given month". Please be more specific and do not misspell my nickname :) So, do you want to give the period, not just for a given month? – Cheery Jan 24 '12 at 20:48
  • :) just realised, soz :) it would be perfect if it could do both and I can just change the dates – Stevanicus Jan 24 '12 at 20:49
  • @Stevanicus btw, it will work only when there are less or exactly 1000 days in the range ) it is possible to write it for any number of days, but it requires a procedure. – Cheery Jan 24 '12 at 21:06
  • thanks. I think 1000 days is enough for me. if you want to post for over stackoverflow readers... be my guest. I'll need max a year at one time. I have altered your second answer a little to demostrate a range – Stevanicus Jan 24 '12 at 21:28
  • @Stevanicus Just in case - there was number 2 missing from all the unions, but I do not think it will affect anything :) – Cheery Feb 16 '12 at 02:23
2

Have you tried something like this?

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_last-day

This function will give you the last day of a month regardless of the variables involved like month/leapyear/etc...

That might be the problem you were having. It's not exactly clear...

SELECT h.holiday_From, h.holiday_To, COUNT( * )
FROM holiday h
JOIN holiday ho 
    ON h.holiday_From BETWEEN DATE( "2012-01-01" ) AND LAST_DAY("2012-01-01" )
GROUP BY h.holiday_From, h.holiday_To
kevin
  • 116
  • 4
  • thanks for your answer, the range is correct its just count all the rows rather than saying on for the 1st (because there is a 1st) there are 3 records with the same date -- i.e. 3 people are on holiday on the 1st. – Stevanicus Jan 23 '12 at 21:12