I have a table with line items from many orders, broken down into sub-orders. Each I need to generate a unique guid for each sub-order, as well as calculate which item was ordered in the largest quantity and set that as the "key item" for the sub-order. The table should look like this:
client|office|day|month|year|sub-order|item|amount|key_item|sub_order_guid
1|1|1|1|2015|1||12875|100|12875||21FA413A-98AE-48FC-943B-504856A1E4AD
1|1|5|2|2015|1||16784|63|16784||B22784B7-274D-4452-AB78-A498D69A91BC
2|1|7|2|2015|1||13547|75|13547||1A86C3D4-050F-4DE5-B340-F58D461A70A0
1|2|13|2|2015|1||13547|43|78467||BC859235-7DE3-43D0-A817-CFAF029A604E
1|2|13|2|2015|1||78467|104|78467||BC859235-7DE3-43D0-A817-CFAF029A604E
1|2|13|2|2015|2||78684|71|78684||DE4C3A38-8ED2-4B9C-A9B9-B6010B62BD9E
How do I go about grouping the sub-order lines together and determining which item was purchased the most? And then how do I generate the guid to assign to the entire sub-order? Am I explaining this sufficiently?
I've tried the following, but it isn't working as I expected:
select client_id, office_id, start_year, start_month, start_day, sub_order_number, item_id, max(item_amount)
over(partition by client_id, office_id, start_year, start_month, start_day, sub_order_number) as 'Max'
from orders
order by start_year, start_month, start_day