Get Date From From Calendar As Per Work Day

Hi ,

I am new to T-SQL , i need to to extract the date from calendar as per work day with below conditions :

  1. Week starts from Saturday
  2. There are 6 days in a week as Friday will not be counted .

Example :

Salesman-1 have a journey plan for Store-X on SATURDAY, MONDAY & WEDNESDAY , Whereas FRIDAY there will be no visit.

Workday's are : 1 , 3 , 5 , 7 , ...

Where : 1 = Saturday (Week Start) , 2 = Sunday , 3 = Monday , 4 = Tuesday ,
5 = Wednesday, 6 = Thursday , 7 = Saturday ( Friday will be skipped )

So the outcome of the query should be :
Saturday ( 07.Oct-2017 ) ,
Monday ( 09.Oct-2017 ) ,
Wednesday ( 11.Oct-2017 ) ,
Saturday ( 14.Oct-2017 )

Regards,
Adil

This returns the dates for as many days as you pass in:

CREATE FUNCTION dbo.itfDates(@Start date,@Days int) RETURNS table AS
RETURN (
       WITH l1 (n) AS (SELECT 0 n UNION ALL SELECT 0) 
          , l2 (n) AS (SELECT a.n FROM l1 a CROSS JOIN l1 b ) 
          , l3 (n) AS (SELECT a.n FROM l2 a CROSS JOIN l2 b ) 
          , l4 (n) AS (SELECT a.n FROM l3 a CROSS JOIN l3 b ) 
          , l5 (n) AS (SELECT a.n FROM l4 a CROSS JOIN l4 b ) 
          , l6 (n) AS (SELECT a.n FROM l5 a CROSS JOIN l5 b ) 
          , l7 (n) AS (SELECT a.n FROM l6 a CROSS JOIN l6 b ) 
          , l8 (n) AS (SELECT a.n FROM l7 a CROSS JOIN l7 b )
          , Nums (n) AS (SELECT Row_Number() OVER (ORDER BY n) FROM l8 UNION SELECT 0) 
        SELECT DateAdd(dd, Nums.n, @Start) [Date] FROM nums WHERE n < @Days
        );
GO

then to get the days without Fridays:

SELECT [Date], DateName(dw, [Date]) WeekDayName
FROM dbo.itfDates('20171012', 14)
WHERE DatePart(dw, [Date]) <> 6 ;

image

1 Like

That was helpful , thanks for your support .