Any pointers on how to do this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Any pointers on how to do this?

Author

Topic

opensourcederry
Starting Member

12 Posts

Posted - 2010-05-11 : 13:04:04

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

asgast
Posting Yak Master

149 Posts

Posted - 2010-05-12 : 08:00:52

I came up with the following script

I think that I got our logic correctly, but there a lot of place for optimization.

;WITH temptrans AS
(
SELECT 1 [RN], 1234 [Accno], '2010-01-01 00:00:00' [TranDate] UNION ALL -- c1
SELECT 2 [RN], 1234 [Accno], '2010-01-15 00:00:00' [TranDate] UNION ALL -- c2
SELECT 3 [RN], 1234 [Accno], '2010-03-01 00:00:00' [TranDate] UNION ALL -- c1
SELECT 4 [RN], 1234 [Accno], '2010-04-15 00:00:00' [TranDate] UNION ALL -- c1
SELECT 5 [RN], 1234 [Accno], '2010-04-23 00:00:00' [TranDate] UNION ALL -- c2
SELECT 6 [RN], 1234 [Accno], '2010-04-27 00:00:00' [TranDate] UNION ALL -- c3
SELECT 7 [RN], 1234 [Accno], '2010-04-29 00:00:00' [TranDate]

)
select *
INTO #temp
from temptrans

ALTER TABLE #temp add class char(2)
/*SEARCH for C1 */
UPDATE t set class ='C1'
FROM #temp t
LEFT JOIN #temp t2 on datediff(day, t2.trandate,t.trandate)<25 AND t.rn <> t2.rn
AND datediff(day, t2.trandate,t.trandate) >0 AND AND t.accno = t2.accno
WHERE t2.rn is null

UPDATE t set class ='C2'
FROM #temp t
LEFT JOIN #temp t2 on datediff(day, t2.trandate,t.trandate)<25 AND t.rn <> t2.rn
AND datediff(day, t2.trandate,t.trandate) >0 AND t.accno = t2.accno
WHERE t2.class = 'C1'

UPDATE t set class ='C3'
FROM #temp t
LEFT JOIN #temp t2 on datediff(day, t2.trandate,t.trandate)<25 AND t.rn <> t2.rn
AND datediff(day, t2.trandate,t.trandate) >0 AND t.accno = t2.accno
WHERE t2.class = 'C2'

UPDATE t set class ='OS'
FROM #temp t
LEFT JOIN #temp t2 on datediff(day, t2.trandate,t.trandate)<25 AND t.rn <> t2.rn
AND datediff(day, t2.trandate,t.trandate) >0 AND t.accno = t2.accno
WHERE t2.class = 'C3'

SELECT * FROM #temp

DROP TABLE #temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources