Get consolidated results with following tables

Question

I have a scenario. Here's my table structure is:

ID          LoginDate             RemovalDate
----------------------------------------
1           2009/08/01            NULL
2           2009/09/12            2010/01/02
3           2009/08/31            2009/10/29
4           2010/02/17            NULL
5           2009/10/18            2009/11/22

I want a consolidated results of how many ID's were not removed in a particular month. So the result set should be

Date         NotRemoved_ID
--------------------------
2009/08      2
2009/09      3
2009/10      3 [One ID got removed in 2009/10]
2010/02      2 [Two got removed in 2009/11 and 2010/01]

Please help.

Answer 1

I think you can do something like this:

Select LoginDate , dbo.fn_NotRemoved( LoginDate ) From YourTable

So you can write fn_NotRemoved based on your needs. However, the performance drawback of this way should be taken into account.

hope this helps

Answer 2

WITH a AS (SELECT CAST('20090801' AS datetime) LoginDate,
                  CAST(NULL AS datetime) RemovalDate
           UNION ALL SELECT '20090912', '20100102'
           UNION ALL SELECT '20090831', '20091029'
           UNION ALL SELECT '20100217', NULL
           UNION ALL SELECT '20091018', '20091122'),
b AS (SELECT LoginDate [Date], 1 [Amount] FROM a
      UNION ALL SELECT RemovalDate, -1 FROM a),
c AS (SELECT DATEADD(month, DATEDIFF(month, 0, [Date]), 0) [Month], [Amount]
      FROM b),
d AS (SELECT [Month], SUM([Amount]) [Amount] FROM c GROUP BY [Month]),
e AS (SELECT d.[Month], SUM(d2.[Amount]) [Amount]
      FROM d JOIN d d2 ON d2.[Month] <= d.[Month] GROUP BY d.[Month])
SELECT [Month], [Amount] FROM e

Answer 3

Far from pretty and probably much left to be optimized but this is tested and works with SQL Server.

Make sure to replace all references to @Table with you actual tabelname.

DECLARE @Table TABLE (ID INTEGER, LoginDate DATETIME, RemovalDate DATETIME)

INSERT INTO @Table
SELECT           1, '2009/08/01', NULL 
UNION ALL SELECT 2, '2009/09/12', '2010/01/02'
UNION ALL SELECT 3, '2009/08/31', '2009/10/29' 
UNION ALL SELECT 4, '2010/02/17', NULL 
UNION ALL SELECT 5, '2009/10/18', '2009/11/22' 

SELECT  CAST(ld.yr AS VARCHAR(4)) + '/' + RIGHT('0' + CAST(ld.mnth AS VARCHAR(2)), 2), ld.RunningTotal - ISNULL(rd.RunningTotal, 0)
FROM    (
          SELECT    yr, mnth, RunningTotal = MAX(RunningTotal)
          FROM      (
                      SELECT    yr = YEAR(t1.LoginDate), mnth = MONTH(t1.LoginDate), RunningTotal = COUNT(*)
                      FROM      @Table t1
                                CROSS JOIN @Table t2 
                      WHERE     t1.LoginDate >= t2.LoginDate
                      GROUP BY  t1.LoginDate
                    ) ld
          GROUP BY  ld.yr, ld.mnth
        ) ld
        LEFT OUTER JOIN (
          SELECT    yr, mnth, RunningTotal = MAX(RunningTotal)
          FROM      (
                      SELECT    yr = YEAR(t1.RemovalDate), mnth = MONTH(t1.RemovalDate), RunningTotal = COUNT(*)
                      FROM      @Table t1
                                CROSS JOIN @Table t2 
                      WHERE     t1.RemovalDate >= t2.RemovalDate
                      GROUP BY  t1.RemovalDate
                    ) ld
          GROUP BY  ld.yr, ld.mnth
        ) rd ON rd.yr <= ld.yr AND rd.mnth <= ld.mnth
ORDER BY  
        1, 2