Guest User

Untitled

a guest
Jan 16th, 2019
85
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.69 KB | None | 0 0
  1. Number Date WW
  2. 392 2012-07-23 30
  3. 439 2012-07-24 30
  4. 735 2012-07-25 30
  5. 882 2012-07-26 30 *
  6. 193 2012-07-30 31
  7. 412 2012-07-31 31
  8. 425 2012-08-01 31
  9. 748 2012-08-02 31
  10. 711 2012-08-03 31 *
  11. 757 2012-08-07 32
  12. 113 2012-08-08 32 *
  13. 444 2012-08-15 33 *
  14.  
  15. 882 30
  16. 711 31
  17. 113 32
  18. 444 33
  19.  
  20. select t1.number, t2.ww
  21. from yourtable t1
  22. inner join
  23. (
  24. select max(date) mxdate, datepart(week, [Date]) ww
  25. from yourtable
  26. group by datepart(week, [Date])
  27. ) t2
  28. on t1.date = t2.mxdate
  29. and datepart(week, t1.[Date]) = t2.ww;
  30.  
  31. ;with cte as
  32. (
  33. select number, datepart(week, [Date]) ww,
  34. row_number() over(partition by datepart(week, [Date])
  35. order by date desc) rn
  36. from yourtable
  37. )
  38. select number, ww
  39. from cte
  40. where rn = 1
  41.  
  42. declare @test table(Number int, Date datetime, WW int);
  43.  
  44. INSERT INTO @test
  45. (Number, Date, WW)
  46. VALUES
  47. (392, '2012-07-22 17:00:00', 30),
  48. (439, '2012-07-23 17:00:00', 30),
  49. (735, '2012-07-24 17:00:00', 30),
  50. (882, '2012-07-25 17:00:00', 30),
  51. (193, '2012-07-29 17:00:00', 31),
  52. (412, '2012-07-30 17:00:00', 31),
  53. (425, '2012-07-31 17:00:00', 31),
  54. (748, '2012-08-01 17:00:00', 31),
  55. (711, '2012-08-05 17:00:00', 31),
  56. (757, '2012-08-06 17:00:00', 32),
  57. (113, '2012-08-07 17:00:00', 32),
  58. (444, '2012-08-14 17:00:00', 33)
  59.  
  60. SELECT * FROM (
  61. select number, date, ww,
  62. row_number() over (partition by ww order by date desc) rn
  63. from @test) v
  64. WHERE rn = 1;
  65.  
  66. SELECT number, [date]
  67. FROM tablename T
  68. WHERE NOT EXISTS (
  69. SELECT 1
  70. FROM tablename
  71. WHERE DATEPART(wk, [date]) = DATEPART(wk, T.[date])
  72. AND date > T.[date]
  73. )
Add Comment
Please, Sign In to add comment