locked
count no of days of each month in a year in sql RRS feed

  • Question

  • User1152553138 posted

    count no of days of each month in a year in sql and remove sundays and custum holiday from holiday table

    Monday, April 16, 2018 10:01 AM

All replies

  • User77042963 posted

    Have a customized calendar table and aggregate your data from there.

    Monday, April 16, 2018 1:46 PM
  • User1152553138 posted
    I just got the result with below query ...
    WITH mycte AS
    (
    SELECT CAST('2018-04-01' AS DATETIME) DateValue
    UNION ALL
    SELECT DateValue + 1
    FROM mycte
    WHERE DateValue + 1 < '2018-09-30'
    )

    SELECT DATENAME(MONTH,(a.DateValue)) as [Months] ,count( a.DateValue) as BalWorkingDays FROM mycte a Left Join Holidays b on a.DateValue=b.OffDate
    WHERE a.DateValue>GetDate() AND NOT (DATEPART(dw, a.DateValue)=1 or a.DateValue IN (Select Distinct OffDate from Holidays))
    Group By DATENAME(MONTH,(a.DateValue))
    OPTION (MAXRECURSION 0);

    Can anybody help me how to achieve the above query without using CTE , just plain select query.
    Tuesday, April 17, 2018 1:43 PM
  • User475983607 posted

    The CTE is just a list of days between two dates.  If you have a Holiday table and all you want is a count of holidays between two dates then use a BETWEEN.

    DECLARE @holidays INT
    SELECT @holidays = Count(b.OffDate)
    FROM Holidays b
    WHERE b.OffDate BETWEEN @startDate and @endDate

    If you want to know the total days between two dates then use DATEDIFF.

    DECLARE @totalDays INT
    SELECT @totalDays = DATEDIFF(d,@startDate, @endDate)
    SELECT @totalDays

    To get the number of Sundays

    DECLARE @sundays INT
    
    WITH mycte AS
    (
    SELECT CAST('2018-04-01' AS DATETIME) DateValue
    UNION ALL
    SELECT DateValue + 1
    FROM mycte 
    WHERE DateValue + 1 < '2018-09-30'
    )
    
    
    SELECT @sundays = Count(1) 
    FROM  mycte
    WHERE DATEPART(dw, DateValue) = 7
    OPTION (MAXRECURSION 0);
    SELECT @sundays

    From there it's simple math.

    DECLARE @workdays INT
    SET @workdays = @totalDays - @sundays - @holidays 
    SELECT @workdays

    Tuesday, April 17, 2018 2:47 PM
  • User1152553138 posted

    Thanks for the reply ...

    The below is my SQL query 

    WITH mycte AS
     (
       SELECT CAST('2018-04-01' AS DATETIME) DateValue
       UNION ALL
       SELECT  DateValue + 1
       FROM    mycte   
       WHERE   DateValue + 1 < '2018-08-31'
     )
    
    
    SELECT  
    DATENAME(MONTH,(a.DateValue)) as [Months] ,count( a.DateValue) as BalWorkingDays FROM mycte a Left Join HolidayTable b on a.DateValue=b.OffDate 
    WHERE a.DateValue>GetDate() AND NOT (DATEPART(dw, a.DateValue)=1 or a.DateValue IN (Select Distinct OffDate from HolidayTable)) 
    Group By DATENAME(MONTH,(a.DateValue))
    OPTION (MAXRECURSION 0)

    This is the output

    Months	BalWorkingDays
    April	8
    August	26
    July	26
    June	26
    May	26

    The above result is fine ... But what i need is, without using CTE in Sql query i would like to generate the above result

    Wednesday, April 18, 2018 3:47 AM