Link to home
Start Free TrialLog in
Avatar of sqlcurious
sqlcuriousFlag for United States of America

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..|Coin20|Uscoin21|WorldCoin22..|EntMov30| 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!
ASKER CERTIFIED SOLUTION
Avatar of James0628
James0628

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sqlcurious

ASKER

@Paul Maxwell thanks a lot for the detailed solution, I sure do understand that I need to unpivot for this case, I was requested to separate out the columns but I can definitely explain the requestor the complexity and keep it the way you mentioned.
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.
@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:
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.
An "expected result"  looks something like this:
| cust_no | Categoryname | CategoryValue | SeriesNo | Ranking |
|---------|--------------|---------------|----------|---------|
|     456 |    WestArt12 |             9 |        1 |       1 |
|     678 |       Coin20 |             7 |        2 |       1 |
|     678 |   EntComic32 |            12 |        3 |       1 |

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of James0628
James0628

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

Open in new window

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

Open in new window

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')
;

Open in new window

also see: http://sqlfiddle.com/#!6/e2310/1
Having made the data correction spotted by hnsar (thanks!)
hnsar?  :-)

 James