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!
sqlcuriousAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
Does table 3 (you have it labeled "Table 2" in your post) really have a column for each category?  That obviously complicates things.  The first thing might be to try rearranging the data to put each category in a separate row.

 Also, you have some "."s in the WestArt12 and WorldCoin22 columns, implying that they could contain more values, or possibly that there could be more columns that are related to those (eg. WestArt13 or WorldCoin22b).  That was my first thought.  But then it occurred to me that maybe you were just indicating that there are columns for other, completely separate, categories that you didn't include in your sample.

 James

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
You most certainly need to "unpivot" Table3, you make the problem vastly more complex than it should be by storing the data that way.  e.g. Table3 should be CustNo, CategroyNo, Value

Below I have used a CROSS APPLY with VALUES to perform the "unpivot" for you. Once we have that data in a normalized form then is is a simple ranking that you need. It does not matter how many categories there are in a series by the way (as long as there is a least one) as all you want is the category with the highest value even if there are 1 or 50 categories in a series.

I would also suggest you store SeriesNo in Table2 as a column.

So, assuming we can store SeriesNo in Table2 I suggest this query:
select
    *
from (
        select
                t3.cust_no
              , ca.Categoryname
              , ca.CategoryValue
              , t2.SeriesNo
              , row_number() over(partition by t2.SeriesNo 
                                  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

Running that query on this MODIFIED SAMPLE DATA:
CREATE TABLE Table1
    ([Custr_no] int, [categoryid] int)
;
    
INSERT INTO Table1
    ([Custr_no], [categoryid])
VALUES
    (123, 10),
    (123, 11),
    (123, 20),
    (123, 30),
    (123, 32),
    (456, 12),
    (456, 22),
    (456, 32),
    (678, 20),
    (678, 21),
    (678, 31)
;
CREATE TABLE Table2
    ([Categoryid] int, [Categoryname] varchar(12), [SeriesNo] int)
;
    
INSERT INTO Table2
    ([Categoryid], [SeriesNo], [])
VALUESCategoryname
    (10,1, 'Art10'),
    (11,1, 'FineArt11'),
    (12,1, 'WestArt12'), --<< changed!!! was: WesternArt12
    (20,2, 'Coin20'),
    (21,2, 'UScoin21'),
    (30,2, 'EntMov30'),
    (31,3, 'EntPlay31'),
    (32,3, 'EntComic32'), --<< added
    (33,3, 'WorldCoin22'), --<< added
    (49,4, 'Curr49')
;

CREATE TABLE Table3
    (  [cust_no] int
     , [Art10] decimal(12,3)
     , [FineArt11] decimal(12,3)
     , [WestArt12] decimal(12,3)
     , [Coin20] decimal(12,3)
     , [Uscoin21] decimal(12,3)
     , [WorldCoin22] decimal(12,3)
     , [EntMov30] decimal(12,3)
     , [EntPlay31] decimal(12,3)
     , [EntComic32] decimal(12,3)
    )
;
    
INSERT INTO Table3
    ([cust_no], [Art10], [FineArt11], [WestArt12], [Coin20], [Uscoin21], [WorldCoin22], [EntMov30], [EntPlay31], [EntComic32])
VALUES
    (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)
;

Open in new window

I got this result:
| 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

Note to make this work:

1.  Include SeriesNo in table2
2.  Must Match Table3 column names to Categoryname in Table2
3.  All Categoryname headings of Table3 are in Categoryname of Table2

nb: Table1 isn't required for this solution.

also see: http://sqlfiddle.com/#!6/d58ab/2
sqlcuriousAuthor Commented:
@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.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PortletPaulEE Topic AdvisorCommented:
@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

James0628Commented:
Paul,

 I'm not sure why you're not showing any results for customer 123, but I think one basic issue is that there should only be 1 result for each customer.  I think sqlcurious is only interested in the lowest series (20 and 21 in the case of 678), and then wants the category with the highest value (Coin20) in that series.  So EntComic32 would be discarded because it's not in the lowest series.

 Also, just an FYI, I noticed that your sample data has EntMov30 in series 2.

    (30,2, 'EntMov30'),

 That presumably should be series 3.

    (30,3, 'EntMov30'),

 FWIW, if sqlcurious already has a table with CategoryId and CategoryName (eg. 10 and 'Art10'), I wonder if it might be simpler (or at least more flexible) to just use something like FLOOR (CategoryId / 10) to calculate the series #?

 James
sqlcuriousAuthor Commented:
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
PortletPaulEE Topic AdvisorCommented:
Thanks for the expected result clarification.

>>"FLOOR (CategoryId / 10) to calculate series
I can only say that if there is any chance of there being more than 10 values in any series - ever - such an algorithm will be a problem. I agree it could be done on the very tiny sample of data shown here - but will it work for all data (& for all time)?


>>"not sure why you're not showing any results for customer 123"

because I forgot to include cust_no is the "partition by" I think

              , row_number() over(partition by t3.cust_no
                                  order by ca.CategoryValue DESC) as Ranking
James0628Commented:
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
sqlcuriousAuthor Commented:
It worked, thank you so much Paul and James for your help.
PortletPaulEE Topic AdvisorCommented:
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
James0628Commented:
Having made the data correction spotted by hnsar (thanks!)
hnsar?  :-)

 James
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.