I have a query which returns the movements to and from our warehouse stock, as well as the current stock for each depot and how much is on order. What I need is a kind of pivot so that each item is shown just once, and then summarises the movements in 4 extra columns: Last 30 days, 30-60 days, 60-90 days and 90-120 days. How can I achieve this with my query below? A sample of some of the results is also shown.Many thanksMartynselect iv.item, iv.descr, ts.loc_total_on_hand [Stock], ts.loc_code [Depot], po.qty [On Order], po.office [Order Depot], po.ponum [O/Num], td.locd_invent [Mvt Qty], td.locd_trans_date [Mvt Date], td.locd_trans_doc_type [Mvt Type], rs.name [reason] from inventory ivleft outer join tbl_inv_loc_summary ts on ts.item_id = iv.itemleft outer join poitem po on po.item = iv.item and po.status = 'A' and po.office = left(ts.loc_code,2)left outer join tbl_inv_loc_detail td on td.item_id = iv.item and td.locd_code = ts.loc_codeleft outer join reasons rs on rs.id = td.reason_idwhere iv.serialized =0andiv.itype = 'S'order by iv.item,ts.loc_code,po.ponum
item descr Stock Depot On Order Order Depot O/Num Mvt Qty Mvt Date Mvt Type reason-------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- -------------------------------------------------- ---------------------- ----------- -------------------- ---------------------- ----------------------- -------- ------------------------------008-645-001 LED AMBER SIDE MARKER [NOOT] 0 01* NULL NULL NULL 0 2013-02-25 00:00:00.000 A1 Stock Adjustment008-645-001 LED AMBER SIDE MARKER [NOOT] 0 01* NULL NULL NULL 0 2013-08-16 16:39:00.000 A1 Stock Adjustment008-645-001 LED AMBER SIDE MARKER [NOOT] 0 02* NULL NULL NULL 0 2013-08-16 16:31:00.000 A1 Stock Adjustment014-PTO 500HR SERVICE KIT DUMPER NULL NULL NULL NULL NULL NULL NULL NULL NULL02/100073 OIL FILTER 0 01* NULL NULL NULL 0 2013-08-16 16:39:00.000 A1 Stock Adjustment02/100073 OIL FILTER 0 01* NULL NULL NULL 1 2014-02-11 14:55:00.000 A1 Stock Adjustment02/100073 OIL FILTER 0 01* NULL NULL NULL -1 2014-02-11 14:56:00.000 S2 Sale02/100073 OIL FILTER 0 02* NULL NULL NULL 1 2013-01-23 11:51:00.000 A1 Stock Adjustment02/100073 OIL FILTER 0 02* NULL NULL NULL 0 2013-04-10 11:51:00.000 A1 Stock Adjustment02/100073 OIL FILTER 0 02* NULL NULL NULL -1 2013-04-10 12:04:00.000 A1 Stock Adjustment02/100073 OIL FILTER 0 02* NULL NULL NULL 0 2013-08-16 16:32:00.000 A1 Stock Adjustment02/100073 OIL FILTER 0 02* NULL NULL NULL 1 2014-01-09 09:10:00.000 A1 Stock Adjustment02/100073 OIL FILTER 0 02* NULL NULL NULL -1 2014-01-09 09:11:00.000 S2 Sale02/100073A 5MT ENG OIL FILTER 8 01* NULL NULL NULL -1 2013-12-12 10:41:00.000 S2 Sale02/100073A 5MT ENG OIL FILTER 8 01* NULL NULL NULL 1 2014-01-02 10:59:00.000 P1 PO Delivery