Calculate days to be excluded based on a given date-CodePudding

I want to calculate days to be excluded where the weekday of given date is on Friday, Saturday & Sunday.

I am using below query to calculate days to be excluded based on weekday for a given date.

SELECT 2 * ((DATEPART(WEEKDAY, '2021-10-29')   5 - 2) / 5) - DATEPART(WEEKDAY, '2021-10-29') / 7
SELECT 2 * ((DATEPART(WEEKDAY, '2021-10-30')   5 - 2) / 5) - DATEPART(WEEKDAY, '2021-10-30') / 7
SELECT 2 * ((DATEPART(WEEKDAY, '2021-10-31')   5 - 2) / 5) - DATEPART(WEEKDAY, '2021-10-31') / 7

Actual result should be 2,1,0 respectively for the above. But I am getting 2,3,0. Can anyone suggest how to get it

Thank you

CodePudding user response：

one way can be to directly check and for Friday, Saturday and Sunday and assign values:

    select case when DATEPART(WEEKDAY, '2021-10-29') = 6 then 2 
            when DATEPART(WEEKDAY, '2021-10-29') = 7 then 1 
            when DATEPART(WEEKDAY, '2021-10-29') = 1 then 0 
            else 0 end as days_to_Excluede

but if this is not your requirement then you can wait for some other answers

CodePudding user response：

If I understand correctly, you want to count the days till Sunday? Correct me if I'm wrong. If that's the case I think this will do, otherwise could you explain a bit more:

SELECT 7 - CASE WHEN DATEPART(WEEKDAY, '2021-10-29') = 1 THEN 7 ELSE DATEPART(WEEKDAY, '2021-10-29') - 1 END
SELECT 7 - CASE WHEN DATEPART(WEEKDAY, '2021-10-30') = 1 THEN 7 ELSE DATEPART(WEEKDAY, '2021-10-30') - 1 END
SELECT 7 - CASE WHEN DATEPART(WEEKDAY, '2021-10-31') = 1 THEN 7 ELSE DATEPART(WEEKDAY, '2021-10-31') - 1 END