Query to select the month and nos of weekend and public holiday in that month

Question

My database table Holiday includes field ad Holidaydate->datetime , WeekendHoliday->bit, PublicHoliday->bit I want the nos of weekendholiday and also publicholiday in a specific month year.

My table values are as(example)

Holidaydate       WeekendHoliday       PublicHoliday 
-----------       --------------       -------------
4/02/2012             true                 false
4/20/2012             true                 false
5/3/2012              true                 False
5/30/2012             false                true
4/05/2013             false                true

So now output should be like this:

year      Month   count(weekend)   count(public)
----      -----   --------------   -------------
2012      April      2               0
2012      May        1               1
2013      April      0               1

You need more detail in this question. What do 3 and 5 in your desired output represent, counts of something, if so, what? Please explain a little more. Based on your question `select 'April' as Month, '3' as Weekend, '5' as Public` would satisfy the question. — RThomas, May 30 '12 at 05:57
Ya the 3 is counting the numbers of weekend holiday in the month of april and 5 represents the count the public holiday in the month of april also if the value of Weekend(field) is true then count else not, if the value of Public(field) is true then count.... — vaishali, May 30 '12 at 06:26
There is no `boolean` data type in SQL Server, did you mean `bit`? And your comments about 3 and 5 do not at all match the data you showed, e.g. you say that 3 is the number of weekend holidays in April, but your data shows there are only 2. You need to explain your logic more precisely. And which of the two result sets that you showed do you actually want? The first one with 3 columns or the second one with 4 columns? Or both? — Pondlife, May 30 '12 at 07:15

score 1 · Answer 1 · answered May 30 '12 at 06:58

1

I strongly suggest to use a calendar table for such kind of queries.

answered May 30 '12 at 06:58

David Brabant

41,623
16
83
111

ejb_guy · Accepted Answer · 2012-05-30T07:30:48.293

1

     SELECT year(holidaydate),month(holidaydate), 
            sum(case Weekend when true then 1 else 0 end) wkEnd, 
            sum(case PublicHoliday when true then 1 else 0 end) pubHol
      FROM Holiday 
      GROUP BY year(holidaydate),month(holidaydate)

I don't have SQL server available. THis is tested on mysql. Here year and month are function to return the year and month of date. Syntax of CASE should be same across database.

edited May 30 '12 at 07:30

answered May 30 '12 at 07:09

ejb_guy

1,125
6
6

this will not work in mssal because of the 'case weekend when true' replace with 'case weekend when 'true'' Also the count will count both 1 and 0. Replace with sum or remove the 'else 0' – t-clausen.dk May 30 '12 at 07:29

score 1 · Answer 3 · edited May 23 '17 at 11:49

Here's one way how you could aggregate your data:

WITH partitioned AS (
  SELECT
    MonthDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),
    WeekendHoliday,
    PublicHoliday
  FROM Holiday
)
SELECT
  Year           = YEAR(MonthDate),
  Month          = DATENAME(MONTH, MonthDate),
  Weekends       = COUNT(NULLIF(WeekendHoliday, 'false')),
  PublicHolidays = COUNT(NULLIF(PublicHoliday , 'false'))
FROM partitioned
GROUP BY
  MonthDate

The partitioned CTE replaces every date with the first of the same month, and that value is then used for grouping and deriving years and month names from. Each NULLIF() transforms every 'false' (0) value into NULL so the corresponding COUNT() omits it. As a result, only the 'true' (1) values are counted.

Query to select the month and nos of weekend and public holiday in that month

3 Answers3

Linked