Not getting Date filter right. 1

[AuctionLamb]

Hi guys!!

I have a problem with my SQL statement, why does it not bring back the QuoteDate specified in my WHERE clause? Can you help me please.......

SELECT QUOTES.FK_sCust AS Customer, QUOTES.FK_sBranch AS BRANCH, QUOTES.dQuote_Date AS QuoteDate
FROM dbo.JOBS
LEFT JOIN dbo.QUOTES
ON dbo.JOBS.FK_iQuote_id = dbo.QUOTES.PK_iQuote_id
LEFT JOIN Staging.dbnlineOrders OO
ON dbo.JOBS.CUST_PO_NUM = OO.jobNr
LEFT JOIN vw_DespatchedGoodsReceived
ON JOBS.JobNumber = vw_DespatchedGoodsReceived.jobnumber
WHERE JOBS.JobNumber <> ''
AND(QUOTES.FK_sCust = 'ZDAM')
OR (QUOTES.FK_sCust = 'INTEC')
OR (QUOTES.FK_sCust = 'DAMELIN DBN')
OR (QUOTES.FK_sCust = 'INTEC DBN')
AND (QUOTES.dQuote_Date >= '2004-03-01')

 

[dhulbert]

You need to have a close look at how you are bracketing the where clause a the moment you are getting

(Job Number = "" and Quotes = ZDAM)
Or
(Quotes = INTEC)
Or
(Quotes = DAMELIN)
or
(Quotes = INTEC and date = 2004-03-01)

 

[vongrunt]

To make it simpler, use IN ('ZDAM', blah blah ) - then you'll have no problems with AND/OR precendence... for now :X

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[AuctionLamb]

How would that statement look like with the IN ZDAM?

 

[vongrunt]

SELECT QUOTES.FK_sCust AS Customer, QUOTES.FK_sBranch AS BRANCH, QUOTES.dQuote_Date AS QuoteDate
FROM dbo.JOBS
LEFT JOIN dbo.QUOTES
    ON dbo.JOBS.FK_iQuote_id = dbo.QUOTES.PK_iQuote_id
LEFT JOIN Staging.dbo.OnlineOrders OO
    ON dbo.JOBS.CUST_PO_NUM = OO.jobNr
LEFT JOIN vw_DespatchedGoodsReceived
    ON JOBS.JobNumber = vw_DespatchedGoodsReceived.jobnumber
WHERE JOBS.JobNumber <> ''
	AND QUOTES.FK_sCust IN ('ZDAM', 'INTEC', 'DAMELIN DBN', 'INTEC DBN')
	AND QUOTES.dQuote_Date >= '2004-03-01'

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[dhulbert]

job number = "" and quotes in(ZDAM, Intec,....) and Date = 01-04-2004

 

[AuctionLamb]

THANKS vongrunt!!