Script Help - Selecting last record
-
matfurrier (7/11/2014)
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.
If you want a full working solution then you need to post complete DDL definitions for all the tables, test data for each table and what the expected result should look like using the test data.
(Note that you should post working scripts that we can copy & paste)
Far away is close at hand in the images of elsewhere.
Anon. -
matfurrier (7/11/2014)
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.
So far, you've avoided doing what has been asked of you, so I'm only going to try one last time to provide what you need:
;WITH MOST_RECENT_PO AS (
SELECT PL.ITEMID, PL.DATAAREAID, PL.DIMENSION3_ AS [FINALIDADE], MAX(PL.PURCHID) AS MAX_PURCHID,
FROM PURCHLINE PL
WHERE PL.QTYORDERED = 0
GROUP BY PL.ITEMID, PL.DATAAREAID, PL.DIMENSION3
),
RECENTES AS (
SELECT MR.*, P.QTYORDERED AS QTD
FROM MOST_RECENT_PO AS MR
INNER JOIN PURCHLINE AS P
ON MR.ITEMID = P.ITEMID
AND MR.DATAAREAID = P.DATAAREAID
AND MR.FINALIDADE = P.DIMENSION3_
AND MR.MAX_PURCHID = P.PURCHID
)
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 RECENTES
ON I.ITEMID = RECENTES.ITEMID
AND I.TRANSREFID = RECENTES.PURCHID
AND I.DATAAREAID = RECENTES.DATAAREAID
WHERE I.QTY = 0
AND I.TRANSREFID NOT LIKE 'EST%'
AND I.ITEMID = '002166'
ORDER BY 3
If this doesn't work, then you're going to need to do what has been asked of you repeatedly - PROVIDE COMPLETE TABLE CREATION SCRIPTS for the relevant tables, along with WORKING INSERT SCRIPTS THAT CONTAIN SAMPLE DATA, as well as EXPECTED RESULTS BASED ON THE SAMPLE DATA.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Friends, see if you could detail:
-- Selects the item in the inventory transactions tableSELECT I.ITEMID ITEM,
-- Selects the number of Invoice associated purchase order
I.INVOICEID NF,
-- Selects the number of the purchase order that generated this transaction in stoc
I.TRANSREFID OC,
-- Select which company this order belongs
RECENTES.FINALIDADE FINALIDADE,
-- Calculate the value of the stock / quantity of the item on the purchase order transaction
convert(decimal(10,2),I.COSTAMOUNTPOSTED/RECENTES.QTD) VALOR_RATEADO
-- Select it from the inventory transactions table
FROM INVENTTRANS I
INNER JOIN
(
-- Selects the largest purchase order by number
SELECT MAX(PL.PURCHID) AS PURCHID,
-- Selects the item
PL.ITEMID AS ITEMID,
-- Select company master (all others are inserted into this, as affiliates)
PL.DATAAREAID,
-- Sum the amount of the item on the purchase order
SUM(PL.QTYORDERED)QTD,
-- Selects the company
PL.DIMENSION3_ FINALIDADE
-- The purchase order table
FROM PURCHLINE PL
-- Group by item master, affiliates, purchase order number
GROUP BY PL.ITEMID, PL.DATAAREAID, PL.DIMENSION3_, PL.PURCHID
) RECENTES
ON
-- Where item in stock transaction = item last order
I.ITEMID = RECENTES.ITEMID
-- Order number in the inventory transactions table = number of the last order
AND I.TRANSREFID = RECENTES.PURCHID
-- Company stock transactions = company in the purchase order
AND I.DATAAREAID = RECENTES.DATAAREAID
WHERE
-- Quantity in stock transactions = 0
I.QTY = 0
-- Quantity in the purchase order> 0
AND RECENTES.QTD > 0
-- Purchase order number can not begin with "EST"
AND I.TRANSREFID NOT LIKE 'EST%'
-- Example of item
AND I.ITEMID = '002166'
group by I.DATAAREAID, RECENTES.FINALIDADE, I.COSTAMOUNTPOSTED, RECENTES.QTD,I.TRANSREFID, I.ITEMID, I.INVOICEID
ORDER BY 3
-
Avoiding what was asked for isn't going to get you anywhere... That last post only makes things ever so slightly more clear, but does not illuminate a solution. Do what we asked you to do. PLEASE ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Steve, these are examples of data that I have in both tables. (InventTrans and PurchLine).
See, I'm more of a purchase order with the same item, but I need only the last.
In this case, the company "BLEM" is not listed in the script since "BAURU" yes.
Would that be?
Thank you.
-
You've been asked several times to create the scripts and yet you still continue down the path of providing something less. If you can't follow instructions, or just aren't willing to do that work, why should we help you? We aren't servants, we're volunteers. Please keep that in mind...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Steve, I just do not really understand what would those scripts that you highlight.
I need help because exhausted all attempts, but as I told you, I can not express myself well in English, perhaps the difficulty is there.
Anyway, let's close the topic, since we are not getting along.
Thank you very much.
-
matfurrier (7/11/2014)
Steve, I just do not really understand what would those scripts that you highlight.I need help because exhausted all attempts, but as I told you, I can not express myself well in English, perhaps the difficulty is there.
Anyway, let's close the topic, since we are not getting along.
Thank you very much.
The point is those excel files are not readily consumable. What we want to see is ddl (create table scripts) and sample data as insert statements. That way we don't have to put in a lot of effort to recreate your problem on our system.
As a side note, I would recommend not using ordinal position in your ORDER BY statements. This is a real pain to work with. Just type in the column name and then changes to the column list don't break your code.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
The scripts would be the T-SQL necessary to create the tables and data to test the query with, for example
The table DDL would be like this
CREATE TABLE PURCHLINE(PURCHID char(12),ITEMID char(6),QTYORDERED decimal 18,2)
The insert statements would look like this
INSERT INTO PURCHLINE(PURCHID,ITEMID,QTYORDERED)
VALUES ('OC_000124621','001089',144.00),('OC_000124621','002166',500.00)
The expected result from a select of the table would be
PURCHIDITEMID QTYORDERED
OC_000124621001089144.00
OC_000124621002166500.00
Far away is close at hand in the images of elsewhere.
Anon. -
Friends, I hope it is this:
CREATE TABLE PURCHLINE(PURCHID nvarchar(20), ITEMID nvarchar(20),QTYORDERED numeric(28,12), DIMENSION3_ nvarchar(10), DATAAREAID nvarchar(4));
CREATE TABLE INVENTTRANS(TRANSREFID nvarchar(20), ITEMID nvarchar(20), QTY numeric(28,12), INVOICEID nvarchar(20), COSTAMOUNTPOSTED numeric(28,12), DATAAREAID nvarchar(4));
INSERT INTO PURCHLINE(PURCHID,ITEMID,QTYORDERED, DIMENSION3_, DATAAREAID)VALUES ('OC_000116081','002166','2000', 'BELEM', '01'),
('OC_000118938','002166','1500', 'BELEM', '01'),
('OC_000123530','002166','800', 'BELEM', '01'),
('OC_000124621','002166','500', 'BELEM', '01'),
('OC_000113385','002166','550', 'BAURU', '01'),
('OC_000114751','002166','1200', 'BAURU', '01'),
('OC_000117690','002166','600', 'BAURU', '01'),
('OC_000119211','002166','800', 'BAURU', '01');
INSERT INTO INVENTTRANS(TRANSREFID,ITEMID,QTY, INVOICEID, COSTAMOUNTPOSTED, DATAAREAID)VALUES ('OC_000116081', '002166','2000', '26674', '15651', '01'),
('OC_000116081', '002166','0', '26674', '1525.24', '01'),
('OC_000118938', '002166','1500', '27317', '10755.90', '01'),
('OC_000118938', '002166','0', '27317', '1021.88', '01'),
('OC_000123530', '002166','800', '27793', '6118.32', '01'),
('OC_000123530', '002166','0', '27793', '519.30', '01'),
('OC_000124621', '002166','500', '28162', '3940.50', '01'),
('OC_000124621', '002166','0', '28162', '311.35', '01'),
('OC_000117690', '002166','600', '27068', '4347', '01'),
('OC_000117690', '002166','0', '27068', '126.15', '01'),
('OC_000119211', '002166','800', '27360', '5970.80', '01'),
('OC_000119211', '002166','0', '27360', '271.10', '01'),
('OC_000113385', '002166','550', '26152', '4275.70', '01'),
('OC_000113385', '002166','0', '26152', '188.27', '01'),
('OC_000114751', '002166','1200', '26386', '9632.40', '01'),
('OC_000114751', '002166','0', '26386', '405.84', '01');
SELECT * FROM PURCHLINEPURCHID ITEMIDQTYORDERED DIMENSION3_DATAAREAIDOC_000113385002166550.000000000000BAURU 01
OC_0001147510021661200.000000000000BAURU 01
OC_0001160810021662000.000000000000BELEM 01
OC_000117690002166600.000000000000BAURU 01
OC_0001189380021661500.000000000000BELEM 01
OC_000119211002166800.000000000000BAURU 01
OC_000123530002166800.000000000000BELEM 01
OC_000124621002166500.000000000000BELEM 01
SELECT * FROM INVENTTRANS
-
I'm still lost. Based on everything you just posted, what is the expected results. What should you see from the processing of a query that does everything correctly with the given data.
-
The result should be:
ITEMNF OC FINALIDADEVALOR_RATEADO
00216627360OC_000119211BAURU 0.34
00216628162OC_000124621BELEM 0.62
-
Turns out my last attempt was actually almost right. I caught a couple of mistakes because I had actual data to work with. Here's the slightly re-factored version of it, including the creation scripts, with improved formatting:
DECLARE @PURCHLINE AS TABLE (
PURCHID nvarchar(20),
ITEMID nvarchar(20),
QTYORDERED numeric(28,12),
DIMENSION3_ nvarchar(10),
DATAAREAID nvarchar(4)
);
DECLARE @INVENTTRANS AS TABLE (
TRANSREFID nvarchar(20),
ITEMID nvarchar(20),
QTY numeric(28,12),
INVOICEID nvarchar(20),
COSTAMOUNTPOSTED numeric(28,12),
DATAAREAID nvarchar(4)
);
INSERT INTO @PURCHLINE(PURCHID,ITEMID,QTYORDERED, DIMENSION3_, DATAAREAID)
VALUES ('OC_000116081','002166','2000', 'BELEM', '01'),
('OC_000118938','002166','1500', 'BELEM', '01'),
('OC_000123530','002166','800', 'BELEM', '01'),
('OC_000124621','002166','500', 'BELEM', '01'),
('OC_000113385','002166','550', 'BAURU', '01'),
('OC_000114751','002166','1200', 'BAURU', '01'),
('OC_000117690','002166','600', 'BAURU', '01'),
('OC_000119211','002166','800', 'BAURU', '01');
INSERT INTO @INVENTTRANS(TRANSREFID,ITEMID,QTY, INVOICEID, COSTAMOUNTPOSTED, DATAAREAID)
VALUES ('OC_000116081', '002166','2000', '26674', '15651', '01'),
('OC_000116081', '002166','0', '26674', '1525.24', '01'),
('OC_000118938', '002166','1500', '27317', '10755.90', '01'),
('OC_000118938', '002166','0', '27317', '1021.88', '01'),
('OC_000123530', '002166','800', '27793', '6118.32', '01'),
('OC_000123530', '002166','0', '27793', '519.30', '01'),
('OC_000124621', '002166','500', '28162', '3940.50', '01'),
('OC_000124621', '002166','0', '28162', '311.35', '01'),
('OC_000117690', '002166','600', '27068', '4347', '01'),
('OC_000117690', '002166','0', '27068', '126.15', '01'),
('OC_000119211', '002166','800', '27360', '5970.80', '01'),
('OC_000119211', '002166','0', '27360', '271.10', '01'),
('OC_000113385', '002166','550', '26152', '4275.70', '01'),
('OC_000113385', '002166','0', '26152', '188.27', '01'),
('OC_000114751', '002166','1200', '26386', '9632.40', '01'),
('OC_000114751', '002166','0', '26386', '405.84', '01');
--================================================================================
--EXPECTED RESULTS
--================================================================================
/*
ITEMNF OC FINALIDADEVALOR_RATEADO
00216627360OC_000119211BAURU 0.34
00216628162OC_000124621BELEM 0.62
*/
--================================================================================
--QUERY
--================================================================================
DECLARE @ITEM AS nvarchar(20) = '002166';
WITH MOST_RECENT_PO AS (
SELECT PL.ITEMID, PL.DATAAREAID, PL.DIMENSION3_ AS [FINALIDADE], MAX(PL.PURCHID) AS MAX_PURCHID
FROM @PURCHLINE AS PL
GROUP BY PL.ITEMID, PL.DATAAREAID, PL.DIMENSION3_
),
RECENTES AS (
SELECT MR.*, P.QTYORDERED AS QTD
FROM MOST_RECENT_PO AS MR
INNER JOIN @PURCHLINE AS P
ON MR.ITEMID = P.ITEMID
AND MR.DATAAREAID = P.DATAAREAID
AND MR.FINALIDADE = P.DIMENSION3_
AND MR.MAX_PURCHID = P.PURCHID
)
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 AS I
INNER JOIN RECENTES
ON I.ITEMID = RECENTES.ITEMID
AND I.TRANSREFID = RECENTES.MAX_PURCHID
AND I.DATAAREAID = RECENTES.DATAAREAID
WHERE I.QTY = 0
AND I.TRANSREFID NOT LIKE 'EST%'
AND I.ITEMID = @ITEM
ORDER BY I.TRANSREFID
It produces the exact result you specified.
Enjoy!
matfurrier (7/11/2014)
The result should be:ITEMNF OC FINALIDADEVALOR_RATEADO
00216627360OC_000119211BAURU 0.34
00216628162OC_000124621BELEM 0.62
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
sgmunson (7/11/2014)
Turns out my last attempt was actually almost right.....It produces the exact result you specified.
Nice 🙂
Far away is close at hand in the images of elsewhere.
Anon. -
friends,
I am very grateful for the help and attention you have given me.
Indeed, with these data, the result was exactly what I need.
But I think there is something with a bigger problem on my database, because some results are still not displayed unless I use Group By in PL.Purchid, and if I use Group By, it brings many other results as before.
Thank you all.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply