MishMish MishMish - 11 days ago 6
SQL Question

Is there a better way to retrieve these data?

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

Answer

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