From data list to time series data? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

From data list to time series data?

Author

Topic

hugob
Starting Member

11 Posts

Posted - 2009-01-09 : 06:53:01

Does someone knows how to create time series data from data list? For example I this kinf of data list:
ID Start End liter/h
01 08/01/01 08/12/31 8.0
02 08/06/11 08/12/31 6.0
...

And I need this to be aggregated and same time changed to time series format:
Date liter/h
08/01/01 8.0
08/01/02 8.0
...
08/06/01 14.0
08/06/02 14.0
...
08/12/31 14.0

Are there some tricks to do this. The data list is huge and this is needed to be calculated several times per day. Now I have solved it by using VBA and SQL database. First I get the data by using these select command and then manipulating data inside the code in arrays and then row by row saving it to database by using insert command.

Are there some other and a lot faster ways of doing it by using SQL commands?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 08:46:01

can you explain how you got 14.0 as liter/h value from 08/06/01 onwards? as pert sample data start for second record is 08/06/11

hugob
Starting Member

11 Posts

Posted - 2009-01-09 : 08:48:50

My mistake...typing error. It should start from 08/06/01 not 08/06/11.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 13:16:32

[code]
SELECT DATEADD(dd,v.number,t.start),t.[liter/h]
FROM (
SELECT ID,start,end,t.[liter/h]+coalesce(tmp1.Total,0) AS [liter/h]
FROM YourTable t1
OUTER APPLY(SELECT SUM([liter/h]) AS Total
FROM YourTable
WHERE Start>t.Start
AND Start<t.End)tmp1
)t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(dd,v.number,t.start)<=t.end
[/code]

hugob
Starting Member

11 Posts

Posted - 2009-01-10 : 02:46:07

Thanks!

If I understood this right (haven't tested it yet) this creates this daily data series. What about saving the new data? Is that needed to be done row by row using this INSERT command?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 02:56:13

quote:
Originally posted by hugob

Thanks!

If I understood this right (haven't tested it yet) this creates this daily data series. What about saving the new data? Is that needed to be done row by row using this INSERT command?

you want insert this new data to table? just use


INSERT INTO Table
SELECT DATEADD(dd,v.number,t.start),t.[liter/h]
FROM (
SELECT ID,start,end,t.[liter/h]+coalesce(tmp1.Total,0) AS [liter/h]
FROM YourTable t1
OUTER APPLY(SELECT SUM([liter/h]) AS Total
            FROM YourTable
            WHERE Start>t.Start
            AND Start<t.End)tmp1
)t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(dd,v.number,t.start)<=t.end

hugob
Starting Member

11 Posts

Posted - 2009-01-10 : 04:47:45

Thanks again. I tried this first set up and got errors:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'OUTER'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'tmp1'.

Do you know what might be the problem? Here is my SQL string:

SELECT DATEADD(dd,v.number,t.STARTS),t.WATERFLOW
FROM (
SELECT ID,STARTS,ENDS,t.WATERFLOW+coalesce(tmp1.Total,0) AS WATERFLOW
FROM WATERFLOWLIST t1
OUTER APPLY(SELECT SUM(WATERFLOW) AS Total
FROM TESTIDLIST
WHERE STARTS>t.STARTS
AND STARTS<t.ENDS)tmp1
)t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(dd,v.number,t.STARTS)<=t.ENDS

My table name is WATERFLOWLIST and there I have four columns: ID, STARTS, ENDS, WATERFLOW. Did I misunderstood something?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 04:56:07

whats TESTIDLIST?

hugob
Starting Member

11 Posts

Posted - 2009-01-10 : 04:58:14

Typing error from my old string. It's WATERFLOWLIST now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 05:01:14

now is it working? or still same error?

hugob
Starting Member

11 Posts

Posted - 2009-01-10 : 05:02:47

Still the same error. I just copied from text file where I had old names but still the same structure. And same error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 05:06:48

[code]
SELECT DATEADD(dd,v.number,t.STARTS),t.WATERFLOW
FROM (
SELECT t1.ID,
t1.STARTS,
t1.ENDS,
t1.WATERFLOW+coalesce(tmp1.Total,0) AS WATERFLOW
FROM WATERFLOWLIST t1
OUTER APPLY
(SELECT SUM(WATERFLOW) AS Total
FROM TESTIDLIST
WHERE STARTS>t1.STARTS
AND STARTS<t1.ENDS) tmp1

)t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(dd,v.number,t.STARTS)<=t.ENDS
[/code]
if it doesnt work, try if atleast this works

[code]
SELECT t1.ID,
t1.STARTS,
t1.ENDS,
t1.WATERFLOW+coalesce(tmp1.Total,0) AS WATERFLOW
FROM WATERFLOWLIST t1
OUTER APPLY
(SELECT SUM(WATERFLOW) AS Total
FROM TESTIDLIST
WHERE STARTS>t1.STARTS
AND STARTS<t1.ENDS) tmp1
[/code]

hugob
Starting Member

11 Posts

Posted - 2009-01-10 : 05:13:23

Nope, Didn't worked. I got these error messages for the last one:

Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'OUTER'.
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'tmp1'.

I got those even I changed this TESTIDLIST to WATERFLOWLIST which is the existing table name nowadays.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 05:22:12

quote:
Originally posted by hugob

Nope, Didn't worked. I got these error messages for the last one:

Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'OUTER'.
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'tmp1'.

I got those even I changed this TESTIDLIST to WATERFLOWLIST which is the existing table name nowadays.

are you using sql 2005 with compatibility level 90?

hugob
Starting Member

11 Posts

Posted - 2009-01-10 : 05:26:50

I think it's still SQL 2000 at the moment. New version is coming soon but we have to leave with this at start.

What does this compatibility level mean?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 05:30:31

quote:
Originally posted by hugob

I think it's still SQL 2000 at the moment. New version is coming soon but we have to leave with this at start.

What does this compatibility level mean?

Ah! sql 2000...then forget compatibility level...APPLY wont work in sql 2000...it works only from SQL 2005 onwards...why have you posted this in sql 2005 forum then?

anyways, try the below


SELECT DATEADD(dd,v.number,t.STARTS),t.WATERFLOW
FROM (
SELECT t1.ID,
t1.STARTS,
t1.ENDS,
t1.WATERFLOW+coalesce((SELECT SUM(WATERFLOW) AS Total
FROM WATERFLOWLIST
WHERE STARTS>t1.STARTS
AND STARTS<t1.ENDS),0) AS WATERFLOW
FROM WATERFLOWLIST t1
)t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(dd,v.number,t.STARTS)<=t.ENDS

hugob
Starting Member

11 Posts

Posted - 2009-01-10 : 05:51:56

Ok. I ddin't understood that there are so big differencies between these servers. Any how, now it went without any error messages. But result is not what I expected. Some of the days were dublicated and some were ok. The reason might be that we have several kind of time frames were we are measuring; months, quarters and years.

My test ID lis t is following:
ID WATERFLOW STARTS ENDS
ENOMJAN-09 5 2009/01/01 2009/01/31
ENOYR-09 5 2009/01/01 2009/12/31
ENOMFEB-09 5 2009/02/01 2009/02/28
ENOMMAR-09 5 2009/03/01 2009/03/31
ENOQ2-09 5 2009/04/01 2009/06/30
ENOQ3-09 5 2009/07/01 2009/09/30
ENOQ4-09 4 2009/10/01 2009/12/31
ENOYR-10 3 2010/01/01 2010/12/31
ENOQ3-10 0 2010/07/01 2010/10/31
ENOQ4-10 1 2010/10/01 2010/12/31
ENOYR-11 2 2011/01/01 2011/12/31

And now I got e.g. 2009/01/01 twice where one was 5 and other 29. This continues until 2009/09/13.
After that it's 5 for each day until 2009/10/01 when it's 4...
This double values starts again on 2010/07/01 when there are 4 and 1 for each day. And this continues until 2010/09/13 when it changes to 1. From 2011/01/01 it's 2.

So there is something wrong in the code still which I don't really understand. Why there are these dublicates and why those are ending in the middle of september?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 07:32:23

quote:
Originally posted by hugob

Ok. I ddin't understood that there are so big differencies between these servers. Any how, now it went without any error messages. But result is not what I expected. Some of the days were dublicated and some were ok. The reason might be that we have several kind of time frames were we are measuring; months, quarters and years.

My test ID lis t is following:
ID WATERFLOW STARTS ENDS
ENOMJAN-09 5 2009/01/01 2009/01/31
ENOYR-09 5 2009/01/01 2009/12/31
ENOMFEB-09 5 2009/02/01 2009/02/28
ENOMMAR-09 5 2009/03/01 2009/03/31
ENOQ2-09 5 2009/04/01 2009/06/30
ENOQ3-09 5 2009/07/01 2009/09/30
ENOQ4-09 4 2009/10/01 2009/12/31
ENOYR-10 3 2010/01/01 2010/12/31
ENOQ3-10 0 2010/07/01 2010/10/31
ENOQ4-10 1 2010/10/01 2010/12/31
ENOYR-11 2 2011/01/01 2011/12/31

And now I got e.g. 2009/01/01 twice where one was 5 and other 29. This continues until 2009/09/13.
After that it's 5 for each day until 2009/10/01 when it's 4...
This double values starts again on 2010/07/01 when there are 4 and 1 for each day. And this continues until 2010/09/13 when it changes to 1. From 2011/01/01 it's 2.

So there is something wrong in the code still which I don't really understand. Why there are these dublicates and why those are ending in the middle of september?

does more than 1 record for same date range exists in your table?

hugob
Starting Member

11 Posts

Posted - 2009-01-10 : 09:05:49

Yes, it does. For some days there is only 1 record. But for some days there are two records.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 14:09:56

quote:
Originally posted by hugob

Yes, it does. For some days there is only 1 record. But for some days there are two records.

then for those days what you want as output, sum of both the records + quantity till them?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources