October 13, 2015 at 1:23 pm
Table definition
Product_ID identity 1,1
Product_Purchased varchar(25)
1 Apple
2 Pear
3 Banana
4 Apple
5 Peach
6 Pear
7 Peach
8 Apple
I am trying to write a query that will go through the data and tell me the amount of products purchased before Apple is purchased again.
Example
1 1 Apple
4 2 Apple
8 3 Apple
October 13, 2015 at 1:44 pm
October 13, 2015 at 1:49 pm
jeffrey.sharp (10/13/2015)
Table definitionProduct_ID identity 1,1
Product_Purchased varchar(25)
1 Apple
2 Pear
3 Banana
4 Apple
5 Peach
6 Pear
7 Peach
8 Apple
I am trying to write a query that will go through the data and tell me the amount of products purchased before Apple is purchased again.
Example
1 1 Apple
4 2 Apple
8 3 Apple
Quick solution
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE TABLE
(
Product_ID INT identity( 1,1) NOT NULL
,Product_Purchased varchar(25) NOT NULL
);
INSERT INTO @SAMPLE(Product_Purchased)
VALUES ('Apple' )
,('Pear' )
,('Banana')
,('Apple' )
,('Peach' )
,('Pear' )
,('Peach' )
,('Apple' )
;
;WITH BASE_DATA AS
(
SELECT
SD.Product_ID
,ROW_NUMBER() OVER
(
PARTITION BY SD.Product_Purchased
ORDER BY SD.Product_ID
) AS SD_RID
,SD.Product_Purchased
FROM @SAMPLE SD
)
SELECT
BD.Product_ID
,(BD.Product_ID - ISNULL(B2.Product_ID,0)) AS PCOUNT
,BD.Product_Purchased
FROM BASE_DATA BD
LEFT OUTER JOIN BASE_DATA B2
ON BD.Product_Purchased = B2.Product_Purchased
AND BD.SD_RID = B2.SD_RID + 1
WHERE BD.Product_Purchased = 'Apple'
;
Results
Product_ID PCOUNT Product_Purchased
----------- ----------- -------------------
1 1 Apple
4 3 Apple
8 4 Apple
October 13, 2015 at 1:50 pm
g.britton (10/13/2015)
Shouldn't your example output be:1 0 Apple
4 2 Apple
8 3 Apple
Guess it's "before and including"
😎
October 13, 2015 at 2:29 pm
Since my table and data already existed I have been working with the query. At this point my result set based on the code help shows....
1 1 Apple
3 2 Apple
8 7 Apple
As if it is always counting from the first level.
Still plugging away but if any input feel free..
October 15, 2015 at 8:30 am
Thank you very much for the assistance. After working with the query properly and comparing side by side your query worked perfectly.. Once again thank you... :hehe:
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply