Query Help

[Former Member]

I got this query from the Forum and I tried to add in 3 Parameters,

[%0] for BP Name

[%1] for Start Date

[%2] for End Date

But it keeps giving me errors, any help is much appreciated.

select cardname, itmsgrpnam, sum(linetotal) as 'Total' from

(select inv1.docentry, inv1.itemcode, inv1.linetotal, oitm.itmsgrpcod,oitb.itmsgrpnam, oinv.cardname, oinv.docdate

from inv1 join oinv on inv1.docentry = oinv.docentry

join oitm on inv1.itemcode = oitm.itemcode

join oitb on oitm.itmsgrpcod = oitb.itmsgrpcod

) invoicedSalesWithGroup

where cardname like [%0] and CONVERT(nchar(8), oinv.docdate, 112) >= [%1] AND CONVERT(nchar(8), oinv.docdate, 112) <= [%2]

group by cardname, itmsgrpnam

order by cardname, itmsgrpnam

[Former Member · Accepted solution]

Try this one:

select T1.cardname, T2.itmsgrpnam, sum(T0.linetotal) as 'Total'

from dbo.INV1 T0

Inner Join dbo.OINV T1 ON T1.DocEntry = T0.DocEntry

Inner Join dbo.OITM T2 ON T2.ItemCode = T0.ItemCode

Inner Join dbo.OITB T3 ON T3.itmsgrpcod = T2.itmsgrpcod

where T0.cardname like '[%0\]' and T1.docdate Beween '[%1\]' AND [%2\]

group by T1.cardname, T2.itmsgrpnam

order by cardname, itmsgrpnam

Thanks,

Gordon

[Former Member · Accepted solution]

Hi Gordon,

Thanks for the query but it's giving an error

"Must specify Table to Select from."

select T1.cardname, T2.itmsgrpnam, sum(T0.linetotal) as 'Total'

from dbo.INV1 T0

Inner Join dbo.OINV T1 ON T1.DocEntry = T0.DocEntry

Inner Join dbo.OITM T2 ON T2.ItemCode = T0.ItemCode

Inner Join dbo.OITB T3 ON T3.itmsgrpcod = T2.itmsgrpcod

where T0.cardname like '[%0]' and T1.docdate Beween '[%1]' AND [%2]

group by T1.cardname, T2.itmsgrpnam

order by cardname, itmsgrpnam

[Former Member · Accepted solution]

Sorry. should be:

select T1.cardname, T3.itmsgrpnam, sum(T0.linetotal) as 'Total'

from dbo.INV1 T0

Inner Join dbo.OINV T1 ON T1.DocEntry = T0.DocEntry

Inner Join dbo.OITM T2 ON T2.ItemCode = T0.ItemCode

Inner Join dbo.OITB T3 ON T3.itmsgrpcod = T2.itmsgrpcod

where T0.cardname like '[%0\]' and T1.docdate Beween '[%1\]' AND '[%2\]'

group by T1.cardname, T3.itmsgrpnam

order by T1.cardname, T3.itmsgrpnam

[Former Member · Accepted solution]

Thanks Gordon, that will do the job!

[former_member325312 · Accepted solution]

HELLO Gordon

try this ur problem will be solved

SELECT OINV.CardName,SUM(INV1.LineTotal)AS[LINE TOTAL]

FROM INV1 INNER JOIN

OINV ON INV1.DocEntry = OINV.DocEntry INNER JOIN

OITM ON INV1.ItemCode = OITM.ItemCode INNER JOIN

OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod

GROUP BY OINV.CardName, INV1.LineTotal

if u r satisfied close the thread