LOJ and MIN brain-freeze 1

[Ach005ki]

Hi guys

I'm really struggling today - I've been laid up in bed all weekend with stomach cramps (I'll not elaborate) and I'm still not feeling 100%...

... I can't get my head around how to pull the earliest outstanding Purchase Order from a PO detail table so that I can show the quantity outstanding on the earliest PO (if one exists) regardless of whether there are any newer POs with an outstanding balance.

I'd be linking the PO info from the Inventory Warehouse table to the Purchase Order detail table, so I'd have thought I need a LOJ but I'm getting confuzzled.

This is as far as I've got. I feel like I should be using a MIN(PurchaseOrder) function to find the smallest live (i.e. not completed or cancelled) purchase order which has an outstanding quantity for each applicable stock item in the Inv Warehouse table.... but then again I think it may be that I need help on a mental health scale this afternoon (in the UK).

Here goes my paltry effort so far:

SELECT InvWarehouse.Warehouse AS 'Warehouse'
     , ISNULL( PorMasterDetail.PurchaseOrder, 'N/A' ) AS 'PurchaseOrder'
     , InvWarehouse.StockCode AS 'StockCode'
     , InvMaster.LeadTime as 'LeadTime'
     , ISNULL( PorMasterDetail.MOrderQty, 0 ) AS 'OrderQty'
     , ISNULL( PorMasterDetail.MReceivedQty, 0 ) AS 'ReceivedQty'
  FROM SysproCompanyH.dbo.InvWarehouse InvWarehouse
  LEFT OUTER
  JOIN SysproCompanyH.dbo.PorMasterDetail PorMasterDetail
    ON InvWarehouse.Warehouse = PorMasterDetail.MWarehouse
   AND InvWarehouse.StockCode = PorMasterDetail.MStockCode 
 WHERE InvWarehouse.Warehouse ='JW' ... run out of ideas!

So, if I had inventory item ABC01 which had two live Purchase Orders with outstanding quantities for item ABC01 .. PO021 and PO036 .. then I would only want PO021 to show on my report:

[tt]
Warehouse Purchase-Order Item LeadTime PO-OrdQty PO-RcvQty
JW PO021 ABC01 2 12 6
[/tt]


Please help!

Mark, somewhere near Blackburn Lancs!

 

[bborissov]

[COLOR=blue]SELECT[/color] InvWarehouse.Warehouse [COLOR=blue]AS[/color] [COLOR=red]'Warehouse'[/color]
     , [COLOR=#FF00FF]ISNULL[/color]( PorMasterDetail.PurchaseOrder, [COLOR=red]'N/A'[/color] ) [COLOR=blue]AS[/color] [COLOR=red]'PurchaseOrder'[/color]
     , InvWarehouse.StockCode [COLOR=blue]AS[/color] [COLOR=red]'StockCode'[/color]
     , InvMaster.LeadTime [COLOR=blue]as[/color] [COLOR=red]'LeadTime'[/color]
     , [COLOR=#FF00FF]ISNULL[/color]( PorMasterDetail.MOrderQty, 0 ) [COLOR=blue]AS[/color] [COLOR=red]'OrderQty'[/color]
     , [COLOR=#FF00FF]ISNULL[/color]( PorMasterDetail.MReceivedQty, 0 ) [COLOR=blue]AS[/color] [COLOR=red]'ReceivedQty'[/color]
  [COLOR=blue]FROM[/color] SysproCompanyH.dbo.InvWarehouse InvWarehouse
  [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] PorMasterDetail.MWarehouse,
                    PorMasterDetail.MStockCode,
                    PorMasterDetail.PurchaseOrder,
                    PorMasterDetail.MReceivedQty,
                    PorMasterDetail.MOrderQty
             [COLOR=blue]FROM[/color] SysproCompanyH.dbo.PorMasterDetail Tbl1
             [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] MWarehouse,
                                MStockCode,
                                [COLOR=#FF00FF]MIN[/color](PurchaseOrder) [COLOR=blue]AS[/color] PurchaseOrder
                         [COLOR=blue]FROM[/color] SysproCompanyH.dbo.PorMasterDetail
                         [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] MWarehouse, MStockCode) Tbl2
             [COLOR=blue]ON[/color] Tbl1.MWarehouse    = Tbl2.MWarehouse AND
                Tbl1.MStockCode    = Tbl2.MStockCode AND
                Tbl1.PurchaseOrder = Tbl2.PurchaseOrder) PorMasterDetail
    [COLOR=blue]ON[/color] InvWarehouse.Warehouse = PorMasterDetail.MWarehouse
   AND InvWarehouse.StockCode = PorMasterDetail.MStockCode
 [COLOR=blue]WHERE[/color] InvWarehouse.Warehouse =[COLOR=red]'JW'[/color]

not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Ach005ki]

Hi Boris, I'm back in work today so I'll try your suugestion ASAP and post up - many thanks for your assistance, I appreciate the code is untested but my whole energy yesterday was devoted to being present at work without lapsing into sickness!

Mark, somewhere near Blackburn Lancs!