SQL Query With Multiple Where Clauses

November 25, 2024

I’m running a query on our system to catch when one of our products is enddated and the other is still active (the 4712 end of time value).

SystemProductID StartDate   EndDate
5148478 2024-05-02 00:00:00.000 4712-12-31 00:00:00.000
5147971 2022-11-17 00:00:00.000 4712-12-31 00:00:00.000
5147313 2023-06-12 00:00:00.000 2024-07-21 00:00:00.000
5146324 2024-05-03 00:00:00.000 2024-07-21 00:00:00.000
5111311 2024-04-23 00:00:00.000 4712-12-31 00:00:00.000

I tried the below but I don’t get any results

select * from product  with (nolock)
where (SystemProductID = '5146324' and EndDate != '4712-12-31 00:00:00.000')
and (SystemProductID = '5148478' and EndDate = '4712-12-31 00:00:00.000')

And this one but I just get the full results like there are no where statements.

select * from product  with (nolock)
where exists (select * from product with (nolock) where SystemProductID = '5146324' and EndDate != '4712-12-31 00:00:00.000')
and exists (select * from product with (nolock) where SystemProductID = '5148478' and EndDate = '4712-12-31 00:00:00.000')

I would like for the results to show as

SystemProductID StartDate   EndDate
5148478 2024-05-02 00:00:00.000 4712-12-31 00:00:00.000
5146324 2024-05-03 00:00:00.000 2024-07-21 00:00:00.000

Any help would be appreciated. Thanks!

>Solution :

In your first query you have “SystemProductID = ‘5146324’ and … SystemProductID = ‘5148478’ “ which obviously won’t return any records as no record can have both values.

You probably want an OR in there somewhere