Comparing fields in same record - Complex 
Author Message
 Comparing fields in same record - Complex

Greetings wise guru's,

Brain well fried on this one. I need to compare fields in the same
record together looking for changes across the fields. Record
structure (ignoring ID fields and junk) as follows:

  last_value | day1 | day2 | day3 | day4 | day5 | day6 | day7

Bear in mind that the record format is fixed. Way too much code to
change to alter it. Original routine in VB looped through each field
comparing it to each other, using "day" & intCounter method. Where I
find a change I need to issue and UPDATE against another (auditing)
table

Rules
-----
 - At least one field WILL have a value
 - Any other the others may be NULL

Examples
--------

     last_value | day1 | day2 | day3 | day4 | day5 | day6 | day7
    -------------------------------------------------------------
1#      X         NULL   NULL   NULL    X     NULL    O     NULL  
2#     NULL        X      X      O      X     NULL   NULL   NULL  
3#      X         NULL   NULL   NULL    X     NULL    O     NULL  

Example 1#  - Would raise an UPDATE on day6 after moving from "X" on
day4
Example 2#  - Would raise an UPDATE on day3 after moving from "X" on
day2
              Would raise another UPDATE on day4 after moving from "O"
on day3

All of this is to run against a CURSOR in a PROC. I'm trying to come
up with a real solution but I'm burning up fast.

Any takers ?

Al

****************************
Alex Maddern
Technical Director
bigAPE Development Ltd

Web:    www.bigape.co.uk
****************************



Sun, 03 Oct 2004 01:34:22 GMT
 Comparing fields in same record - Complex

Alex,

  Using an idea for normalizing your data that Itzik Ben-Gan posted
yesterday in another thread, this should do it.  I might have misunderstood
something, since I'm assuming you want an UPDATE posted for example
#3 on day 6 as well, and I have no idea what the last_value column
represents, so I didn't use it.

CREATE TABLE Alex
(
  key_col INT NOT NULL PRIMARY KEY,
  day1 char(1) NULL,
  day2 char(1) NULL,
  day3 char(1) NULL,
  day4 char(1) NULL,
  day5 char(1) NULL,
  day6 char(1) NULL,
  day7 char(1) NULL
)

INSERT INTO Alex VALUES(1,null,null,null,'X',null,'O',null)
INSERT INTO Alex VALUES(2,'X','X','O','X',null,null,null)
INSERT INTO Alex VALUES(3,null,null,null,'X',null,'O',null)

-- Auxiliary table Nums
CREATE TABLE Nums(n INT NOT NULL PRIMARY KEY)

INSERT INTO Nums VALUES(1)
INSERT INTO Nums VALUES(2)
INSERT INTO Nums VALUES(3)
INSERT INTO Nums VALUES(4)
INSERT INTO Nums VALUES(5)
INSERT INTO Nums VALUES(6)
INSERT INTO Nums VALUES(7)
GO

CREATE VIEW AlexNormalized AS
  SELECT key_col, n as daynum, dayval
  FROM (SELECT
          key_col,
          n,
          CASE
            WHEN n = 1 THEN day1
            WHEN n = 2 THEN day2
            WHEN n = 3 THEN day3
            WHEN n = 4 THEN day4
            WHEN n = 5 THEN day5
            WHEN n = 6 THEN day6
            WHEN n = 7 then day7
          END AS dayval
        FROM Alex CROSS JOIN Nums) AS N
  WHERE dayval IS NOT NULL
GO

SELECT
  A1.key_col,
  'day'+right(A2.daynum,4) as Changed,
  A1.dayval as PrevVal,
  A2.dayval as Updated
FROM AlexNormalized A1 JOIN AlexNormalized A2
ON A1.daynum = (
  SELECT MAX(daynum) FROM AlexNormalized
  WHERE daynum < A2.daynum
)
AND A1.key_col = A2.key_col
WHERE A1.dayval <> A2.dayval
GO

DROP TABLE Alex
DROP TABLE Nums
DROP VIEW AlexNormalized

Steve Kass
Drew University

Quote:

> Greetings wise guru's,

> Brain well fried on this one. I need to compare fields in the same
> record together looking for changes across the fields. Record
> structure (ignoring ID fields and junk) as follows:

>   last_value | day1 | day2 | day3 | day4 | day5 | day6 | day7

> Bear in mind that the record format is fixed. Way too much code to
> change to alter it. Original routine in VB looped through each field
> comparing it to each other, using "day" & intCounter method. Where I
> find a change I need to issue and UPDATE against another (auditing)
> table

> Rules
> -----
>  - At least one field WILL have a value
>  - Any other the others may be NULL

> Examples
> --------

>      last_value | day1 | day2 | day3 | day4 | day5 | day6 | day7
>     -------------------------------------------------------------
> 1#      X         NULL   NULL   NULL    X     NULL    O     NULL
> 2#     NULL        X      X      O      X     NULL   NULL   NULL
> 3#      X         NULL   NULL   NULL    X     NULL    O     NULL

> Example 1#  - Would raise an UPDATE on day6 after moving from "X" on
> day4
> Example 2#  - Would raise an UPDATE on day3 after moving from "X" on
> day2
>               Would raise another UPDATE on day4 after moving from "O"
> on day3

> All of this is to run against a CURSOR in a PROC. I'm trying to come
> up with a real solution but I'm burning up fast.

> Any takers ?

> Al

> ****************************
> Alex Maddern
> Technical Director
> bigAPE Development Ltd

> Web:    www.bigape.co.uk
> ****************************



Sun, 03 Oct 2004 06:26:15 GMT
 Comparing fields in same record - Complex
Thanks for the post Steve. I surely would never have come up with that
one. Itzik's simply on another plane. Only problem is that its not
picking up changes between the "Last Known Value" and "Day1"

I've altered the naming and added a couple of description fields to
the base table only for my own benefit. The primary change to the code
is the inclusion of the "last_changed" field in the base table the
comparision should start from there and for the life of me I cant get
it to recognise them

-----------------------------------
View
-----------------------------------
1       CLOCK1  MG1     0       X
1       CLOCK1  MG1     4       X
1       CLOCK1  MG1     6       O
2       CLOCK2  MG2     1       X
2       CLOCK2  MG2     2       X
2       CLOCK2  MG2     3       O
2       CLOCK2  MG2     4       X
3       CLOCK3  MG3     0       O
3       CLOCK3  MG3     4       X
3       CLOCK3  MG3     6       O

-----------------------------------
Results
-----------------------------------
1       CLOCK1  MG1     day6    X       O
2       CLOCK2  MG2     day3    X       O
2       CLOCK2  MG2     day4    O       X
3       CLOCK3  MG3     day6    X       O

SHOULD also pick up

3       CLOCK3  MG3     day4    O       X

======================  NEW CODE  =========================

DROP TABLE week_check
GO
CREATE TABLE week_check
(
  key_col INT NOT NULL PRIMARY KEY,
  clock_number nvarchar(15) NULL,
  match_group nvarchar(3) NULL,
  last_changed char(1) NULL,
  day1 char(1) NULL,
  day2 char(1) NULL,
  day3 char(1) NULL,
  day4 char(1) NULL,
  day5 char(1) NULL,
  day6 char(1) NULL,
  day7 char(1) NULL
)

INSERT INTO week_check
VALUES(1,'CLOCK1','MG1','X',null,null,null,'X',null,'O',null)
INSERT INTO week_check
VALUES(2,'CLOCK2','MG2',null,'X','X','O','X',null,null,null)
INSERT INTO week_check
VALUES(3,'CLOCK3','MG3','O',null,null,null,'X',null,'O',null)

-- Auxiliary table Nums
DROP TABLE day_numbers
GO
CREATE TABLE day_numbers(day_number INT NOT NULL PRIMARY KEY)

INSERT INTO day_numbers VALUES(0)
INSERT INTO day_numbers VALUES(1)
INSERT INTO day_numbers VALUES(2)
INSERT INTO day_numbers VALUES(3)
INSERT INTO day_numbers VALUES(4)
INSERT INTO day_numbers VALUES(5)
INSERT INTO day_numbers VALUES(6)
INSERT INTO day_numbers VALUES(7)
GO

DROP VIEW week_check_norm
GO
CREATE VIEW week_check_norm AS
  SELECT key_col, clock_number, match_group, day_number as daynum,
dayval
  FROM (SELECT
          key_col,
          clock_number,
          match_group,
          day_number,
          CASE
            WHEN day_number = 0 THEN last_changed
            WHEN day_number = 1 THEN day1
            WHEN day_number = 2 THEN day2
            WHEN day_number = 3 THEN day3
            WHEN day_number = 4 THEN day4
            WHEN day_number = 5 THEN day5
            WHEN day_number = 6 THEN day6
            WHEN day_number = 7 then day7
          END AS dayval
        FROM week_check CROSS JOIN day_numbers) AS temp_table
  WHERE dayval IS NOT NULL
GO

select * from week_check_norm
SELECT
  wc1.key_col,
  wc1.clock_number,
  wc1.match_group,
  'day'+right(wc2.daynum,4) as Changed,
  wc1.dayval as PrevVal,
  wc2.dayval as Updated
FROM
  week_check_norm wc1
  JOIN week_check_norm wc2 ON wc1.daynum = (
    SELECT
      MAX(daynum)
    FROM
      week_check_norm
    WHERE
      daynum < wc2.daynum
  )
AND wc1.key_col = wc2.key_col
WHERE wc1.dayval <> wc2.dayval
GO

======================================================================

Quote:

> Alex,

>   Using an idea for normalizing your data that Itzik Ben-Gan posted
> yesterday in another thread, this should do it.  I might have misunderstood
> something, since I'm assuming you want an UPDATE posted for example
> #3 on day 6 as well, and I have no idea what the last_value column
> represents, so I didn't use it.

> CREATE TABLE Alex
> (
>   key_col INT NOT NULL PRIMARY KEY,
>   day1 char(1) NULL,
>   day2 char(1) NULL,
>   day3 char(1) NULL,
>   day4 char(1) NULL,
>   day5 char(1) NULL,
>   day6 char(1) NULL,
>   day7 char(1) NULL
> )

> INSERT INTO Alex VALUES(1,null,null,null,'X',null,'O',null)
> INSERT INTO Alex VALUES(2,'X','X','O','X',null,null,null)
> INSERT INTO Alex VALUES(3,null,null,null,'X',null,'O',null)

> -- Auxiliary table Nums
> CREATE TABLE Nums(n INT NOT NULL PRIMARY KEY)

> INSERT INTO Nums VALUES(1)
> INSERT INTO Nums VALUES(2)
> INSERT INTO Nums VALUES(3)
> INSERT INTO Nums VALUES(4)
> INSERT INTO Nums VALUES(5)
> INSERT INTO Nums VALUES(6)
> INSERT INTO Nums VALUES(7)
> GO

> CREATE VIEW AlexNormalized AS
>   SELECT key_col, n as daynum, dayval
>   FROM (SELECT
>           key_col,
>           n,
>           CASE
>             WHEN n = 1 THEN day1
>             WHEN n = 2 THEN day2
>             WHEN n = 3 THEN day3
>             WHEN n = 4 THEN day4
>             WHEN n = 5 THEN day5
>             WHEN n = 6 THEN day6
>             WHEN n = 7 then day7
>           END AS dayval
>         FROM Alex CROSS JOIN Nums) AS N
>   WHERE dayval IS NOT NULL
> GO

> SELECT
>   A1.key_col,
>   'day'+right(A2.daynum,4) as Changed,
>   A1.dayval as PrevVal,
>   A2.dayval as Updated
> FROM AlexNormalized A1 JOIN AlexNormalized A2
> ON A1.daynum = (
>   SELECT MAX(daynum) FROM AlexNormalized
>   WHERE daynum < A2.daynum
> )
> AND A1.key_col = A2.key_col
> WHERE A1.dayval <> A2.dayval
> GO

> DROP TABLE Alex
> DROP TABLE Nums
> DROP VIEW AlexNormalized

> Steve Kass
> Drew University


> > Greetings wise guru's,

> > Brain well fried on this one. I need to compare fields in the same
> > record together looking for changes across the fields. Record
> > structure (ignoring ID fields and junk) as follows:

> >   last_value | day1 | day2 | day3 | day4 | day5 | day6 | day7

> > Bear in mind that the record format is fixed. Way too much code to
> > change to alter it. Original routine in VB looped through each field
> > comparing it to each other, using "day" & intCounter method. Where I
> > find a change I need to issue and UPDATE against another (auditing)
> > table

> > Rules
> > -----
> >  - At least one field WILL have a value
> >  - Any other the others may be NULL

> > Examples
> > --------

> >      last_value | day1 | day2 | day3 | day4 | day5 | day6 | day7
> >     -------------------------------------------------------------
> > 1#      X         NULL   NULL   NULL    X     NULL    O     NULL
> > 2#     NULL        X      X      O      X     NULL   NULL   NULL
> > 3#      X         NULL   NULL   NULL    X     NULL    O     NULL

> > Example 1#  - Would raise an UPDATE on day6 after moving from "X" on
> > day4
> > Example 2#  - Would raise an UPDATE on day3 after moving from "X" on
> > day2
> >               Would raise another UPDATE on day4 after moving from "O"
> > on day3

> > All of this is to run against a CURSOR in a PROC. I'm trying to come
> > up with a real solution but I'm burning up fast.

> > Any takers ?

> > Al

> > ****************************
> > Alex Maddern
> > Technical Director
> > bigAPE Development Ltd

> > Web:    www.bigape.co.uk
> > ****************************



Sun, 03 Oct 2004 18:14:06 GMT
 Comparing fields in same record - Complex
Alex,

  I left out a check for matching key_col in the subquery.
This will work:

SELECT
  wc1.key_col,
  wc1.clock_number,
  wc1.match_group,
  'day'+right(wc2.daynum,4) as Changed,
  wc1.dayval as PrevVal,
  wc2.dayval as Updated
FROM
  week_check_norm wc1
  JOIN week_check_norm wc2 ON wc1.daynum = (
    SELECT
      MAX(daynum)
    FROM
      week_check_norm
    WHERE
      daynum < wc2.daynum
    AND
      key_col = wc2.key_col
  )
AND wc1.key_col = wc2.key_col
WHERE wc1.dayval <> wc2.dayval
GO

Steve

Quote:

> Thanks for the post Steve. I surely would never have come up with that
> one. Itzik's simply on another plane. Only problem is that its not
> picking up changes between the "Last Known Value" and "Day1"

> I've altered the naming and added a couple of description fields to
> the base table only for my own benefit. The primary change to the code
> is the inclusion of the "last_changed" field in the base table the
> comparision should start from there and for the life of me I cant get
> it to recognise them

> -----------------------------------
> View
> -----------------------------------
> 1       CLOCK1  MG1     0       X
> 1       CLOCK1  MG1     4       X
> 1       CLOCK1  MG1     6       O
> 2       CLOCK2  MG2     1       X
> 2       CLOCK2  MG2     2       X
> 2       CLOCK2  MG2     3       O
> 2       CLOCK2  MG2     4       X
> 3       CLOCK3  MG3     0       O
> 3       CLOCK3  MG3     4       X
> 3       CLOCK3  MG3     6       O

> -----------------------------------
> Results
> -----------------------------------
> 1       CLOCK1  MG1     day6    X       O
> 2       CLOCK2  MG2     day3    X       O
> 2       CLOCK2  MG2     day4    O       X
> 3       CLOCK3  MG3     day6    X       O

> SHOULD also pick up

> 3       CLOCK3  MG3     day4    O       X

> ======================  NEW CODE  =========================

> DROP TABLE week_check
> GO
> CREATE TABLE week_check
> (
>   key_col INT NOT NULL PRIMARY KEY,
>   clock_number nvarchar(15) NULL,
>   match_group nvarchar(3) NULL,
>   last_changed char(1) NULL,
>   day1 char(1) NULL,
>   day2 char(1) NULL,
>   day3 char(1) NULL,
>   day4 char(1) NULL,
>   day5 char(1) NULL,
>   day6 char(1) NULL,
>   day7 char(1) NULL
> )

> INSERT INTO week_check
> VALUES(1,'CLOCK1','MG1','X',null,null,null,'X',null,'O',null)
> INSERT INTO week_check
> VALUES(2,'CLOCK2','MG2',null,'X','X','O','X',null,null,null)
> INSERT INTO week_check
> VALUES(3,'CLOCK3','MG3','O',null,null,null,'X',null,'O',null)

> -- Auxiliary table Nums
> DROP TABLE day_numbers
> GO
> CREATE TABLE day_numbers(day_number INT NOT NULL PRIMARY KEY)

> INSERT INTO day_numbers VALUES(0)
> INSERT INTO day_numbers VALUES(1)
> INSERT INTO day_numbers VALUES(2)
> INSERT INTO day_numbers VALUES(3)
> INSERT INTO day_numbers VALUES(4)
> INSERT INTO day_numbers VALUES(5)
> INSERT INTO day_numbers VALUES(6)
> INSERT INTO day_numbers VALUES(7)
> GO

> DROP VIEW week_check_norm
> GO
> CREATE VIEW week_check_norm AS
>   SELECT key_col, clock_number, match_group, day_number as daynum,
> dayval
>   FROM (SELECT
>           key_col,
>           clock_number,
>           match_group,
>           day_number,
>           CASE
>             WHEN day_number = 0 THEN last_changed
>             WHEN day_number = 1 THEN day1
>             WHEN day_number = 2 THEN day2
>             WHEN day_number = 3 THEN day3
>             WHEN day_number = 4 THEN day4
>             WHEN day_number = 5 THEN day5
>             WHEN day_number = 6 THEN day6
>             WHEN day_number = 7 then day7
>           END AS dayval
>         FROM week_check CROSS JOIN day_numbers) AS temp_table
>   WHERE dayval IS NOT NULL
> GO

> select * from week_check_norm
> SELECT
>   wc1.key_col,
>   wc1.clock_number,
>   wc1.match_group,
>   'day'+right(wc2.daynum,4) as Changed,
>   wc1.dayval as PrevVal,
>   wc2.dayval as Updated
> FROM
>   week_check_norm wc1
>   JOIN week_check_norm wc2 ON wc1.daynum = (
>     SELECT
>       MAX(daynum)
>     FROM
>       week_check_norm
>     WHERE
>       daynum < wc2.daynum
>   )
> AND wc1.key_col = wc2.key_col
> WHERE wc1.dayval <> wc2.dayval
> GO

> ======================================================================


> > Alex,

> >   Using an idea for normalizing your data that Itzik Ben-Gan posted
> > yesterday in another thread, this should do it.  I might have misunderstood
> > something, since I'm assuming you want an UPDATE posted for example
> > #3 on day 6 as well, and I have no idea what the last_value column
> > represents, so I didn't use it.

> > CREATE TABLE Alex
> > (
> >   key_col INT NOT NULL PRIMARY KEY,
> >   day1 char(1) NULL,
> >   day2 char(1) NULL,
> >   day3 char(1) NULL,
> >   day4 char(1) NULL,
> >   day5 char(1) NULL,
> >   day6 char(1) NULL,
> >   day7 char(1) NULL
> > )

> > INSERT INTO Alex VALUES(1,null,null,null,'X',null,'O',null)
> > INSERT INTO Alex VALUES(2,'X','X','O','X',null,null,null)
> > INSERT INTO Alex VALUES(3,null,null,null,'X',null,'O',null)

> > -- Auxiliary table Nums
> > CREATE TABLE Nums(n INT NOT NULL PRIMARY KEY)

> > INSERT INTO Nums VALUES(1)
> > INSERT INTO Nums VALUES(2)
> > INSERT INTO Nums VALUES(3)
> > INSERT INTO Nums VALUES(4)
> > INSERT INTO Nums VALUES(5)
> > INSERT INTO Nums VALUES(6)
> > INSERT INTO Nums VALUES(7)
> > GO

> > CREATE VIEW AlexNormalized AS
> >   SELECT key_col, n as daynum, dayval
> >   FROM (SELECT
> >           key_col,
> >           n,
> >           CASE
> >             WHEN n = 1 THEN day1
> >             WHEN n = 2 THEN day2
> >             WHEN n = 3 THEN day3
> >             WHEN n = 4 THEN day4
> >             WHEN n = 5 THEN day5
> >             WHEN n = 6 THEN day6
> >             WHEN n = 7 then day7
> >           END AS dayval
> >         FROM Alex CROSS JOIN Nums) AS N
> >   WHERE dayval IS NOT NULL
> > GO

> > SELECT
> >   A1.key_col,
> >   'day'+right(A2.daynum,4) as Changed,
> >   A1.dayval as PrevVal,
> >   A2.dayval as Updated
> > FROM AlexNormalized A1 JOIN AlexNormalized A2
> > ON A1.daynum = (
> >   SELECT MAX(daynum) FROM AlexNormalized
> >   WHERE daynum < A2.daynum
> > )
> > AND A1.key_col = A2.key_col
> > WHERE A1.dayval <> A2.dayval
> > GO

> > DROP TABLE Alex
> > DROP TABLE Nums
> > DROP VIEW AlexNormalized

> > Steve Kass
> > Drew University


> > > Greetings wise guru's,

> > > Brain well fried on this one. I need to compare fields in the same
> > > record together looking for changes across the fields. Record
> > > structure (ignoring ID fields and junk) as follows:

> > >   last_value | day1 | day2 | day3 | day4 | day5 | day6 | day7

> > > Bear in mind that the record format is fixed. Way too much code to
> > > change to alter it. Original routine in VB looped through each field
> > > comparing it to each other, using "day" & intCounter method. Where I
> > > find a change I need to issue and UPDATE against another (auditing)
> > > table

> > > Rules
> > > -----
> > >  - At least one field WILL have a value
> > >  - Any other the others may be NULL

> > > Examples
> > > --------

> > >      last_value | day1 | day2 | day3 | day4 | day5 | day6 | day7
> > >     -------------------------------------------------------------
> > > 1#      X         NULL   NULL   NULL    X     NULL    O     NULL
> > > 2#     NULL        X      X      O      X     NULL   NULL   NULL
> > > 3#      X         NULL   NULL   NULL    X     NULL    O     NULL

> > > Example 1#  - Would raise an UPDATE on day6 after moving from "X" on
> > > day4
> > > Example 2#  - Would raise an UPDATE on day3 after moving from "X" on
> > > day2
> > >               Would raise another UPDATE on day4 after moving from "O"
> > > on day3

> > > All of this is to run against a CURSOR in a PROC. I'm trying to come
> > > up with a real solution but I'm burning up fast.

> > > Any takers ?

> > > Al

> > > ****************************
> > > Alex Maddern
> > > Technical Director
> > > bigAPE Development Ltd

> > > Web:    www.bigape.co.uk
> > > ****************************



Sun, 03 Oct 2004 19:33:43 GMT
 Comparing fields in same record - Complex
Thanks Steve,

Who would have guessed it... I was trying to figure out what was
missing. Nice one. I'm now off to implement and do a metrics test. I
wonder how it will work against the real table with 50K records.

Al



Sun, 03 Oct 2004 19:54:24 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. PDOX4.5--Comparing fields between records (except ONE field)

2. Complex queries - excluding records on some, but not all, of the field list

3. compare all the records of all the fields of two tables

4. Comparing fields from different records

5. HELP!! comparing fields from same record

6. Copying a record in a field to several records in same field

7. Comparing a Null field with a char field

8. How to Compare Tables, Fields, & Field Properties

9. Comparing field by field

10. Complex challenge / finding disjointed data across history records

11. Problem with complex sysindexes and complex stored procedures

12. Using a complex query to limit form records.


 
Powered by phpBB® Forum Software