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:
may be it helps you to get filtered record sets inside the inner section.