user3838498 user3838498 - 4 months ago 11
SQL Question

Count of weekdays in a given month

I was facing a problem on how to create a calculated column based on a column which contains dates. For example, I have a column which contains dates starting from July.

The DAY needs to be calculated as described in the picture using SQL server.

So basically can a column be created based on an existing column containing a lot of dates? I need this to be dynamic.

WeekDay Date Day
---------------------------
Friday 3-Jan-14 1
Monday 6-Jan-14 2
Tuesday 7-Jan-14 3
Wednesday 8-Jan-14 4
Thursday 9-Jan-14 5
Friday 10-Jan-14 6
Monday 13-Jan-14 7
Tuesday 14-Jan-14 8
Wednesday 15-Jan-14 9
Thursday 16-Jan-14 10
Friday 17-Jan-14 11
Monday 20-Jan-14 12
Tuesday 21-Jan-14 13
Wednesday 22-Jan-14 14
Thursday 23-Jan-14 15
Friday 24-Jan-14 16
Monday 27-Jan-14 17
Tuesday 28-Jan-14 18
Wednesday 29-Jan-14 19
Thursday 30-Jan-14 20
Friday 31-Jan-14 21

Answer

With table, DateTable with a column Date of type Date, the following query will do what you ask.

SELECT
    DATENAME(dw, Date) AS WeekDay
    ,Date
    ,ROW_NUMBER() OVER (ORDER BY Date) AS Day
FROM DateTable
WHERE DATEPART(dw, Date) NOT IN (1, 7)
ORDER BY Date
Comments