asked
How to count active members in Time period on monthly basis
Hi Guys, I am looking to develop a code in SQL, since it is an urgent need, wanted to check this question with you all. I have a member table containing four main columns as CustId, EmpId,StartDate,EndDate. Now I have to break the given time range from Start and End date and provide the counts by breaking it in months. Member table: CustID MemberID BeginDT EndDT 1 a 12/1/2011 6/30/2012 1 b 1/1/2012 3/31/2012 1 c 1/1/2012 6/30/2012 2 a 11/1/2011 1/31/2012 2 b 1/1/2012 12/31/2012 2 c 5/1/2012 6/30/2012 Here is the quick query for table: CREATE TABLE Members(CustID INT Not Null ,MbrID varchar(4) ,STDate datetime Not Null ,EndDate datetime Not Null); INSERT INTO Members ( CustID,MbrID,STDate,EndDate) SELECT 1, 'a', '12/1/2011', '6/30/2012' UNION ALL SELECT 1, 'b', '1/1/2012' ,'3/31/2012' UNION ALL SELECT 1, 'c', '1/1/2012', '6/30/2012' UNION ALL SELECT 2, 'a', '11/1/2011', '1/31/2012' UNION ALL SELECT 2, 'b', '1/1/2012', '12/31/2012' UNION ALL SELECT 2, 'c', '5/1/2012', '6/30/2012'; And Final Result should be as: CustID Year Month Active_Member_Counts 1 2012 1 3 1 2012 2 3 1 2012 3 3 1 2012 4 2 1 2012 5 2 1 2012 6 2 1 2012 7 0 1 2012 8 0 1 2012 9 0 1 2012 10 0 1 2012 11 0 1 2012 12 0 2 2012 1 2 2 2012 2 1 2 2012 3 1 2 2012 4 1 2 2012 5 2 2 2012 6 2 2 2012 7 1 2 2012 8 1 2 2012 9 1 2 2012 10 1 2 2012 11 1 2 2012 12 1 can any one let me know what should be done to get the end result.How to break the given date range and counts n monthly basis. Would appreciate your help on this. Thanks,
If you have a date dimension or calendar table available, then you could join to that using the start and end dates and distinct count the member IDs. I borrowed the basic structure of this dimDate table from the AdventureWorksDW sample database. SELECT m.CustID , DimDate.CalendarYear , DimDate.MonthNumberOfYear , COUNT(DISTINCT MbrID) AS Active_Member_Counts FROM Members as m INNER JOIN DimDate ON DimDate.FullDateAlternateKey >= m.STDate AND DimDate.FullDateAlternateKey
15 People are following this question.
