Posted by --CELKO-- on 09/14/07 21:54

On Sep 13, 11:36 am, 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?

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.

[Back to original message]