August 18, 2009 at 12:20 pm
I am trying to compare 2 months of data in SQL from the same table. It compares the data to see if members where in the previous month or not. If they were not...they are considered as an "Add". I was doing this manually comparing month to month but I want to automate this process where it will give the number of Adds for a specific time frame for each month. So far I have this and now I am stuck....any advice?
Declare @ThisMonth datetime
,@lastMonth datetime
SET @ThisMonth = '2009-08-01'
SET @lastMonth = '2009-07-01'
Select ThisMonth.EligMonth as Add_Month
,Count(thisMonth.Membid) as Adds
FROM where LastMonth.MEMBID is null
(select MEMBID, EligMonth
from dbo.Membership_Final
where EligMonth = @ThisMonth
group by MEMBID, EligMonth) as ThisMonth
left join
(select MEMBID, EligMonth
from dbo.Membership_Final
where EligMonth = @lastMonth
group by MEMBID, EligMonth) as LastMonth
August 18, 2009 at 12:29 pm
table structure and sample data would really help, but you could probably do something like this:
DECLARE @startDate SMALLDATETIME
DECLARE @endDate SMALLDATETIME
SET @startDate = '2009-01-01'
SET @endDate = '2009-12-01'
SELECT
m1.MEMBID,
(
CASE
WHEN m2.MEMBID IS NULL OR DATEDIFF(mm, m2.EligMonth, m1.EligMonth) = 1 THEN 'ADD'
ELSE 'DON''T ADD'
END
) AS [Status]
FROM [SomeTable] m1
JOIN [SomeTable] m2 ON m1.MEMBID = m2.MEMBID AND m1.[IdentityField] = m2.[IdentityField]
this is of course dependant upon you having some identity field in the table to differentiate the records. if you don't, then you'll have to use the ROW_NUMBER() OVER (PARTITION BY ORDER BY) in order to create a pseudo-identifier field to use.
*edited to fix some code problems
August 18, 2009 at 12:39 pm
Why can't you just test their membership date against the first day of this month?
declare @sample table (member int, memberDate datetime)
insert into @sample
select 1, '7/9/2009' union all
select 2, '7/19/2009' union all
select 3, '8/1/2009' union all
select 4, '8/11/2009'
--------------------------------------------------------------
declare @thisMonth datetime
set @thisMonth = dateadd(mm,datediff(mm,0,getdate()),0)
select @thisMonth as [@thisMonth]
select 'Add' as Action,* from @sample
where memberDate >= @thisMonth
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 18, 2009 at 12:41 pm
if i understood his requirement correctly, what he wants to do is find all the members who didn't have a membership the month before their current one.
August 18, 2009 at 12:48 pm
If a member can appear multiple times with multiple eligibility dates, and you are ONLY concerned about the current month and the previous month, then
declare @sample table (member int, eligibleDate datetime)
insert into @sample
select 1, '7/9/2009' union all
select 2, '7/19/2009' union all
select 2, '8/19/2009' union all -- 2,3, and 4 are current month but only 2 was last month
select 3, '8/1/2009' union all
select 4, '8/11/2009'
declare @thisMonth datetime, @lastMonth datetime, @nextMonth datetime
set @thisMonth = dateadd(mm,datediff(mm,0,getdate()),0)
set @lastMonth = dateadd(mm,-1,@thisMonth)
select @thisMonth as [@thisMonth]
------------------------------------------------------
select *
from @sample s1
where eligibleDate >= @thisMonth
and member not in (select member from @sample where eligibleDate >= @lastmonth and eligibleDate < @thisMonth)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 18, 2009 at 12:52 pm
You are right of course that it would be nice to have some sample data from the author of the question. For example, we don't know if a member might appear multiple times in the same month with differing eligibility dates. If so, a SELECT DISTINCT or two is in order.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 18, 2009 at 1:07 pm
All, here is a sample data set. So basically I am looking for members who are in the new months table and not in the previous months. They would be considered an "Add". In this case i would have 2 Adds for 2009-08-01
Previous Months:
EligMonth MEMBID
2009-07-01 899931100
2009-07-01 899931700
2009-07-01 899931000
ELIGMONTH MEMBID
2009-08-01 899931100
2009-08-01 899931600
2009-08-01 899931500
August 18, 2009 at 1:21 pm
With the column changes in place, this works.
declare @sample table (eligMonth dateTime, membID int)
insert into @sample
select '2009-07-01', 899931100 union all
select '2009-07-01', 899931700 union all
select '2009-07-01', 899931000 union all
select '2009-08-01', 899931100 union all
select '2009-08-01', 899931600 union all
select '2009-08-01', 899931500
declare @thisMonth datetime, @lastMonth datetime, @nextMonth datetime
set @thisMonth = dateadd(mm,datediff(mm,0,getdate()),0)
set @lastMonth = dateadd(mm,-1,@thisMonth)
select @thisMonth as [@thisMonth]
select *
from @sample s1
where eligMonth >= @thisMonth
and membID not in (select membID from @sample where eligMonth >= @lastmonth and eligMonth < @thisMonth)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 18, 2009 at 1:27 pm
Without sample data it's really hard to know, but it sounds like you don't really need to compare months, you just want members who were added this month. There is a much simpler way to get this info.
Declare @ThisMonth datetime
,@lastMonth datetime
SET @ThisMonth = '2009-08-01'
SET @lastMonth = '2009-07-01'
SELECT Add_Month, Count(MembID) AS Adds
FROM (
select MEMBID, Min(EligMonth) As Add_Month
from dbo.Membership_Final
where EligMonth >= @LastMonth -- This may not be necessary
group by MEMBID
HAVING Min(EligMonth) >= @ThisMonth
) AS Recent_Adds
GROUP BY Add_Month
If you look at this as a property of the Member (minimum EligMonth) rather than comparing two different months, the solution is much easier to spot.
Drew
Edited to correct column name and add a GROUP BY on the outer query.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 18, 2009 at 1:48 pm
Thank You Guys...much appreciated!!
August 18, 2009 at 4:16 pm
The way I interpreted this thread was that what was needed was a count of new members that were not members the previous month. Have changed the MemberID's to smaller integers for testing so what are everyones thoughts on this?
DECLARE @Table TABLE(EligMonth DATETIME, MemberID INT)
INSERT INTO @Table VALUES('2009-06-01', 1)
INSERT INTO @Table VALUES('2009-06-01', 2)
INSERT INTO @Table VALUES('2009-06-01', 3)
INSERT INTO @Table VALUES('2009-07-01', 1)
INSERT INTO @Table VALUES('2009-07-01', 4)
INSERT INTO @Table VALUES('2009-07-01', 6)
INSERT INTO @Table VALUES('2009-07-01', 10)
INSERT INTO @Table VALUES('2009-08-01', 6)
INSERT INTO @Table VALUES('2009-08-01', 1)
INSERT INTO @Table VALUES('2009-08-01', 7)
INSERT INTO @Table VALUES('2009-08-01', 9)
; WITH myCTE(EligMonth, MemberID, SomeField) AS
(
SELECT
T.EligMonth,
T.MemberID,
CASE WHEN T.MemberID IN(SELECT MemberID FROM @Table WHERE EligMonth = DATEADD(M,-1,T.EligMonth)) THEN 0
-- You can remove the below if you want to, to show members never in the table
WHEN T.EligMonth = (SELECT MIN(EligMonth) FROM @Table) THEN 0
ELSE 1
END
FROM @Table T
)
SELECT
EligMonth,
SUM(SomeField) AS NewMembersThisMonth
FROM myCTE
GROUP BY EligMonth
Returns:
EligMonth|NewMembersThisMonth
2009-06-01 00:00:00.000|0
2009-07-01 00:00:00.000|3
2009-08-01 00:00:00.000|2
Actual Data within the CTE:
EligMonth|MemberID|SomeField
2009-06-01 00:00:00.000|1|0
2009-06-01 00:00:00.000|2|0
2009-06-01 00:00:00.000|3|0
2009-07-01 00:00:00.000|1|0
2009-07-01 00:00:00.000|4|1
2009-07-01 00:00:00.000|6|1
2009-07-01 00:00:00.000|10|1
2009-08-01 00:00:00.000|6|0
2009-08-01 00:00:00.000|1|0
2009-08-01 00:00:00.000|7|1
2009-08-01 00:00:00.000|9|1
You can see from june to july the new members were 4, 6 and 10
from july to august the new members are 7 and 9.
The CTE is obviously not needed, used for testing and I was thinking - if the member needed to be added specifically to another table the CTE woud provide the [SomeField] Column to help with the DML.
Dave
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply