May 28, 2015 at 5:25 pm
Hello comunity
I need to build TSQL query to return the Last unit Cost from my table of movement of goods SL (on CTE)
but the MAX(Datalc) must be Less or Equal to my HeaderInvoice.
This is my script:
With MaxDates as (
SELECT ref,
MAX(epcpond)[Unitcostprice],
MAX(datalc) MaxDate
FROM sl
GROUP BY ref
)
SELECT
HeaderInvoice.fdata [Invoice_Date],HeaderInvoice.fno [Invoice_Numb],
HeaderInvoice.nmdoc [InvoiceDocument],
maxdates.[MaxDate],
InvoiceLines.Ref , InvoiceLines.[quantity],
InvoiceLines.epcp [InvoiceLine_UnitCost],
[MaxDates].[epcpond] [UnitCostMovGoods],
InvoiceLines.[quantity] * Maxdates.Unitcostprice,
InvoiceLines.[quantity] * InvoiceLines.epcp
FROM
[dbo].[fi] [InvoiceLines]
INNER JOIN [dbo].[ft] [HeaderInvoice] ON InvoiceLines.[ftstamp] =HeaderInvoice.ftstamp
INNER JOIN [MaxDates] ON [MaxDates].[ref] = InvoiceLines.[ref]
WHERE
HeaderInvoice.fdata BETWEEN '20150101' AND '20150131'
AND InvoiceLines.ref <> ''
AND HeaderInvoice.[tipodoc] IN(1,2,3)
AND [InvoiceLines].[stns] = 0
the problem I have right now is that the Unitcostprice of my table of goods movements has a top date greather than the date of my bill.
Example:
invoice date : 29.01.2015 unitcost on invoice line = 13,599722
Maxdate (CTE) : 19.03.2015 unitCost from my table of movement of goods = 14,075
That ´s not correct because the MAxdates > invoice date and the unitCost of 14,075 is the cost on 19.03.2015 and not just before my invoice date.
Someone could hel me to solve this ?
Many thanks and best regards
Luis Santos
May 29, 2015 at 1:34 am
luissantos (5/28/2015)
Hello comunityI need to build TSQL query to return the Last unit Cost from my table of movement of goods SL (on CTE)
but the MAX(Datalc) must be Less or Equal to my HeaderInvoice.
This is my script:
With MaxDates as (
SELECT ref,
MAX(epcpond)[Unitcostprice],
MAX(datalc) MaxDate
FROM sl
GROUP BY ref
)
SELECT
HeaderInvoice.fdata [Invoice_Date],HeaderInvoice.fno [Invoice_Numb],
HeaderInvoice.nmdoc [InvoiceDocument],
maxdates.[MaxDate],
InvoiceLines.Ref , InvoiceLines.[quantity],
InvoiceLines.epcp [InvoiceLine_UnitCost],
[MaxDates].[epcpond] [UnitCostMovGoods],
InvoiceLines.[quantity] * Maxdates.Unitcostprice,
InvoiceLines.[quantity] * InvoiceLines.epcp
FROM
[dbo].[fi] [InvoiceLines]
INNER JOIN [dbo].[ft] [HeaderInvoice] ON InvoiceLines.[ftstamp] =HeaderInvoice.ftstamp
INNER JOIN [MaxDates] ON [MaxDates].[ref] = InvoiceLines.[ref]
WHERE
HeaderInvoice.fdata BETWEEN '20150101' AND '20150131'
AND InvoiceLines.ref <> ''
AND HeaderInvoice.[tipodoc] IN(1,2,3)
AND [InvoiceLines].[stns] = 0
the problem I have right now is that the Unitcostprice of my table of goods movements has a top date greather than the date of my bill.
Example:
invoice date : 29.01.2015 unitcost on invoice line = 13,599722
Maxdate (CTE) : 19.03.2015 unitCost from my table of movement of goods = 14,075
That ´s not correct because the MAxdates > invoice date and the unitCost of 14,075 is the cost on 19.03.2015 and not just before my invoice date.
Someone could hel me to solve this ?
Many thanks and best regards
Luis Santos
Looking at your sample code is a bit difficult without some DDL and test data to back it up
With MaxDates as (
SELECT ref,
MAX(epcpond)[Unitcostprice],
MAX(datalc) MaxDate
FROM sl
GROUP BY ref
)
SELECT
HeaderInvoice.fdata [Invoice_Date],HeaderInvoice.fno [Invoice_Numb],
HeaderInvoice.nmdoc [InvoiceDocument],
maxdates.[MaxDate],
InvoiceLines.Ref , InvoiceLines.[quantity],
InvoiceLines.epcp [InvoiceLine_UnitCost],
[MaxDates].[epcpond] [UnitCostMovGoods],
InvoiceLines.[quantity] * Maxdates.Unitcostprice,
InvoiceLines.[quantity] * InvoiceLines.epcp
FROM
[dbo].[fi] [InvoiceLines]
INNER JOIN [dbo].[ft] [HeaderInvoice] ON InvoiceLines.[ftstamp] =HeaderInvoice.ftstamp
INNER JOIN [MaxDates] ON [MaxDates].[ref] = InvoiceLines.[ref]
WHERE
HeaderInvoice.fdata BETWEEN '20150101' AND '20150131'
AND InvoiceLines.ref <> ''
AND HeaderInvoice.[tipodoc] IN(1,2,3)
AND [InvoiceLines].[stns] = 0
You mention that the max date is beyond what is on the invoice table, but you appear to be filtering the header invoice by a date range in the WHERE clause. Should this also be applied to the 'sl' table in the CTE or is it necessary?
May 29, 2015 at 3:28 am
Hello Dohsan
i understand your doubt but i need the range of my invoiceheader dates .
without this range all invoices are appear.
In pratice, and i don´t know if it is possible, i pretend to see all my invoice between 20150101 and 20150131, but for each article i need the last price cost (Last movement before or equal to my headerinvoice date).
suppose the last movment on my table SL for the article AAA is on 20141031, and this article in on my invoice line that have a date of 20150115, i need to see the unitcost price on 20141031, independently of the invoice date are between 20150101 and 20150131, because is the last movment of my table SL before my invoice date.
This is the unique form to see/check if the unitcostprice on invoice line are equal to the last unitcostprice of my table of movement of goods SL.
Thanks for your help.
Best regards
Luis Santos
June 3, 2015 at 7:46 am
luissantos (5/29/2015)
Hello Dohsani understand your doubt but i need the range of my invoiceheader dates .
without this range all invoices are appear.
In pratice, and i don´t know if it is possible, i pretend to see all my invoice between 20150101 and 20150131, but for each article i need the last price cost (Last movement before or equal to my headerinvoice date).
suppose the last movment on my table SL for the article AAA is on 20141031, and this article in on my invoice line that have a date of 20150115, i need to see the unitcost price on 20141031, independently of the invoice date are between 20150101 and 20150131, because is the last movment of my table SL before my invoice date.
This is the unique form to see/check if the unitcostprice on invoice line are equal to the last unitcostprice of my table of movement of goods SL.
Thanks for your help.
Best regards
Luis Santos
I'm thinking that the problem is the initial CTE. You are going after the MAX value for both the price you seek as well as the date, but you need the date to be prior to the invoice, so take a look at the following and let me know if that helps:
SELECT
HeaderInvoice.fdata AS Invoice_Date,
HeaderInvoice.fno AS Invoice_Numb,
HeaderInvoice.nmdoc AS InvoiceDocument,
MaxDates.MaxDate,
InvoiceLines.Ref, InvoiceLines.quantity,
InvoiceLines.epcp AS InvoiceLine_UnitCost,
MaxDates.Unitcostprice AS UnitCostMovGoods,
InvoiceLines.[quantity] * Maxdates.Unitcostprice,
InvoiceLines.[quantity] * InvoiceLines.epcp
FROM dbo.fi AS InvoiceLines
INNER JOIN dbo.ft AS HeaderInvoice
ON InvoiceLines.[ftstamp] = HeaderInvoice.ftstamp
CROSS APPLY (
SELECT R.ref, R.MaxDate, MAX(S.epcpond) AS Unitcostprice
FROM (
SELECT sl.ref, MAX(sl.datalc) AS MaxDate
FROM sl
WHERE sl.datalc < HeaderInvoice.fdata
GROUP BY sl.ref
) AS R
INNER JOIN sl AS S
ON R.ref = S.ref
AND R.MaxDate = S.datalc
WHERE R.ref = InvoiceLines.ref
) AS MaxDates
WHERE HeaderInvoice.fdata BETWEEN '20150101' AND '20150131'
AND InvoiceLines.ref <> ''
AND HeaderInvoice.tipodoc IN (1, 2, 3)
AND InvoiceLines.stns = 0
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 3, 2015 at 9:08 am
Hello Steve
Thanks for your reply, i will go to test it.
Best Regards,
Luis Santos
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply