SELECT query that "expans" results

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 2000 Forums

SQL Server Development (2000)

SELECT query that "expans" results

Author

Topic

flamz
Starting Member

21 Posts

Posted - 2008-08-25 : 09:29:14

Hi,
I have a table that contains records of inventory, stored by date. this way:


AMOUNT, DATE
1,      01/01/2008
3,      02/01/2008
4,      03/01/2008
8,      06/01/2008

Notice that there are no entries for January 4 and 5, this is because the inventory did not change (remained at 4). So, in the interest of space, we do not insert a record when the inventory does not change.

Now, I need to create a select statement that will return "expanded" results... meaning I need to get:


AMOUNT, DATE
1,      01/01/2008
3,      02/01/2008
4,      03/01/2008
4,      04/01/2008
4,      05/01/2008
8,      06/01/2008

I was wondering if someone could tell me if it's possible to create a nifty query that would help me do this?

tx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-25 : 09:41:15

[code]DECLARE @Sample TABLE (Amount INT, Date DATETIME)

SET DATEFORMAT DMY

INSERT @Sample
SELECT 1, '01/01/2008' UNION ALL
SELECT 3, '02/01/2008' UNION ALL
SELECT 4, '03/01/2008' UNION ALL
SELECT 8, '06/01/2008'

SELECT d.Amount,
DATEADD(DAY, v.Number, d.Date) AS theDate
FROM (
SELECT s.Amount,
s.Date,
COALESCE((SELECT MIN(c.Date) FROM @Sample AS c WHERE c.Date > s.Date), s.Date + 1) AS dt
FROM @Sample AS s
) AS d
INNER JOIN master..spt_values AS v ON v.Type = 'P'
WHERE v.Number < DATEDIFF(DAY, d.Date, d.dt)[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources