CODEWITHSUNDEEP
sqlsql-server
msitrocker1990
msitrocker1990

Reputation: 3

Maximum Week and last 8 weeks in Date Column

I have a Table where time dimension column is in Weeks grain. Below is the sample data present

Date_Column
2022-07-30
2022-07-23
2022-07-16
2022-07-09
2022-07-02
2022-06-25
2022-06-18
2022-06-11
2022-06-04

I need to find only Max(Date-Column) and 4 more weeks(Date_Column) value using query. I am trying with DATEADD Function but its not giving desired result

my desired result will be

Date_Column
2022-07-30
2022-07-23
2022-07-16
2022-07-09
2022-07-02

I am trying with this SQL query

DATEADD(DAY,-7*5,DATE_COLUMN)

Upvotes: 0

Views: 169

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12959

you can use Row_number() in the descending order and select top 5 ranks.

DECLARE @table table(date_column date)

insert into @table values
('2022-07-30')
,('2022-07-23')
,('2022-07-16')
,('2022-07-09')
,('2022-07-02')
,('2022-06-25')
,('2022-06-18')
,('2022-06-11')
,('2022-06-04');

SELECT t.date_column 
from
(SELECT *,ROW_NUMBER() OVER(ORDER BY DATE_COLUMN DESC) AS Rnk_desc FROM @TABLE ) as t
WHERE t.Rnk_desc < = 5

-- Or

SELECT TOP 5 date_column 
FROM @TABLE 
order by date_column desc
date_column
2022-07-30
2022-07-23
2022-07-16
2022-07-09
2022-07-02

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

It seems that you want the past 4 weeks (28 days) inclusive from the latest date in the table

SELECT *
FROM yourTable
WHERE DATEADD(day, 28, DATE_COLUMN) >= (SELECT MAX(DATE_COLUMN) FROM yourTable);

Upvotes: 2

Related Questions