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)
CardCode | ItemCode | From | To | Price |
---|---|---|---|---|
*12 | QMB01 | 200 | xxx | 5.60 |
*12 | QMB01 | 400 | xxx | 5.45 |
*12 | QMB02 | 200 | xxx | 5.60 |
*12 | QMB02 | 400 | xxx | 5.45 |
*13 | QMB01 | 200 | xxx | 5.16 |
*13 | QMB01 | 400 | xxx | 5.03 |
*13 | QMB02 | 200 | xxx | 5.16 |
*13 | QMB02 | 400 | xxx | 5.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.
3条答案
按热度按时间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 byAmount
. 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:
Specifically this is using the
LEAD()
function (because we want the next row) and getting the[Amount]
field offset by1
row and will returnNULL
if there is no row in the partition group. The offset is in the context of theOVER
clause which defines our partition group asCardCode,ItemCode
and then orders the rows byAmount
. To get 399 instead of 400 it then subtracts 1 (which will still giveNULL
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.
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)
agyaoht73#
Thanks for the help,
Code below shows how i used the "LEAD" Function to partition by both CardCode and ItemCode.