x

RowNumber, Rank, Min(date)

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

more ▼

asked Jul 22, 2011 at 03:06 PM in Default

siera_gld gravatar image

siera_gld
1k 78 84 85

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

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]
more ▼

answered Jul 22, 2011 at 04:23 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

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, 2011 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, 2011 at 02:06 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x11
x3

asked: Jul 22, 2011 at 03:06 PM

Seen: 1279 times

Last Updated: Jul 22, 2011 at 03:06 PM