August 18, 2016 at 3:38 pm
I have a following table that I need to update based on records in another one.
My tbl1 columns are Product | Code | Price
MY tbl2 columns are Product | Code | Price | startdate | enddate | Action
tbl1 needs to be updated based on records from tbl2
Basically
if columns: Product and Code are equal for both tables and price is different and current date > startdate and < enddate then tbl1 price - tbl2 price and the column tbl2.Action needs to be updated to C.
if columns tbl2.Product and tbl2.Code not found in tbl1 and current date between startdate and enddate then add (Insert) new records to tbl1. and update the column tbl2.Action to A
if tbl1.Product , tbl1.Code, tbl1.Price not found in tbl2 then delete the whole record from tbl1. and update tbl2.Action to D
I would appreciate some help with the above dilemma. Still on SQL server 2005
Thanks
August 18, 2016 at 7:09 pm
When you're asking questions about specific SQL queries, it's generally good form to post the table creation and population queries to save us the time of having to do that ourselves to test our solutions.
SQL Server 2008+ introduced the MERGE statement, which might have simplified your work. You'll need to write a stored procedure, and handle the different cases one at a time instead. Note that you could probably do this a bit differently using a CTE, but I thought it would be cleaner to just write it out independently instead.
Also note that I would highly recommend you change your table design - rather than having a composite key, introduce IDENTITY PRIMARY KEY columns into both tables, create a separate Product table with it's own IDENTITY PRIMARY KEY column, and then add FOREIGN KEY relationships between your two original tables and this new table. Then, your joins will simply be where that FOREIGN KEY column is equal in both tables, rather than having to join both composite values together.
If you have two values in Tbl1 with the same product and code values, and two values in Tbl2 with the same product and code values, the joins below will produce four records instead of two otherwise.
Lastly, your 'D' update in Table 2 is impossible - if the record is not there in Table 2, how can you update it?
Output of below code:
Table1 After Updates:
Product1P110.942016-08-19 01:08:002016-08-19 01:08:00NULL
Product2P244.152016-08-19 01:08:002016-08-19 01:08:002016-08-19 01:08:00
Product3P355.152016-08-19 01:08:00NULLNULL
Product4P44.212016-08-19 01:08:00NULLNULL
Table2 After Updates
Product1P14.212015-01-01 00:00:002016-12-31 00:00:00C
Product3P34.212015-01-01 00:00:002015-12-31 00:00:00NULL
Product4P44.212016-01-01 00:00:002016-12-31 00:00:00A
CREATE TABLE #Tbl1
(
Product VARCHAR(50),
Code VARCHAR(5),
Price DECIMAL(18, 2),
DateCreated SMALLDATETIME DEFAULT GETDATE(),
DateUpdated SMALLDATETIME,
DateDeleted SMALLDATETIME
)
CREATE TABLE #Tbl2
(
Product VARCHAR(50),
Code VARCHAR(5),
Price DECIMAL(18, 2),
StartDate SMALLDATETIME,
EndDate SMALLDATETIME,
Action CHAR(1)
)
INSERT INTO #Tbl1 (Product, Code, Price)
VALUES ('Product1', 'P1', 15.15) -- RECORD TO BE UPDATED WITH LOWER PRICE
INSERT INTO #Tbl1 (Product, Code, Price)
VALUES ('Product2', 'P2', 44.15) -- RECORD TO BE REMOVED
INSERT INTO #Tbl1 (Product, Code, Price)
VALUES ('Product3', 'P3', 55.15) -- RECORD NOT UPDATED BECAUSE DATE DOESN'T MATCH
INSERT INTO #Tbl2 (Product, Code, Price, StartDate, EndDate)
VALUES ('Product1', 'P1', 4.21, '2015-01-01', '2016-12-31')
INSERT INTO #Tbl2 (Product, Code, Price, StartDate, EndDate)
VALUES ('Product3', 'P3', 4.21, '2015-01-01', '2015-12-31')
INSERT INTO #Tbl2 (Product, Code, Price, StartDate, EndDate)
VALUES ('Product4', 'P4', 4.21, '2016-01-01', '2016-12-31') -- RECORD TO BE INSERTED
-- RESET ACTION CODES
UPDATE #Tbl2 SET Action = NULL
-- UPDATE EXISTING RECORDS
UPDATE t2
SET t2.Action =(
CASE
WHEN t1.Product IS NOT NULL AND GETDATE() BETWEEN t2.StartDate AND t2.EndDate THEN 'C'
WHEN t1.Product IS NULL THEN 'A'
END
)
FROM #Tbl2 t2
LEFT JOIN #Tbl1 t1 ON t1.Product = t2.Product AND t1.Code = t2.Code
UPDATE t1
SET t1.Price = t1.Price - t2.Price, -- If negative, then what? Possibly put a CASE statement here to handle negative results
t1.DateUpdated = GETDATE()
FROM #Tbl1 t1
JOIN #Tbl2 t2 ON t2.Product = t1.Product AND t2.Code = t1.Code AND t2.Action = 'C'
-- ADD NEW RECORDS
INSERT INTO #Tbl1 (Product, Code, Price)
SELECT Product, Code, Price
FROM #Tbl2
WHERE Action = 'A'
-- DELETE OLD RECORDS
UPDATE t1
SET t1.DateUpdated = GETDATE(),
t1.DateDeleted = GETDATE()
FROM #Tbl1 t1
LEFT JOIN #Tbl2 t2 ON t2.Product = t1.Product AND t2.Code = t1.Code
WHERE t2.Product IS NULL
SELECT * FROM #Tbl1
SELECT * FROM #Tbl2
DROP TABLE #Tbl1
DROP TABLE #Tbl2
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply