How do I fill in the dates with the same data as the previous record until it changes in SQL Server 2000?

Question

Post reply

How do I fill in the dates with the same data as the previous record until it changes in SQL Server 2000?

LCUY

Valued Member

Points: 64
More actions
June 29, 2008 at 10:25 am

#184070

I have events for employees in a table "EmpEvents":
[font="Courier New"]
EmpID EvtDate EvtData1 EvtData2
100 1/1/2008 1 51
100 1/3/2007 7 32
100 1/1/2000 3 88
100 1/2/1999 9 23
200 9/1/2005 5 44
200 1/1/2005 6 87
[/font]
I need it returned as follows using a function accepting a datetime for the as of date (ex. '12/31/2007').
Basically fill in the missing days with the same information as the previous record until it changes as follows:
[font="Courier New"]
EmpID EvtDate EvtData1 EvtData2
100 1/3/2007 7 32
100 1/3/2006 7 32
100 1/3/2005 7 32
100 1/3/2004 7 32
100 1/3/2003 7 32
100 1/3/2002 7 32
100 1/3/2001 7 32
100 1/1/2000 3 88
100 1/2/1999 9 23
200 9/1/2007 5 44
200 9/1/2006 5 44
200 9/1/2005 5 44
[/font]

Viewing 4 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

Sorry... I don't get it... Why were 7 rows for EventData1 = 7 included in the output?

Also, please see the link in my signature for how to post data so we don't have to refactor it to test our code.

--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)

LCUY Valued Member Points: 64 More actions · Answer 2

Sorry... I don't get it... Why were 7 rows for EventData1 = 7 included in the output?

My mistake it should have been the data based on 1/1/2000. Since there is no data from 2001 through 2006, the data from 2000 should propagate from 2001 through 2006.

Also note that for employee 200, there for 2 rows for 2005, but no data from 2006 and 2007. I need to use the latest data(Sep 1 2005) and propagate them to 2006 and 2007.

Thanks!

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#original','U') IS NOT NULL

DROP TABLE #original

IF OBJECT_ID('TempDB..#results','U') IS NOT NULL

DROP TABLE #results

-- Create temporary tables

CREATE TABLE #original(EmpID INT,

EvtDate DATETIME,

EvtValue1 INT,

EvtValue2 INT)

CREATE TABLE #results(EmpID INT,

EvtDate DATETIME,

EvtValue1 INT,

EvtValue2 INT)

--===== Setup any special required conditions especially where dates are concerned

SET DATEFORMAT DMY

-- originals values

INSERT INTO #original(EmpID, EvtDate, EvtValue1, EvtValue2)

SELECT '100','JAN 2 1999 12:00AM','9','23' UNION ALL

SELECT '100','JAN 1 2000 12:00AM','3','88' UNION ALL

SELECT '100','JAN 3 2007 12:00AM','7','32' UNION ALL

SELECT '100','JAN 8 2008 12:00AM','1','51' UNION ALL

SELECT '200','JAN 1 2005 12:00AM','6','87' UNION ALL

SELECT '200','SEP 1 2005 12:00AM','5','44'

-- expected results

INSERT INTO #results(EmpID, EvtDate, EvtValue1, EvtValue2)

SELECT '100','JAN 2 1999 12:00AM','9','23' UNION ALL

SELECT '100','JAN 1 2000 12:00AM','3','88' UNION ALL

SELECT '100','JAN 1 2001 12:00AM','3','88' UNION ALL

SELECT '100','JAN 1 2002 12:00AM','3','88' UNION ALL

SELECT '100','JAN 1 2003 12:00AM','3','88' UNION ALL

SELECT '100','JAN 1 2004 12:00AM','3','88' UNION ALL

SELECT '100','JAN 1 2005 12:00AM','3','88' UNION ALL

SELECT '100','JAN 1 2006 12:00AM','3','88' UNION ALL

SELECT '100','JAN 3 2007 12:00AM','7','32' UNION ALL

SELECT '100','JAN 8 2008 12:00AM','1','51' UNION ALL

SELECT '200','SEP 1 2005 12:00AM','5','44' UNION ALL

SELECT '200','SEP 1 2006 12:00AM','5','44' UNION ALL

SELECT '200','SEP 1 2007 12:00AM','5','44'

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

Search the following article for "Dozens of Other Uses" and look at the first script after that... correctly applied, it's the answer to your problem...

--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)