SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pivot results with columns for movements in period
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wembleybear
Yak Posting Veteran

United Kingdom
58 Posts

Posted - 03/07/2014 :  09:18:59  Show Profile  Reply with Quote
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

India
22769 Posts

Posted - 03/10/2014 :  05:07:18  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

wembleybear
Yak Posting Veteran

United Kingdom
58 Posts

Posted - 03/10/2014 :  07:17:17  Show Profile  Reply with Quote
That's brilliant - works exactly as I had hoped.

Thank you very much for your help!


Martyn
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000