Insert row with values from previous row if does not exist

Question

Insert row with values from previous row if does not exist

PSB

SSChampion

Points: 13195
More actions
January 7, 2014 at 9:27 am

#280929

Hi,
CREATE TABLE #MyTable
(
Teams VARCHAR(10),
StartDate DATETIME,
Count INT
)
INSERT INTO #MyTable (Teams,StartDate,Count)
SELECT 'Team A', '01/01/2014',10
UNION
SELECT 'Team B', '01/01/2014',30
UNION
SELECT 'Team B', '01/02/2014',40
UNION
SELECT 'Team C', '01/01/2014',50
UNION
SELECT 'Team C', '01/02/2014',60
UNION
SELECT 'Team C', '01/03/2014',70
SELECT * FROM #MyTable
DROP TABLE #MyTable
'Team A' Has No records for '01/02/2014' and '01/03/2014' so I need to insert values of '01/01/2014' StartDate for Team A for the missing dates
So 'Team A' will now have 3 records
Team A2014-01-01 00:00:00.00010
Team A2014-01-02 00:00:00.00010
Team A2014-01-03 00:00:00.00010
'Team B' Has No records for '01/03/2014' so I need to insert values of '01/02/2014' StartDate for Team B for the missing date
So 'Team B' will now have 3 records
Team B2014-01-01 00:00:00.00030
Team B2014-01-02 00:00:00.00040
Team B2014-01-03 00:00:00.00040
As for 'Team C' we have values for all 3 dates, no inserts needed.
Thanks,
PSB

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

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

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

I would recommend NOT inserting the data into your table. You will be fighting a losing battle. You are going to have a real challenge ensuring that the "missing" data is always created. If at all possible I would look at using a tally table to generate the "missing" data when you query the table.

_______________________________________________________________

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/

sestell1 SSChampion Points: 10230 More actions · Answer 2

Like Sean said, there's no need to store the missing rows, since the query you use to generate them could be used to query the table. If you search this site for "tally table" you'll find quite a bit of information on how to generate the missing records on the fly.

Here's some code to get you started:

;WITH DateRange (DayDate)

AS

(

SELECT CONVERT(DateTime, '2014-01-01') AS DayDate UNION ALL

SELECT CONVERT(DateTime, '2014-01-02') AS DayDate UNION ALL

SELECT CONVERT(DateTime, '2014-01-03') AS DayDate UNION ALL

SELECT CONVERT(DateTime, '2014-01-04') AS DayDate UNION ALL

SELECT CONVERT(DateTime, '2014-01-05') AS DayDate UNION ALL

SELECT CONVERT(DateTime, '2014-01-06') AS DayDate

)

SELECT

Teams.Teams, DateRange.DayDate,

(SELECT MAX(X.StartDate) FROM #MyTable X WHERE X.StartDate <= DateRange.DayDate AND X.Teams = Teams.Teams) AS StartDate,

*

FROM

DateRange

Cross Join

(SELECT DISTINCT Teams FROM #MyTable) Teams

Left Join

#MyTable TeamData

ON

Teams.Teams = TeamData.Teams

AND DateRange.DayDate = TeamData.StartDate

ORDER BY

Teams.Teams, DateRange.DayDate

That will produce a row for every date between 1/1 and 1/6 for each team, with the missing rows showing the data as null. There are fancier ways to generate larger date ranges (lookup "recursive CTE"s), but this should get you started.

One way to fill in the null data with the most recent data from previous rows, would be to use the correlated sub-query in the SELECT portion in your join to #MyTable: