## 2 replies

Last post Jul 30, 2016 10:15 AM by K_Pramod

Member

65 Points

323 Posts

### How to get Last 10 Working Dates

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 @i=@i+1
print @i
End

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

Thanks

skg

All-Star

47480 Points

9055 Posts

### Re: How to get Last 10 Working Dates

#### sureshtalla

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

Thanks,
A2H
My Blog | Linkedin | Dotnet Funda

Member

30 Points

5 Posts

### Re: How to get Last 10 Working Dates

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 )