Sql-server – Adding field from another table into exising functioning query

sql servert-sql

We have 2 systems, the Warehouse management system (WMS) and SAP.

I have a query I can run in SAP which will generate me a TRUE free stock report for all items based on information in the WMS (A1WMS).

The query generate 2 columns, 'WH Free Stock' and PRODUCT

Each PRODUCT is also in our SAP database. I want to pull the product group assigned to these products into the report as a third column. How do I achieve this?

thanks

SELECT 

   CAST(TB2.QTY_UNALLOCATED as nvarchar) [WH Free Stock], TB2.PRODUCT
FROM 
 (SELECT T40.PRODUCT , SUM(T40.QUANTITY * T40.PACKSIZE) - SUM(ISNULL(T41.QTY_PICKLOC, 0)) - SUM(ISNULL(T42.QTY_REPLENIS, 0)) QTY_UNALLOCATED
                              FROM A1WMS.DBO.BINLOCAT T40 
                                    JOIN A1WMS.DBO.BINMSTR T47 ON T40.BINLABEL = T47.BINLABEL
                                    LEFT JOIN (SELECT T41.EXTENDED, T41.BINLABEL, T41.PACKSIZE, SUM((T41.QTY_TOPICK - T41.QTY_PICKED) / T41.PACKSIZE) QTY_PICKLOC
                                                      FROM A1WMS.DBO.PICKLOC T41 WHERE T41.PCKLOC_ST = '' AND T41.ALLOCATED = '1'
                                                      GROUP BY T41.EXTENDED, T41.BINLABEL, T41.PACKSIZE) T41 ON T40.EXTENDED = T41.EXTENDED AND T40.BINLABEL = T41.BINLABEL AND T40.PACKSIZE = T41.PACKSIZE
                                    LEFT JOIN (SELECT T42.EXTENDED, T42.BULKSLOT, T42.BULKPACK, SUM((T42.QTY_REQD * T42.PACKSIZE) / T42.BULKPACK) QTY_REPLENIS
                                                      FROM A1WMS.DBO.REPLENIS T42
                                                      GROUP BY T42.EXTENDED, T42.BULKSLOT, T42.BULKPACK) T42 ON T40.EXTENDED = T42.EXTENDED AND T40.BINLABEL = T42.BULKSLOT AND T40.PACKSIZE = T42.BULKPACK
                              WHERE (T40.QUANTITY > 0 OR T40.BINTYPE <> 'S') AND T40.RESERVED NOT IN ('B', 'I') AND
                              T40.BINLABEL NOT LIKE '#%' AND T47.RESERVED <> 'B' AND T40.BINLABEL NOT LIKE 'RECV%' AND
                              T40.BINLABEL NOT LIKE 'QUAR%' AND T40.BINLABEL NOT LIKE 'REP%' AND T40.BINLABEL NOT LIKE 'RET%' AND
                              T40.ZONE NOT IN ('Q','S','L','C','R')
                              GROUP BY T40.PRODUCT) TB2

CURRENT OUTCOME / DESIRED OUTCOME

Best Answer

Using the comment provided by MguerraTorres, here is the full select:

SELECT 
   CAST(TB2.QTY_UNALLOCATED as nvarchar) [WH Free Stock], TB2.PRODUCT, ISNULL(NewLive.ProductGroup,'NoProductGroupFound')
FROM 
 (SELECT T40.PRODUCT , SUM(T40.QUANTITY * T40.PACKSIZE) - SUM(ISNULL(T41.QTY_PICKLOC, 0)) - SUM(ISNULL(T42.QTY_REPLENIS, 0)) QTY_UNALLOCATED
                              FROM A1WMS.DBO.BINLOCAT T40 
                                    JOIN A1WMS.DBO.BINMSTR T47 ON T40.BINLABEL = T47.BINLABEL
                                    LEFT JOIN (SELECT T41.EXTENDED, T41.BINLABEL, T41.PACKSIZE, SUM((T41.QTY_TOPICK - T41.QTY_PICKED) / T41.PACKSIZE) QTY_PICKLOC
                                                      FROM A1WMS.DBO.PICKLOC T41 WHERE T41.PCKLOC_ST = '' AND T41.ALLOCATED = '1'
                                                      GROUP BY T41.EXTENDED, T41.BINLABEL, T41.PACKSIZE) T41 ON T40.EXTENDED = T41.EXTENDED AND T40.BINLABEL = T41.BINLABEL AND T40.PACKSIZE = T41.PACKSIZE
                                    LEFT JOIN (SELECT T42.EXTENDED, T42.BULKSLOT, T42.BULKPACK, SUM((T42.QTY_REQD * T42.PACKSIZE) / T42.BULKPACK) QTY_REPLENIS
                                                      FROM A1WMS.DBO.REPLENIS T42
                                                      GROUP BY T42.EXTENDED, T42.BULKSLOT, T42.BULKPACK) T42 ON T40.EXTENDED = T42.EXTENDED AND T40.BINLABEL = T42.BULKSLOT AND T40.PACKSIZE = T42.BULKPACK
                              WHERE (T40.QUANTITY > 0 OR T40.BINTYPE <> 'S') AND T40.RESERVED NOT IN ('B', 'I') AND
                              T40.BINLABEL NOT LIKE '#%' AND T47.RESERVED <> 'B' AND T40.BINLABEL NOT LIKE 'RECV%' AND
                              T40.BINLABEL NOT LIKE 'QUAR%' AND T40.BINLABEL NOT LIKE 'REP%' AND T40.BINLABEL NOT LIKE 'RET%' AND
                              T40.ZONE NOT IN ('Q','S','L','C','R')
                              GROUP BY T40.PRODUCT) TB2
LEFT JOIN NewLive.dbo.OITM NewLive on NewLive.Product = TB2.Product

The usage of a LEFT JOIN against NewLive.dbo.OITM ensures that you'll get all rows from TB2 even if no matching product can be found on OITM. If a matching product can't be found on OITM, nulls will be returned for Newlive.ProductGroup. You could then use ISNULL to provide a more meaningful description like 'NoProductGroupFound' NewLive.ProductGroup was added to the top most SELECT statement.

This ALL assumes that the login that is running this query also has appropriate permissions in the NewLive database.