CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2pivotdatepart
user2040295
user2040295

Reputation: 107

SQL Count No of Mondays tuesdays etc from year

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

Upvotes: 3

Views: 2254

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44316

-- 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)

Upvotes: 6

Related Questions