/* 
    Author:		    adamrob https://adamrob.co.uk / https:adamrob.me
    Create date:    25OCT2022
    Description:	Exlude weekends from any datetime query.
    @since          25OCT2022    adamrob    Original

    Exlude weekends from any datetime query.
    This solution is tolerant to any localisation settings on target server.
    
    1. Work out the difference in days between the two dates. +1 to be inclusive of the first date.
    2. Work out the difference in weeks between the two dates. *2 for the two weekend days.
    3. Check for an edge case where if the first date is a sunday it is counted.
    4. Check for an edge case where if the last date is a saturday it is missed.
    
    Stack overflow answer: https://stackoverflow.com/a/74192360/13842575
*/

DECLARE @StartQuery DATETIME = '10/01/2022';
DECLARE @EndQuery DATETIME = '10/31/2022';

--Work out total days between start and end, exluding weekends.
SELECT (datediff(dd, @StartQuery, @EndQuery)+1) - (datediff(wk, @StartQuery, dateadd(dd,1,@EndQuery)) * 2) 
    - CASE WHEN datename(WEEKDAY, @StartQuery) = 'Sunday' THEN 1 ELSE 0 END -- This includes for start date edge case
    + CASE WHEN DATEname(WEEKDAY, @EndQuery) = 'Saturday'  THEN 1 ELSE 0 END -- This includes for  end date edge case.