Capture Changed records

Question

Post reply

Capture Changed records

kshah82

SSC Veteran

Points: 203
More actions
May 8, 2012 at 10:33 am

#392209

Hi,
I am trying to capture changed records in a given specific period i.e From and To dates. I have two tables Employee and Assets. If the End date is not null then the record changed. Changed records from both tables need to be shown.
Is this easy to do? Please help. The create and insert statements are as follows:
CREATE TABLE Employee
(
EmployeeID INT,
EmployeeName VARCHAR(20),
ManagerName varchar(20),
Start_Date smalldatetime,
End_Date smalldatetime
);
CREATE TABLE Assets
(
BadgeID int,
AssetName VARCHAR(20),
AssetValue varchar(30),
StartDate smalldatetime,
EndDate smalldatetime
);
INSERT INTO Employee(EmployeeID, EmployeeName, ManagerName,Start_Date,End_Date) VALUES(12345, 'Brown', 'Green','01/01/2012','01/02/2012');
INSERT INTO Employee(EmployeeID, EmployeeName, ManagerName,Start_Date,End_Date) VALUES(12345, 'Brown', 'Red','01/02/2012',null);
INSERT INTO Employee(EmployeeID, EmployeeName, ManagerName,Start_Date,End_Date) VALUES(67890, 'Orange', 'Blue','01/01/2012',null);
INSERT INTO Employee(EmployeeID, EmployeeName, ManagerName,Start_Date,End_Date) VALUES(66666, 'Fred', 'Red','01/30/2012','02/01/2012');
INSERT INTO Employee(EmployeeID, EmployeeName, ManagerName,Start_Date,End_Date) VALUES(66666, 'Fred', 'Violet','02/01/2012',null);
INSERT INTO Assets(BadgeID, AssetName,AssetValue,StartDate,EndDate) VALUES(12345,'Comp', '1234567','01/03/2012','01/04/2012');
INSERT INTO Assets(BadgeID, AssetName,AssetValue,StartDate,EndDate) VALUES(12345,'Comp', '1234567','01/04/2012',null);
INSERT INTO Assets(BadgeID, AssetName,AssetValue,StartDate,EndDate) VALUES(67890,'Comp', '8888888','01/04/2012','03/01/2012');
INSERT INTO Assets(BadgeID, AssetName,AssetValue,StartDate,EndDate) VALUES(67890,'Comp', '7777777','01/04/2012','03/01/2012');
INSERT INTO Assets(BadgeID, AssetName,AssetValue,StartDate,EndDate) VALUES(66666,'Comp', '9999999','03/01/2012',null);
Thanks in advance.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Something like this?

select * from Employee

where End_Date between @StartDate and @EndDate

select * from Assets

where EndDate between @StartDate and @EndDate

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kshah82 SSC Veteran Points: 203 More actions · Answer 2

Thanks I tried this but I want to see records from both tables together.

1. changes to both tables - show records from both table as a single row for the specified period.

2. If the record changed in the Employee table but not in the second record for the specified period. Show the Employee information.

3. If the record changed in the Assets table for the specified period, show the Asset information.

Do I have to use a union or full outer join or is there an easier way?

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

kshah82 (5/8/2012)
Thanks I tried this but I want to see records from both tables together.
1. changes to both tables - show records from both table as a single row for the specified period.

HUH?

2. If the record changed in the Employee table but not in the second record for the specified period. Show the Employee information.

So in this case you don't want to see the asset information?

3. If the record changed in the Assets table for the specified period, show the Asset information.

So in this case you don't want to see the Employee information?

Can you show me what you want as expected output based on your sample data? Your descriptions are making me more confused the more I read them.

Thanks

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kshah82 SSC Veteran Points: 203 More actions · Answer 4

Sorry about the confusion.

1 Records in both tables have changed then:

Employee Asset

ID Name MgrName Start_Date End_Date ID AssetName AssetValue StartDate EndDate

12345BrownGreen 2012-01-01 2012-01-02 12345 Comp 12345672012-01-03 2012-01-04

2. Records in Asset table only has changed then:

ID Name MgrName Start_Date End_Date ID AssetName AssetValue StartDate EndDate

67890 Orange Blue 2012-01-01 NULL 67890 COMP 88888882012-01-04 2012-03-01

3. Records in Employee table changed:

ID Name MgrName Start_Date End_Date ID AssetName AssetValue StartDate EndDate

66666Fred Red 2012-01-30 2012-02-02 666666 COMP 9999999 2012-01-04 NULL

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

I think the main point of confusion is that you have two unrelated tables of information. From what you posted there is nothing to join these on.

select *

from tableA a

join tableB b on a.PrimaryKey = b.PrimaryKey

where a.End_Date between @StartDate and @EndDate

OR

b.EndDate between @StartDate and @EndDate

Does this help?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kshah82 SSC Veteran Points: 203 More actions · Answer 6

The EmployeeBadgeID in the Employee and the BadgeID in the Assets table are related.

kshah82 SSC Veteran Points: 203 More actions · Answer 7

Sorry, I meant the EmployeeID and not EmployeeBadgeID.

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Did my pseudocode above show you how to do this or do you still need help?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

KS-321165 Default port Points: 1442 More actions · Answer 9

Thanks but it does not do what I want to do. The query shows null End_Date and null EndDates. As I am only trying to capture records that changed (End_date or EndDate not being null), this does not help. Also, I am not seeing any records where there was a change in the Employee table but not in the assets table and vice versa.

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

I am willing and able to help but your description is very unclear. Can you post exactly what you want for desired output from your sample data?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

kshah82 (5/8/2012)
Hi,

Kalpa? Washington State?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)