on 04-11-2016 12:19 AM
HI I Have data like this:
Order# Order_code Order_Amt
123 Code1 100.00
123 Code2 100.00
123 Code3 100.00
123. Code4 100.00
Most of our orders will have only one order_code per order, but some have more than 1.Different parts require different works etc..
So in the above suppose we have a hierarchy, if there is a code1, only show that. if not a code1 in this list, then code2 is next.
How do I code this in the record select?
Hi Paul,
What is the data source of the report?
If you're reporting against a Command Object, you'd need to handle this in the SQL query.
If you're reporting against tables/views, there is a way however, I prefer to know the source first.
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You should be able to handle this in a SQL Query like this:
Select
T2.Order#,
T2.Code,
T2.Rows
From
(
Select T.Order#,
T.Code,
Dense_Rank() Over(Partition By Order# Order By Case When Code = 'Code1' then 1
When Code = 'Code2' then 2
When Code = 'Code3' then 3
When Code = 'Code4' then 4 END ASC) Rows
from Table T
)T2
Where
T2.Rows = 1
Order by T2.Code
-Abhilash
User | Count |
---|---|
84 | |
10 | |
10 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.