Is there a better way to retrieve these data?

Question

My below code works fine. What it does is it updates each product number when it moves to a new location

select
 a.loc1 As [Location 1], 
b.loc2 as [Location 2], 
c.loc3 as [Location 3],
d.loc4 as [Location 4]

FROM (select distinct a.ProductNR as Loc1
from LocationsTest a
where a.Date= (select max(Date) from LocationsTest where a.ProductNR = ProductNR)
AND a.Location = 1) as a

FULL OUTER JOIN

(select distinct a.ProductNR as Loc2
from LocationsTest a
where a.Date= (select max(Date) from LocationsTest where a.ProductNR = ProductNR)
AND a.Location = 2) as b
on a.Loc1 = b.Loc2

FULL OUTER JOIN

(select distinct a.ProductNR as Loc3
from LocationsTest a
where a.Date= (select max(Date) from LocationsTest where a.ProductNR = ProductNR)
AND a.Location = 3) as c
ON ISNULL(A.Loc1, b.Loc2) = c.Loc3

FULL OUTER JOIN

(select distinct a.ProductNR as Loc4
from LocationsTest a
where a.Date= (select max(Date) from LocationsTest where a.ProductNR = ProductNR)
AND a.Location = 4) as d
ON ISNULL(b.Loc2, c.Loc3) = d.Loc4

An example to demonstrate how this works, is as you can see the below 4 locations with different product numbers in them.

----------------------------------------------------------
| Location 1   | Location 2   | Location 3   | Location 4
----------------------------------------------------------
| 1234         |              |              |           |          
| 4567         |              |              |           |
| 8978         |              |              |           |
| 2578         |              |              |           |
----------------------------------------------------------

When a product later on get's scanned into a new location it will still remain in my history data as it was in Location 1, but my above query shows it this:

----------------------------------------------------------
| Location 1   | Location 2   | Location 3   | Location 4
----------------------------------------------------------
|              | 1234         |              |           |          
| 4567         |              |              |           |
| 8978         |              |              |           |
| 2578         |              |              |           |
----------------------------------------------------------

It's retrieves data based on last updated date. Question is my above code looks long, especially when I'm planning to add more locations in the future. So any better ways to do it?

EDIT - Sample data:

  CREATE TABLE LocationsTest
(
ProductNR varchar (14),
Location int,
Date Datetime,

);

Insert Into LocationsTest (ProductNR, Location, Date)
Values('1234', 1, '2016-11-17 12:30:50.010'), 
      ('4567', 1, '2016-11-17 12:35:50.010'), 
      ('8978', 1, '2016-11-17 12:37:50.010'), 
      ('2578', 1, '2016-11-17 12:50:50.010');

Please provide the source tables and some data to populate them. — iamdave, Nov 24 '16 at 12:45
This is not the real table, it's an example of almost same table as in my database. Do you know a site where I can upload some data? like fiddle? — pancake, Nov 24 '16 at 12:50
@MishMish Do you track when a product moves from one location to another? If there is a table where all the locations traversed by a product are maintained, it would be a straightforward query. Basically, if you want history data, you need to track it. — Satwik Nadkarny, Nov 24 '16 at 12:53
@SatwikNadkarny Yes I track a product when it moves from one location another in my where clause. It's working as a charm, I just need to know if the above code can be done a bit smarter? — pancake, Nov 24 '16 at 12:56
@MishMish Just type out a `create table...insert into` script with representative data and we can then use that in our local environments. — iamdave, Nov 24 '16 at 12:57
@MishMish As mentioned above by Iamdave, you'll have to provide your source tables — Satwik Nadkarny, Nov 24 '16 at 12:58
I just updated my question with everything you need @iamdave — pancake, Nov 24 '16 at 13:10
@MishMish Thanks. Do you absolutely have to output the different locations into different columns? What are you presenting this data in? If you can pivot the data in your application, this is a lot simpler. — iamdave, Nov 24 '16 at 14:06

Matt · Answer 1 · 2016-11-25T15:50:55.220

I actually suggest a variation of conditional aggregation which might be a little cleaner and then I would like to point out the nuance that iamdave's technique to get the MostRecent record would produce more than 1 result if a ProductNR had multiple records with the MAX(date). I realize that may not be likely in your dataset it could be for others reading the post. For that reason I would suggest using ROW_NUMBER() to determine the record you want if you do want the ties and then use RANK() or DENSE_RANK() if you do.

If you don't want ties I think you could simplify and just go with something like this:

;WITH cteRowNums AS (
    SELECT
       Location
       ,ProductNR
       ,RowNumber = ROW_NUMBER() OVER (PARTITION BY ProductNR ORDER BY Date DESC)
    FROM
       LocationsTest
)

SELECT DISTINCT
    Location1 = CASE WHEN Location = 1 THEN ProductNR END
    ,Location2 = CASE WHEN Location = 2 THEN ProductNR END
    ,Location3 = CASE WHEN Location = 3 THEN ProductNR END
    ,Location4 = CASE WHEN Location = 4 THEN ProductNR END
FROM       
    cteRowNums
WHERE
     RowNumber = 1

If you do want ties it just becomes true conditional aggregation as follows:

;WITH cteRowNums AS (
    SELECT DISTINCT
       Location
       ,ProductNR
       ,RowNumber = RANK() OVER (PARTITION BY ProductNR ORDER BY Date DESC)
    FROM
       LocationsTest
)

SELECT
    Location1 = MAX(CASE WHEN Location = 1 THEN ProductNR END)
    ,Location2 = MAX(CASE WHEN Location = 2 THEN ProductNR END)
    ,Location3 = MAX(CASE WHEN Location = 3 THEN ProductNR END)
    ,Location4 = MAX(CASE WHEN Location = 4 THEN ProductNR END)
FROM       
    cteRowNums
WHERE
     RowNumber = 1
GROUP BY
    ProductNR

Then to use iamdave's method you could do pretty much the same thing only use ROW_NUMBER() or RANK() to identify what you want as follows:

;WITH cteRowNums AS (
    SELECT
       Location = 'Location' + CAST(Location AS VARCHAR(10))
       ,ProductNR
       ,RowNumber = ROW_NUMBER() OVER (PARTITION BY ProductNR ORDER BY Date DESC)
    FROM
       LocationsTest
)

, cteDesiredRecords AS (
    SELECT
       Location
       ,ProductNR
       ,ProductNR2 = ProductNR
    FROM      
       cteRowNums
    WHERE
       RowNumber = 1
)

SELECT *
FROM
    cteDesiredRecords
    PIVOT (
       MAX(ProductNR)
       FOR Location IN ([Location1],[Location2],[Location3],[Location4])
    ) p

Bottom line is that PIVOT is an awesome command but sometimes it takes a bit of prep with your recordset to massage it to do what you want. In cases like those you could consider Conditional Aggregation as a potential alternative.

Hi Matt, thank you, I will give your answer a shot aswell :) — pancake, Nov 25 '16 at 08:30

score 0 · Accepted Answer · answered Nov 24 '16 at 14:16

I get the impression you are trying to find a formatting solution from within your SQL code, which is generally a no-no. How the data looks should be left to your presentation layer.

That aside, the below code includes two examples; the first is how you probably should be returning your data to your application layer and the second is in the format you have requested. As new locations are included however, you will need to keep updating the PIVOT statement to include them:

CREATE TABLE LocationsTest
(
ProductNR varchar (14),
Location int,
Date Datetime

);

Insert Into LocationsTest (ProductNR, Location, Date)
Values('1234', 1, '2016-11-17 12:30:50.010'), 
      ('4567', 1, '2016-11-17 12:35:50.010'), 
      ('8978', 1, '2016-11-17 12:37:50.010'), 
      ('2578', 1, '2016-11-17 12:50:50.010'),
      ('1234', 2, '2016-11-18 12:30:50.010');   -- I have added this row to simulate a Location move.

-- This just drops out the relevant data for use in application level formatting:
with mr
as
(
    select ProductNR
            ,max(Date) as MostRecent
    from LocationsTest
    group by ProductNR
)
select l.ProductNr
        ,l.Location
from LocationsTest l
    inner join mr
        on l.ProductNR = mr.ProductNR
            and l.Date = mr.MostRecent;


-- This actually PIVOTs the data for you, but will need updating for every new location:
with mr
as
(
    select ProductNR
            ,max(Date) as MostRecent
    from LocationsTest
    group by ProductNR
)
select [1] as Location1
        ,[2] as Location2
        ,[3] as Location3
        ,[4] as Location4
from(
    select l.ProductNr
            ,l.ProductNr as ProductNr2  -- This ensures all rows are returned in the PIVOT
            ,l.Location
    from LocationsTest l
        inner join mr
            on l.ProductNR = mr.ProductNR
                and l.Date = mr.MostRecent
) d
pivot
(max(ProductNr) for Location in([1],[2],[3],[4])) pvt
;

Hello Dave You answer works perfectly. Question is would you recommend I use your code instead of mine? — pancake, Nov 24 '16 at 14:21
@MishMish I *think* it would out perform your multiple `FULL OUTER JOIN`s, though that is for you to test on your own environment and go from there. — iamdave, Nov 24 '16 at 14:26

Is there a better way to retrieve these data?

2 Answers2