I have the following stored procedure:

ALTER PROCEDURE [dbo].[GetRepeatIssues]
@thirty datetime
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Repeat varchar(50);
SELECT
e.first_name,e.last_name,db1.number,db1.date,db2.n umber,db2.date from
dashboard db1, dashboard db2, employees e where
(db1.date > @thirty OR db2.date > @thirty) AND
e.employee_id=db1.employee and
db1.employee=db2.employee and
db1.number <> db2.number and
db1.date <> db2.date and
db1.date<=db2.date and
(db1.date + 30) >= db2.date and
(
(db1.live_1 = '2' and db2.live_1 = '2') or
(db1.live_2 = '2' and db2.live_2 = '2') or
(db1.live_3 = '2' and db2.live_3 = '2') or
(db1.live_4 = '2' and db2.live_4 = '2') or
(db1.live_5 = '2' and db2.live_5 = '2') or
(db1.live_6 = '2' and db2.live_6 = '2') or
(db1.live_7 = '2' and db2.live_7 = '2') or
(db1.live_8 = '2' and db2.live_8 = '2') or
(db1.review_1 = '2' and db2.review_1 = '2') or
(db1.review_2 = '2' and db2.review_2 = '2') or
(db1.review_3 = '2' and db2.review_3 = '2') or
(db1.review_4 = '2' and db2.review_4 = '2') or
(db1.review_5 = '2' and db2.review_5 = '2') or
(db1.review_6 = '2' and db2.review_6 = '2') or
(db1.review_7 = '2' and db2.review_7 = '2') or
(db1.review_8 = '2' and db2.review_8 = '2') or
(db1.review_9 = '2' and db2.review_9 = '2') or
(db1.review_10 = '2' and db2.review_10 = '2') or
(db1.review_11 = '2' and db2.review_11 = '2') or
(db1.review_12 = '2' and db2.review_12 = '2')
)
ORDER BY db2.date DESC;
END

I am trying to find a way to track which one of the conditions
(db1.blah = '2' and db2.blah = '2') is coming true when the procedure
runs, so I can provide more data than just the fact that "there is an
issue".

Anyone have any ideas?

On Sep 13, 12:36 pm, Nate <nate.borl... (AT) westecnow (DOT) com> wrote:
Add each of those conditions to your select list as a case statement:

case when db1.live_1 = '2' and db2.live_1 = '2' then 1 else 0 end
cond1,
case when db1.live_2 = '2' and db2.live_2 = '2' then 1 else 0 end
cond2,
....

EXCELLENT! Thank you, Zeldor.


On Sep 13, 1:55 pm, ZeldorBlat <zeldorb... (AT) gmail (DOT) com> wrote:

On Sep 13, 11:36 am, Nate <nate.borl... (AT) westecnow (DOT) com> wrote:
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

What you did post has vague reserved words for data element names
(employee_<what??>, <something??>_date, <something??>_number, etc.)
It also looks like you have a non-1NF table, assuming that those
numbered columns are repeated groups.

In general, a transition history ought to have columns for a prior and
a current status with the appropriate temporal stamps. You are
mimicking a clipboard wher you write down a list in chronological
order and not creating a proper table at all. Then the bad design
leads you to trying to get the right structure in a query with all the
needless overhead.

Let's start over with some specs, please.