Finding Last Cost by article and compare with invoice line cost

  • 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

  • luissantos (5/28/2015)


    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

    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?

  • 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

  • luissantos (5/29/2015)


    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

    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)

  • 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