Applying Precedence Rule

Question

Post reply

Applying Precedence Rule

karthik M

One Orange Chip

Points: 29139
More actions
July 6, 2011 at 12:57 am

#239186

Hi All,
I have one requirement.
create table #t1
(
assetid int,
type varchar(50),
dt datetime,
ret float
)
insert into #t1
select 1,'MonthlyFinal','31/Jan/2011',2.5
insert into #t1
select 1,'MonthlyFinal','28/Feb/2011',0.3435
insert into #t1
select 1,'MonthlyFinal','31/Mar/2011',1.22321
insert into #t1
select 1,'MonthlyEstimated','31/Jan/2011',1.22321
insert into #t1
select 1,'MonthlyEstimated','30/Apr/2011',0.121
insert into #t1
select 1,'MonthlyEstimated','31/May/2011',5.121
insert into #t1
select 1,'DailyEstimated','31/Mar/2011',3.5
insert into #t1
select 1,'DailyEstimated','31/May/2011',5.21
insert into #t1
select 1,'DailyEstimated','30/Jun/2011',1.21
I have to apply the below precedence rule to get the final output.
precedence rule:
1. MonthlyFinal
2. MonthlyEstimated
3. MTDFinal
4. MTDEstimated
5. DailyEstimated
so...
1 MonthlyFinal 31/Jan/2011 2.5
1 MonthlyFinal 28/Feb/2011 0.3435
1 MonthlyFinal 31/Mar/2011 1.22321
1 MonthlyEstimated 31/Jan/2011 1.22321 -- should be removed (Date is in MonthlyFinal)
1 MonthlyEstimated 30/Apr/2011 0.121
1 MonthlyEstimated 31/May/2011 5.121
1 DailyEstimated 31/Mar/2011 3.5 – should be removed (Date is in MonthlyFinal)
1 DailyEstimated 31/May/2011 5.21-should be removed (Date is in MonthlyEstimated)
1 DailyEstimated 30/Jun/2011 1.21
Expected Output:
1 MonthlyFinal 31/Jan/2011 2.5
1 MonthlyFinal 28/Feb/2011 0.3435
1 MonthlyFinal 31/Mar/2011 1.22321
1 MonthlyEstimated 30/Apr/2011 0.121
1 MonthlyEstimated 31/May/2011 5.121
1 DailyEstimated 30/Jun/2011 1.21
Inputs are welcome!
karthik

Viewing 8 posts - 1 through 7 (of 7 total)

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

karthik M One Orange Chip Points: 29139 More actions · Answer 1

karthik M

One Orange Chip

Points: 29139

July 6, 2011 at 7:04 am

#1348910

Any inputs?

karthik

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

John Mitchell-245523

SSC Guru

Points: 148809

July 6, 2011 at 7:12 am

#1348923

Karthik

What have you tried so far?

John

kramaswamy SSCoach Points: 18181 More actions · Answer 3

This should work. Although it would be better if, instead, you just modified your original table to include that "TypeOrder" column, so you don't need to make two CTE statements.

create table #t1

(

assetid int,

type varchar(50),

dt datetime,

ret float

)

insert into #t1

select 1,'MonthlyFinal','31/Jan/2011',2.5

insert into #t1

select 1,'MonthlyFinal','28/Feb/2011',0.3435

insert into #t1

select 1,'MonthlyFinal','31/Mar/2011',1.22321

insert into #t1

select 1,'MonthlyEstimated','31/Jan/2011',1.22321

insert into #t1

select 1,'MonthlyEstimated','30/Apr/2011',0.121

insert into #t1

select 1,'MonthlyEstimated','31/May/2011',5.121

insert into #t1

select 1,'DailyEstimated','31/Mar/2011',3.5

insert into #t1

select 1,'DailyEstimated','31/May/2011',5.21

insert into #t1

select 1,'DailyEstimated','30/Jun/2011',1.21

;WITH cte1 AS

(

SELECT

AssetID,

[Type],

dt,

ret,

(

CASE

WHEN [type] = 'MonthlyFinal' THEN 1

WHEN [Type] = 'MonthlyEstimated' THEN 2

WHEN [Type] = 'MTDFinal' THEN 3

WHEN [Type] = 'MTDEstimated' THEN 4

WHEN [Type] = 'DailyEstimated' THEN 5

END

) AS TypeOrder

FROM #t1

),

cte2 AS

(

SELECT

AssetID,

[Type],

dt,

ret,

ROW_NUMBER() OVER (PARTITION BY dt ORDER BY TypeOrder) AS rowNum

FROM cte1

)

SELECT

AssetID,

[Type],

dt,

ret

FROM cte2

WHERE rowNum = 1

karthik M One Orange Chip Points: 29139 More actions · Answer 4

I used while loop to do this. But it is taking more than 30 minutes to complete the execution.

I used the below logic:

select assetid,dt,count(dt) as dt

into #multiples

from #t1

group by assetid,dt

loop through each assetid and dt

check whether the assetid &dt has 'MonthlyFinal' and then set MF flag to 1

check whether the assetid &dt has 'MonthlyEstimated' and then set ME flag to 1

check whether the assetid &dt has 'DailyEstimated' and then set DE flag to 1

if MF flag = 1 then delete all the records (only for that particular assetid) where type != 'MonthlyFinal'

if ME flag = 1 then delete all the records (only for that particular assetid) where type != 'MonthlyEstimated'

if DE flag = 1 then delete all the records (only for that particular assetid) where type != 'DailyEstimated'

karthik

Mark Cowne One Orange Chip Points: 26966 More actions · Answer 5

WITH Rules(RuleNo,RuleName) AS (

SELECT 1,'MonthlyFinal' UNION ALL

SELECT 2,'MonthlyEstimated' UNION ALL

SELECT 3,'MTDFinal' UNION ALL

SELECT 4,'MTDEstimated' UNION ALL

SELECT 5,'DailyEstimated'),

Ordered AS (

SELECT t.assetid,t.type,t.dt,t.ret,

ROW_NUMBER() OVER(PARTITION BY t.assetid,t.dt ORDER BY r.RuleNo) AS rn

FROM #t1 t

INNER JOIN Rules r ON r.RuleName=t.type)

SELECT assetid,type,dt,ret

FROM Ordered

WHERE rn=1;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Mark Cowne One Orange Chip Points: 26966 More actions · Answer 6

Any feedback?

Did either of the solutions work for you?

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

karthik M One Orange Chip Points: 29139 More actions · Answer 7

Hey..Mark..I'm sorry..

Both the query is working fine. I appreciate you guys help...

karthik