shruthy (7/22/2016)
Hi,Thanks to every one who is supporting here.
I have a query:
Table A: supplierid, suppliername
TableB: ordernum, supplierid, category, orderqty, orderdate
1) grouping is done by month
2) total quantity is irrespective of category qty
3) Category would be passed by parameter in storedproc
4) Whole idea is that user wishes to see the how many units of a category has been ordered againt the total quantity of a month
I want results something like :
ordrnum suppliername categoryqty totalqty
Jan 123 abc 100 1000
Feb 456 qwe 200 3000
Mar 789 str 150 1500
what query should be passed to achieve this.
Many thanks in advance.
sunny
If I'm not your requirements correctly, this should get you part way there.
WITH cteTotals AS (
SELECT Category, TotalQty = SUM(OrderQty)
FROM dbo.TableB
GROUP BY Category
)
SELECT b.OrderNum, a.SupplierName, b.OrderQty, t.TotalQty
FROM dbo.TableA a
INNER JOIN dbo.TableB b ON b.SupplierID = a.SupplierID
CROSS APPLY cteTotals t
WHERE t.Category = b.Category
ORDER BY b.OrderNum;
Because there's no DDL with test data, this is untested. Nonetheless, I hope it helps.