Retention counts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Retention counts

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2007-12-04 : 11:46:27

I have members in a database who have paid thru dates. I am creating retention reports

I created a cross tab in Crystal (using SQL) that counts records that paid within a certain year. I need to create a script that will let me find when members skip payment for a year. Any ideas?

I was thinking of running a count of all paid (Activity) records, but still kind of stuck.

DZ

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 11:49:31

Without knowing anything at all about tables and so on, I would guess something like this

SELECT		t.*
FROM		(
			SELECT DISTINCT YEAR(DateTimeColumn) AS y
			FROM		Table1
		) AS q
LEFT JOIN	Table1 AS t ON YEAR(t.DateTimeColumn) = q.y
WHERE		t.DateTimeColumn IS NULL

E 12°55'05.25"
N 56°04'39.16"

dzabor
Posting Yak Master

138 Posts

Posted - 2007-12-04 : 12:14:04

Thanks for the quick reply Peso!!

This will not work. Let me provide more specific information.

There is only one table - Activity
This table has all of the payment activity for a member.

The field I am using for the retention is the Activity.Thru_Date. I am counting and totaling all members by their Activity.Thru_Date.

I hope this clarifies - sorry for the vagueness in the first posting.

DZ

sshelper
Posting Yak Master

216 Posts

Posted - 2007-12-04 : 12:27:41

Try this query:

SELECT MemberID
FROM Activity
GROUP BY MemberID
HAVING MAX(YEAR(Thru_Date)) - MIN(YEAR(Thru_Date)) + 1 != COUNT(*)

SQL Server Helper
http://www.sql-server-helper.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 12:32:45

[code]-- Prepare sample data
DECLARE @Sample TABLE (UserID INT, Payment DATETIME)

INSERT @Sample
SELECT 1, '20050101' UNION ALL
SELECT 1, '20060101' UNION ALL
SELECT 1, '20070101' UNION ALL
SELECT 2, '20050101' UNION ALL
SELECT 2, '20070101'

-- Show the expected output
SELECT u.UserID,
y.y AS PaymentYear
FROM (
SELECT DISTINCT YEAR(Payment) AS y
FROM @Sample
) AS y
CROSS JOIN (
SELECT DISTINCT UserID
FROM @Sample
) AS u
LEFT JOIN @Sample AS s ON YEAR(s.Payment) = y.y AND s.UserID = u.UserID
WHERE s.UserID IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 12:33:42

quote:
Originally posted by sshelper

SELECT MemberID
FROM Activity
GROUP BY MemberID
HAVING MAX(YEAR(Thru_Date)) - MIN(YEAR(Thru_Date)) + 1 != COUNT(*)

Hmmm...
What if MemberID has paid for 2005 and 2006, but not 2007?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 12:36:20

[code]-- Prepare sample data
DECLARE @Sample TABLE (UserID INT, Payment DATETIME)

INSERT @Sample
SELECT 1, '20050211' UNION ALL
SELECT 1, '20060115' UNION ALL
SELECT 1, '20070422' UNION ALL
SELECT 2, '20050102' UNION ALL
SELECT 2, '20070317' UNION ALL
SELECT 3, '20050201' UNION ALL
SELECT 3, '20060202'

-- Peso
SELECT u.UserID,
y.y AS PaymentYear
FROM (
SELECT DISTINCT YEAR(Payment) AS y
FROM @Sample
) AS y
CROSS JOIN (
SELECT DISTINCT UserID
FROM @Sample
) AS u
LEFT JOIN @Sample AS s ON YEAR(s.Payment) = y.y AND s.UserID = u.UserID
WHERE s.UserID IS NULL

-- sshelper
SELECT UserID
FROM @Sample
GROUP BY UserID
HAVING MAX(YEAR(Payment)) - MIN(YEAR(Payment)) + 1 <> COUNT(*)[/code]

E 12°55'05.25"
N 56°04'39.16"

dzabor
Posting Yak Master

138 Posts

Posted - 2007-12-04 : 12:42:56

That is exactly what I am trying to catch. I have all of the counts by year, but they want to know when a members skip years for retention purposes.

I will have something like this.

2005 2006 2007
RegMember Renewed 2000 2600 3300
RegMember Skipped 136 135 21

I am trying to figure our how to grab the skipped members (ones that did not pay for that year, but paid for the year before. What does the 3rd script you posted do?

DZ

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 12:45:20

I have absolutely no idea what your data represents!
Do you think I am psychic?

When you have learned to provide proper and accurate sample data together with expected output based on the provided sample data, I will return to help you.

E 12°55'05.25"
N 56°04'39.16"

dzabor
Posting Yak Master

138 Posts

Posted - 2007-12-04 : 12:59:05

You are right - this is too hard to explain, but the information you have provided has given me a clearer understanding of how I can make this work and what I need. To really simplify my question:

Is there a way to count years or assign a number to each year withing a field.
12/31/2005 returns 1
12/31/2006 returns 2
12/31/2007 returns 3
when 12/31/2008 come it automatically adds on and returns 4

I can even use 12/31/2005 as the start year if one is needed. If not I will try to see if there is another way of doing this directly thru Crystal without throwing in commands from SQL.

Thanks for your time.

DZ

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources