Hi ,
I am new to T-SQL , i need to to extract the date from calendar as per work day with below conditions :
- Week starts from Saturday
- 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 ;
1 Like
That was helpful , thanks for your support .