Missing Rows Problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Missing Rows Problem

Author

Topic

Mr ChriZ
Starting Member

10 Posts

Posted - 2010-03-18 : 12:50:50

Hi All,

I have a table which has rows like so:
Date | Value
2010/03/18 50
2010/03/20 30

What it actually represents looks more like
Date | Value
2010/03/18 50
2010/03/19 50
2010/03/20 30

That is for any date that is missing it's value is actually represented by the previous date that is in the table, ie only the changes have been stored, not each day.

I need to do some manipulation on this data for a given set of dates, would my best bet be to create a temporary table and attempt to fill in the missing values?

Cheers
Chris.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 13:02:27

[code]SELECT DATEADD(dd,v.number,t.Date) AS Date,t.Value
FROM Table t
OUTER APPLY (SELECT TOP 1 Date
FROM Table
WHERE Date > t.Date
ORDER BY Date ASC)t1
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND (DATEADD(dd,v.number,t.Date) < t1.Date
OR t1.Date IS NULL)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mr ChriZ
Starting Member

10 Posts

Posted - 2010-03-19 : 08:02:33

You're a genius!

Thanks Very Much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 12:32:09

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mr ChriZ
Starting Member

10 Posts

Posted - 2010-03-24 : 07:33:17

OK I now need to go back in the opposite direction!
Imagine if I had a table with a number of rows like so:

Date Total 1 - Total 2 - Total 3 - Total 4
2010-03-19 10 0 5 0
2010-03-20 20 0 5 0
2010-03-21 10 0 5 0
2010-03-22 10 5 5 0
2010-03-23 30 0 5 0

I need to select the rows in which any one of the total columns have changed from previous row, in order to insert it back into the original table.

Thanks in advance,

Chris.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-24 : 07:45:11

Is there always one single row for each and every day?

There are 10 types of people in the world, those that understand binary, and those that don't.

Mr ChriZ
Starting Member

10 Posts

Posted - 2010-03-24 : 07:46:24

I've just realised in my example table the rows always changed...
Obviously... that's not always the case!

Posted - 03/24/2010 : 07:33:17
OK I now need to go back in the opposite direction!
Imagine if I had a table with a number of rows like so:

Date Total 1 - Total 2 - Total 3 - Total 4
2010-03-19 10 0 5 0
2010-03-20 20 0 5 0
2010-03-21 10 0 5 0
2010-03-22 10 5 5 0
2010-03-23 30 0 5 0
2010-03-24 30 0 5 0
2010-03-25 30 0 5 0

Mr ChriZ
Starting Member

10 Posts

Posted - 2010-03-24 : 07:47:11

quote:
Originally posted by DBA in the making

Is there always one single row for each and every day?

There are 10 types of people in the world, those that understand binary, and those that don't.

Yes there is, thanks.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-24 : 07:54:49

Does this help?


CREATE TABLE #Test (
	Date DATETIME,
	Total1 INT,
	Total2 INT,
	Total3 INT,
	Total4 INT)

INSERT INTO #Test 
SELECT '2010-03-19', 10, 0, 5, 0
UNION ALL SELECT '2010-03-20', 20, 0, 5, 0
UNION ALL SELECT '2010-03-21', 10, 0, 5, 0
UNION ALL SELECT '2010-03-22', 10, 5, 5, 0
UNION ALL SELECT '2010-03-23', 30, 0, 5, 0
UNION ALL SELECT '2010-03-24', 30, 0, 5, 0
UNION ALL SELECT '2010-03-25', 30, 0, 5, 0
UNION ALL SELECT '2010-03-26', 30, 0, 5, 2
UNION ALL SELECT '2010-03-27', 30, 0, 5, 0


SELECT t1.*, t2.Total1 AS LastTotal1, t2.Total2 AS LastTotal2, t2.Total3 AS LastTotal3, t2.Total4 AS LastTotal4
FROM #Test t1
INNER JOIN #Test t2
	ON t1.Date = t2.Date + 1
	AND (t1.Total1 != t2.Total1
		OR t1.Total2 != t2.Total2
		OR t1.Total3 != t2.Total3
		OR t1.Total4 != t2.Total4)
		

DROP TABLE #Test

NOTE: This will give undesirable results if there is a missing date record.

There are 10 types of people in the world, those that understand binary, and those that don't.

Mr ChriZ
Starting Member

10 Posts

Posted - 2010-03-24 : 08:55:39

That's great a part from the fact it doesn't include the very first entry?

Cheers
Chris.

Mr ChriZ
Starting Member

10 Posts

Posted - 2010-03-24 : 10:45:19

Think I've got it

SELECT t1.*, t2.Total1 AS LastTotal1, t2.Total2 AS LastTotal2, t2.Total3 AS LastTotal3, t2.Total4 AS LastTotal4
FROM #Test t1
left JOIN #Test t2
ON t1.Date = t2.Date + 1
WHERE (t1.Total1 != t2.Total1
OR t1.Total2 != t2.Total2
OR t1.Total3 != t2.Total3
OR t1.Total4 != t2.Total4
or t2.Total1 is null
or t2.Total2 is null
or t2.Total3 is null
or t2.Total4 is null)

:-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-24 : 11:35:31

wont work well if you have gaps in your table for date values (what about weekends)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources