SHARE
TWEET

Untitled

a guest Jun 20th, 2019 55 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. +------------+----------+-------+
  2. |    date    |  status  | count |
  3. +------------+----------+-------+
  4. | 1-May-2019 | active   |     0 |
  5. | 1-May-2019 | inactive |     0 |
  6. | 1-May-2019 | risky    |     1 |
  7. | 2-May-2019 | active   |     1 |
  8. | 2-May-2019 | inactive |     0 |
  9. | 2-May-2019 | risky    |     1 |
  10. | 3-May-2019 | active   |     1 |
  11. | 3-May-2019 | inactive |     0 |
  12. | 3-May-2019 | risky    |     1 |
  13. | 4-May-2019 | active   |     1 |
  14. | 4-May-2019 | inactive |     0 |
  15. | 4-May-2019 | risky    |     1 |
  16. | 5-May-2019 | active   |     3 |
  17. | 5-May-2019 | inactive |     0 |
  18. | 5-May-2019 | risky    |     1 |
  19. | ...        | ...      |   ... |
  20. +------------+----------+-------+
  21.      
  22. --create date table (not sure if usable)
  23. CREATE TABLE #dates ([date] date)
  24. DECLARE @dIncr DATE = '2019-05-01'
  25. DECLARE @dEnd DATE = dateadd(day,-1,getdate())
  26. WHILE (@dIncr <= @dEnd)
  27. BEGIN
  28.   INSERT INTO #dates ([date]) VALUES (@dIncr)
  29.   SELECT @dIncr = DATEADD(day,1,@dIncr)
  30. END
  31. GO
  32.  
  33. -- ID + Subscribed Date (starts active at joindate)
  34. create table #t1 (id int, [subdate] date)
  35. insert into #t1 values
  36. (9, '2019-01-01'),
  37. (1, '2019-05-02'),
  38. (2, '2019-05-05'),
  39. (3, '2019-05-05'),
  40. (4, '2019-05-10')
  41. GO
  42.  
  43. -- ID + Latest activity date
  44. create table #t2 (id int, [status] varchar(max), [datestatus] date)
  45. insert into #t2 values
  46. (9,'risky', '2019-03-01'),
  47. (1, 'active', '2019-05-02'),
  48. (2, 'inactive', '2019-05-13'),
  49. (3, 'active', '2019-05-14'),
  50. (4, 'risky', '2019-05-15')
  51. GO
  52.  
  53. -- ID + Activity Logs Date
  54. create table #t3 (id int, [statuschange] varchar(max), [datechange] date)
  55. insert into #t3 values
  56. (9,'inactive', '2019-01-01'),
  57. (9,'active', '2019-02-01'),
  58. (9,'risky', '2019-03-01'),
  59. (2, 'risky', '2019-05-08'),
  60. (2, 'inactive', '2019-05-13'),
  61. (3, 'inactive', '2019-05-08'),
  62. (3, 'active', '2019-05-14'),
  63. (4, 'inactive', '2019-05-15'),
  64. (4, 'risky', '2019-05-15')
  65. GO
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top