Script Help - Selecting last record
-
Hello everybody!
I have the need to bring the past records of certain releases.
It is + / - so ... I need the last record entry of an item to perform the calculation of freight cost.
A friend helped me with a script that at first looked like it was correct, but for some items did not work.
But I saw some things and need help to understand why ... follows the script I'm using:
SELECT I.ITEMID ITEM, I.INVOICEID NF, I.TRANSREFID OC, RECENTES.FINALIDADE FINALIDADE,convert(decimal(10,2),I.COSTAMOUNTPOSTED/RECENTES.QTD) VALOR_RATEADO
FROM INVENTTRANS I INNER JOIN
(SELECT MAX(PL.PURCHID) AS PURCHID, PL.ITEMID AS ITEMID, PL.DATAAREAID,SUM(PL.QTYORDERED)QTD, PL.DIMENSION3_ FINALIDADE
FROM PURCHLINE PL GROUP BY PL.ITEMID, PL.DATAAREAID, PL.DIMENSION3_) RECENTES ON
I.ITEMID = RECENTES.ITEMID AND I.TRANSREFID = RECENTES.PURCHID AND I.DATAAREAID = RECENTES.DATAAREAID
WHERE
I.QTY = 0
AND RECENTES.QTD > 0
AND I.TRANSREFID NOT LIKE 'EST%'
AND I.ITEMID = '002166'
group by I.TRANSREFID, I.ITEMID, I.INVOICEID, I.DATAAREAID, I.COSTAMOUNTPOSTED, RECENTES.FINALIDADE, RECENTES.QTD
ORDER BY 3
With this script I can not bring you the latest transactions from all the stores, bring almost all, but there are still some ...
If I put in the PL.PURCHID Group By column, it brings the missing, but then not only the latest transactions.
He should bring 32 lines (32 branches) ...
If not PL.PURCHID Troop, it brings me 21 lines. If I Group, he brings 1310.
What can I do?
Sorry my english.
-
Without some sample data to work with, I'm guessing here, and I suspect you have an INNER JOIN where you need a LEFT OUTER JOIN. Also, your query formatting is pretty tough to read, so I've made it a little more formal and readable:
SELECT I.ITEMID AS ITEM,
I.INVOICEID AS NF,
I.TRANSREFID AS OC,
RECENTES.FINALIDADE,
CONVERT(decimal(10,2), I.COSTAMOUNTPOSTED/RECENTES.QTD) AS VALOR_RATEADO
FROM INVENTTRANS I
LEFT OUTER JOIN (
SELECT MAX(PL.PURCHID) AS PURCHID, PL.ITEMID AS ITEMID, PL.DATAAREAID,
SUM(PL.QTYORDERED) AS QTD, PL.DIMENSION3_ AS FINALIDADE
FROM PURCHLINE PL
GROUP BY PL.ITEMID, PL.DATAAREAID, PL.DIMENSION3_
) AS RECENTES
ON I.ITEMID = RECENTES.ITEMID
AND I.TRANSREFID = RECENTES.PURCHID
AND I.DATAAREAID = RECENTES.DATAAREAID
WHERE I.QTY = 0
AND RECENTES.QTD > 0
AND I.TRANSREFID NOT LIKE 'EST%'
AND I.ITEMID = '002166'
GROUP BY I.TRANSREFID, I.ITEMID, I.INVOICEID, I.DATAAREAID, I.COSTAMOUNTPOSTED, RECENTES.FINALIDADE, RECENTES.QTD
ORDER BY 3
Let me know if that does the trick, and if not, you're going to need to provide table creates for those tables and some sample data plus the expected results. You should also try and explain what a record in each of the source tables represents.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Friend, thanks for the help ...
Tested with LEFT OUTER JOIN and the result was the same.
See this script below the result that I have two tables ...
Below is a picture of 2 items I test for comparison ... the same purchase order, item 002166 does not come in the script when no Troop, but item 001089 brings.
This helps?
Thank you very much.
-
I'm afraid that doesn't make things any clearer. Remember, I can't see these tables and I don't know what you want for a result. The words you are using aren't making it at all clear. One thing you said in your first post was the word "recent", which also appears in a table alias, yet there doesn't appear to be any logic in the query to examine a date or datetime field. When that kind of ambiguity exists, I have to go back to the original poster and ask for very specific details. Be complete and spell it out with every necessary detail. Provide table create statements for the tables involved, and provide sample data for the contents of the tables, so that we have some shot at helping you. As we can't just pick up the phone and call you, you need to work a little harder in explaining what your task is, and what the exact conditions are that the solution must meet.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
I need to show the last transaction of purchase of all items (Itemid).
This, separated by company (Dimension3_).
Each purchase order (PL.Purchid) generates a transaction in stock (I.TransrefId).
What I could not understand is why when I use Group By it does not return the result and when not to use Group By it returns, but in this case, it not only brings the last transaction by company ...
Unable to explain?
Thank you.
-
matfurrier (7/11/2014)
I need to show the last transaction of purchase of all items (Itemid).This, separated by company (Dimension3_).
Each purchase order (PL.Purchid) generates a transaction in stock (I.TransrefId).
What I could not understand is why when I use Group By it does not return the result and when not to use Group By it returns, but in this case, it not only brings the last transaction by company ...
Unable to explain?
Thank you.
Ok, but again, you used a word that refers to time. You said "show the last transaction ...". There is no date logic in your query, so how, exactly, does one determine the LAST transaction ? Using GROUP BY doesn't automatically determine such a thing. You would have to use a MAX aggregate within a GROUP BY to determine the last transaction date for a given grouping, and then take records from that table that match the group values and the MAX date determined. Does that make sense?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
For me, the last transaction is not defined by date but by purchase order number.
The order with the highest number is the last created.
The numeric sequence of purchase orders (PL.Purchid) is for all 32 companies.
I do not use data, but the number of the purchase order to determine the last transaction (which is the latest).
Thank you!
-
matfurrier (7/11/2014)
For me, the last transaction is not defined by date but by purchase order number.The order with the highest number is the last created.
The numeric sequence of purchase orders (PL.Purchid) is for all 32 companies.
I do not use data, but the number of the purchase order to determine the last transaction (which is the latest).
Thank you!
Ok, now we're getting somewhere. The RECENTES.QTD field has to be greater than 0, or that data will not appear in the results, so you could be missing data for stores where that item hasn't sold. Take a close look at the other WHERE clause restrictions to be sure you aren't eliminating data that you need to see...
As a previous post indicated you are looking for the last transaction, be aware that using the group by in the subquery is taking the SUM of ALL the order quantities within an item, dataareaid, and company, and presents this data along with the most recent purchase order id. I'm not sure that is what you want. If you want only the data from the most recent purchase order within that grouping, then you first need to derive the purchase order id, and then only select data for that specific id.
Also, I'm wondering about the order in which the fields appear in your final GROUP BY clause. Wouldn't a company tend to be at the beginning of such a grouping ? Just asking...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Thank you Steve.
I know this is somewhat complex's what I need.
This calculation I am doing is to apportion the value of freight.
In our system (Dynamics AX 2009), the freight charge is launched at the item purchase order.
If you look at the picture above that a select tables might understand me.
The purchase order is the line with the item and quantity.
The transaction has the stock value of the item and shipping fees.
See that freight is with quantity = zero.
The idea is that I take all the items that have that quantity = 0 (it is shipping) and see what was your last purchase order.
I need:
freight value / quantity of the item.
You see?
Many thanks for the help.
I'm really sorry for my english.
-
SELECT I.ITEMID AS ITEM,
I.INVOICEID AS NF,
I.TRANSREFID AS OC,
RECENTES.FINALIDADE,
CONVERT(decimal(10,2), I.COSTAMOUNTPOSTED/RECENTES.QTD) AS VALOR_RATEADO
FROM INVENTTRANS I
LEFT OUTER JOIN (
SELECT PL.ITEMID, PL.PURCHID, PL.DATAAREAID, PL.DIMENSION3_ AS [FINALIDADE],
SUM(PL.QTYORDERED) AS [QTD],
ROW_NUMBER() OVER (
PARTITION BY PL.ITEMID, PL.DATAAREAID, PL.DIMENSION3_
ORDER BY PL.PURCHID DESC) AS [RowID]
FROM PURCHLINE PL
GROUP BY PL.ITEMID, PL.PURCHID, PL.DATAAREAID, PL.DIMENSION3_
ORDER BY PL.ITEMID DESC
) AS RECENTES
ON I.ITEMID = RECENTES.ITEMID
AND I.TRANSREFID = RECENTES.PURCHID
AND I.DATAAREAID = RECENTES.DATAAREAID
AND RowID = 1
WHERE I.QTY = 0
AND RECENTES.QTD > 0
AND I.TRANSREFID NOT LIKE 'EST%'
AND I.ITEMID = '002166'
ORDER BY 3
Far away is close at hand in the images of elsewhere.
Anon. -
Going by the query that you have provided in your original post, it doesn't look very difficult.
It would be easier for people here to provide you solutions if you give some sort of script that would replicate the problem in their local machines.
This script should contain some DDL of the tables involved, some sample data and the expected results.
Explaining the problem in words doesn't help sometimes.
Please check the link in my signature on how to provide the DDL and sample data.
I am sure you will get quick and good answers.
Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
David,
thanks for the help too.
However, it is returning me the same result.
No returns me all the last purchase orders.
The purchase order image above is an example of order should appear and is not returned.
If I leave the Order By in sub query, it displays an error.
If I remove the Order By, it returns me the same amount of lines before.
Thank you, but still worked.
-
That LEFT OUTER JOIN really needs to be an INNER JOIN, and will become one anyway due to the WHERE clause. However, this otherwise appears to do what the original poster is seeking, save for the ORDER BY clause that appears just after the GROUP BY in the subquery, which doesn't need to be there and SSMS won't like it anyway. I'm also pretty sure that the GROUP BY in the subquery isn't needed either.
Here's my re-factored version:
SELECT I.ITEMID AS ITEM,
I.INVOICEID AS NF,
I.TRANSREFID AS OC,
RECENTES.FINALIDADE,
CONVERT(decimal(10,2), I.COSTAMOUNTPOSTED/RECENTES.QTD) AS VALOR_RATEADO
FROM INVENTTRANS I
INNER JOIN (
SELECT PL.ITEMID, PL.PURCHID, PL.DATAAREAID, PL.DIMENSION3_ AS [FINALIDADE],
PL.QTYORDERED AS [QTD],
ROW_NUMBER() OVER (
PARTITION BY PL.ITEMID, PL.DATAAREAID, PL.DIMENSION3_
ORDER BY PL.PURCHID DESC) AS [RowID]
FROM PURCHLINE PL
WHERE PL.QTYORDERED = 0
) AS RECENTES
ON I.ITEMID = RECENTES.ITEMID
AND I.TRANSREFID = RECENTES.PURCHID
AND I.DATAAREAID = RECENTES.DATAAREAID
AND RECENTES.RowID = 1
WHERE I.QTY = 0
AND I.TRANSREFID NOT LIKE 'EST%'
AND I.ITEMID = '002166'
ORDER BY 3
EDIT: The above query was edited twice after posting when mistakes were discovered.
David Burrows (7/11/2014)
SELECT I.ITEMID AS ITEM,
I.INVOICEID AS NF,
I.TRANSREFID AS OC,
RECENTES.FINALIDADE,
CONVERT(decimal(10,2), I.COSTAMOUNTPOSTED/RECENTES.QTD) AS VALOR_RATEADO
FROM INVENTTRANS I
LEFT OUTER JOIN (
SELECT PL.ITEMID, PL.PURCHID, PL.DATAAREAID, PL.DIMENSION3_ AS [FINALIDADE],
SUM(PL.QTYORDERED) AS [QTD],
ROW_NUMBER() OVER (
PARTITION BY PL.ITEMID, PL.DATAAREAID, PL.DIMENSION3_
ORDER BY PL.PURCHID DESC) AS [RowID]
FROM PURCHLINE PL
GROUP BY PL.ITEMID, PL.PURCHID, PL.DATAAREAID, PL.DIMENSION3_
ORDER BY PL.ITEMID DESC
) AS RECENTES
ON I.ITEMID = RECENTES.ITEMID
AND I.TRANSREFID = RECENTES.PURCHID
AND I.DATAAREAID = RECENTES.DATAAREAID
AND RowID = 1
WHERE I.QTY = 0
AND RECENTES.QTD > 0
AND I.TRANSREFID NOT LIKE 'EST%'
AND I.ITEMID = '002166'
ORDER BY 3
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
friends,
I'm grateful for all the help.
However, all the scripts that I have tried here are returning me the same result.
I still do not understand why the Group By (PL.Purchid) is influencing this result.
If anyone else has any ideas, thank you very much.
It is very important to know these values ??to define our Forecast.
Hugs.
-
sgmunson (7/11/2014)
...save for the ORDER BY clause that appears just after the GROUP BY in the subquery, which doesn't need to be there and SSMS won't like it anyway...My bad :blush: was trying different methods and forgot to remove it and could not be bothered to create test tables to verify.
You are probably right about the GROUP BY as well if you assume the test data is complete (which I doubt) and there are no duplicate purchase id and item id in the table 🙂
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply