Pivot results with columns for movements in period

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pivot results with columns for movements in period

Author

Topic

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2014-03-07 : 09:18:59

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 thanks
Martyn

select
      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 iv
left outer join tbl_inv_loc_summary ts on ts.item_id = iv.item
left 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_code
left outer join reasons rs on rs.id = td.reason_id



where iv.serialized =0
and
iv.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 Adjustment
008-645-001          LED AMBER SIDE MARKER [NOOT]                                                                                                                                                                                                                                    0                      01*                                                NULL                   NULL        NULL                 0                      2013-08-16 16:39:00.000 A1       Stock Adjustment
008-645-001          LED AMBER SIDE MARKER [NOOT]                                                                                                                                                                                                                                    0                      02*                                                NULL                   NULL        NULL                 0                      2013-08-16 16:31:00.000 A1       Stock Adjustment
014-PTO              500HR SERVICE KIT DUMPER                                                                                                                                                                                                                                        NULL                   NULL                                               NULL                   NULL        NULL                 NULL                   NULL                    NULL     NULL
02/100073            OIL FILTER                                                                                                                                                                                                                                                      0                      01*                                                NULL                   NULL        NULL                 0                      2013-08-16 16:39:00.000 A1       Stock Adjustment
02/100073            OIL FILTER                                                                                                                                                                                                                                                      0                      01*                                                NULL                   NULL        NULL                 1                      2014-02-11 14:55:00.000 A1       Stock Adjustment
02/100073            OIL FILTER                                                                                                                                                                                                                                                      0                      01*                                                NULL                   NULL        NULL                 -1                     2014-02-11 14:56:00.000 S2       Sale
02/100073            OIL FILTER                                                                                                                                                                                                                                                      0                      02*                                                NULL                   NULL        NULL                 1                      2013-01-23 11:51:00.000 A1       Stock Adjustment
02/100073            OIL FILTER                                                                                                                                                                                                                                                      0                      02*                                                NULL                   NULL        NULL                 0                      2013-04-10 11:51:00.000 A1       Stock Adjustment
02/100073            OIL FILTER                                                                                                                                                                                                                                                      0                      02*                                                NULL                   NULL        NULL                 -1                     2013-04-10 12:04:00.000 A1       Stock Adjustment
02/100073            OIL FILTER                                                                                                                                                                                                                                                      0                      02*                                                NULL                   NULL        NULL                 0                      2013-08-16 16:32:00.000 A1       Stock Adjustment
02/100073            OIL FILTER                                                                                                                                                                                                                                                      0                      02*                                                NULL                   NULL        NULL                 1                      2014-01-09 09:10:00.000 A1       Stock Adjustment
02/100073            OIL FILTER                                                                                                                                                                                                                                                      0                      02*                                                NULL                   NULL        NULL                 -1                     2014-01-09 09:11:00.000 S2       Sale
02/100073A           5MT ENG OIL FILTER                                                                                                                                                                                                                                              8                      01*                                                NULL                   NULL        NULL                 -1                     2013-12-12 10:41:00.000 S2       Sale
02/100073A           5MT ENG OIL FILTER                                                                                                                                                                                                                                              8                      01*                                                NULL                   NULL        NULL                 1                      2014-01-02 10:59:00.000 P1       PO Delivery

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-03-10 : 05:07:18

Something like below will work

select 
item, 
sum(case when locd_trans_date>=dateadd(day,-30,getdate()) then 1 else 0 end) as [Last 30 days],
sum(case when locd_trans_date>=dateadd(day,-60,getdate()) and locd_trans_date<dateadd(day,-30,getdate()) then 1 else 0 end) as [30-60 days],
.
.
from
( Your original query ) as t
group by item

Madhivanan

Failing to plan is Planning to fail

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2014-03-10 : 07:17:17

That's brilliant - works exactly as I had hoped.

Thank you very much for your help!

Martyn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources