Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

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!
0
cipriano555
Asked:
cipriano555
1 Solution
 
cyberkiwiCommented:

CREATE TABLE [dbo].[INPUT_DATA](
      [START_TIME] [datetime] NULL,
      [END_TIME] [datetime] NULL,
      [UNITS] [real] NULL
)
insert [INPUT_DATA] select '2010-05-26 13:30:00.000', '2010-05-26 13:35:00.000', 15
insert [INPUT_DATA] select '2010-05-27 13:30:00.000', '2010-05-27 13:35:00.000', 17

CREATE TABLE [dbo].[OUTPUT_DATA](
      [TIME] [datetime] NULL,
      [UNITS] [real] NULL
)

--- IGNORE ABOVE THIS LINE. IT SETS UP THE TABLES AND DATA

insert OUTPUT_DATA
select dateadd(mi, number, d.start_time),
	d.UNITS / m.n
from input_data d
cross apply (select datediff(mi,d.START_TIME, d.END_TIME) n) m
inner join master..spt_values v on v.type='P' and v.number between 0 and m.n-1

Open in new window

0
 
cipriano555Author Commented:
Thanks!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now