Killer query, too muc for t-sql

Question

Killer query, too muc for t-sql

derrysql

Old Hand

Points: 308
More actions
May 11, 2010 at 11:03 am

#218794

Hi all,
I have a killer query which is cracking me up.
Imagine I have the simplified data set below. I need to go through a set of customer transactions and bolt on a new field that classifies them according to the following logic
The first trans is alway type C1
If there is a 2nd transaction and it is within 25 days of the first then this is classed as C2. If it occurs more than 25 days then class this as type C1 and start again.
If there is a third transaction and it occurs within 25 days of the second then class this as C3. If it occurs more than 25 days then class it as C1 and start again.
If there is a 4th transaction (and up) and it is within 25 days of the third then class this as 'OS' If less than 25 days then class as C1 and start again
Any ideas on how I can achieve this?
Thanks,
ds
WITH temptrans AS
(
SELECT 1 [RN], 1234 [Accno], '01/01/2010' [TranDate] UNION ALL -- c1
SELECT 2 [RN], 1234 [Accno], '15/01/2010' [TranDate] UNION ALL -- c2
SELECT 3 [RN], 1234 [Accno], '01/03/2010' [TranDate] UNION ALL -- c1
SELECT 4 [RN], 1234 [Accno], '15/04/2010' [TranDate] UNION ALL -- c1
SELECT 5 [RN], 1234 [Accno], '23/04/2010' [TranDate] UNION ALL -- c2
SELECT 6 [RN], 1234 [Accno], '27/04/2010' [TranDate] UNION ALL -- c3
SELECT 7 [RN], 1234 [Accno], '29/04/2010' [TranDate]
)
select * from temptrans

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please have a look at Jeff Moden's "Running Total" article[/url].

I think it's the best performing apporach. But you need to understand the requirements in order to get consistent results. Therefore, I didn't just post some sample code.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mike01 SSChampion Points: 11412 More actions · Answer 2

give this a try

declare @temptrans table (rn int, accno int, trandate smalldatetime,

classification char(3), runningtotal tinyint)

insert into @temptrans

SELECT 1 [RN], 1234 [Accno], '01/01/2010' [TranDate] , null, null UNION ALL -- c1

SELECT 2 [RN], 1234 [Accno], '01/15/2010' [TranDate] , null, null UNION ALL -- c2

SELECT 3 [RN], 1234 [Accno], '03/01/2010' [TranDate] , null, null UNION ALL -- c1

SELECT 4 [RN], 1234 [Accno], '04/15/2010' [TranDate] , null, null UNION ALL -- c1

SELECT 5 [RN], 1234 [Accno], '04/23/2010' [TranDate] , null, null UNION ALL -- c2

SELECT 6 [RN], 1234 [Accno], '04/27/2010' [TranDate] , null, null UNION ALL -- c3

SELECT 7 [RN], 1234 [Accno], '04/29/2010' [TranDate], null, null union all

SELECT 1 [RN], 3456 [Accno], '01/01/2010' [TranDate] , null, null UNION ALL -- c1

SELECT 2 [RN], 3456 [Accno], '01/15/2010' [TranDate] , null, null UNION ALL -- c2

SELECT 3 [RN], 3456 [Accno], '03/01/2010' [TranDate] , null, null UNION ALL -- c1

SELECT 1 [RN], 2222 [Accno], '04/15/2010' [TranDate] , null, null -- c1

;with cte as

(Select rn, accno, Trandate, classification, cast(1 as int) runningtotal, cast(null as smalldatetime) PrevDate

from @temptrans

where rn = 1

union all

Select t.rn, t.accno, t.Trandate, t.classification, case when DateDiff(dd, c.Trandate, t.trandate) > 25 then 1 else c.runningtotal + 1 end, c.TranDate PrevDate

from cte c join @tempTrans t

on t.accno = c.accno

and c.rn = t.rn - 1)

select rn, accno, TranDate, 'C' + cast(RunningTotal as varchar) Classification

from cte

order by accno, rn

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

LutzM SSC Guru Points: 107049 More actions · Answer 3

@mike-2:

the recursice CTE apporach is one possible solution. But as soon as there is a larger data volume to deal with, the quirky update is the better choice (from my point of view).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]