CODEWITHSUNDEEP
sqlsql-servert-sql
remh
remh

Reputation: 189

T-SQL, rows where a specific column does not change over a date range?

I have a data table structured like so:

ID        Date        purchaseType
01        03-01-18    apple
01        04-01-18    apple
02        05-01-18    spinach
01        05-01-18    apple
02        06-01-18    spinach
02        07-01-18    apple
...

I want to look at all Id's where the purchase type was the same over 3 months. That is to say, the results I would get from the above table would be:

ID    purchaseType    Length(months)
01    apple           3
...

and ID=02 is not included, as in the third month, the purchase type was switched to apple from spinach. I hope this makes sense!

Edit: There is always a record per month and ID, There should not be any duplicate records (That is to say, one purchase type, per ID, per month). It is always on the first of the month.

Edit2: I have tried doing something along the lines of 

select Min(Date) as 'Min', max(date) as 'Max',ID,purchaseType
From someTableName
GroupBy ID,purchasetype

but not sure where to take it from here

Edit 3: I don't need a specific date range. Just if for an ID:X, if there at anypoint existed a 3 month period where purchase type did not change.

Upvotes: 0

Views: 143

Answers (2)

Hogan
Hogan

Reputation: 70523

Based on the comments I understand the problem like this. You want to find 3 months of more with no gap and no product change. If a product was purchased there will exist a item in that month.

First you have gaps -- or hills and valleys. There is a trick to do this. You have two row numbers -- one based on increment every month and another on the value changing -- the difference of these two will give you "groups". Then you need to look at the max and the min per group.

prior answer

SELECT DISTINCT ID, PURCHASE_TYPE 
FROM (
  SELECT ID, PURCHASE_TYPE, 
      ROW_NUMBER() OVER (PARTITION BY ID, PURCHASE_TYPE ORDER BY DATE) AS RN
  FROM your_table_name_goes_here
) X
WHERE RN >= 3

Upvotes: 1

donPablo
donPablo

Reputation: 1959

Here is my contribution, for requirements known to date.

(I needed to change ID as string to Idnum as int.)

CREATE TABLE #my_table
    (     IDnum          INTEGER 
        , DATEp          DATE
        , PurchaseType   VARCHAR(10)    )

    INSERT INTO #my_table VALUES( '01', '03-01-18', 'apple'   );
    INSERT INTO #my_table VALUES( '01', '04-01-18', 'apple'   );
    INSERT INTO #my_table VALUES( '02', '05-01-18', 'spinach' );
    INSERT INTO #my_table VALUES( '01', '05-01-18', 'apple'   );
    INSERT INTO #my_table VALUES( '02', '06-01-18', 'spinach' );
    INSERT INTO #my_table VALUES( '02', '07-01-18', 'apple'   );

SELECT
    M1.IDnum
  , M1.PurchaseType
FROM #my_table AS M1
INNER JOIN #my_table AS M2
    ON M1.IDnum = M2.IDnum 
   AND DATEADD(MONTH, 1, M1.DATEp) = M2.DATEp
INNER JOIN #my_table AS M3
    ON M1.IDnum = M3.IDnum 
   AND DATEADD(MONTH, 2, M1.DATEp) = M3.DATEp
WHERE M1.PurchaseType = M2.PurchaseType 
  AND M1.PurchaseType = M3.PurchaseType
GROUP BY
    M1.IDnum
  , M1.PurchaseType

-- RESULT:
--         IDnum  PurchaseType
--           1      apple

Upvotes: 2

Related Questions