on 01-14-2015 10:10 AM
Hi Experts,
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:
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,T4.[ItmsGrpNam]
FROM dbo.INV1 T1 inner join OINV T3 on T1.docentry = t3.docentry
LEFT OUTER JOIN OITM T2 on T1.ItemCode = T2.ItemCode INNER JOIN OITB T4 ON T4.ItmsGrpCod = T2.ItmsGrpCod
WHERE T1.ItemCode is not null and t3.docdate between [%0] and [%1] and T4.[ItmsGrpNam] = '[%2]'
GROUP BY T1.ItemCode, T2.ItemName, T1.Currency,T4.[ItmsGrpNam]
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
Try
This One
SELECT TOP 20 T1.ItemCode, T2.ItemName, T3.[ItmsGrpNam], 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 INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod
WHERE T1.ItemCode is not null and t3.docdate between [%0] and [%1] and T3.[ItmsGrpNam] =[%0]
GROUP BY T1.ItemCode, T2.ItemName, T1.Currency
order by sum(T1.Quantity) desc
Thanks&Regards
Andakondaramudu
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Use the below query
SELECT TOP 20 T4.ItmsGrpNam,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
inner join OITB T4 ON T4.ItmsGrpCod=T2.ItmsGrpCod
WHERE T1.ItemCode is not null and t3.docdate between [%0] and [%1]
GROUP BY T4.ItmsGrpNam,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 |
---|---|
107 | |
12 | |
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.