Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- +------------+----------+-------+
- | date | status | count |
- +------------+----------+-------+
- | 1-May-2019 | active | 0 |
- | 1-May-2019 | inactive | 0 |
- | 1-May-2019 | risky | 1 |
- | 2-May-2019 | active | 1 |
- | 2-May-2019 | inactive | 0 |
- | 2-May-2019 | risky | 1 |
- | 3-May-2019 | active | 1 |
- | 3-May-2019 | inactive | 0 |
- | 3-May-2019 | risky | 1 |
- | 4-May-2019 | active | 1 |
- | 4-May-2019 | inactive | 0 |
- | 4-May-2019 | risky | 1 |
- | 5-May-2019 | active | 3 |
- | 5-May-2019 | inactive | 0 |
- | 5-May-2019 | risky | 1 |
- | ... | ... | ... |
- +------------+----------+-------+
- --create date table (not sure if usable)
- CREATE TABLE #dates ([date] date)
- DECLARE @dIncr DATE = '2019-05-01'
- DECLARE @dEnd DATE = dateadd(day,-1,getdate())
- WHILE (@dIncr <= @dEnd)
- BEGIN
- INSERT INTO #dates ([date]) VALUES (@dIncr)
- SELECT @dIncr = DATEADD(day,1,@dIncr)
- END
- GO
- -- ID + Subscribed Date (starts active at joindate)
- create table #t1 (id int, [subdate] date)
- insert into #t1 values
- (9, '2019-01-01'),
- (1, '2019-05-02'),
- (2, '2019-05-05'),
- (3, '2019-05-05'),
- (4, '2019-05-10')
- GO
- -- ID + Latest activity date
- create table #t2 (id int, [status] varchar(max), [datestatus] date)
- insert into #t2 values
- (9,'risky', '2019-03-01'),
- (1, 'active', '2019-05-02'),
- (2, 'inactive', '2019-05-13'),
- (3, 'active', '2019-05-14'),
- (4, 'risky', '2019-05-15')
- GO
- -- ID + Activity Logs Date
- create table #t3 (id int, [statuschange] varchar(max), [datechange] date)
- insert into #t3 values
- (9,'inactive', '2019-01-01'),
- (9,'active', '2019-02-01'),
- (9,'risky', '2019-03-01'),
- (2, 'risky', '2019-05-08'),
- (2, 'inactive', '2019-05-13'),
- (3, 'inactive', '2019-05-08'),
- (3, 'active', '2019-05-14'),
- (4, 'inactive', '2019-05-15'),
- (4, 'risky', '2019-05-15')
- GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement