transform a current table and add end dates on a record

[olrac2]

Hi,
I write basic queries (using tables and nested queries) but this is not enough for my current problem.
I have a table that looks like:
current table
emp payroll eff_date strp rate
2 VIHA 20030910 9 35.31
2 VIHA 20060401 9 36.37
2 VIHA 20070401 9 37.64
2 VIHA 20080401 9 38.58
2 VIHA 20090403 9 40.2
2 VIHA 20100402 9 41.41
2 VIHA 20110401 9 42.65


but I need an end date so i can use this table in other areas:

transformed to :
emp payroll eff_date end_date strp rate
2 VIHA 20030910 20060331 9 35.31
2 VIHA 20060401 20070331 9 36.37
2 VIHA 20070401 20080331 9 37.64
2 VIHA 20080401 20090402 9 38.58
2 VIHA 20090403 20100401 9 40.2
2 VIHA 20100402 20110331 9 41.41
2 VIHA 20110401 9 42.65


basically use the effective_date from the next record , subtract 1 day and use this as the end date.
any suggestions?

Thanks in advance

 

[gmmastros]

basically use the effective_date from the next record

The trick to solving this problem is to determine what the "next record" means. Humans that look at the data can infer that the next record is the one with the same emp and has the next highest eff_date. To solve this problem, we need to "teach" SQL Server how to determine this next record. To do this, we will first put a row number on each row so your data looks like this:

[tt]
Emp Payroll eff_Date strp rate RowId
----- -------- ----------- ----- ------- -------------
2 VIHA 2003-09-10 9 35.31 1
2 VIHA 2006-04-01 9 36.37 2
2 VIHA 2007-04-01 9 37.64 3
2 VIHA 2008-04-01 9 38.58 4
2 VIHA 2009-04-03 9 40.20 5
2 VIHA 2010-04-02 9 41.41 6
2 VIHA 2011-04-01 9 42.65 7
[/tt]

Notice the RowId column. We can use this to join the data back to itself so we have a nice incrementing value to join on.

From there, the rest is easy. Since we are joining back to the same data, we will have 2 eff_date columns, one from the first set of data and another from the second. Just subtract one day from the second, and we're done.

To demonstrate this query, I will post some code. The code I show will build a table variable that holds your sample data. Then I will show you the code to get your expected results based on the table variable. Once you understand how this code works, you should be able to modify this to use the data from your real table with minor modifications to the code.

Declare @Temp Table(Emp Int, Payroll VarChar(20), eff_Date DateTime, strp int, rate DEcimal(10,2))

Insert Into @Temp Values(2,'VIHA','20030910',9,35.31)
Insert Into @Temp Values(2,'VIHA','20060401',9,36.37)
Insert Into @Temp Values(2,'VIHA','20070401',9,37.64)
Insert Into @Temp Values(2,'VIHA','20080401',9,38.58)
Insert Into @Temp Values(2,'VIHA','20090403',9,40.2)
Insert Into @Temp Values(2,'VIHA','20100402',9,41.41)
Insert Into @Temp Values(2,'VIHA','20110401',9,42.65)

;With Data As
(
  Select *, 
         Row_Number() Over(Partition By Emp Order By eff_date) As RowId
  From   @Temp
)
Select A.Emp,
       A.Payroll,
       A.eff_date As eff_start,
       DateAdd(Day, -1, B.eff_date) As eff_end,
       A.strp,
       A.Rate
From   Data As A
       Left Join Data As B
         On  A.emp = B.emp
         And A.RowId = B.RowId - 1

Notice the "WITH" block at the top of the code. The Row_Number() function allows us to number the rows. The partition by part basically says, start renumbering when the data in the Emp column changes. The ORDER BY part basically says, number the rows for each Emp sorted by eff_date.

Next, notice the query that follows the with block. The with block named the query inside the parenthesis "Data". The query following the with block uses the "Data" as though it were a table. We simply join the data to itself based on Emp and the RowId's.

I encourage you to study this code. If you have any questions about it, let me know and I will explain it more.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[olrac2]

Hi gmmastros,

Thanks for the explaination and the code. This worked fantastically..just two lines of code and it solved all my problems (On A.emp = B.emp & And A.RowId = B.RowId - 1).

I wasn't sure about the "with" statement so I created two copies of the original table and crossed them as you explained and this solved everything.

Thanks again!

 

[gmmastros]

You're welcome, and please call me George.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom