3

I want to create a table which will show me a count of every weeks day for the current year by months as shown in the table below.

I know i need to use a with statement but have not managed to crack it just yet.

I am using MS SQL Server 2008 r2 and I am also a Junior in SQL So any help will be greatly appreciated

OUTPUT Expected:

Month       Mon Tue Wed Thu Fri Sat Sun Sum
-------------------------------------------
January     4   5   5   5   4   4   4   31
February    4   4   4   4   4   4   4   28
March       4   4   4   4   5   5   5   31
April       5   5   4   4   4   4   4   30
May         4   4   5   5   5   4   4   31
June        4   4   4   4   4   5   5   30
July        5   5   5   4   4   4   4   31
August      4   4   4   5   5   5   4   31
September   5   4   4   4   4   4   5   30
October     4   5   5   5   4   4   4   31
November    4   4   4   4   5   5   4   30
December    5   5   4   4   4   4   5   31
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
user2040295
  • 107
  • 1
  • 9
  • 3
    Hint: you need DATEPART and multiple SUM(CASE WHEN ) – dnoeth Sep 17 '13 at 10:51
  • Which database are you really using? – Gordon Linoff Sep 17 '13 at 10:59
  • is there a chance you can explain how this query works basically i want to be also be able to use a date field from my database to find out what how many days work has been completed and then work out the difference of the current years calender against the data in the database. – user2040295 Sep 17 '13 at 19:46

1 Answers1

6
-- count weekdays in a year
declare @y int = 2013
declare @d datetime = dateadd(year, @y - 1900, 0)

;with cte
as
(
    select 1 a, 
      left(datename(weekday, @d), 3) b,
      datename(month, 0) Month,
      1 sort
    union all
    select a + 1 a, 
      left(datename(weekday, @d + a), 3) b, 
      datename(month, @d + a) Month, 
      datepart(month, @d + a) sort
    from cte where a < datepart(dayofyear, dateadd(year, 1, @d)-1)
)
select month, [Mon],[Tue],[Wed],[Thu],[Fri],[Sat],[Sun], 
     [Mon]+[Tue]+[Wed]+[Thu]+[Fri]+[Sat]+[Sun] [Sum] 
from cte
pivot (count(a) FOR [b] IN ([Mon],[Tue],[Wed],[Thu],[Fri],[Sat],[Sun],[Sum])) AS pvt
order by sort 
option (maxrecursion 366)
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92