Link to home
Start Free TrialLog in
Avatar of cipriano555
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!
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cipriano555
cipriano555

ASKER

Thanks!