|
|
Posted by ZeldorBlat on 09/13/07 18:55
On Sep 13, 12:36 pm, Nate <nate.borl...@westecnow.com> wrote:
> 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.number,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?
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,
....
[Back to original message]
|