where condition filter list of values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

where condition filter list of values

Author

Topic

madhan
Yak Posting Veteran

59 Posts

Posted - 2015-03-24 : 14:48:25

[code]
SELECT
c.CustomerId,
c.CustomerName,
co.OrderKey,
co.OrderNumber,
co.CustomerPO,
co.DueDate,
co.PriorityNumber,
sb.BOLNbr,
sb.ActDeparture,
sb.ACTDEPARTURECUST,
sb.ACTDELIVERY,
DATEADD(MINUTE, COALESCE(st.StandardTransportMinutes,0), sb.ActDeparture) EstDelivery,
COALESCE(st.StandardTransportMinutes,0) StandardTransportMinutes,
COALESCE(ci.InvoiceNumber,'') InvoiceNumber,
ci.Created InvoiceDate,
coc.LastTimeOff,
sb.CARRIERID
FROM CustomerOrder co
JOIN ShipTo st ON co.SiteLocationKey = st.SiteLocation
JOIN Customers c ON co.CustomerId = c.CustomerId
JOIN OrderTypes ot ON co.OrderType = ot.OrderType AND ot.Shipped = 'Y'
JOIN (SELECT DISTINCT u.OrderKey, u.BOLKey FROM Units u WHERE u.CurrentStatusKey IN (22,23,25)) u ON co.OrderKey = u.OrderKey
JOIN ShippingBOL sb ON u.BOLKey = sb.BOLNbr
JOIN CustomerOrderCache coc ON co.OrderKey = coc.OrderKey
LEFT OUTER JOIN CustomerInvoiceItems cii ON co.OrderKey = cii.OrderKey AND cii.SetupCharge = 'N'
LEFT OUTER JOIN CustomerInvoices ci ON cii.CustomerInvoiceKey = ci.CustomerInvoiceKey
----LEFT OUTER JOIN Carriers Car ON sb.CarrierId <> Car.ExcludeCarriers
WHERE co.CurrentStatusKey <> 7 AND
co.DueDate >= '2014-12-01' AND co.DueDate < DATEADD(d,1,'2014-12-31')
AND c.CustomerId LIKE 10 and
sb.CARRIERID <> (SELECT Value ExcludeCarriers FROM xxx.Properties WHERE Name = 'xxxx.reports.orderOnTimeDelivery.excludeCarriers')
[/code]

sb.CarrierId data is Sec

Carriers.ExcludeCarriers I have value as 'Sec,QB' list of values

I am trying to display sb.Carrierid that is not equal to Carriers.excludeCarriers list of values. please help. It fails now and displays sec values in resultset

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-24 : 14:57:09

[code]
(SELECT Value ExcludeCarriers FROM xxx.Properties WHERE Name = 'xxxx.reports.orderOnTimeDelivery.excludeCarriers')
[/code]

is not valid SQL Server syntax. You would at least need a comma between Value and ExcludeCarriers.

Note if you mean that e.g. Carrierid = 'QB' should be excluded, you need something more like this:

[code]
ExcludedCarriers not like '%,' + sb.carrierid + ',%' and ExcludedCarriers not like sb.carrierid + ',%' ExcludedCarriers not like '%,' + sb.carrierid
[/code]

but really it would be better if you had ExcludedCarriers in a table or derived table with one carrier per row, Then you could just say:

[code]
sb.CARRIERID NOT IN (select carrierid from excludedcarriers)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources