July 22, 2016 at 3:12 pm
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
July 23, 2016 at 6:38 am
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.
July 23, 2016 at 7:58 am
Thanks a lot SSCrazy Eights. This solves the core purpose. 🙂
August 1, 2016 at 7:52 am
Glad it helped. Always nice to see when returning from vacation. Thanks for the feedback.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply