Wednesday, January 22, 2014

how many saturdays and sundays between two dates with sql server

declare @StartDate datetime,@EndDate datetime
set @StartDate='20140101'
set @EndDate='20140131'

                           
                            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)) in ( 1,7)



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


                                                                         
          -- mondays count with replication                                      
     DECLARE
   @StartDate date = '2014-01-01',
   @EndDate date = '2014-01-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;

No comments:

Post a Comment