Calculate all “Sundays,Mondays…Saturdays” between

Question

I want to calculate total say Sundays,Mondays...Saturdays between two days. I want a do it in a select query because that is mandatory according to the situation where I'm currently working.

I have some working code where I'm able to calculate all sundays but not working in case of mondays

DECLARE  @StartDate DATE = '2011-10-01',
         @EndDate   DATE = '2011-10-31' 

SELECT DayCount = count(* )
FROM   (SELECT TOP ( datediff(DAY,@StartDate,@EndDate) + 1 )
                        [Date] = dateadd(DAY,ROW_NUMBER()
                  OVER(ORDER BY c1.name),
                  DATEADD(DD,-1,@StartDate))
        FROM   [master].[dbo].[spt_values] c1 ) x
WHERE  datepart(dw,[Date]) = 1;

Answer 1

SELECT  dATE , COUNT(dATE)   FROM   (
                SELECT TOP (datediff(DAY,@StartDate,@EndDate) + 1 ) [Date] = DATENAME(dw , dateadd(DAY,ROW_NUMBER() OVER(ORDER BY c1.name),
                            DATEADD(DD,-1,@StartDate)))         
                            FROM   [master].[dbo].[spt_values] c1 ) X
                            GROUP BY Date

Answer 2

I think your query delivers the correct result but could be simplified a bit.

It is however dependent on SET DATEFIRST setting.

datepart(dw,[Date]) = 1 will count the number of Mondays if SET DATEFIRST is 1.

Try this:

set datefirst 7 -- Sunday
select datepart(dw, '20111227')
set datefirst 1 -- Monday
select datepart(dw, '20111227')

Result:

-----------
3

-----------
2

Update: Another query that does the same.

select count(*) as Daycount
from master..spt_values as Number
where Number.type = 'P' and
      dateadd(day, Number.number, @StartDate) <= @EndDate and
      datepart(dw, dateadd(day, Number.number, @StartDate)) = 1

Answer 3

select
SUM(CASE WHEN datepart(dw, dateadd(day, Number.number, @fromDate)) = 1 THEN 1 ELSE 0 END) Sunday,
SUM(CASE WHEN datepart(dw, dateadd(day, Number.number, @fromDate)) = 2 THEN 1 ELSE 0 END) Monday,
SUM(CASE WHEN datepart(dw, dateadd(day, Number.number, @fromDate)) = 3 THEN 1 ELSE 0 END) Tuesday,
SUM(CASE WHEN datepart(dw, dateadd(day, Number.number, @fromDate)) = 4 THEN 1 ELSE 0 END) Wednesday,
SUM(CASE WHEN datepart(dw, dateadd(day, Number.number, @fromDate)) = 5 THEN 1 ELSE 0 END) Thursday,
SUM(CASE WHEN datepart(dw, dateadd(day, Number.number, @fromDate)) = 6 THEN 1 ELSE 0 END) Friday,
SUM(CASE WHEN datepart(dw, dateadd(day, Number.number, @fromDate)) = 7 THEN 1 ELSE 0 END) Saturday
from master..spt_values as Number
where Number.type = 'P' and dateadd(day, Number.number, @fromDate) <= @toDate

Answer 4

Calculating a simple number of particular days between two dates should NOT require a numbers table. Forcing I/O into a simple date calculation is not good practice.

You can do a simple calculation by figuring out the number of weeks between the two dates, and adjusting for where the desired day falls in relation to the end periods.

But I want to present another way to do it that actually allows you to specify any pattern of days you want to count. For example, it is just as easy to calculate the number of Saturdays and Sundays at once as it is to calculate the number of Mondays. I took pains to also make this query entirely independent of the SET DATEFIRST setting.

DECLARE
   @StartDate date = '2011-10-01',
   @EndDate date = '2011-10-31';

WITH A AS (SELECT DayCount = DateDiff(day, @StartDate, @EndDate) + 1),
B AS (
   SELECT
      DayCount,
      WeekCount = DayCount + 6 / 7,
      Dow = DateDiff(day, '18991230', @StartDate) % 7 FROM A
)
SELECT
   MondayCount =
      Len(Replace(Substring(
         Replicate('0100000', WeekCount),
         Dow, DayCount
      ), '0', ''))
FROM B;

The trick is the string inside of the Replicate function. It encodes Monday by placing a 1 in the second position (starting with Sunday as the first day).

You may feel repelled by such a string-munching solution as this--but I have played with this before and expect that you will find it very difficult to improve on its speed with regular date math (especially the part about being able to calculate for multiple days at once).