Advertisement
Guest User

Untitled

a guest
Jun 16th, 2019
72
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.84 KB | None | 0 0
  1. WITH data
  2. AS ( SELECT SYSDATE - (LEVEL - 1) this_year_date,
  3. TO_NUMBER (TO_CHAR (SYSDATE - (LEVEL - 1), 'D'))
  4. this_year_day_of_week,
  5. ADD_MONTHS (SYSDATE - (LEVEL - 1), -12) last_year_date,
  6. TO_NUMBER (
  7. TO_CHAR (ADD_MONTHS (SYSDATE - (LEVEL - 1), -12), 'D'))
  8. last_year_day_of_week
  9. FROM DUAL
  10. CONNECT BY LEVEL <= 300),
  11. crunching
  12. AS (SELECT data.*,
  13. (CASE
  14. WHEN this_year_day_of_week > last_year_day_of_week
  15. THEN
  16. this_year_day_of_week - last_year_day_of_week
  17. WHEN this_year_day_of_week = last_year_day_of_week
  18. THEN
  19. 0
  20. ELSE
  21. last_year_day_of_week - this_year_day_of_week
  22. END)
  23. math
  24. FROM data)
  25. SELECT TO_CHAR (crunching.this_year_date, 'yyyy-MM-dd Day') ty_date,
  26. math,
  27. (CASE
  28. WHEN math = 0
  29. THEN
  30. TO_CHAR (last_year_date, 'yyyy-MM-dd Day')
  31. WHEN math > 2
  32. THEN
  33. TO_CHAR ( (last_year_date - math) + 7, 'yyyy-MM-dd Day')
  34. ELSE
  35. TO_CHAR (last_year_date + math, 'yyyy-MM-dd Day')
  36. END)
  37. final_answer
  38. FROM crunching
  39.  
  40. 2016-03-17 Thursday 2 2015-03-19 Thursday
  41. 2016-03-16 Wednesday 2 2015-03-18 Wednesday
  42. 2016-03-15 Tuesday 2 2015-03-17 Tuesday
  43. 2016-03-14 Monday 5 2015-03-16 Monday
  44. 2016-03-13 Sunday 5 2015-03-15 Sunday
  45. 2016-03-12 Saturday 2 2015-03-14 Saturday
  46. 2016-03-11 Friday 2 2015-03-13 Friday
  47. 2016-03-10 Thursday 2 2015-03-12 Thursday
  48. 2016-03-09 Wednesday 2 2015-03-11 Wednesday
  49. 2016-03-08 Tuesday 2 2015-03-10 Tuesday
  50. 2016-03-07 Monday 5 2015-03-09 Monday
  51. 2016-03-06 Sunday 5 2015-03-08 Sunday
  52. 2016-03-05 Saturday 2 2015-03-07 Saturday
  53. 2016-03-04 Friday 2 2015-03-06 Friday
  54. 2016-03-03 Thursday 2 2015-03-05 Thursday
  55. 2016-03-02 Wednesday 2 2015-03-04 Wednesday
  56. 2016-03-01 Tuesday 2 2015-03-03 Tuesday
  57. 2016-02-29 Monday 5 2015-03-02 Monday
  58. 2016-02-28 Sunday 6 2015-03-01 Sunday
  59. 2016-02-27 Saturday 1 2015-02-28 Saturday
  60. 2016-02-26 Friday 1 2015-02-27 Friday
  61. 2016-02-25 Thursday 1 2015-02-26 Thursday
  62. 2016-02-24 Wednesday 1 2015-02-25 Wednesday
  63. 2016-02-23 Tuesday 1 2015-02-24 Tuesday
  64. 2016-02-22 Monday 1 2015-02-23 Monday
  65. 2016-02-21 Sunday 6 2015-02-22 Sunday
  66. 2016-02-20 Saturday 1 2015-02-21 Saturday
  67. 2016-02-19 Friday 1 2015-02-20 Friday
  68. 2016-02-18 Thursday 1 2015-02-19 Thursday
  69. 2016-02-17 Wednesday 1 2015-02-18 Wednesday
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement