Exclusion Query - 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

Exclusion Query

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2012-09-19 : 09:31:23

Hello,

I have the below query in which I need to handle the following:
Customers could have multiple process reason id's. I want to pick only one process reason per customer with Process Reason Id's 4,5,6,7 taking precedence.
So, if a customer has 2 process id's 3 and 5 , it should count only 5. If there is no 5 then count 3. If it has 4 and 5 then pick just one.

select
Process Description,
Customer_ID
From
Product p
inner join Process pr
on p.process_id =pr.Process_ID
where Process_Reason_id in (3,4,5,6,7)
and process_date>='8/1/2012'
and process_date<'9/1/2012'

I appreciate all your help.

Thanks,
petronas

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-19 : 09:35:23

please post some sample data and the required result

KH
[spoiler]Time is always against us[/spoiler]

Petronas
Posting Yak Master

134 Posts

Posted - 2012-09-19 : 09:54:46

Sample Data
Process Description Process Id's Customer ID
Auto 3 76567
Manual 4 38901
Credit 5 76567
BAC 6 45671
NOSHW 7 38901

Expected Output
Process Description Process Id's Customer ID
Manual 4 38901
Credit 5 76567
BAC 6 45671

76567 falls in 3 and 5. 5 takes precedence.
38901 falls in 4 and 7 and is counted once either 4 or 7 it does not matter .

Thanks,
Petronas

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-19 : 10:20:56

[code]
select *
from
(
select *, rn = row_number() over (partition by CustomerID order by ProcessID desc)
from yourtable
) d
where rn = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Petronas
Posting Yak Master

134 Posts

Posted - 2012-09-19 : 10:57:26

Thank you very much! It worked perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources