Home Select specific value for all corresponding rows
Reply: 1

Select specific value for all corresponding rows

sǝɯɐſ
1#
sǝɯɐſ Published in 2018-02-13 17:37:33Z

I am trying to get a result set of Top Customers which is ordered by a rank value based on year and total. Easy, right?

BUT, now I want to specify a year, and have ALL rows for that customer return the rank value for that year.

For example, say I have the following data (rank hardcoded for ease):

SELECT * FROM 
(
    SELECT 'Customer A' as Cust,'123.45' as Total,'2016' as [year],1 as [rank]
    UNION
    SELECT 'Customer A','123.45','2017',3
    UNION
    SELECT 'Customer B','46.67','2016',2
    UNION
    SELECT 'Customer B','423.45','2017',1
    UNION
    SELECT 'Customer B','123.45','2018',1
    UNION
    SELECT 'Customer C','23.45','2016',3
    UNION
    SELECT 'Customer C','223.45','2017',2
    UNION
    SELECT 'Customer C','23.45','2018',2
) as a
ORDER BY a.[year], a.[rank]

If I specify year 2016, I want to pick the rank value for the year 2016 for each customer, and return that for the customer for each of that customer's rows in the result set - should look like the following:

=>

The closest I can manage is the following, but it just NULLs the other cells:

DECLARE @RankBy VARCHAR(4) = '2016'

SELECT [Year],
    Cust,
    [Total],
    [rank] = (SELECT a.[rank] WHERE Cust = a.Cust AND [Year] = @RankBy)
FROM
(
    SELECT 'Customer A' as Cust,'123.45' as Total,'2016' as [year],1 as [rank]
    UNION
    SELECT 'Customer A','123.45','2017',3
    UNION
    SELECT 'Customer B','46.67','2016',2
    UNION
    SELECT 'Customer B','423.45','2017',1
    UNION
    SELECT 'Customer B','123.45','2018',1
    UNION
    SELECT 'Customer C','23.45','2016',3
    UNION
    SELECT 'Customer C','223.45','2017',2
    UNION
    SELECT 'Customer C','23.45','2018',2
) as a
ORDER BY a.Cust, a.rank

I know I can do this with a temp table and an update statement, but I'm trying to find a way to do it in a single select statement if possible.

(In case anyone is wondering, this is for an SSRS Report, but I don't see that being relevant here.)

dnoeth
2#
dnoeth Reply to 2018-02-13 17:43:04Z

Try a Windowed Aggregate over a CASE:

MIN(CASE WHEN [Year] = @RankBy THEN a.[rank] END)
OVER (PARTITION BY Cust)
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.317278 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO