Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

rolling monthly analysis Expand / Collapse
Author
Message
Posted Thursday, August 2, 2012 11:09 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 12:57 PM
Points: 646, Visits: 3,779
Hi All,

I'd like to perform a rolling monthly analysis.

Let's say I have a sales table and if a client has sales during a particular month a record gets entered into the table with the month end date indicating sales activity during that month.

I want to determine when there is a new client and when a client is lost.

Logically I would use NOT IN logic to compare the second month against the first and then the first month against the second to determine the second months results (February in my sample data)

Then I would compare the third month(March) against the second( February). And so on...

If a client drops out and then comes back we can still consider this a 'NewClient'

The number of months is indeterminate.

Thanks if you can help.

CREATE TABLE #Sales
(
ClientID int,
SaleDate date
)

INSERT INTO #Sales (ClientID, SaleDate)VALUES (1, '20120131')
INSERT INTO #Sales (ClientID, SaleDate)VALUES (2, '20120131')
INSERT INTO #Sales (ClientID, SaleDate)VALUES (3, '20120131')
INSERT INTO #Sales (ClientID, SaleDate)VALUES (4, '20120131')

INSERT INTO #Sales (ClientID, SaleDate)VALUES (1, '20120229')
INSERT INTO #Sales (ClientID, SaleDate)VALUES (2, '20120229')
INSERT INTO #Sales (ClientID, SaleDate)VALUES (4, '20120229')
INSERT INTO #Sales (ClientID, SaleDate)VALUES (5, '20120229')

INSERT INTO #Sales (ClientID, SaleDate)VALUES (1, '20120331')
INSERT INTO #Sales (ClientID, SaleDate)VALUES (2, '20120331')
INSERT INTO #Sales (ClientID, SaleDate)VALUES (5, '20120331')
INSERT INTO #Sales (ClientID, SaleDate)VALUES (6, '20120331')

INSERT INTO #Sales (ClientID, SaleDate)VALUES (1, '20120430')
INSERT INTO #Sales (ClientID, SaleDate)VALUES (6, '20120430')
INSERT INTO #Sales (ClientID, SaleDate)VALUES (7, '20120430')

INSERT INTO #Sales (ClientID, SaleDate)VALUES (1, '20120531')
INSERT INTO #Sales (ClientID, SaleDate)VALUES (5, '20120531')
INSERT INTO #Sales (ClientID, SaleDate)VALUES (7, '20120531')

SELECT * FROM #Sales
DROP TABLE #Sales

--Expected Results

ClientAction,MonthYear,ClientID
----------------------
Newclient,February 2012,5
Lostclient,February 2012,3
Lostclient,March 2012,4
Newclient,March 2012,6
Lostclient,April 2012,2
Lostclient,April 2012,5
Newclient,April 2012,7
Lostclient,May 2012,6
Newclient,May 2012,5
Post #1339387
Posted Thursday, August 2, 2012 1:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:57 AM
Points: 141, Visits: 842
Hi,

I've solved a problem similar to this before, and I can't for the life of me figure out how I did it.
Maybe the code below will put you (or someone) on the right track...
Good luck!

;WITH Moo AS
(
SELECT ClientID_L = T1.ClientID
,SaleDate_L = T1.SaleDate
,ClientID_R = T2.ClientID
,SaleDate_R = T2.SaleDate
FROM #Sales T1
LEFT JOIN #Sales T2 ON T1.ClientID = T2.ClientID AND DATEDIFF(MONTH, T1.SaleDate, T2.SaleDate) = 1
),
Moo2 AS
(
SELECT ClientID_L
,SaleDate_L
,sStatus = CASE
WHEN ClientID_R IS NOT NULL THEN 'Start' ELSE 'End'
END
FROM Moo
)
SELECT *
FROM Moo2
ORDER BY ClientID_L, SaleDate_L

Post #1339491
Posted Thursday, August 2, 2012 1:41 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 7:16 AM
Points: 945, Visits: 1,769
Here using a full join where both sides are null we can get all our new or old clients and depending on where the nulls are can assign whether they were a new client or lost client.

DECLARE @StartMonth DATETIME = (SELECT DATEADD(MM,DATEDIFF(MM,0,MIN(SaleDate)) + 1,0) FROM #Sales)
DECLARE @EndMonth DATETIME = (SELECT DATEADD(MM,DATEDIFF(MM,0,MAX(SaleDate)),0) FROM #Sales)

SELECT CASE WHEN s2.ClientID IS NULL THEN 'NewClient' ELSE 'LostClient' END AS ClientAction,
RIGHT(CONVERT(VARCHAR, ISNULL(s1.SaleDate, DATEADD(M,1,s2.SaleDate)), 106), 8) AS SaleDate,
ISNULL(s1.ClientID, s2.ClientID) AS ClientID
FROM #Sales s1
FULL JOIN #Sales s2
ON s1.ClientID = s2.ClientID
AND MONTH(s1.SaleDate) = MONTH(s2.SaleDate) + 1
WHERE (s1.ClientID IS NULL
OR s2.ClientID IS NULL)
AND (s2.SaleDate < @EndMonth
OR s1.SaleDate > @StartMonth)
ORDER BY ISNULL(s1.SaleDate, DATEADD(M,1,s2.SaleDate)), ISNULL(s1.ClientID, s2.ClientID)

EDIT: Fixed code to eliminate the fact that all clients in january were new (no DEC 2011 DATA) and all clients in Jun were lost (no data for JUN 2012).

this should work when you add in more months as i take the max and min from the table.



For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1339498
Posted Thursday, August 2, 2012 2:20 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 23,000, Visits: 31,482
Something like this maybe?


CREATE TABLE #Sales
(
ClientID int,
SaleDate date
);

INSERT INTO #Sales (ClientID, SaleDate)VALUES (1, '20120131');
INSERT INTO #Sales (ClientID, SaleDate)VALUES (2, '20120131');
INSERT INTO #Sales (ClientID, SaleDate)VALUES (3, '20120131');
INSERT INTO #Sales (ClientID, SaleDate)VALUES (4, '20120131');

INSERT INTO #Sales (ClientID, SaleDate)VALUES (1, '20120229');
INSERT INTO #Sales (ClientID, SaleDate)VALUES (2, '20120229');
INSERT INTO #Sales (ClientID, SaleDate)VALUES (4, '20120229');
INSERT INTO #Sales (ClientID, SaleDate)VALUES (5, '20120229');

INSERT INTO #Sales (ClientID, SaleDate)VALUES (1, '20120331');
INSERT INTO #Sales (ClientID, SaleDate)VALUES (2, '20120331');
INSERT INTO #Sales (ClientID, SaleDate)VALUES (5, '20120331');
INSERT INTO #Sales (ClientID, SaleDate)VALUES (6, '20120331');

INSERT INTO #Sales (ClientID, SaleDate)VALUES (1, '20120430');
INSERT INTO #Sales (ClientID, SaleDate)VALUES (6, '20120430');
INSERT INTO #Sales (ClientID, SaleDate)VALUES (7, '20120430');

INSERT INTO #Sales (ClientID, SaleDate)VALUES (1, '20120531');
INSERT INTO #Sales (ClientID, SaleDate)VALUES (5, '20120531');
INSERT INTO #Sales (ClientID, SaleDate)VALUES (7, '20120531');

--SELECT * FROM #Sales;

declare @StartDate date = '20120101',
@EndDate date = '20120630';
WITH SalesActivity as (
SELECT
CASE WHEN s2.ClientID IS NULL
THEN 'NewClient'
ELSE 'LostClient'
END AS ClientAction,
RIGHT(CONVERT(VARCHAR, ISNULL(s1.SaleDate, DATEADD(M,1,s2.SaleDate)), 106), 8) AS SaleDate,
ISNULL(s1.ClientID, s2.ClientID) AS ClientID,
ISNULL(s1.SaleDate, DATEADD(M,1,s2.SaleDate)) SalesDate
FROM
#Sales s1
FULL JOIN #Sales s2
ON s1.ClientID = s2.ClientID
AND MONTH(s1.SaleDate) = MONTH(s2.SaleDate) + 1
WHERE
s1.ClientID IS NULL
OR s2.ClientID IS NULL
)
SELECT
ClientAction,
SaleDate,
ClientID
FROM
SalesActivity
WHERE
SalesDate between dateadd(mm,1,@StartDate) and dateadd(mm,datediff(mm,0,@EndDate),-1)
ORDER BY
SalesDate,
ClientID;
GO

DROP TABLE #Sales
go





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1339514
Posted Thursday, August 2, 2012 2:21 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 23,000, Visits: 31,482
That's what I get for not checking first.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1339515
Posted Thursday, August 2, 2012 2:25 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 7:16 AM
Points: 945, Visits: 1,769
Lynn Pettis (8/2/2012)
That's what I get for not checking first.


LOL

i posted what i had figuring on working on it and actually expecting some one to beat me to it as i was getting a little busy with other stuff.



For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1339517
Posted Thursday, August 2, 2012 2:28 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 23,000, Visits: 31,482
capn.hector (8/2/2012)
Lynn Pettis (8/2/2012)
That's what I get for not checking first.


LOL

i posted what i had figuring on working on it and actually expecting some one to beat me to it as i was getting a little busy with other stuff.


I got interupted as well.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1339518
Posted Thursday, August 2, 2012 6:14 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 12:57 PM
Points: 646, Visits: 3,779
I'm fascinated.....I am working with Lynn's code.

Say I wanted certain clients those whose ClientID was < than 3.

Clearly I can add any conditions to the final SELECT statement but performance would suffer if I had a multitude of clients.

Can I filter on the CTE?

I tried the following but did not get the expected results.

WHERE
(s1.ClientID IS NULL OR s1.ClientID < 3)
OR
(s2.ClientID IS NULL OR s2.ClientID < 3)

I appreciate the input. Obviously these are not real tables and data, just an abstraction of my real problem. Sometimes I over simplify things and don't anticipate all my needs. Bust since I am not exposing real data or data structure I get to keep my job!

I am also wondering if I should solve each problem (new and lost) separately and union the results...
Post #1339585
Posted Friday, August 3, 2012 7:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
I hope you know that tables need keys and were just sloppy. We now have row constructors in T-SQL, so insertion is easier. Quick clean up:

CREATE TABLE Sales
(client_id INTEGER NOT NULL,
sale_month CHAR(10) NOT NULL,
PRIMARY KEY (client_id, sale_month));

INSERT INTO Sales (client_id, sale_month)
VALUES (1,2012-01-00'), (2,2012-01-00'), (3,2012-01-00'), (4,2012-01-00'),
(1, '2012-02-00'), (2, '2012-02-00'), (4, '2012-02-00'), (5, '2012-02-00'),
(1, '2012-03-00'), (2, '2012-03-00'), (5, '2012-03-00'), (6, '2012-03-00'),
(1, '2012-04-00'), (6, '2012-04-00'), (7, '2012-04-00'),
(1, '2012-05-00'), (5, '2012-05-00'), (7, '2012-05-00');

You use a date to represent what ANSI would call an INTERVAL temporal type, which T-SQL still lacks. We can do this with an axillary table that gives a name to an interval which is shown as a pair of (start_time, end_time). I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL.

CREATE TABLE Month_Periods
(current_month_name CHAR(10) NOT NULL PRIMARY KEY,
prior_month_name CHAR(10) NOT NULL,
next_month_name CHAR(10) NOT NULL,
month_start_date DATE NOT NULL,
month_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (month_start_date <= month_end_date));

The prior and next month names could be done with LEAD() and LAG() functions, but this is complicated enough.

INSERT INTO Month_Periods
VALUES ('2012-01-00', '2011-12-00', '2012-02-00',
'2012-01-01', '2012-01-31');

Here is the basic query:

SELECT P.current_month_name,
S1.client_id,
CASE WHEN (S1.client_id IS NOT NULL
AND S2.client_id IS NOT NULL)
THEN 'cont'
WHEN (S1.client_id IS NOT NULL
AND S2.client_id IS NULL)
THEN 'new'
WHEN (S1.client_id IS NULL
AND S2.client_id IS NOT NULL)
THEN 'lost'
ELSE 'not possible' END
AS client_status
FROM (Month_Periods AS P
LEFT OUTER JOIN
Sales AS S1
ON S1.current_month_name
= P.current_month_name)
LEFT OUTER JOIN
Sales AS S2
ON S2.current_month_name
= P.prior_month_name
WHERE S1.client_id = S2.client_id;

The idea is to join a calendar to the current month sales (if any) and the prior month sales (if any) for each client. I did not bother to do the counts since it seems obvious.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1339838
Posted Friday, August 3, 2012 7:58 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 7:16 AM
Points: 945, Visits: 1,769
Chrissy321 (8/2/2012)
I'm fascinated.....I am working with Lynn's code.

Say I wanted certain clients those whose ClientID was < than 3.

Clearly I can add any conditions to the final SELECT statement but performance would suffer if I had a multitude of clients.

Can I filter on the CTE?

I tried the following but did not get the expected results.

WHERE
(s1.ClientID IS NULL OR s1.ClientID < 3)
OR
(s2.ClientID IS NULL OR s2.ClientID < 3)

I appreciate the input. Obviously these are not real tables and data, just an abstraction of my real problem. Sometimes I over simplify things and don't anticipate all my needs. Bust since I am not exposing real data or data structure I get to keep my job!

I am also wondering if I should solve each problem (new and lost) separately and union the results...


Lynn took my code and wrapped it so that Jan 2012 would not have 3 "New" clients and Jun 2012 would not have every client "Lost" i was able to fix my code before lynn posted his reply.

i am currently working on a test bed as i do believe my code will be more performant as i only have the single select but as i may get busy at work it may wait till monday.



For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1339847
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse