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);
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 ...
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);
Member
90 Points
892 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.
All-Star
51271 Points
15123 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
Suwandi - Non Graduate Programmer
Member
90 Points
892 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
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 ...
All-Star
51271 Points
15123 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
Suwandi - Non Graduate Programmer
Member
90 Points
892 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
Perfect Solution