Top 10 Sales Item Report

[Former Member]

Hi Experts,

Am using the following query to get Top 10 Sales Items Report.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT TOP 20 T1.ItemCode, T2.ItemName, cast(sum(T1.Quantity) as numeric) as 'Quantity Sold', sum(T1.LineTotal - T1.VatSum) as 'Total Net Value', sum(T1.LineTotal) as 'Total Gross Value', T1.Currency

FROM dbo.INV1 T1

LEFT OUTER JOIN OITM T2 on T1.ItemCode = T2.ItemCode

WHERE T1.ItemCode is not null

GROUP BY T1.ItemCode, T2.ItemName, T1.Currency

order by sum(T1.Quantity) desc

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Now I wand to return this report between a date range. How its possible

Please help Me

Thanks & Regards.

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try:

SELECT TOP 20 T1.ItemCode, T2.ItemName, cast(sum(T1.Quantity) as numeric) as 'Quantity Sold', sum(T1.LineTotal - T1.VatSum) as 'Total Net Value', sum(T1.LineTotal) as 'Total Gross Value', T1.Currency

FROM dbo.INV1 T1 inner join OINV T3 on T1.docentry = t3.docentry

LEFT OUTER JOIN OITM T2 on T1.ItemCode = T2.ItemCode

WHERE T1.ItemCode is not null and t3.docdate between [%0] and [%1]

GROUP BY T1.ItemCode, T2.ItemName, T1.Currency

order by sum(T1.Quantity) desc

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi,

Thanks.................

This is OK

Thanks & Regards

ABDUL RASHEED

[kothandaraman_nagarajan · Accepted solution]

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

[Former Member · Accepted solution]

Hi Sir,

Following Query is retrieving top 20 sales items between a date range.

SELECT TOP 20 T1.ItemCode, T2.ItemName, cast(sum(T1.Quantity) as numeric) as 'Quantity Sold', sum(T1.LineTotal - T1.VatSum) as 'Total Net Value', sum(T1.LineTotal) as 'Total Gross Value', T1.Currency

FROM dbo.INV1 T1 inner join OINV T3 on T1.docentry = t3.docentry

LEFT OUTER JOIN OITM T2 on T1.ItemCode = T2.ItemCode

WHERE T1.ItemCode is not null and t3.docdate between [%0] and [%1]

GROUP BY T1.ItemCode, T2.ItemName, T1.Currency

order by sum(T1.Quantity) desc

Now I wand to generate this report based on item group separately.

Please include item group selection parameter in this query.

Thanks & Regards,

ABDUL RASHEED P

[kothandaraman_nagarajan · Accepted solution]

Please post as new discussion. This is standard forum rule.

[former_member184146]

Hi,

     Try this

SELECT TOP 20 T1.ItemCode, T2.ItemName, cast(sum(T1.Quantity) as numeric) as 'Quantity Sold', sum(T1.LineTotal - T1.VatSum) as 'Total Net Value', sum(T1.LineTotal) as 'Total Gross Value', T1.Currency

FROM dbo.INV1 T1 INNER JOIN OINV T3 ON T1.DocEntry=T3.DocEntry

LEFT OUTER JOIN OITM T2 on T1.ItemCode = T2.ItemCode

WHERE T1.ItemCode is not null AND T3.DocDate>= [%0] AND T3.DocDate<=[1%]

GROUP BY T1.ItemCode, T2.ItemName, T1.Currency

order by sum(T1.Quantity) desc

Regards,

Manish