I’m trying to generate a report that would group Bin Codes and only show if sum total of the Bin if 0.
Right now I’m using the Bin Contents tbl and Warehouse Entry tbl. The commented in Red-- Sum is what I found in developer but doesn’t look like sql commands?
use Custom_Dev_Test
SELECT RWE.[Location Code]
,RWE.[Item No_]
,RBC.[Bin Code]
,RBC.[Default]
,RWE.[Unit of Measure Code]
,RWE.[Variant Code]
,RWE.[Lot No_]
,RWE.[Serial No_]
,SUM(Cast(RWE.[Quantity] as float)) AS TotalQty
–, Sum(RWE.Quantity WHERE (Location Code=FIELD(Location Code),Bin Code=FIELD(Bin Code),Item No.=FIELD(Item No.),Variant Code=FIELD(Variant Code),Unit of Measure Code=FIELD(Unit of Measure Code),Lot No.=FIELD(Lot No. Filter),Serial No.=FIELD(Serial No. Filter)))
FROM Navision.dbo.[Company$Warehouse Entry] AS RWE
LEFT JOIN Navision.dbo.[Company$Bin Content] AS RBC ON
RWE.[Location Code] = RBC.[Location Code]
AND RWE.[Item No_] = RBC.[Item No_]
AND RWE.[Bin Type Code] = RBC.[Bin Type Code]
WHERE RWE.[Location Code] IN (N’BK MAIN’, N’BK PROD’, N’BK TBR’, N’BK SD’, N’BK LQ’, N’BK OFFSITE’, N’BK REBOX’, N’FIX SELL’)
AND RWE.[Unit of Measure Code] IN (N’EA’)
AND RBC.[Bin Code] not in (N’ASSEMBLYIN’, N’KIT’, N’SHIPPING’, N’PROD’, N’RECEIVING’, N’CREDITS’)
–AND RBC.[Bin Code] (N’ASSEMBLYIN’, N’KIT’, N’SHIPPING’, N’PROD’, N’RECEIVING’, N’CREDITS’)
AND RWE.[Variant Code] is Null
AND RWE.[Lot No_] is Null
AND RWE.[Serial No_] is Null
GROUP BY RWE.[Location Code]
,RWE.[Item No_]
,RBC.[Bin Code]
,RBC.[Default]
,RWE.[Unit of Measure Code]
,RWE.[Variant Code]
,RWE.[Lot No_]
,RWE.[Serial No_]
–HAVING SUM(Cast(RWE.[Quantity] as float)) <= 0 – You may want this to be = 0 to avoid negative values
–ORDER BY RBC.[Bin Code], RWE.[Location Code], RWE.[Item No_]