CODEWITHSUNDEEP
sqlsql-servert-sql
RobB
RobB

Reputation: 33

SQL get value from next row

I'm looking for an SQL way to get the value from the next row.

The data I have looks like:

CUST    PROD     From_Qty    Disc_Pct
23      Brush    1           0
23      Brush    13          1
23      Brush    52          4
77      Paint    1           0
77      Paint    22          7

What I need to end up with is this, (I want to create the To_Qty row):

CUST    PROD     From_Qty    To_Qty    Disc_Pct
23      Brush    1           12        0
23      Brush    13          51        1              #13 is 12+1
23      Brush    52          99999     4              #52 is 51+1
77      Paint    1           21        0              #1  is 99999+1
77      Paint    22          99999     7              #22 is 21+1

I've got 100K+ rows to do this to, and it has to be SQL because my ETL application allows SQL but not stored procedures etc.

How can I get the value from the next row so I can create To_Qty?

Upvotes: 3

Views: 17478

Answers (6)

vinoth raj
vinoth raj

Reputation: 71

SELECT
 CUST, 
PROD, 
FROM_QTY , 
COALESCE(MIN(FROM_QTY) OVER (PARTITION BY CUST, PROD  ORDER BY FROM_QTY DESC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) , 10000)-1, 
DISC_PCT
 FROM <tablename>
 ORDER BY CUST, PROD, FROM_QTY

Upvotes: 1

JamieD77
JamieD77

Reputation: 13949

SELECT  *,
        LEAD([From_Qty], 1, 100000) OVER (PARTITION BY [CUST] ORDER BY [From_Qty]) - 1 AS To_Qty
FROM    myTable

LEAD() will get you the next value based on the order of [From_Qty].. you use PARTITION BY [CUST] to reset when [Cust] changes values

or you can use a CTE and Row_Number.

WITH cte AS 
(
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY [CUST] ORDER BY [From_Qty]) Rn
    FROM    myTable
)
SELECT  t1.*,
        ISNULL(t2.From_Qty - 1, 99999) To_Qty
FROM    cte t1
        LEFT JOIN cte t2 ON t1.Cust = t2.Cust AND t1.Rn + 1 = t2.Rn

Upvotes: 6

Ahmed Saeed
Ahmed Saeed

Reputation: 851

declare  @Table table(CUST int, PROD varchar(50), From_Qty int, Disc_Pct int)
insert into @Table values
(23, 'Brush',    1,      0)
,(23, 'Brush',    13,     1)
,(23, 'Brush',    52,     4)
,(77, 'Paint',    1,      0)
,(77, 'Paint',    22,     7)

SELECT  CUST, Prod, From_qty, 
        LEAD(From_Qty,1,100000) OVER(PARTITION BY cust ORDER BY from_qty)-1 AS To_Qty,
        Disc_Pct
  FROM @Table

Upvotes: 0

Teja
Teja

Reputation: 13534

You can use LEAD and FIRST_VALUE analytic functions to generate the result you mentioned. By using LEAD() function the next value with in the customer group can be retrieved an the FIRST_VALUE() will give the first value with in the customer group.

Say for eg. CUST=23... LEAD will return 13 and FIRST_VALUE will return 1... TO_QTY= LEAD - FIRST_VALUE i.e.. 13-1=12. In similar way the formula mentioned below will compute for all the 100k rows in your table.

    SELECT CUST,
           PROD,
           FROM_QTY,
           CASE WHEN LEAD( FROM_QTY,1 ) OVER ( PARTITION BY CUST ORDER BY FROM_QTY ) IS NOT NULL 
                THEN
           LEAD( FROM_QTY,1 ) OVER ( PARTITION BY CUST ORDER BY FROM_QTY ) -
 FIRST_VALUE( FROM_QTY ) OVER ( PARTITION BY CUST ORDER BY FROM_QTY )
                ELSE 99999
            END AS TO_QTY,
           DISC_PCT   
      FROM Yourtable;

Upvotes: 1

Will Rickards
Will Rickards

Reputation: 2786

Insert the data into a temp table with the same columns but an id auto increment field added. Insert them ordered, I'm assuming by cust, prod, then from_qty. Now you can run an update statement on the temp table.

UPDATE #mytable
SET To_Qty = (SELECT From_Qty - 1 FROM #mytable AS next WHERE next.indexfield = #mytable.indexfield + 1 AND next.cust = #mytable.cust and next.prod = #mytable.prod)

and then another one to do the 99999 with a not exists clause.

Then insert the data back to your new or modified table.

Upvotes: 0

Sebz
Sebz

Reputation: 502

If you are running SQL Server 2012 or later versions, you can use the LAG and LEAD functions for accessing prior or subsequent rows along with the current row.

Upvotes: 1

Related Questions