Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Number Date WW
- 392 2012-07-23 30
- 439 2012-07-24 30
- 735 2012-07-25 30
- 882 2012-07-26 30 *
- 193 2012-07-30 31
- 412 2012-07-31 31
- 425 2012-08-01 31
- 748 2012-08-02 31
- 711 2012-08-03 31 *
- 757 2012-08-07 32
- 113 2012-08-08 32 *
- 444 2012-08-15 33 *
- 882 30
- 711 31
- 113 32
- 444 33
- select t1.number, t2.ww
- from yourtable t1
- inner join
- (
- select max(date) mxdate, datepart(week, [Date]) ww
- from yourtable
- group by datepart(week, [Date])
- ) t2
- on t1.date = t2.mxdate
- and datepart(week, t1.[Date]) = t2.ww;
- ;with cte as
- (
- select number, datepart(week, [Date]) ww,
- row_number() over(partition by datepart(week, [Date])
- order by date desc) rn
- from yourtable
- )
- select number, ww
- from cte
- where rn = 1
- declare @test table(Number int, Date datetime, WW int);
- INSERT INTO @test
- (Number, Date, WW)
- VALUES
- (392, '2012-07-22 17:00:00', 30),
- (439, '2012-07-23 17:00:00', 30),
- (735, '2012-07-24 17:00:00', 30),
- (882, '2012-07-25 17:00:00', 30),
- (193, '2012-07-29 17:00:00', 31),
- (412, '2012-07-30 17:00:00', 31),
- (425, '2012-07-31 17:00:00', 31),
- (748, '2012-08-01 17:00:00', 31),
- (711, '2012-08-05 17:00:00', 31),
- (757, '2012-08-06 17:00:00', 32),
- (113, '2012-08-07 17:00:00', 32),
- (444, '2012-08-14 17:00:00', 33)
- SELECT * FROM (
- select number, date, ww,
- row_number() over (partition by ww order by date desc) rn
- from @test) v
- WHERE rn = 1;
- SELECT number, [date]
- FROM tablename T
- WHERE NOT EXISTS (
- SELECT 1
- FROM tablename
- WHERE DATEPART(wk, [date]) = DATEPART(wk, T.[date])
- AND date > T.[date]
- )
Add Comment
Please, Sign In to add comment