July 24, 2008 at 11:07 am
Hello
I'm in the middle of a migration project, and one of the tasks I've been asked to do is
move data out of our 'System 1' tables to our new, shiny, 'system 2' tables
One of the areas I'm looking at is haulier costs. One of the new concepts (to our system) is
a (Third) Party Location table, which holds a locationId for all customers, traders and our own depots.
The old system uses codes, and I need to join to the new table using a different column depending on the
'direction' of the records involved. By that I mean that a 'Haulage_Cost' can be different when
goods are Inwards or Outwards
I'd like to do something similar to the pseudo code below (pseudo because it doesn't work ;))
So here's some test data to play with
Create Table #oldHaulCost
(HaulId int not null Identity(1,1) primary Key,
Depot varchar(10),
Party varchar(10),
Cost money default 0,
Direction char(1))
Insert into #oldHaulCost
(Depot, Party, Cost, Direction)
select 'London', 'Dave', 12, 'O' UNION
select 'London', 'Dave', 15, 'I' UNION
select 'Birmingham', 'Dave', 10, 'O' UNION
select 'Birmingham', 'Dave', 12, 'I' UNION
select 'Glasgow', 'Dave', 18, 'O' UNION
select 'Glasgow', 'Dave', 22, 'I' UNION
select 'Nottingham', 'Dave', 23, 'O' UNION
select 'Nottingham', 'Dave', 24, 'I'
Insert into #oldHaulCost
(Depot, Party, Cost, Direction)
select 'London', 'Eric', 12, 'O' UNION
select 'London', 'Eric', 15, 'I' UNION
select 'Birmingham', 'Eric', 10, 'O' UNION
select 'Birmingham', 'Eric', 12, 'I' UNION
select 'Glasgow', 'Eric', 18, 'O' UNION
select 'Glasgow', 'Eric', 22, 'I' UNION
select 'Nottingham', 'Eric', 23, 'O' UNION
select 'Nottingham', 'Eric', 24, 'I'
Insert into #oldHaulCost
(Depot, Party, Cost, Direction)
select 'London', 'Fred', 12, 'O' UNION
select 'London', 'Fred', 15, 'I' UNION
select 'Birmingham', 'Fred', 10, 'O' UNION
select 'Birmingham', 'Fred', 12, 'I' UNION
select 'Glasgow', 'Fred', 18, 'O' UNION
select 'Glasgow', 'Fred', 22, 'I' UNION
select 'Nottingham', 'Fred', 23, 'O' UNION
select 'Nottingham', 'Fred', 24, 'I'
Insert into #oldHaulCost
(Depot, Party, Cost, Direction)
select 'London', 'Jane', 12, 'O' UNION
select 'London', 'Jane', 15, 'I' UNION
select 'Birmingham', 'Jane', 10, 'O' UNION
select 'Birmingham', 'Jane', 12, 'I' UNION
select 'Glasgow', 'Jane', 18, 'O' UNION
select 'Glasgow', 'Jane', 22, 'I' UNION
select 'Nottingham', 'Jane', 23, 'O' UNION
select 'Nottingham', 'Jane', 24, 'I'
Insert into #oldHaulCost
(Depot, Party, Cost, Direction)
select 'London', 'Joel', 12, 'O' UNION
select 'London', 'Joel', 15, 'I' UNION
select 'Birmingham', 'Joel', 10, 'O' UNION
select 'Birmingham', 'Joel', 12, 'I' UNION
select 'Glasgow', 'Joel', 18, 'O' UNION
select 'Glasgow', 'Joel', 22, 'I' UNION
select 'Nottingham', 'Joel', 23, 'O' UNION
select 'Nottingham', 'Joel', 24, 'I'
select * from #oldHaulCost
Create table #PartyLocation
(LocationId int not null Identity(1,1) primary Key,
location varchar(50),
locationAlias varchar(10))
Insert into #PartyLocation
(location, locationAlias)
Select 'London Town', 'London' UNION
Select 'Birmingham Town', 'Birmingham' UNION
Select 'Glasgow Town', 'Glasgow' UNION
Select 'Nottingham Town', 'Nottingham' UNION
Select 'Dave J', 'Dave' UNION
Select 'Eric F', 'Eric' UNION
Select 'Fred T', 'Fred' UNION
Select 'Jane D', 'Jane' UNION
Select 'Joel B', 'Joel'
select * from #PartyLocation
--Valid for Outwards
select distinct pl1.locationId as FromId, pl2.locationId as ToId, Cost from #oldHaulCost hc
inner join #PartyLocation pl1
on hc.Depot = pl1.locationAlias
inner join #PartyLocation pl2
on hc.Party = pl2.locationAlias
where Direction = 'O'
--Valid for Inwards
select distinct pl1.locationId as FromId, pl2.locationId as ToId, Cost from #oldHaulCost hc
inner join #PartyLocation pl1
on hc.Party = pl1.locationAlias
inner join #PartyLocation pl2
on hc.Depot = pl2.locationAlias
where Direction = 'I'
-- Where what I'd like is:
-- commented out as it doesn't work
/*
select distinct pl1.locationId as FromId, pl2.locationId as ToId, Cost from #oldHaulCost hc
inner join #PartyLocation pl1
on Case Direction
When 'O' Then hc.Depot = pl1.locationAlias
When 'I' Then hc.Party = pl1.locationAlias
End
inner join #PartyLocation pl2
on Case Direction
When 'O' Then hc.Party = pl2.locationAlias
When 'I' Then hc.Depot = pl2.locationAlias
End
*/
I know I can do this with two queries and the where clause, but that's not very elegant is it. 😀 These migration script have to change as the requirements do, (which is more often that any one here would like :w00t: ) so I'd like to maintain one extract query.
Any Ideas?
Dave J
July 24, 2008 at 11:52 am
You know what it's like, you post a question and then while reflecting on your problem, you solve it having a smoke...
This works, but I still don't like it.
select distinct hc.HaulId,
Case hc.Direction
When 'O' then pl1.locationId
When 'I' then pl2.locationId
END as FromId,
Case hc.Direction
When 'I' then pl1.locationId
When 'O' then pl2.locationId
END as ToId,
hc.Cost, hc.Direction
from #oldHaulCost hc
inner join (select pl.locationId, pl.locationAlias from #PartyLocation pl
inner join #oldHaulCost hc
on hc.Depot = pl.locationAlias
where Direction = 'O') pl1
on pl1.locationAlias = hc.Depot
inner join (select pl.locationId, pl.locationAlias from #PartyLocation pl
inner join #oldHaulCost hc
on hc.Party = pl.locationAlias
where Direction = 'I') as pl2
on pl2.locationAlias = hc.Party
Dave J
July 24, 2008 at 12:01 pm
David Jackson (7/24/2008)
You know what it's like, you post a question and then while reflecting on your problem, you solve it having a smoke...This works, but I still don't like it.
select distinct hc.HaulId,
Case hc.Direction
When 'O' then pl1.locationId
When 'I' then pl2.locationId
END as FromId,
Case hc.Direction
When 'I' then pl1.locationId
When 'O' then pl2.locationId
END as ToId,
hc.Cost, hc.Direction
from #oldHaulCost hc
inner join (select pl.locationId, pl.locationAlias from #PartyLocation pl
inner join #oldHaulCost hc
on hc.Depot = pl.locationAlias
where Direction = 'O') pl1
on pl1.locationAlias = hc.Depot
inner join (select pl.locationId, pl.locationAlias from #PartyLocation pl
inner join #oldHaulCost hc
on hc.Party = pl.locationAlias
where Direction = 'I') as pl2
on pl2.locationAlias = hc.Party
Dave J
Take a look at the following - this puts a route into a single row so you can see the inward/outward cost.
SELECT hc1.Depot
,hc1.Party
,hc1.Cost As InwardCost
,pl1.Location As InwardLocation
,pl1.LocationId As InLocationId
,hc2.Cost As OutwardCost
,pl2.Location As OutwardLocation
,pl2.LocationId As OutLocationId
FROM #oldHaulCost hc1
JOIN #oldHaulCost hc2 ON hc2.Depot = hc1.Depot
AND hc2.Party = hc1.Party
AND hc2.Direction = 'O'
JOIN #PartyLocation pl1 ON pl1.LocationAlias = hc1.Party
JOIN #PartyLocation pl2 ON pl2.LocationAlias = hc2.Depot
WHERE hc1.Direction = 'I';
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 24, 2008 at 12:19 pm
I think this fixes your original question, which was the CASE in the WHERE clause.
select distinct
pl1.locationId as FromId,
pl2.locationId as ToId,
Cost
from #oldHaulCost hc inner join
#PartyLocation pl1
on pl1.locationAlias =
Case When hc.direction = 'O' Then hc.Depot
When hc.direction = 'I' Then hc.Party
End
inner join #PartyLocation pl2
on pl2.locationAlias =
Case When hc.direction = 'O' Then hc.Party
When hc.direction = 'I' Then hc.Depot
End
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 25, 2008 at 3:40 am
Greg Snidow (7/24/2008)
I think this fixes your original question, which was the CASE in the WHERE clause.
It does indeed Greg, thanks very much. Bl*&%y syntax! 😀 Kudos to, to Jeffrey for his very elegant solution.
Dave J
July 25, 2008 at 4:17 am
Dave, I know you have a working solution for this problem now - but a different take on it gives this result:
SELECT FromID = CASE hc.Direction WHEN 'O' THEN pl1.location WHEN 'I' THEN pl2.location End,
ToId = CASE hc.Direction WHEN 'O' THEN pl2.location WHEN 'I' THEN pl1.location End,
Cost
FROM #oldHaulCost hc
INNER JOIN #PartyLocation pl1
ON pl1.locationAlias = hc.Depot
INNER JOIN #PartyLocation pl2
ON pl2.locationAlias = hc.Party
- which seems a little simpler and easier to follow (at least to me, on a friday) because the CASE is in the SELECT rather than the JOINs.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 25, 2008 at 4:56 am
Chris Morris (7/25/2008)
... which seems a little simpler and easier to follow (at least to me, on a friday) because the CASE is in the SELECT rather than the JOINs.
Oh I couldn't agree more. 🙂 The same query does exactly that with a different field
,CASE x.Price_Type
When 'C' then 2 --Collected
When 'D' then 1 --Delivered
End ContractLineDeliveryMethodID
In my defence, I posted the question at the end of a long day... 😛
Thanks for your thoughts
Dave J
July 25, 2008 at 5:10 am
No worries Dave - beer time soon! 😀
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply