CODEWITHSUNDEEP
sql-servert-sql
booody
booody

Reputation: 79

T-Sql Query : how to make two conditions met in one record and not one only

I have a query the following query :

select Opp.[ID], Opp.ContactID, Opp.RTSID, Opp.[ProjectName],
       CONVERT(varchar, Opp.[PurchaseDate], 103) as PurchaseDate,
       CONVERT(varchar,Opp.[SubmissionDate],103) as SubmissionDate,
       Opp.[Cost], Opp.[Selling], Opp.[CompanyName], Opp.[Telephone], 
       Opp.[Fax], Con.[ContactName], Con.[ContactPosition], 
       Con.[ContactDirectPhone], Con.[ContactMobile], Con.[ContactEmail],
       lookup.DescriptionEn as ProjectStatus, Opp.[Wining], Opp.[Remark],
       Opp.[CustomerReference], Sales.FName + ' ' + Sales.LName as SalesMan
from Opportunities Opp inner join Sales on Opp.Sales_Num = Sales.Sales_Num
     left join
       (select MINOR, DescriptionEn from lookup_table where MAJOR = '1') lookup
       ON lookup.MINOR = Opp.ProjectStatus
     left join
       (select ID, ContactName, ContactPosition, ContactDirectPhone, ContactMobile,
                   ContactEmail from Contacts) Con ON Con.ID = Opp.ContactID
where (lookup.DescriptionEn = @ProjectStatus or @ProjectStatus ='-1') and 
      (Opp.Sales_Num = @SalesMan or @SalesMan ='-1') and
      (Opp.Selling >= @Selling or @Selling ='0') and 
      (lookup.MINOR <> '7' and datediff(day , Opp.SubmissionDate , getdate()) < 30 )
End

The last Line which conntains Condition and (lookup.MINOR <> '7' or .......) doesn't work as expected it either gets the records where MINOR <> '7' or other

I want to specify the two condition to met in one record only

How to do that?

Upvotes: 1

Views: 610

Answers (2)

Oleg Dok
Oleg Dok

Reputation: 21766

Use AND instead of OR.

Upvotes: 2

aF.
aF.

Reputation: 66707

Replace

(lookup.MINOR <> '7' and datediff(day , Opp.SubmissionDate , getdate()) < 30 )

by

(lookup.MINOR <> '7' OR datediff(day , Opp.SubmissionDate , getdate()) < 30 )

Upvotes: 2

Related Questions