Skip Sunday and Other Holiday Detail in Sql and Count Bal days from Today of each Month [Answered]RSS

• • Reply
  Ashraf007

  Member

  102 Points

  952 Posts

  Skip Sunday and Other Holiday Detail in Sql and Count Bal days from Today of each Month

  Apr 09, 2018 08:04 AM|Ashraf007|LINK

  How to Skip Sunday and Other Holiday Detail In Sql and Count Bal days from Today ... I have separate custom Holiday Table

  I just tried, i am getting Working days and not able to get bal days from today of each month.

  WITH mycte AS
   (
     SELECT CAST('2018-04-01' AS DATETIME) DateValue
     UNION ALL
     SELECT  DateValue + 1
     FROM    mycte   
     WHERE   DateValue + 1 < '2018-05-31'
   )
  
   SELECT  DATENAME(MONTH,(a.DateValue)) as [Months]
   ,count( a.DateValue) as WorkingDays
  --,CAST( DATEDIFF(day,GETDATE(),count( a.DateValue) ) AS VARCHAR(10)) as BalDays 
   FROM    mycte a Left Join HolidayTable b on a.DateValue=b.OffDate WHERE ((DATEPART(dw, a.DateValue) + @@DATEFIRST) % 7) NOT IN (1) and a.DateValue Not In (Select Distinct OffDate from HolidayTable)
   Group By DATENAME(MONTH,(a.DateValue))
   OPTION (MAXRECURSION 0);

  Thanks In Advance ....
• • Reply
  oned_gk

  All-Star

  52262 Points

  15556 Posts

  Re: Skip Sunday and Other Holiday Detail in Sql and Count Bal days from Today of each Month

  Apr 09, 2018 08:39 AM|oned_gk|LINK

  WHERE ... AND NOT (DATEPART(dw, a.DateValue)=1 or a.DateValue IN (Select Distinct OffDate from HolidayTable)) 

  Programming to simplify, don't look for difficult way
  Suwandi - Non Graduate Programmer
• • Reply
  Ashraf007

  Member

  102 Points

  952 Posts

  Re: Skip Sunday and Other Holiday Detail in Sql and Count Bal days from Today of each Month

  Apr 09, 2018 08:54 AM|Ashraf007|LINK

  WITH mycte AS
   (
     SELECT CAST('2018-04-01' AS DATETIME) DateValue
     UNION ALL
     SELECT  DateValue + 1
     FROM    mycte   
     WHERE   DateValue + 1 < '2018-05-31'
   )
  
   SELECT  DATENAME(MONTH,(a.DateValue)) as [Months]
   ,count( a.DateValue) as WorkingDays
  --,CAST( DATEDIFF(day,GETDATE(),count( a.DateValue) ) AS VARCHAR(10)) as BalDays 
   FROM    mycte a Left Join HolidayTable b on a.DateValue=b.OffDate WHERE ((DATEPART(dw, a.DateValue) + @@DATEFIRST) % 7) NOT IN (1) and a.DateValue Not In (Select Distinct OffDate from HolidayTable)
   Group By DATENAME(MONTH,(a.DateValue))
   OPTION (MAXRECURSION 0);

  Months	WorkingDays
  April	22
  May	26

  The above is the output after skipping Sunday and custom holiday from holiday table.

  I want the remaining days of each month from Today (i.e.) GetDate() after skipping Sunday and custom Holiday ...

  Thanks In Advance ....
• • Reply
  oned_gk

  All-Star

  52262 Points

  15556 Posts

  Re: Skip Sunday and Other Holiday Detail in Sql and Count Bal days from Today of each Month

  Apr 09, 2018 02:33 PM|oned_gk|LINK

  WHERE a.DateValue>GetDate() AND NOT (DATEPART(dw, a.DateValue)=1 or a.DateValue IN (Select Distinct OffDate from HolidayTable)) 

  Programming to simplify, don't look for difficult way
  Suwandi - Non Graduate Programmer
• • Reply
  Ashraf007

  Member

  102 Points

  952 Posts

  Re: Skip Sunday and Other Holiday Detail in Sql and Count Bal days from Today of each Month

  Apr 10, 2018 03:25 AM|Ashraf007|LINK

  WITH mycte AS
   (
     SELECT CAST('2018-04-01' AS DATETIME) DateValue
     UNION ALL
     SELECT  DateValue + 1
     FROM    mycte   
     WHERE   DateValue + 1 < '2018-05-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);

  Perfect Solution

  Thanks In Advance ....