RowNumber, Rank, Min(date)

Question

I am trying to find the min date where the previous price was 0 - not all items have a previous prc being 0 -

I could use some guidance to figure this out....

    CREATE TABLE #TEST(
PRODUCT CHAR (4),
PRC MONEY,
EFF_DT DATETIME,
END_DT DATETIME)

INSERT INTO #TEST VALUES('1234',10,'2011-01-01','2011-01-03')
INSERT INTO #TEST VALUES('1234',20,'2011-01-04','2011-01-07')
INSERT INTO #TEST VALUES('1234',30,'2011-01-08','2011-01-14')
INSERT INTO #TEST VALUES('1234',0,'2011-01-15','2011-01-21')
INSERT INTO #TEST VALUES('1234',25,'2011-01-22','2011-01-28')
INSERT INTO #TEST VALUES('1234',35,'2011-01-29','2011-02-06')
INSERT INTO #TEST VALUES('5678',100,'2011-01-01','2011-01-03')
INSERT INTO #TEST VALUES('5678',200,'2011-01-04','2011-01-07')
INSERT INTO #TEST VALUES('5678',300,'2011-01-08','2011-01-14')
INSERT INTO #TEST VALUES('5678',300,'2011-01-15','2011-01-21')
INSERT INTO #TEST VALUES('5678',300,'2011-01-22','2011-01-28')
INSERT INTO #TEST VALUES('5678',300,'2011-01-29','2011-02-06')
INSERT INTO #TEST VALUES('3456',50,'2011-01-01','2011-01-03')
INSERT INTO #TEST VALUES('3456',50,'2011-01-04','2011-01-07')
INSERT INTO #TEST VALUES('3456',0,'2011-01-08','2011-01-14')
INSERT INTO #TEST VALUES('3456',50,'2011-01-15','2011-01-21')
INSERT INTO #TEST VALUES('3456',52,'2011-01-22','2011-01-28')
INSERT INTO #TEST VALUES('3456',53,'2011-01-29','2011-02-06')
INSERT INTO #TEST VALUES('6543',1,'2011-01-01','2011-01-03')
INSERT INTO #TEST VALUES('6543',2,'2011-01-04','2011-01-07')
INSERT INTO #TEST VALUES('6543',3,'2011-01-08','2011-01-14')
INSERT INTO #TEST VALUES('6543',4,'2011-01-15','2011-01-21')
INSERT INTO #TEST VALUES('6543',5,'2011-01-22','2011-01-28')
INSERT INTO #TEST VALUES('6543',6,'2011-01-29','2011-02-06')

SELECT ROW_NUMBER() OVER (PARTITION BY  PRC, EFF_DT,PRODUCT ORDER BY  PRC, EFF_DT,PRODUCT  ) AS rn
,PRODUCT 
,PRC 
,EFF_DT 
,END_DT

FROM #TEST

so in the example the date i want for 3456 is 1-15-2011

Answer 1

0

If you add this to your script does it give the results you need ?

SELECT  ROW_NUMBER() OVER ( PARTITION BY PRODUCT ORDER BY EFF_DT, PRC, PRODUCT ) AS rn ,
        PRODUCT ,
        PRC ,
        EFF_DT ,
        END_DT
INTO    #Base01
FROM    #TEST  

SELECT  [b].[rn] ,
        [b].[PRODUCT] ,
        [b].[PRC] ,
        [b].[EFF_DT] ,
        [b].[END_DT]
FROM    [#Base01] AS b
        INNER JOIN [#Base01] AS b2 ON [b].[PRODUCT] = [b2].[PRODUCT]
                                      AND [b].[rn] = [b2].[rn] + 1
WHERE   [b2].[PRC] = 0
ORDER BY [b].[PRODUCT]

answered Jul 22 '11 at 04:23 PM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

FatherJack - My apologies for not getting back to you but Yes - it does

somewhat - what about other items that did not have 0 prc in the amount (5678) for example

Jul 26 '11 at 11:57 AM siera_gld

@siera_gld If you simply need the product to show up in the results but show NULL for PRC, EFF_DT and END_DT (as these should because they don't satisfy the [b2].[PRC] = 0 condition then you can simply replace the inner join with left join and move the PRC = 0 to the join condition, i.e.

from [#Base01] as b left [#Base01] as b2 
    on [b].[PRODUCT] = [b2].[PRODUCT]
    and [b].[rn] = [b2].[rn] + 1
    and [b2].[PRC] = 0;

Jul 26 '11 at 02:06 PM Oleg

(comments are locked)

RowNumber, Rank, Min(date)

Follow this question

Related Questions