Date manipulation - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Date manipulation

Author

Topic

_sqllearner
Starting Member

9 Posts

Posted - 2007-07-20 : 14:34:28

I have two tables TableA and TableB as shown in the example below. I will have to Update TableA based on the data in TableB.
If a member in TableB has an EmployerId different from the one in TableA where the EffectiveDate in TableB between the EffectiveDate and ExpirationDate in TableA, then it should void the row in TableA and create rows as shown in the example below (Please refer to the 1st record and the last three records).

I would like to get some suggestions on how to do this efficiently. I am not looking for queries, but I need some ideas...

Thanks in advance.

TableA

MemberId EmployerId EffectiveDate ExpirationDate VoidIn
12345 111 1/1/2006 10/31/2006 0
12345 222 11/1/2006 5/31/2007 0
12345 333 6/1/2007 12/31/9999 0

TableB

MemberId EmployerId EffectiveDate
12345 444 4/1/2006

TargetTable (TableA Updated)

MemberId EmployerId EffectiveDate ExpirationDate VoidIn
12345 111 1/1/2006 10/31/2006 1
12345 222 11/1/2006 5/31/2007 0
12345 333 6/1/2007 12/31/9999 0
12345 111 1/1/2006 3/31/2006 0
12345 444 4/1/2006 4/30/2006 0
12345 111 5/1/2006 10/31/2006 0

Thanks
Suresh

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-20 : 14:47:23

In your example, how did you decide that the ExpirationDate on the second row was 4/30/2006? It isn't in TableB.


MemberId	EmployerId 	EffectiveDate 	ExpirationDate 	VoidIn
12345 		111 		1/1/2006 	3/31/2006 	0
12345 		444 		4/1/2006 	4/30/2006 	0
12345 		111 		5/1/2006 	10/31/2006 	0

CODO ERGO SUM

_sqllearner
Starting Member

9 Posts

Posted - 2007-07-20 : 15:16:23

The dat range valid in tableB is only one month. so it will usually be the end of the month.

Thanks
Suresh

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-20 : 15:30:59

quote:
Originally posted by _sqllearner

The dat range valid in tableB is only one month. so it will usually be the end of the month.

Thanks
Suresh

Usually?

How giving an about an exact rule that always applies?

CODO ERGO SUM

_sqllearner
Starting Member

9 Posts

Posted - 2007-07-20 : 17:30:53

my bad....it is the case always.

Suresh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources