CODEWITHSUNDEEP
sql-servert-sqldatesql-update
Chris Kotsiopoulos
Chris Kotsiopoulos

Reputation: 57

SQL Server UPDATE based on previous date

This is about a list of products (prodID) for a number of customers (custID) fetched daily from the web. The problem is that sometimes when a product is unavailable, the page redirects to a similar product and we end up fetching wrong information. So the custID and prodID are correct but price, seller and title are wrong. The correct title is in masterTitle column.

I need to flag the wrong rows as notAvailable = 1.

Note that in some cases, title is different comparing to masterTitle but this is valid as it may be a simple title rename. For example 350 ml - Product 01 = Product 01 - 350 ml.

I'm familiar with C# but I would prefer to do this using T-SQL. The way I imagine it, I need to compare each day with the previous 'last good day' (last date having valid data) for each custID/prodID.

Pseudocode. For each record:

If title = masterTitle then 
{
Keep current values (title, price, seller) as lastGoodValues
}
else
If lastGoodValues.price <> nextDay.price AND lastGoodValues.seller <> nextDay.seller
{
notAvailable = 1
}
move to the next record/day

So, I have this:

ReportDate | custID | prodID | price |  sellers |        title          | masterTitle         | notAvail
-----------+--------+--------+-------+----------+-----------------------+---------------------+---------
16/04/2020 | 266    | 191750 | 15.59 | Seller01 | Product 01 - 350 ml   | Product 01 - 350 ml | 0
17/04/2020 | 266    | 191750 | 15.59 | Seller01 | 350 ml - Product 01   | Product 01 - 350 ml | 0
18/04/2020 | 266    | 191750 |    18 | Seller02 | Procuct 02 - 1 Litres | Product 01 - 350 ml | 0
19/04/2020 | 266    | 191750 |    18 | Seller02 | Procuct 02 - 1 Litres | Product 01 - 350 ml | 0
20/04/2020 | 266    | 191750 | 15.59 | Seller01 | Product 01 - 350 ml   | Product 01 - 350 ml | 0
21/04/2020 | 266    | 191750 |    18 | Seller01 | Procuct 02 - 1 Litres | Product 01 - 350 ml | 0
22/04/2020 | 266    | 191750 | 15.59 | Seller01 | Product 01 - 350 ml   | Product 01 - 350 ml | 0
18/04/2020 | 301    |    565 |     5 | Seller Y | Procuct Y             | Product X           | 0
19/04/2020 | 301    |    565 |     8 | Seller X | Product - X           | Product X           | 0
20/04/2020 | 301    |    565 |     8 | Seller X | Product X             | Product X           | 0
21/04/2020 | 301    |    565 |     5 | Seller Y | Procuct Y             | Product X           | 0
22/04/2020 | 301    |    565 |     8 | Seller X | Product X             | Product X           | 0

...and I need this (see last column - notAvail):

ReportDate | custID | prodID | price |  sellers |        title          | masterTitle         | notAvail
-----------+--------+--------+-------+----------+-----------------------+---------------------+---------
16/04/2020 | 266    | 191750 | 15.59 | Seller01 | Product 01 - 350 ml   | Product 01 - 350 ml | 0
17/04/2020 | 266    | 191750 | 15.59 | Seller01 | 350 ml - Product 01   | Product 01 - 350 ml | 0
18/04/2020 | 266    | 191750 |    18 | Seller02 | Procuct 02 - 1 Litres | Product 01 - 350 ml | 1
19/04/2020 | 266    | 191750 |    18 | Seller02 | Procuct 02 - 1 Litres | Product 01 - 350 ml | 1
20/04/2020 | 266    | 191750 | 15.59 | Seller01 | Product 01 - 350 ml   | Product 01 - 350 ml | 0
21/04/2020 | 266    | 191750 |    18 | Seller01 | Procuct 02 - 1 Litres | Product 01 - 350 ml | 1
22/04/2020 | 266    | 191750 | 15.59 | Seller01 | Product 01 - 350 ml   | Product 01 - 350 ml | 0
18/04/2020 | 301    |    565 |     5 | Seller Y | Procuct Y             | Product X           | 1
19/04/2020 | 301    |    565 |     8 | Seller X | Product - X           | Product X           | 0
20/04/2020 | 301    |    565 |     8 | Seller X | Product X             | Product X           | 0
21/04/2020 | 301    |    565 |     5 | Seller Y | Procuct Y             | Product X           | 1
22/04/2020 | 301    |    565 |     8 | Seller X | Product X             | Product X           | 0

Upvotes: 0

Views: 60

Answers (2)

Chris Kotsiopoulos
Chris Kotsiopoulos

Reputation: 57

Thanks Raznan. It worked with a couple of minor modifications: Fiddle available here.

MERGE INTO @LastGoodValues L
USING (
    SELECT * FROM @YourTable
    WHERE title=masterTitle
) T ON L.title=T.title
WHEN MATCHED THEN UPDATE SET reportDate = T.reportDate, custID = T.custID, prodID = T.prodID, price=T.price, seller=T.seller
WHEN NOT MATCHED THEN INSERT (reportDate, custID, prodID, title, price, seller) VALUES (T.reportDate, T.custID, T.prodID, T.title, T.price, T.seller);


UPDATE @YourTable SET notAvailable=1
FROM @YourTable T
INNER JOIN @LastGoodValues L ON L.custID=T.custID and L.prodID = T.prodID
WHERE T.title<>T.masterTitle
AND L.price<>T.price AND L.seller<>T.seller

Upvotes: 0

Razvan Socol
Razvan Socol

Reputation: 5694

If you have another table named lastGoodValues, you could try something like this (untested):

MERGE INTO LastGoodValues L
USING (
    SELECT * FROM YourTable
    WHERE title=masterTitle
) T ON L.title=T.title
WHEN MATCHED THEN UPDATE SET price=T.price, seller=T.seller
WHEN NOT MATCHED THEN INSERT (title, price, seller) VALUES (T.title, T.price, T.seller);

UPDATE YourTable SET notAvailable=1
FROM YourTable T
INNER JOIN LastGoodValues L ON L.title=T.title
WHERE T.title<>T.masterTitle
AND L.price<>T.price AND L.seller<>T.seller

Upvotes: 1

Related Questions