Declare @Date1 Datetime
Declare @Date2 Datetime
Set @Date1 = (Select min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >= [%A])
Set @Date2 = (Select max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <= [%B])
SELECT S0."ItemCode", S0."Dscription", S0."Warehouse", S0."OpBal", S1."InQty", S1."OutQty", S2."ClBal" FROM (SELECT "ItemCode", "Dscription", "Warehouse", SUM("InQty" - "OutQty") AS "OpBal" FROM OINM WHERE "DocDate" < @Date1 GROUP BY "ItemCode", "Dscription", "Warehouse") S0 INNER JOIN (SELECT "ItemCode", "Dscription", "Warehouse", SUM("InQty") AS "InQty", SUM("OutQty") AS "OutQty" FROM OINM WHERE "DocDate" >= @Date1 AND "DocDate" <= @Date2 GROUP BY "ItemCode", "Dscription", "Warehouse") S1 ON S0."ItemCode" = S1."ItemCode" AND S0."Warehouse" = S1."Warehouse" INNER JOIN (SELECT "ItemCode", "Dscription", "Warehouse", SUM("InQty" - "OutQty") AS "ClBal" FROM OINM WHERE "DocDate" <= @Date2 GROUP BY "ItemCode", "Dscription", "Warehouse") S2 ON S2."ItemCode" = S0."ItemCode" AND S2."Warehouse" = S0."Warehouse" WHERE S0."Warehouse" = [%E]
Add comment