Query to select the month and nos of weekend and p

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

Answer 1

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

Answer 2

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.

Answer 3

     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.