June 9, 2015 at 1:51 pm
I have a product table and my task is to select only the products that had been paid for, but later their status has been changed.
I need to report the original paid date, the most recent status, and most recent updated date. Here is the sample table:
CREATE TABLE #Products (primKey int, productId int, productName varchar(100), productStatus varchar(50), logDate datetime )
Insert into #Products(primKey, productId, productName, productStatus, logDate)
Values
(1, 201, 'pen', 'received', '01/01/2011'),
(2, 201, 'pen', 'sold', '01/02/2011'),
(3, 201, 'pen', 'paid', '01/03/2011'),
(4, 201, 'pen', 'returned', '01/04/2011'),
(5, 201, 'pen', 'refurbished', '01/05/2011'),
(6, 202, 'pencil', 'received', '01/06/2011'),
(7, 202, 'pencil', 'sold', '01/07/2011'),
(8, 202, 'pencil', 'paid', '01/08/2011'),
(9, 201, 'pen', 'sold', '01/09/2011'),
(10, 201, 'pen', 'paid', '01/10/2011')
/* temp table records */
Select * From #Products order by productId
/* The desired outcomes would be showing only the Record 3 and 10.
This is a "fake" query to get the results:
*/
Select * From #Products Where primKey in (3, 10) order by productId
June 9, 2015 at 2:30 pm
Hmm... well there's only one date in that table, how do you plan to get the original paid date and the most recent change date? And based on the expected results both the ones listed have a status of paid, weren't the requirements records whose status was paid but is now changed?
June 9, 2015 at 2:44 pm
Why should 10 be selected? Its status is paid, but that status has not changed.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 9, 2015 at 3:03 pm
Alvin Ramard (6/9/2015)
Why should 10 be selected? Its status is paid, but that status has not changed.
How are you determining whether or not the status value has changed? This question is also good for the original poster, as it is key to the solution.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 10, 2015 at 6:36 am
Sorry for the confusion. Let me try my best to clarify the question a little.
Some products we sold were returned by customers, and we'd refurbish them and put them on shelf again. In the original temp table, the first PAID status is considered as initial completed transaction. In theory, a product can be sold and returned multiple times. The management would like to know that once a product is sold and paid, what is the most recent status for that product, which could be anything ranging from RECEIVED to PAID.
The issue is due to that in the original DB design, there is no RETURNED status.
Thanks all.
June 10, 2015 at 7:40 am
jay-125866 (6/10/2015)
Sorry for the confusion. Let me try my best to clarify the question a little.Some products we sold were returned by customers, and we'd refurbish them and put them on shelf again. In the original temp table, the first PAID status is considered as initial completed transaction. In theory, a product can be sold and returned multiple times. The management would like to know that once a product is sold and paid, what is the most recent status for that product, which could be anything ranging from RECEIVED to PAID.
The issue is due to that in the original DB design, there is no RETURNED status.
Thanks all.
I'm not sure this is going to work, but take a look at the query and the result:
CREATE TABLE #Products (primKey int, productId int, productName varchar(100), productStatus varchar(50), logDate datetime )
Insert into #Products(primKey, productId, productName, productStatus, logDate)
Values
(1, 201, 'pen', 'received', '01/01/2011'),
(2, 201, 'pen', 'sold', '01/02/2011'),
(3, 201, 'pen', 'paid', '01/03/2011'),
(4, 201, 'pen', 'returned', '01/04/2011'),
(5, 201, 'pen', 'refurbished', '01/05/2011'),
(6, 202, 'pencil', 'received', '01/06/2011'),
(7, 202, 'pencil', 'sold', '01/07/2011'),
(8, 202, 'pencil', 'paid', '01/08/2011'),
(9, 201, 'pen', 'sold', '01/09/2011'),
(10, 201, 'pen', 'paid', '01/10/2011')
SELECT P.*
FROM #Products AS P
LEFT OUTER JOIN #Products AS P2
ON P.productId = P2.productId
AND P.primKey <> P2.primKey
AND P.logDate < P2.logDate
AND P2.productStatus = 'paid'
LEFT OUTER JOIN #Products AS P3
ON P.productId = P3.productId
AND P.primKey <> P3.primKey
AND P.logDate > P3.logDate
AND P3.productStatus = 'paid'
WHERE P.productStatus = 'paid'
AND COALESCE(P2.productId, P3.productId) IS NOT NULL;
DROP TABLE #Products;
What do you do when you sell more than one pen ? Does a product sold have a serial number or other unique identifier? ProductId might just indicate that you sold a pen, but you might sell them 100 at a time...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 10, 2015 at 7:50 am
I hope this is a homework question. I have trouble imagining the data presented as realistic.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 10, 2015 at 8:58 am
Thank you for your reply, Steve.
I've tried your suggestion, and it didn't work for me. I think your query is somehow based on status, so if I remove the last row in sample table, it returns nothing.
June 10, 2015 at 9:16 am
jay-125866 (6/9/2015)
...select only the products that had been paid for, but later their status has been changed.I need to report the original paid date, the most recent status, and most recent updated date
That would be rows 3, 8 and 10.
Edit: scrub that, it's 3 and 10.
jay-125866 (6/10/2015)
...In the original temp table, the first PAID status is considered as initial completed transaction. In theory, a product can be sold and returned multiple times. The management would like to know that once a product is sold and paid, what is the most recent status for that product, which could be anything ranging from RECEIVED to PAID...
An accurate and complete specification isn't necessary to get the job done but it does save a lot of time. This query satisfies your first requirement and kinda satisfies your second, maybe:
SELECT primKey, productId, productName, productStatus, logDate
FROM #Products
WHERE productStatus = 'paid'
UNION
SELECT primKey, productId, productName, productStatus, logDate
FROM (SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY productId ORDER BY logDate DESC) FROM #Products) d
WHERE rn = 1
ORDER BY productId, logDate
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
June 10, 2015 at 9:40 am
Try this:
;WITH PaidProducts AS (
SELECT *,
rn1 = ROW_NUMBER() OVER (PARTITION BY productId ORDER BY logDate DESC),
rn2 = CASE ProductStatus WHEN 'paid' THEN ROW_NUMBER() OVER (PARTITION BY productId, ProductStatus ORDER BY logDate) ELSE NULL END
FROM #Products
)
SELECT primKey, productId, productName, productStatus, logDate
FROM PaidProducts p
WHERE (rn1 = 1 OR rn2 = 1)
AND rn1 <> rn2
ORDER BY productId, logDate
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
June 10, 2015 at 10:22 am
Thank you, Chris. Your solution works about right.
The issue remaining is that the Product Pencil is also shown in the result. It's not supposed to be there since there is no actions after initial completed sales.
Again, thanks to all!
June 10, 2015 at 7:34 pm
jay-125866 (6/10/2015)
Thank you, Chris. Your solution works about right.The issue remaining is that the Product Pencil is also shown in the result. It's not supposed to be there since there is no actions after initial completed sales.
Again, thanks to all!
I think I now understand what you're looking for, so here's an updated attempt that produces the correct result:
CREATE TABLE #Products (primKey int, productId int, productName varchar(100), productStatus varchar(50), logDate datetime )
Insert into #Products(primKey, productId, productName, productStatus, logDate)
Values
(1, 201, 'pen', 'received', '01/01/2011'),
(2, 201, 'pen', 'sold', '01/02/2011'),
(3, 201, 'pen', 'paid', '01/03/2011'),
(4, 201, 'pen', 'returned', '01/04/2011'),
(5, 201, 'pen', 'refurbished', '01/05/2011'),
(6, 202, 'pencil', 'received', '01/06/2011'),
(7, 202, 'pencil', 'sold', '01/07/2011'),
(8, 202, 'pencil', 'paid', '01/08/2011'),
(9, 201, 'pen', 'sold', '01/09/2011'),
(10, 201, 'pen', 'paid', '01/10/2011');
WITH PAID AS (
SELECT P.*
FROM #Products AS P
WHERE P.productStatus = 'paid'
AND EXISTS (
SELECT 1
FROM #Products AS P2
WHERE P2.productId = P.productId
AND P2.primKey <> P.primKey
AND P2.logDate > P.logDate
)
)
SELECT *
FROM PAID
UNION ALL
SELECT P3.*
FROM #Products AS P3
INNER JOIN PAID AS P1
ON P3.productId = P1.productId
AND P3.primKey <> P1.primKey
AND P3.logDate = (
SELECT MAX(P4.logDate)
FROM #Products AS P4
WHERE P4.productId = P1.productId
AND P4.primKey <> P1.primKey
AND P4.logDate > P1.logDate);
DROP TABLE #Products;
Let me know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 10, 2015 at 10:16 pm
This might work too using sgmunson's setup data:
WITH AddRowNumber AS
(
SELECT *
,rn1=ROW_NUMBER() OVER (PARTITION BY productid ORDER BY logDate DESC)
FROM #Products
),
StatusAfterPaid AS
(
SELECT productId
FROM
(
SELECT productId
,rn2=rn1 -
ROW_NUMBER() OVER
(
PARTITION BY productid
ORDER BY CASE ProductStatus WHEN 'paid' THEN 0 ELSE 1 END, logDate DESC
)
FROM AddRowNumber
) a
WHERE rn2 < 0
GROUP BY productID
)
SELECT b.*
FROM StatusAfterPaid a
CROSS APPLY
(
SELECT *
FROM AddRowNumber b
WHERE a.productID = b.productID AND
(rn1 = 1 OR productStatus = 'paid')
) b;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 11, 2015 at 1:54 am
jay-125866 (6/10/2015)
Thank you, Chris. Your solution works about right.The issue remaining is that the Product Pencil is also shown in the result. It's not supposed to be there since there is no actions after initial completed sales.
Again, thanks to all!
Try the second one.
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
June 12, 2015 at 9:27 am
This is great, Thanks, Steve!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply