Inventory Script

 SELECT T1.[ITEMID]

  , T7.[Name]

      ,CASE WHEN [ITEMTYPE] = 0 THEN 'ITEM'

WHEN [ITEMTYPE] = 2 THEN 'SERVICE'

ELSE 'ITEM' END AS ITEMTYPE

-- , ISNULL(d.ITEM_STATUS, 'ACTIVE') ITEM_STATUS

  , T11.NAME [ITEM_GROUP]

      ,[PRIMARYVENDORID]

      ,[NETWEIGHT]

      ,[UNITVOLUME]

      ,[ORIGCOUNTRYREGIONID]

      ,[NAMEALIAS]

      ,[ITEMBUYERGROUPID]

      ,T12.[UNITID] SKU 

      ,[TARAWEIGHT]

      ,[GOD_ISMOVEDTOWMS]ISMOVEDTOWMS

      ,[GITL_BPCPRODUCTCATEGORY_ID]BPCPRODUCTCATEGORYID

      ,[GOD_ACTIVECHANGEDBY]ACTIVECHANGEDBY

      ,[GOD_ACTIVECHANGEDDATE]ACTIVECHANGEDDATE

      ,[GOD_AVERAGEWEIGHT]AVERAGEWEIGHT

      ,[GOD_BPCPRODUCT_SUB_CATEGORYID]BPCPRODUCT_SUB_CATEGORYID

      ,T1.[GOD_BPCPRODUCTCATEGORY_ID]BPCPRODUCTCATEGORY_ID

      ,T1.[GOD_BPCPRODUCTGROUP_ID]BPCPRODUCTGROUP_ID

      ,[GOD_BRANDCODE]BRANDCODE

      ,ISNULL(T4.[GOD_BPCBRAND_NAME],'NA') [GOD_BPCBRAND_NAME]

      ,T1.[GOD_BRANDMANAGER_ID]BRANDMANAGER_ID

      ,ISNULL(T5.[GOD_BRANDMANAGER_NAME],'NA')[GOD_BRANDMANAGER_NAME]

      ,ISNULL(T5.[GOD_BMREFERENCE],'NA')[GOD_BMREFERENCE]

      ,[GOD_CONDITION]CONDITION

      ,[GOD_CSCNT]

      ,[GOD_INACTIVE]

      ,[GOD_IPCNT]

      ,[GOD_ITEMGROUPCODE]

      ,[GOD_ITEMPACKINGTYPE]

      ,[GOD_PLCNT]

      ,[GOD_STORE]STORE

      ,[GOD_STOREAREA]STOREAREA

      ,[GOD_STOREZONE]STOREZONE

      ,[GOD_WEIGHABLE]WEIGHABLE

      ,[GOD_WMSUNIT]WMSUNIT

      ,[GOD_PROMOITEM]PROMOITEMD

      ,T1.[GOD_FAMILYCODE]FAMILYCODE

      ,ISNULL(T9.[DESCRIPTION],'NA')FAMILY

      ,[GOD_LATESTPURCHPRICE]LATESTPURCHPRICE

      ,[GOD_CNT]

      ,[GOD_TEMPERATURE]TEMPERATURE

      ,[GOD_PDSSHELFLIFE]PDSSHELFLIFE

      ,T14.EXTERNALITEMID

      ,T1.[MODIFIEDDATETIME]

      ,T1.[DEL_MODIFIEDTIME]

      ,T1.[MODIFIEDBY]

      ,T1.[CREATEDDATETIME]

      ,T1.[DEL_CREATEDTIME]

      ,T1.[CREATEDBY]

      ,T1.[DATAAREAID]

      ,T1.[RECVERSION]

      ,T1.[RECID]

      ,T15.[UNITID] PurchUnit

 FROM   [INVENTTABLE] T1

 INNER JOIN   ECORESPRODUCT T6 ON T1.PRODUCT = T6.RECID

 INNER JOIN   ECORESPRODUCTTRANSLATION T7 ON T1.PRODUCT = T7.PRODUCT AND T7.LANGUAGEID  = 'EN-US'

 INNER JOIN    INVENTITEMGROUPITEM T8 ON T8.ITEMID = T1.ITEMID AND T8.ITEMDATAAREAID = T1.DATAAREAID 

 LEFT join   inventitemgroup T11 on T11.ITEMGROUPID = T8.ITEMGROUPID and T8.ITEMDATAAREAID = T11.DATAAREAID

 LEFT JOIN   GOD_ProductCategory T2 ON T1.[GOD_BPCPRODUCTCATEGORY_ID] = T2.GOD_BPCProductCategory_ID and T1.DataareaID = T2.DataAreaID

 LEFT JOIN   GOD_ProductGroupMaster T3 on T3.[GOD_BPCPRODUCTGROUP_ID] = T1.[GOD_BPCPRODUCTGROUP_ID]     and T3.DataareaID = T1.DataAreaID

 LEFT JOIN   GOD_BRAND_SECTIONMASTER T4 on T4.[GOD_BPCBRAND_ID] = T1.[GOD_BRANDCODE] and T4.DATAAREAID = T1.DATAAREAID

 LEFT JOIN   God_BrandManagerCode T5 on T5.God_BrandManager_ID = T1.[GOD_BRANDMANAGER_ID] and  T5.DATAAREAID = T1.DATAAREAID

 LEFT JOIN   GOD_ItemFamilyMaster T9 ON T1.[GOD_FAMILYCODE] = T9.CODE AND T1.DATAAREAID = T9.DATAAREAID

 INNER JOIN   INVENTTABLEMODULE T12 ON T12.ITEMID = T1.ITEMID AND T12.DATAAREAID = T1.DATAAREAID AND T12.MODULETYPE = 0 

 INNER JOIN   INVENTTABLEMODULE T15 ON T15.ITEMID = T1.ITEMID AND T15.DATAAREAID = T1.DATAAREAID AND T15.MODULETYPE = 1 

 INNER JOIN   UNITOFMEASURE T13 ON T13.SYMBOL = T12.UNITID

 LEFT JOIN CustVendExternalItem T14 ON T14.ITEMID = T1.ITEMID AND T14.DATAAREAID = T1.DATAAREAID 

 WHERE T1.DATAAREAID = 'GH01'

DeadStock Sql Query

 declare @DataAreaId nvarchar(4) = 'GH01',

@datePeriod int = 180

 

 

select s.DATAAREAID,s.ITEMID,i.GOD_BRANDMANAGER_ID, i.PmfProductType, i.GOD_INACTIVE,isnull(t1.lastdate,'03-01-2020') [LASTDATE],m.UNITID, sum(s.AVAILPHYSICAL) [AVAILPHYSICAL] ,sum(s.RESERVPHYSICAL) [RESERVORDERED],

          sum(s.AVAILPHYSICAL) + sum(s.RESERVPHYSICAL) [TOTALPHYSICAL]

, sum(s.PHYSICALVALUE)[PHYSICALVALUE] , sum(s.POSTEDVALUE) [POSTEDVALUE],  sum(s.PHYSICALVALUE) + sum(s.POSTEDVALUE) [TOTALCOST]

 

from INVENTSUM s

LEFT join (

          select tr.DATAAREAID, tr.ITEMID, max(tr.DATESTATUS) [LastDate]

          from INVENTTRANS tr

          inner join INVENTTRANSORIGIN o on o.RECID = tr.INVENTTRANSORIGIN

          where tr.DATAAREAID = @DataAreaId and o.REFERENCECATEGORY = 0 and tr.STATUSISSUE <> 0

          group by tr.DATAAREAID,tr.ITEMID

          ) t1 on t1.DATAAREAID = s.DATAAREAID and t1.ITEMID = s.ITEMID

          inner join INVENTTABLEMODULE m on m.DATAAREAID = s.DATAAREAID and m.ITEMID = s.ITEMID and m.MODULETYPE = 0

  inner join inventTable I on I.ITEMID = s.ITEMID and i.DATAAREAID= s.DATAAREAID

 

 

where s.DATAAREAID = @DataAreaId  and s.CLOSED = 0 and isnull(t1.LastDate,'03-01-2020') <= GETDATE() - @datePeriod

 

group by s.DATAAREAID,s.ITEMID,isnull(t1.lastdate,'03-01-2020'),m.UNITID,i.GOD_BRANDMANAGER_ID, i.GOD_INACTIVE, i.PmfProductType

Having sum(s.AVAILPHYSICAL) + sum(s.RESERVPHYSICAL) > 0