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