sqlcurious
asked on
Have a tricky way of picking id's and categories, need help pls
Table 1:
Custr_no categoryid
123 10
123 11
123 20
123 30
123 32
456 12
456 22
456 32
678 20
678 21
678 31
Table 2 Lookuptable:
Categoryid | Categoryname
10 | Art10
11 | FineArt11
12 | WesternArt12
20 | Coin20
21 | UScoin21
30 | EntMov30
31 | EntPlay31
49 | Curr49
Table 2:
cust_no|Art10 | FineArt11|WestArt12..|Coin 20|Uscoin2 1|WorldCoi n22..|EntM ov30| EntPlay31| EntComic32 |
123 | 5.0 | 6.0 | 0.0 ..| 1.0 | 0.0 | 0.0 ... | 2.0 | 0.0 | 0.0 |
..
456 | 0.0 | 0.0 | 9.0 ..| 0.0 | 0.0 | 6.0 .. .| 0.0 | 0.0 | 0.0 |
678 | 0.0 | 0.0 | 0.0 ..| 7.0 | 4.0 | 0.0 ...| 0.0 | 1.0 | 12.0 |
RESULT:
Customer_no | WinningCategory
123 | FineArt11
456 | WesternArt12
678 | Coin20
Hi Experts, Above are the sample tables for a small project I have, the logic presented to me is tricky.
There are almost 40 categories with 40 different id's, 10-19 fall under one series let's say series1, similar 20-29->series2, 30-39
->series3 and 40-49 ->series4. In the tables I have only mentioned few for example sake.
For a given customer from table 1, I have to find the minimum categoryid and check if there are more than one in that series. If it's just one in the 4 series above, then I report it in the result. If not, I have to pick the related categorynames and compare the amounts from table 3 and pick the highest amount category and report in the result.
For instance: For customer 456, in table 1 the lowest categoryid amongst 12, 22, 32 is '12', so I report the relevant category WesternArt12. But for customer_no's 123 and 678 it's different.
For 123 the lowest categoryid is 10 but there are 2 of them in the series1 - (10, 11), I have to check their amounts in Table 3 for relevant categories Art10 and FineArt11 and since FineArt11 amount '6.0' is higher I have to report FineArt11 in the result table.
Similarly, for customer 678, the lowest category id is 20 but there are 2 in the series2 - (20, 21), I have to check their amounts in Table 3 for relevant categories Coin20 and UScoin21 and since Coin20 amount '7.0' is higher I should report Coin20 in the result.
Please suggest me how I code this.
Thanks!
Custr_no categoryid
123 10
123 11
123 20
123 30
123 32
456 12
456 22
456 32
678 20
678 21
678 31
Table 2 Lookuptable:
Categoryid | Categoryname
10 | Art10
11 | FineArt11
12 | WesternArt12
20 | Coin20
21 | UScoin21
30 | EntMov30
31 | EntPlay31
49 | Curr49
Table 2:
cust_no|Art10 | FineArt11|WestArt12..|Coin
123 | 5.0 | 6.0 | 0.0 ..| 1.0 | 0.0 | 0.0 ... | 2.0 | 0.0 | 0.0 |
..
456 | 0.0 | 0.0 | 9.0 ..| 0.0 | 0.0 | 6.0 .. .| 0.0 | 0.0 | 0.0 |
678 | 0.0 | 0.0 | 0.0 ..| 7.0 | 4.0 | 0.0 ...| 0.0 | 1.0 | 12.0 |
RESULT:
Customer_no | WinningCategory
123 | FineArt11
456 | WesternArt12
678 | Coin20
Hi Experts, Above are the sample tables for a small project I have, the logic presented to me is tricky.
There are almost 40 categories with 40 different id's, 10-19 fall under one series let's say series1, similar 20-29->series2, 30-39
->series3 and 40-49 ->series4. In the tables I have only mentioned few for example sake.
For a given customer from table 1, I have to find the minimum categoryid and check if there are more than one in that series. If it's just one in the 4 series above, then I report it in the result. If not, I have to pick the related categorynames and compare the amounts from table 3 and pick the highest amount category and report in the result.
For instance: For customer 456, in table 1 the lowest categoryid amongst 12, 22, 32 is '12', so I report the relevant category WesternArt12. But for customer_no's 123 and 678 it's different.
For 123 the lowest categoryid is 10 but there are 2 of them in the series1 - (10, 11), I have to check their amounts in Table 3 for relevant categories Art10 and FineArt11 and since FineArt11 amount '6.0' is higher I have to report FineArt11 in the result table.
Similarly, for customer 678, the lowest category id is 20 but there are 2 in the series2 - (20, 21), I have to check their amounts in Table 3 for relevant categories Coin20 and UScoin21 and since Coin20 amount '7.0' is higher I should report Coin20 in the result.
Please suggest me how I code this.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@sqlcurious
I do wish I could "see" the results you wanted, and the data it is based on.
If you have sample data, and the expected result, then please do supply both. Until then I'm not sure how to help because I simply do not understand any of this:
I do wish I could "see" the results you wanted, and the data it is based on.
If you have sample data, and the expected result, then please do supply both. Until then I'm not sure how to help because I simply do not understand any of this:
My only problem now is that I will still need result for the customer 123 and it should be FineArt11 with Categoryvalue = 6.00An "expected result" looks something like this:
And also, I don't have to show 678 | Entcomic32 | 12 |3 |1
as the lowest series is 2, and amongst 7.0, 4.0 category values, 7.0 is highest and this is coming out right, just that I don't have to show Entcomic32 at all in the result.
| cust_no | Categoryname | CategoryValue | SeriesNo | Ranking |
|---------|--------------|---------------|----------|---------|
| 456 | WestArt12 | 9 | 1 | 1 |
| 678 | Coin20 | 7 | 2 | 1 |
| 678 | EntComic32 | 12 | 3 | 1 |
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Paul, Below would be the expected result. Sample data remains same as mentioned in your post
The result
| cust_no | Categoryname | CategoryValue
| 456 | WestArt12 | 9 |
| 678 | Coin20 | 7 |
| 123 | EntComic32 | 6 |
This is the above expected result, I don't need category value but would be good to have.
thanks
The result
| cust_no | Categoryname | CategoryValue
| 456 | WestArt12 | 9 |
| 678 | Coin20 | 7 |
| 123 | EntComic32 | 6 |
This is the above expected result, I don't need category value but would be good to have.
thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're right of course. The Floor idea only works if every series will have x0 - x9 numbers like the ones in the sample data.
James
James
ASKER
It worked, thank you so much Paul and James for your help.
I got this result:
| cust_no | Categoryname | CategoryValue | SeriesNo | Ranking |
|---------|--------------|---------------|----------|---------|
| 123 | FineArt11 | 6 | 1 | 1 |
| 456 | WestArt12 | 9 | 1 | 1 |
| 678 | EntComic32 | 12 | 3 | 1 |
Using this query
select
*
from (
select
t3.cust_no
, ca.Categoryname
, ca.CategoryValue
, t2.SeriesNo
, row_number() over(partition by t3.cust_no
order by ca.CategoryValue DESC) as [Ranking]
from Table3 t3
cross apply (
values
('Art10' , t3.[Art10] )
, ('FineArt11' , t3.[FineArt11] )
, ('WestArt12' , t3.[WestArt12] )
, ('Coin20' , t3.[Coin20] )
, ('Uscoin21' , t3.[Uscoin21] )
, ('WorldCoin22', t3.[WorldCoin22] )
, ('EntMov30' , t3.[EntMov30] )
, ('EntPlay31' , t3.[EntPlay31] )
, ('EntComic32' , t3.[EntComic32] )
) ca (Categoryname, CategoryValue)
left join Table2 t2 on ca.Categoryname = t2.Categoryname
) as derived
where [Ranking] = 1
Having made the data correction spotted by hnsar (thanks!)
INSERT INTO Table2
([Categoryid], [SeriesNo], [Categoryname])
VALUES
(10,1, 'Art10'),
(11,1, 'FineArt11'),
(12,1, 'WestArt12'),
(20,2, 'Coin20'),
(21,2, 'UScoin21'),
(30,3, 'EntMov30'),
(31,3, 'EntPlay31'),
(32,3, 'EntComic32'),
(33,3, 'WorldCoin22'),
(49,4, 'Curr49')
;
also see: http://sqlfiddle.com/#!6/e2310/1
Having made the data correction spotted by hnsar (thanks!)hnsar? :-)
James
ASKER
My only problem now is that I will still need result for the customer 123 and it should be FineArt11 with Categoryvalue = 6.00
And also, I don't have to show 678 | Entcomic32 | 12 |3 |1
as the lowest series is 2, and amongst 7.0, 4.0 category values, 7.0 is highest and this is coming out right, just that I don't have to show Entcomic32 at all in the result.
@James0628, I am sorry I meant table 3. I do completely agree that having that kind of table structure would complicate things though that was how it was initially requested.But I am changing it now according yours and Paul's suggestion. And also, "."s in the WestArt12 meant that I have more columns in series 1, hope I answered your question and thanks again for the response.