Query Issue

[amol_bairagi]

Dear Experts,

I have an issue in the below query, I want to display single value in DocumentTotal field (Row Level) (not repeated value). Kindly provide the solution for that.

SELECT T4.[SeriesName], T0.[DocNum] as 'A/R Invoice', T0.[DocDate] as 'Inv. Date', T0.[CardName] as 'Customer Name', T0.[NumAtCard] as 'Bill No. & Date',  T1.[LineTotal] as 'Sales Amount', T1.[GTotal] as 'Total Sales', t0.doctotal FROM [dbo].[OINV]  T0 INNER JOIN [dbo].[INV1]  T1 ON T0.DocEntry = T1.DocEntry INNER JOIN [dbo].[NNM1]  T4 ON T0.Series = T4.Series

WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND  T4.[SeriesName] =[%3]

#	Series Name	A/R Invoice	Inv. Date	Customer Name	Bill No. & Date	Total Sales	Document Total
1	BADDI	1004	12/4/2014	ABC CO	ABC/001	172,592.55	2,123,173.00
2	BADDI	1004	12/4/2014	ABC CO	ABC/001	1,994,908.50	2,123,173.00

Regards

Amol

[amol_bairagi · Accepted solution]

Thanks Nagarajan

[kothandaraman_nagarajan · Accepted solution]

Hi,

If you got answer, please close this thread by marking correct answer.

Thanks & Regards,

Nagarajan

[kothandaraman_nagarajan]

Hi,

Try this query:

SELECT T4.[SeriesName], T0.[DocNum] as 'A/R Invoice', T0.[DocDate] as 'Inv. Date', T0.[CardName] as 'Customer Name', T0.[NumAtCard] as 'Bill No. & Date',  sum(T1.[LineTotal]) as 'Sales Amount', sum(T1.[GTotal]) as 'Total Sales', t0.doctotal FROM [dbo].[OINV]  T0 INNER JOIN [dbo].[INV1]  T1 ON T0.DocEntry = T1.DocEntry INNER JOIN [dbo].[NNM1]  T4 ON T0.Series = T4.Series

WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND  T4.[SeriesName] =[%3] group by  T4.[SeriesName], T0.[DocNum],T0.[DocDate],T0.[CardName],T0.[NumAtCard],t0.doctotal

Thanks & Regards,

Nagarajan

[former_member186712]

Hi Amol,

Write

SELECT Distinct T4.[SeriesName], T0.[DocNum] as 'A/R Invoice', T0.[DocDate] as 'Inv. Date', T0.[CardName] as 'Customer Name', T0.[NumAtCard] as 'Bill No. & Date',  T1.[LineTotal] as 'Sales Amount', T1.[GTotal] as 'Total Sales', t0.doctotal FROM [dbo].[OINV]  T0 INNER JOIN [dbo].[INV1]  T1 ON T0.DocEntry = T1.DocEntry INNER JOIN [dbo].[NNM1]  T4 ON T0.Series = T4.Series

WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND  T4.[SeriesName] =[%3]

Hope it helps