1,105,633 Community Members

Exclude records in a query

Member Avatar
jovillanuev
Junior Poster in Training
81 posts since Jun 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hmmm....
nope.

Member Avatar
urtrivedi
Posting Virtuoso
1,900 posts since Dec 2008
Reputation Points: 249 [?]
Q&As Helped to Solve: 409 [?]
Skill Endorsements: 27 [?]
 
0
 

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
Member Avatar
urtrivedi
Posting Virtuoso
1,900 posts since Dec 2008
Reputation Points: 249 [?]
Q&As Helped to Solve: 409 [?]
Skill Endorsements: 27 [?]
 
0
 

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
Member Avatar
Eagletalon
Junior Poster
135 posts since Mar 2011
Reputation Points: 34 [?]
Q&As Helped to Solve: 16 [?]
Skill Endorsements: 0 [?]
 
0
 

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
You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article