locked
How to get Last 10 Working Dates RRS feed

  • Question

  • User194385433 posted

    Hi,

    How to get Last 10 Working Dates based on Current Date (excludes saturday and Sundays)

    Is there any alternative

    DECLARE @i int
    set @i=1

    create table #tmp_dates(
    begindate datetime,
    daynumber int)

    while (@i<15)
    begin
    insert into #tmp_dates(begindate,daynumber)
    SELECT CAST(DATEADD(d,-@i,GETDATE()) AS DATE) AS Yesterday,DATEPART(dw,CAST(DATEADD(d,-@i,GETDATE()) AS DATE))
    select @i=@i+1
    print @i
    End

    select top(10) * from #tmp_dates where daynumber not in(1,7) order by 1 desc

    Thanks 

    Friday, July 29, 2016 3:13 PM

Answers

  • User2103319870 posted

    Hi,

    How to get Last 10 Working Dates based on Current Date (excludes saturday and Sundays)

    Thanks 

    You can try with the below code

    WITH
    	E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    	E2(N) AS (SELECT 1 FROM E1 a union all select 1 from E1 b), 
    	tablecte(N) AS 
    	(
    		SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
    	)
    , dateValues as
    (
    	SELECT *, DATEADD(DAY, 1 - N, GETDATE()) as NewDate
    	FROM tablecte
    	WHERE N <= 14
    )
    
    SELECT NewDate
    FROM dateValues
    --Get the date values which are not in 1 and 7 which is Sunday and Saturday
    WHERE DATEPART(WEEKDAY, NewDate) in (2, 3, 4, 5, 6)
    ORDER BY N DESC

    Reference URL

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 29, 2016 4:09 PM
  • User-157633207 posted

    Hi Sureshtalla,

    Try this query..

    select top 10 DATEADD(dd, -number, DateAdd(dd, DateDiff(dd, 0, Getdate()), 0)) dt 
    from master..spt_values where type='P' and DATEPART(WEEKDAY, DATEADD(dd, -number, DateAdd(dd, DateDiff(dd, 0, Getdate()), 0))) not in (1,7)
    DECLARE @i int;
    SET @i = 1;
    CREATE TABLE #tmp_dates
    ( 
            begindate datetime, 
    	daynumber int
    );
    
    WHILE(@i < 15)
    BEGIN
    	INSERT INTO #tmp_dates( begindate, daynumber )
    		   SELECT DATEADD(dd, -@i, DateAdd(dd, DateDiff(dd, 0, Getdate()), 0)), DATEPART(WEEKDAY, DATEADD(dd, -@i, DateAdd(dd, DateDiff(dd, 0, Getdate()), 0)));
    	SELECT @i = @i + 1;
    END;
    
    SELECT TOP (10) *
    FROM #tmp_dates
    WHERE daynumber NOT IN( 1, 7 )
    ORDER BY 1 DESC;
    
    DROP TABLE #tmp_dates

    Thanks..

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, July 30, 2016 10:15 AM

All replies

  • User2103319870 posted

    Hi,

    How to get Last 10 Working Dates based on Current Date (excludes saturday and Sundays)

    Thanks 

    You can try with the below code

    WITH
    	E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    	E2(N) AS (SELECT 1 FROM E1 a union all select 1 from E1 b), 
    	tablecte(N) AS 
    	(
    		SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
    	)
    , dateValues as
    (
    	SELECT *, DATEADD(DAY, 1 - N, GETDATE()) as NewDate
    	FROM tablecte
    	WHERE N <= 14
    )
    
    SELECT NewDate
    FROM dateValues
    --Get the date values which are not in 1 and 7 which is Sunday and Saturday
    WHERE DATEPART(WEEKDAY, NewDate) in (2, 3, 4, 5, 6)
    ORDER BY N DESC

    Reference URL

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 29, 2016 4:09 PM
  • User-157633207 posted

    Hi Sureshtalla,

    Try this query..

    select top 10 DATEADD(dd, -number, DateAdd(dd, DateDiff(dd, 0, Getdate()), 0)) dt 
    from master..spt_values where type='P' and DATEPART(WEEKDAY, DATEADD(dd, -number, DateAdd(dd, DateDiff(dd, 0, Getdate()), 0))) not in (1,7)
    DECLARE @i int;
    SET @i = 1;
    CREATE TABLE #tmp_dates
    ( 
            begindate datetime, 
    	daynumber int
    );
    
    WHILE(@i < 15)
    BEGIN
    	INSERT INTO #tmp_dates( begindate, daynumber )
    		   SELECT DATEADD(dd, -@i, DateAdd(dd, DateDiff(dd, 0, Getdate()), 0)), DATEPART(WEEKDAY, DATEADD(dd, -@i, DateAdd(dd, DateDiff(dd, 0, Getdate()), 0)));
    	SELECT @i = @i + 1;
    END;
    
    SELECT TOP (10) *
    FROM #tmp_dates
    WHERE daynumber NOT IN( 1, 7 )
    ORDER BY 1 DESC;
    
    DROP TABLE #tmp_dates

    Thanks..

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, July 30, 2016 10:15 AM