cipriano555
asked on
sql query that does an "allocation"
I'm need a query that does an "allocation".
I start with a table where each row is data for a span of multiple minutes,
I want to generate a table that has that data equally distributed
among one minute time intervals starting at the same time.
For example, I start with this:
CREATE TABLE [dbo].[INPUT_DATA](
[START_TIME] [datetime] NULL,
[END_TIME] [datetime] NULL,
[UNITS] [real] NULL
) ON [PRIMARY]
<table has one row of data, covering 5 minutes>
START_TIME END_TIME UNITS
2010-05-26 13:30:00.000 2010-05-26 13:35:00.000 15
I want this:
CREATE TABLE [dbo].[OUTPUT_DATA](
[TIME] [datetime] NULL,
[UNITS] [real] NULL
) ON [PRIMARY]
<table has 5 rows of data, each covering one minute, starting at the same time as above>
TIME UNITS
2010-05-26 13:30:00.000 3
2010-05-26 13:31:00.000 3
2010-05-26 13:32:00.000 3
2010-05-26 13:33:00.000 3
2010-05-26 13:34:00.000 3
Seems a little like a pivot but I can't quite see how to do it.
Thanks!
I start with a table where each row is data for a span of multiple minutes,
I want to generate a table that has that data equally distributed
among one minute time intervals starting at the same time.
For example, I start with this:
CREATE TABLE [dbo].[INPUT_DATA](
[START_TIME] [datetime] NULL,
[END_TIME] [datetime] NULL,
[UNITS] [real] NULL
) ON [PRIMARY]
<table has one row of data, covering 5 minutes>
START_TIME END_TIME UNITS
2010-05-26 13:30:00.000 2010-05-26 13:35:00.000 15
I want this:
CREATE TABLE [dbo].[OUTPUT_DATA](
[TIME] [datetime] NULL,
[UNITS] [real] NULL
) ON [PRIMARY]
<table has 5 rows of data, each covering one minute, starting at the same time as above>
TIME UNITS
2010-05-26 13:30:00.000 3
2010-05-26 13:31:00.000 3
2010-05-26 13:32:00.000 3
2010-05-26 13:33:00.000 3
2010-05-26 13:34:00.000 3
Seems a little like a pivot but I can't quite see how to do it.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER