Comparing one day's table dump with another's? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Comparing one day's table dump with another's?

Author

Topic

dbrowne
Starting Member

11 Posts

Posted - 2005-06-07 : 06:09:24

There is a denormalised table describing a company structure
which is wiped and repopulated daily. As there is a need for
keeping track of changes, a DTS package copies over the
whole hierarchy every night (yup - massive redundancy...)
to a SQL Server 2000 table with a timestamp and identity
field, so that there is an archive containing the
company's structure for each day (it holds 60 days of
data).

I know that this is very far from an ideal scenario but I
am stuck with it, unfortunately. My problem is -
can I use a single query to show all differences (inserts,
updates and deletions) between how the structure looked
today, and, say, 7 days ago?

I've spent quite some time on this but am stuck in a mess of
subqueries, WHERE....NOT INs, WHERE....INs, etc. The table
structure is:

structure_id int IDENTITY,
costcentre_code char(10),
costcentre_desc char(100),
region_code char(10),
region_desc char(100),
insertion_date datetime

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-07 : 06:48:42

Provide some sample data to work on.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rockmoose

dbrowne
Starting Member

11 Posts

Posted - 2005-06-07 : 07:45:28

This shows a small set I've just made up from 2 May and 9 May and gives enough to demonstrate everything I want to get from a query. The set from 9 May has a deletion, an update and an insertion when compared with the set from 2 May.

INSERT INTO structure(costcentre_code,costcentre_desc,region_code,
region_desc,insertion_date)

VALUES 'CC01', 'Cost Centre AA', 'RC01', 'Region AA', '02-05-2005'
VALUES 'CC02', 'Cost Centre AB', 'RC01', 'Region AA', '02-05-2005'
VALUES 'CC50', 'Cost Centre MM', 'RC02', 'Region AB', '02-05-2005'
VALUES 'CC02', 'Cost Centre AB (Central)', 'RC01', 'Region AA', '09-05-2005'
VALUES 'CC50', 'Cost Centre MM', 'RC02', 'Region AB', '09-05-2005'
VALUES 'CC03', 'Cost Centre AC', 'RC01', 'Region AA', '09-05-2005'

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-07 : 08:44:37

thanks for the DDL and DML, but what do you want returned? What kind of resultset? How would like to it to be indicated what is new, updated, or deleted? is just a list of rows that are different between the two dates OK? One thing you missed in your sample data are rows that did NOT change -- you definitely want to have those in there and test with them since not returning those rows is just as important as indentifying the ones that DO change.

Anyway, this returns all rows that are not exactly the same between the two dates, and also any rows from either date that are not present in the other (which is unfortunately your entire sample set but if you try it on your real data it should return something more meaningful):


select 
  costcentre_code, costcentre_desc, region_code, region_desc, min(Insertion_date) as Insertion_Date
from 
  structure
where
  insertion_Date in ('02-05-2005','09-05-2005')
group by 
  costcentre_code, costcentre_desc, region_code, region_desc
having 
  count(*) =1

- Jeff

dbrowne
Starting Member

11 Posts

Posted - 2005-06-07 : 08:58:12

Hi Jeff, rows 3 and 5 of my test data did not change - I'm looking for differences aside from the timestamp, which is there to allow each structure set to be distinguished. Your solution is really nice, though I suppose in an ideal world I would like to produce a resultset which showed changes and appended either *INSERTED*, *MODIFIED*, or *DELETED* as a column at the end of each changed row.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-07 : 09:16:59

How do you determine if a row is new or if it is modified?

Am I correct in assuming your table has primary key of CostCenterCode/RegionCode/InsertionDate and that a change would be a change in a column OTHER than one of those?

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-07 : 09:26:36

I think you want something like this

http://weblogs.sqlteam.com/brettk/archive/2004/04/23/1281.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

dbrowne
Starting Member

11 Posts

Posted - 2005-06-07 : 09:33:33

Jeff,

That assumption is exactly right. A row is new if the costcentre_code + region_code combination did not exist on the previous day, it's modified if it did exist on the previous day and either of the _desc fields have changed, and it's deleted if it existed on the previous day but not today.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-07 : 10:03:34

for the following code, assume:

@Date1 = the first date you wish to compare
@Date2 = the second date

To see rows added or deleted:


select 
  a.*, case when Insertion_Date = @Date1 then '** DELETED **' else '** ADDED **' END as Change 
from
  structure a
inner join
  (
  select 
    costcentre_code, region_code, min(Insertion_date) as Insertion_Date
  from 
    structure
  where
    insertion_Date in (@date1,@date2)
  group by 
    costcentre_code, region_code
  having 
    count(*) =1
  ) b
on
  a.CostCentre_Code = b.CostCentre_code and
  a.Region_Code = b.Region_Code and
  a.Insertion_Date = b.Insertion_Date

to see all updates (only the row from @Date2 is returned)


select 
  a.*, '** UPDATED **' as Change
from
  structure a
inner join
  structure b
on
  a.Transaction_Date = @Date2 and
  b.Transaction_Date = @Date1 and
  a.CostCentre_Code = b.CostCentre_Code and
  a.Region_Code = b.RegionCode and
  a.CostCentre_Desc <> b.CostCentre_Desc and
  a.Region_Desc <> b.Region_Desc
  ... and other columns to compare here is necessary ...

you can UNION ALL both of these together to get the complete picture all in 1 result if you like.

- Jeff

dbrowne
Starting Member

11 Posts

Posted - 2005-06-07 : 10:51:55

That is perfect! Thank you very much indeed, I really do appreciate it.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-07 : 10:58:58

You're welcome, glad to help.

And thank YOU for the detailed informatin about your problem, it was very well-stated.

- Jeff

dbrowne
Starting Member

11 Posts

Posted - 2005-06-22 : 05:01:15

Jeff,

I've tested this with a good set of test data and it throws up an error which I'm having some difficulty trapping, namely "Didn't expect 'Insertion_Date' after the SELECT column list".

When I remove the CASE statement which defines column "Change", and replace it with a placeholder e.g. 'ADDED' as Change, the query runs fine, but there's nothing wrong with the CASE logic.

I'm baffled....

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-22 : 08:37:49

you need to post the SQL that is causing the error.

- Jeff

dbrowne
Starting Member

11 Posts

Posted - 2005-06-22 : 10:09:55

I was using MS Query which doesn't seem to support the syntax. I'll try again with another query tool...

dbrowne
Starting Member

11 Posts

Posted - 2005-06-23 : 06:45:55

I've tried again in Enterprise Manager using your SQL -

select
a.*, case when Insertion_Date = @Date1 then '** DELETED **' else '** ADDED **' END as Change
from
structure a
inner join
(
select
costcentre_code, region_code, min(Insertion_date) as Insertion_Date
from
structure
where
insertion_Date in (@date1,@date2)
group by
costcentre_code, region_code
having
count(*) =1
) b
on
a.CostCentre_Code = b.CostCentre_code and
a.Region_Code = b.Region_Code and
a.Insertion_Date = b.Insertion_Date

the error returned is

[Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name 'Insertion_Date'.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-23 : 09:02:15

Did you look at my link?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

dbrowne
Starting Member

11 Posts

Posted - 2005-06-23 : 13:29:27

Hi Brett,

Yes I did, unfortunately I only have SELECT access on this table and no write-access to any other tables... this situation is non-negotiable unfortunately.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-23 : 13:59:28

Try this

USE Northwind
GO

SELECT * INTO #temp FROM Orders

Does than run in Query Analyzer?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

dbrowne
Starting Member

11 Posts

Posted - 2005-06-23 : 19:14:29

No - Northwind's not there any more. Do you have any idea what could be causing the error in Jeff's query?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-23 : 19:18:27

Yes. You would need to specify which Insertion_Date you are dealing with. Check this out:

quote:

case when Insertion_Date = @Date1 then '** DELETED **' else '** ADDED **' END as Change

Notice Insertion_Date doesn't specify the table name or alias that it came from. And you've got it in two tables, so it doesn't know which one. Since they are equal between a and b (aliases), just pick one.

case when a.Insertion_Date = @Date1 then '** DELETED **' else '** ADDED **' END as Change

or

case when b.Insertion_Date = @Date1 then '** DELETED **' else '** ADDED **' END as Change

Tara

dbrowne
Starting Member

11 Posts

Posted - 2005-06-24 : 06:37:29

<slaps forehead> Of course, how dumb of me! Thank you very much, Tara.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources