Sql-server – WHERE statement on an inner join with GROUP BY

sql server

I need some assistance with the below. Essentially I am grabbing all lines that match certain parameters (matching itemid, custaccount, qtyordered). I need to also add a where statement to my inner join, i.e. where b.salesstatus in '3','4'. The issue I have is that I can't select salesstatus in my inner join unless I am also grouping by salesstatus? How can I add a where statement for the inner join that will be acceptable?

SELECT
 ST.DATAAREAID
,ST.CREATEDDATETIME
,ST.SALESID
,ST.SALESNAME
,SL.ITEMID
,SL.CUSTACCOUNT
,SL.QTYORDERED
,SL.SALESQTY
,SL.LINEAMOUNT
,SL.CUSTOMERREF
,ST.SALESPOOLID
,ST.RETURNITEMNUM
,CASE WHEN CONVERT(nvarchar(12), SL.SAB_PICKINGSTATUS) = '1'   THEN 'Confirmed' WHEN CONVERT(nvarchar(12), SL.SAB_PICKINGSTATUS) = '2'     THEN 'Invoiced' WHEN CONVERT(nvarchar(12), SL.SAB_PICKINGSTATUS) = '0'   THEN 'OpenOrder' ELSE 'UNKNOWN' END AS PickStatus
,CASE WHEN CONVERT(nvarchar(12), SL.SALESSTATUS) = '1' THEN 'OpenOrder'   WHEN CONVERT(nvarchar(12), SL.SALESSTATUS) = '3' THEN 'Invoiced' WHEN CONVERT  (nvarchar(12), SL.SALESSTATUS) = '4' THEN 'Cancelled' ELSE 'UNKNOWN' END AS   LineStatus
FROM dbo.SALESLINE AS SL
INNER JOIN dbo.SALESTABLE AS ST
ON SL.SALESID = ST.SALESID
AND SL.DATAAREAID = ST.DATAAREAID 
INNER JOIN dbo.INVENTTABLE ON SL.ITEMID = dbo.INVENTTABLE.ITEMID 
AND     ST.DATAAREAID = dbo.INVENTTABLE.DATAAREAID 
INNER JOIN (
  SELECT
  LINE.ITEMID
  ,LINE.CUSTACCOUNT
  ,LINE.QTYORDERED
FROM dbo.SALESLINE AS LINE
  INNER JOIN dbo.SALESTABLE AS STABLE
ON LINE.SALESID = STABLE.SALESID
AND LINE.DATAAREAID = STABLE.DATAAREAID 
GROUP BY
 LINE.CUSTACCOUNT
,LINE.QTYORDERED
,LINE.ITEMID
HAVING COUNT(*) > 1
) AS B
ON B.CUSTACCOUNT = SL.CUSTACCOUNT
AND B.QTYORDERED = SL.QTYORDERED
AND B.ITEMID = SL.ITEMID
**strong text**WHERE (SL.CREATEDDATETIME >= '2019-01-01 00:00:00')  AND (SL.SALESSTATUS != '4') AND (SL.CUSTACCOUNT NOT IN (N'2ALD217')) AND (SL.ITEMID NOT IN (N'15MEGA', N'15TACK', N'10MEGA', N'10TACK')) AND (NOT (dbo.INVENTTABLE.SAB_INVENTCLASSGROUPID IN (N'CATBRO', N'CHARGES', N'KIPLREG', N'MERCH', N'POS', N'SAMPLES', N'SWAFLD'))) AND (SL.QTYORDERED > 1) AND (SL.CUSTOMERREF NOT IN (N'')) AND (ST.SALESPOOLID NOT IN (N'POSE', N'DISP', N'INTC'))

Best Answer

Can you just put LINE.SALESSTATUS in (3,4) in inner part of subquery like below:

 SELECT
  LINE.ITEMID
  ,LINE.CUSTACCOUNT
  ,LINE.QTYORDERED
FROM dbo.SALESLINE AS LINE
INNER JOIN dbo.SALESTABLE AS STABLE ON LINE.SALESID = STABLE.SALESID AND LINE.DATAAREAID = STABLE.DATAAREAID 
where  LINE.SALESSTATUS in (3,4)
GROUP BY LINE.CUSTACCOUNT,LINE.QTYORDERED,LINE.ITEMID
HAVING COUNT(*) > 1

may be it helps you to get filtered record sets inside the inner section.