SQL Server SQL Query to include values from the next line based on a condition?

dced5bon  于 2023-03-28  发布在  其他
关注(0)|答案(3)|浏览(153)

I use SAP B1 running on SQL Server, I'm currently working on an integration which brings price breaks through from SAP to a Wordpress website.

I need to include a "Start" and "End" Quantity for the price break unfortunately SAP does not use an end, it simply knows when to stop when it reaches the next limit... The website integration on the other hand doesn't.

a simplified version of the code which I'm integrating is shown below.

SELECT 
T2.CardCode,
T2.ItemCode,
T2.Amount AS 'From',
'xxx' AS 'To',
T2.Price

FROM SPP2 T2

WHERE T2.ItemCode IN ('QMB01','QMB02')

AND T2.CardCode IN ('*12','*13')

This code returns the following results : (SAMPLE DATA)

CardCodeItemCodeFromToPrice
*12QMB01200xxx5.60
*12QMB01400xxx5.45
*12QMB02200xxx5.60
*12QMB02400xxx5.45
*13QMB01200xxx5.16
*13QMB01400xxx5.03
*13QMB02200xxx5.16
*13QMB02400xxx5.03

My Desired SAMPLE DATA

Desired sample data would be.
| CardCode | ItemCode | From | To | Price |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| *12 | QMB01 | 200 | 399 | 5.60 |
| *12 | QMB01 | 400 | | 5.45 |
| *12 | QMB02 | 200 | 399 | 5.60 |
| *12 | QMB02 | 400 | | 5.45 |
| *13 | QMB01 | 200 | 399 | 5.16 |
| *13 | QMB01 | 400 | | 5.03 |
| *13 | QMB02 | 200 | 399 | 5.16 |
| *13 | QMB02 | 400 | | 5.03 |

The goal I need to achieve is filling in the "To" field. This "To" field will be the line below minus 1. When the below conditions are met.

When T2.CardCode and T2.ItemCode FROM Line 1 are the same as T2.CardCode and T2.ItemCode from Line 2... Return the value "From - 1" (from line 2) If this makes sense?

If this doesn't meet the criteria essentially this price break doesn't have an upper limit, so it just needs to be blank?

Any help is really appreciated on this... I've spent 2 full days on this integration to get it to work seamlessly and I did successfully do it... Now the goal post has been massively changed by putting this spanner in the works!

Many Thanks

Andy

Full details are listed above, but i'm basically trying to return values from Row 2, to show into Row 1, based on a comparison between these two rows.

mqkwyuun

mqkwyuun1#

You can use the LAG and LEAD analytic functions to access fields from relatively positioned rows. You just need to be sure that you are ordering the data correctly. From what you've shown it looks like you are partitioning the counts by CardCode,ItemCode and ordering by Amount . You can then use that in the OVER clause when using the analytic function. (They are also called a window functions.)

Specifically this will get the results you are after:

SELECT T2.CardCode,
  T2.ItemCode,
  T2.Amount AS 'From',
  LEAD([Amount], 1, NULL) OVER(PARTITION BY T2.CardCode, T2.ItemCode ORDER BY T2.Amount)-1 AS 'To',
  T2.Price
FROM SPP2 T2
WHERE T2.ItemCode IN ('QMB01','QMB02')
  AND T2.CardCode IN ('*12','*13');

Specifically this is using the LEAD() function (because we want the next row) and getting the [Amount] field offset by 1 row and will return NULL if there is no row in the partition group. The offset is in the context of the OVER clause which defines our partition group as CardCode,ItemCode and then orders the rows by Amount . To get 399 instead of 400 it then subtracts 1 (which will still give NULL for empty results.)

But do double-check the ordering and final values. You should be able to get it to work how you need it to.

cygmwpex

cygmwpex2#

What version of SQL Server are you using? An easy way of doing this may be the lead function .

Outside of that you can do a self join on CardCode and ItemCode where From in the first table is less than from in the second table. Something like this (using a temp table as an intermediary here, but you could do it on the base tables if you want)

SELECT 
    T2.CardCode,
    T2.ItemCode,
    T2.Amount AS 'From',
    'xxx' AS 'To',
    T2.Price
    INTO #temp
    FROM SPP2 T2
    WHERE T2.ItemCode IN ('QMB01','QMB02')
    AND T2.CardCode IN ('*12','*13')

select t1.CardCode
, t1.ItemCode
, t1.[From]
, t2.[From] - 1 as [To]
, t1.Price
from #temp t1
        left join #temp t2 on t1.CardCode = t2.CardCode 
                                and t1.ItemCode = t2.ItemCode
                                and t1.[From] < t2.[From]
agyaoht7

agyaoht73#

Thanks for the help,

Code below shows how i used the "LEAD" Function to partition by both CardCode and ItemCode.

LEAD (T2.Amount,1) OVER (PARTITION BY T2.CardCode, T2.ItemCode ORDER BY T2.ItemCode) -1 AS V5B

相关问题