on 01-10-2015 5:47 AM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.