Exclude records in a query

[jovillanuev]

Hmmm....
nope.

[urtrivedi]

If your item id list is fixed as shown above you can write where condition as given below

Select
v.ITEMID,
i.ITEMNAME,
AVG(CASE WHEN v.lvl=3 THEN v.LEADTIME END) AS ThreeMonths,
AVG(CASE WHEN v.lvl=6 THEN v.LEADTIME END) AS Sixmnths,
--activate to get the proportional leadtime
ROUND(SUM(CASE WHEN v.lvl=3 THEN v.Total END) / SUM(CASE WHEN v.lvl=3 THEN v.OrderQty END),0) AS ThreeProportionalLT,
ROUND(SUM(CASE WHEN v.lvl=6 THEN v.Total END) / SUM(CASE WHEN v.lvl=6 THEN v.OrderQty END),0) AS SixProportionalLT
From LeadTable v
left Outer Join Dynamics.dbo.INVENTTABLE i
On v.ITEMID = i.ITEMID
where i.ITEMID NOT IN ('HTC1012','HTC1016','HTC1017')
Group by v.ITEMID, i.ITEMNAME
order by v.ITEMID

[urtrivedi]

If your item id list is fixed as shown above you can write where condition as given below

Select
v.ITEMID,
i.ITEMNAME,
AVG(CASE WHEN v.lvl=3 THEN v.LEADTIME END) AS ThreeMonths,
AVG(CASE WHEN v.lvl=6 THEN v.LEADTIME END) AS Sixmnths,
--activate to get the proportional leadtime
ROUND(SUM(CASE WHEN v.lvl=3 THEN v.Total END) / SUM(CASE WHEN v.lvl=3 THEN v.OrderQty END),0) AS ThreeProportionalLT,
ROUND(SUM(CASE WHEN v.lvl=6 THEN v.Total END) / SUM(CASE WHEN v.lvl=6 THEN v.OrderQty END),0) AS SixProportionalLT
From LeadTable v
left Outer Join Dynamics.dbo.INVENTTABLE i
On v.ITEMID = i.ITEMID
where i.ITEMID NOT IN ('HTC1012','HTC1016','HTC1017')
Group by v.ITEMID, i.ITEMNAME
order by v.ITEMID

[Eagletalon]

I agree with urtrivedi... Indeed "NOT IN" looks like the clause you are looking for JOV... however if it is not fixed... find a query that only displays those items you want to exclude (1 column) and run the query as:

Code blocks are created by indenting at least 4 spaces
... and can span multiple lines
            Select
            v.ITEMID,
            i.ITEMNAME,
            AVG(CASE WHEN v.lvl=3 THEN v.LEADTIME END) AS ThreeMonths,
            AVG(CASE WHEN v.lvl=6 THEN v.LEADTIME END) AS Sixmnths,
            --activate to get the proportional leadtime
            ROUND(SUM(CASE WHEN v.lvl=3 THEN v.Total END) / SUM(CASE WHEN v.lvl=3 THEN v.OrderQty END),0) AS ThreeProportionalLT,
            ROUND(SUM(CASE WHEN v.lvl=6 THEN v.Total END) / SUM(CASE WHEN v.lvl=6 THEN v.OrderQty END),0) AS SixProportionalLT
            From LeadTable v
            left Outer Join Dynamics.dbo.INVENTTABLE i
            On v.ITEMID = i.ITEMID
            where i.ITEMID NOT IN (/*<INSERT SUB QUERY HERE>*/)
            Group by v.ITEMID, i.ITEMNAME
            order by v.ITEMID