Query Updation

[former_member593234]

Hi All,

I have this query report, I need 3 additional informations in this below query,

1. Document Date

2. Last Purchase Price (Last purchased from vendor), already updated with Purchase price, I dnt want that.

3. Total Before discount (Middle of last purchase price & Document total in the report)

declare @From date
set @From= /* select min(T0.[F_refdate]) from [dbo].[OFPR] T0 where T0.[F_refdate]=*/ [%0]
declare @To Date
set @To= /*select max(T0.[T_refdate]) from [dbo].[OFPR] T0 where T0.[T_refdate]=*/ [%1]
declare @name VarcHar(150)
set @name =/* Select max(a.CardName) from OCRD a where a.CardName= */'[%2]'
SELECT T0.docnum , 'Delivery' as 'DocType' , t0.cardcode , t0.cardname ,
t1.U_part_no , t1.itemcode, t1.Dscription , t1.Quantity , t1.price as 'Purchase Price',t0.Doctotal,t0.TrackNo
FROM ODLN T0
JOIN DLN1 T1 ON T1.DocENtry=T0.DocEntry
Where T0.DocDate >= @from and T0.DocDate <= @to and T0.CardName = @name Union all SELECT T0.docnum , 'Invoice' as 'DocType' , t0.cardcode , t0.cardname , t1.U_part_no , t1.itemcode, t1.Dscription , t1.Quantity , t1.price as 'Purchase Price',t0.Doctotal,t0.TrackNo FROM OINV T0 JOIN INV1 T1 ON T1.DocENtry=T0.DocEntry Where T0.DocDate >= @from and T0.DocDate <= @to and T0.CardName = @name

[kvbalakumar · Accepted solution]

Hi athulkm123 ,

Try the below query:

DECLARE @From DATE;
SET @From=/* select min(T0.[F_refdate]) from [dbo].[OFPR] T0 where T0.[F_refdate]=*/ [%0];
DECLARE @To DATE;
SET @To=/*select max(T0.[T_refdate]) from [dbo].[OFPR] T0 where T0.[T_refdate]=*/ [%1];
DECLARE @name VARCHAR(150);
SET @name=/* Select max(a.CardName) from OCRD a where a.CardName= */ '[%2]';
SELECT T0.DocNum, 'Delivery' [DocType], T0.CardCode, T0.CardName, T1.U_part_no, 
    T1.ItemCode, T1.Dscription, T1.Quantity, T2.LastPurPrc [Last Purchase Price],
    T1.PriceBefDi * T1.Quantity [Total Before Discount], T0.DocTotal, T0.TrackNo, T0.TaxDate [Document Date]
FROM dbo.ODLN T0
     INNER JOIN dbo.DLN1 T1 ON T1.DocEntry=T0.DocEntry
     INNER JOIN dbo.OITM T2 ON T2.ItemCode=T1.ItemCode
WHERE T0.DocDate>=@From AND T0.DocDate<=@To AND T0.CardName=@name
UNION ALL
SELECT T0.DocNum, 'Invoice', T0.CardCode, T0.CardName, T1.U_part_no, 
    T1.ItemCode, T1.Dscription, T1.Quantity, T2.LastPurPrc , T1.PriceBefDi * T1.Quantity, T0.DocTotal, T0.TrackNo, T0.TaxDate
FROM dbo.OINV T0
     INNER JOIN dbo.INV1 T1 ON T1.DocEntry=T0.DocEntry
     INNER JOIN dbo.OITM T2 ON T2.ItemCode=T1.ItemCode
WHERE T0.DocDate>=@From AND T0.DocDate<=@To AND T0.CardName=@name;

Regards,

Bala