September 2, 2010 at 12:11 pm
Hello,
I need to move 2 columns of data from one table to another. The task is simple in theory but I don't know how to connect the 2 tables correctly.
I have a table (@Services below) that contains information based on a service preformed. This service has specifications (@Specifications) associated with it. I need to move 2 of the specification columns to the Services table.
Here is the tricky part. The 2 tables are connected through a third table (@Location) and the LocationID's are different between @services and @Specifications however they reference the same LocationName. Notice the @Location table has multiple ID's for the same LocationNames. I cannot change the structure of the @Location table.
I need to connect the 2 tables based on Location.LocationName and Service.SpecName = Specifications.SpecName and update the Service.MinReq to = the Specification.MinReq and the Service.MaxReq to = Specification.MaxReq
DECLARE @Location TABLE (LocationID int, LocationName varchar(10))
INSERT INTO @Location
SELECT 1, 'Location A' UNION ALL
SELECT 2, 'Location A' UNION ALL
SELECT 3, 'Location B' UNION ALL
SELECT 4, 'Location B' UNION ALL
SELECT 5, 'Location C' UNION ALL
SELECT 6, 'Location C'
DECLARE @Serivce TABLE (ServiceID int, LocationID int, SpecName varchar(1), MinReq int, MaxReq int)
INSERT INTO @Serivce
SELECT 10, 1, 'B', NULL, NULL UNION ALL
SELECT 11, 1, 'A', NULL, NULL UNION ALL
SELECT 12, 3, 'A', NULL, NULL UNION ALL
SELECT 13, 5, 'C', NULL, NULL
DECLARE @Specifications TABLE (ServiceID int, LocationID int, SpecName varchar(1), MinReq int, MaxReq int)
INSERT INTO @Specifications
SELECT 10, 2, 'A', 8, 12 UNION ALL
SELECT 11, 2, 'B', 7, 11 UNION ALL
SELECT 12, 4, 'A', 9, 15 UNION ALL
SELECT 13, 6, 'A', 0, 20
DECLARE @ServiceResults TABLE (ServiceID int, LocationID int, SpecName varchar(1), MinReq int, MaxReq int)
INSERT INTO @ServiceResults
SELECT 10, 1, 'B', 7, 11 UNION ALL
SELECT 11, 1, 'A', 8, 12 UNION ALL
SELECT 12, 3, 'A', 9, 15 UNION ALL
SELECT 13, 5, 'C', NULL, NULL
Select * from @ServiceResults
the @ServiceResults is what I want the outcome to be.
Thanks,
Zane
September 2, 2010 at 12:34 pm
Can you clarify a couple things?
Based on your sample data, service #10 links to specification #10 and specification #11, so how are we determining which spec's value to use?
Also, please confirm that the location names will at least be exact matches.
And why does Service #13 not get a min max in your sample output?
EDIT: sorry, missed the specname comment
September 2, 2010 at 12:44 pm
LocationName will an exact match.
As far as the min/max question. It is based on the SpecName and LocationName
So if the Service table has SpecName = 'A' then on the Specification table I need to pull the SpecName = 'A' AND make sure that the LocationName matches for both tables.
There is not need for a MIN() or MAX() operation because the SpecName will be unique per LocationName.
September 2, 2010 at 12:47 pm
As long as the location name exactly matches:
DECLARE @Location TABLE (LocationID int, LocationName varchar(10))
INSERT INTO @Location
SELECT 1, 'Location A' UNION ALL
SELECT 2, 'Location A' UNION ALL
SELECT 3, 'Location B' UNION ALL
SELECT 4, 'Location B' UNION ALL
SELECT 5, 'Location C' UNION ALL
SELECT 6, 'Location C'
DECLARE @Serivce TABLE (ServiceID int, LocationID int, SpecName varchar(1), MinReq int, MaxReq int)
INSERT INTO @Serivce
SELECT 10, 1, 'B', NULL, NULL UNION ALL
SELECT 11, 1, 'A', NULL, NULL UNION ALL
SELECT 12, 3, 'A', NULL, NULL UNION ALL
SELECT 13, 5, 'C', NULL, NULL
DECLARE @Specifications TABLE (ServiceID int, LocationID int, SpecName varchar(1), MinReq int, MaxReq int)
INSERT INTO @Specifications
SELECT 10, 2, 'A', 8, 12 UNION ALL
SELECT 11, 2, 'B', 7, 11 UNION ALL
SELECT 12, 4, 'A', 9, 15 UNION ALL
SELECT 13, 6, 'A', 0, 20
DECLARE @ServiceResults TABLE (ServiceID int, LocationID int, SpecName varchar(1), MinReq int, MaxReq int)
INSERT INTO @ServiceResults
SELECT 10, 1, 'B', 7, 11 UNION ALL
SELECT 11, 1, 'A', 8, 12 UNION ALL
SELECT 12, 3, 'A', 9, 15 UNION ALL
SELECT 13, 5, 'C', NULL, NULL
Select * from @ServiceResults
;
WITH ServLoc (ServiceID, LocationName,SpecName) AS
(
SELECT
s.ServiceID,
l.LocationName,
s.SpecName
FROM
@Serivce s
INNER JOIN @Location l ON l.LocationId = s.LocationId
),
SpecLoc (LocationName,MinReq,MaxReq,SpecName) AS
(
SELECT
l.LocationName,
s.MinReq,
s.MaxReq,
s.SpecName
FROM
@Specifications s
INNER JOIN @Location l ON l.LocationID = s.LocationID
)
UPDATE @Serivce
SET MinReq = s2.MinReq, MaxReq = s2.MaxReq
FROM @Serivce s
INNER JOIN ServLoc s1 ON s.ServiceId=s1.ServiceID
INNER JOIN SpecLoc s2 ON s1.LocationName = s2.LocationName AND s1.SpecName = s2.SpecName
SELECT * FROM @Serivce
September 2, 2010 at 12:57 pm
Works like a charm. THANKS!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply