on 12-27-2014 10:40 AM
Hi All,
I want a report similar to Inventory Audit report, but instead of Document, I want the report to display the Document details viz, doc no, doc date, vendor/customer. I tried to use PDN1 & DLN1 but system shows error, saying it is not contained in group by function.
Please help.
Hi,
If you have query, please post here to check.
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Declare @FromDate Datetime
Declare @ToDate Datetime
select @FromDate = min(S0.Docdate) from dbo.OINM S0 where ((S0.Docdate = '[%0]' or ('[%0]' = '')))
select @ToDate = max(S1.Docdate) from dbo.OINM s1 where ((S1.Docdate = '[%1]') or ('[%1]' = ''))
SELECT Doc_No, Doc_Date, LPO_Ref, BP_Name, Item_No, Item_Name, Height, Width, Length, OpeningBalance, Cases, Sheets_per_Case, SqM
From
(
SELECT T0.[DocNum] as "Doc_No", T0.[DocDate] as "Doc_Date", T0.[NumAtCard] as "LPO_Ref", T0.[CardName] as "BP_Name",T1.[ItemCode] as "Item_No", T1.[Dscription] as "Item_Name", T1.[Height1] as "height", T1.[Width1] as "Width", T1.[Length1] as "Length", (sum(T3.inqty)-sum(T3.outqty)) as "OpeningBalance", T1.[U_EA_Case] as "Cases", T1.[U_EA_Packsheet] as "Sheets_per_Case", T1.[Quantity] as "SqM"
FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
INNER JOIN OINM T3 ON T2.[ItemCode] = T3.[ItemCode]
Where T0.[DocDate] between @fromdate and @todate
Group by T0.[DocNum], T0.[DocDate], T0.[NumAtCard], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Height1], T1.[Width1], T1.[Length1], T1.[U_EA_Case], T1.[U_EA_Packsheet], T1.[Quantity]
UNION ALL
SELECT T0.[DocNum] as "Doc No", T0.[DocDate] as "Doc_Date", T0.[NumAtCard] as "LPO_Ref", T0.[CardName] as "BP_Name",T1.[ItemCode] as "Item_No", T1.[Dscription] as "Item_Name",T1.[Height1] as "height", T1.[Width1] as "Width", T1.[Length1] as "Length", (sum(T3.inqty)-sum(T3.outqty)) as "OpeningBalance", T1.[U_EA_Case] as "Cases", T1.[U_Ea_Sheet] as "Sheets_per_Case",T1.[Quantity] as "SqM"
FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
INNER JOIN OINM T3 ON T2.[ItemCode] = T3.[ItemCode]
Where T0.[DocDate] between @fromdate and @todate
Group by T0.[DocNum], T0.[DocDate], T0.[NumAtCard], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Height1], T1.[Width1], T1.[Length1], T1.[U_EA_Case], T1.[U_EA_Packsheet], T1.[Quantity])a
Group By Doc_No, Doc_Date, LPO_Ref, BP_Name, Item_No, Item_Name, Height, Width, Length, OpeningBalance, Cases, Sheets_per_Case, SqM
Hi,
Try below query without UDF fields:
Declare @FromDate Datetime
Declare @ToDate Datetime
select @FromDate = min(S0.Docdate) from dbo.OINM S0 where ((S0.Docdate = '[%0]' or ('[%0]' = '')))
select @ToDate = max(S1.Docdate) from dbo.OINM s1 where ((S1.Docdate = '[%1]') or ('[%1]' = ''))
SELECT Doc_No, Doc_Date, LPO_Ref, BP_Name, Item_No, Item_Name, Height, Width, Length, OpeningBalance, SqM
From
(
SELECT T0.[DocNum] as "Doc_No", T0.[DocDate] as "Doc_Date", T0.[NumAtCard] as "LPO_Ref", T0.[CardName] as "BP_Name",T1.[ItemCode] as "Item_No", T1.[Dscription] as "Item_Name", T1.[Height1] as "height", T1.[Width1] as "Width", T1.[Length1] as "Length", (sum(T3.inqty)-sum(T3.outqty)) as "OpeningBalance", T1.[Quantity] as "SqM"
FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
INNER JOIN OINM T3 ON T2.[ItemCode] = T3.[ItemCode]
Where T0.[DocDate] between @fromdate and @todate
Group by T0.[DocNum], T0.[DocDate], T0.[NumAtCard], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Height1], T1.[Width1], T1.[Length1], T1.[Quantity]
UNION ALL
SELECT T0.[DocNum] as "Doc No", T0.[DocDate] as "Doc_Date", T0.[NumAtCard] as "LPO_Ref", T0.[CardName] as "BP_Name",T1.[ItemCode] as "Item_No", T1.[Dscription] as "Item_Name",T1.[Height1] as "height", T1.[Width1] as "Width", T1.[Length1] as "Length", (sum(T3.inqty)-sum(T3.outqty)) as "OpeningBalance",T1.[Quantity] as "SqM"
FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
INNER JOIN OINM T3 ON T2.[ItemCode] = T3.[ItemCode]
Where T0.[DocDate] between @fromdate and @todate
Group by T0.[DocNum], T0.[DocDate], T0.[NumAtCard], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Height1], T1.[Width1], T1.[Length1],T1.[Quantity])a
Group By Doc_No, Doc_Date, LPO_Ref, BP_Name, Item_No, Item_Name, Height, Width, Length, OpeningBalance, SqM
User | Count |
---|---|
95 | |
11 | |
11 | |
6 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.