|
|
You're in the
sql server programming
group:Deriving values for a 15 Minute Increment Time Table
sql server programming:
SQL Server 2000
Here is a data sample: DDate Acc Amount 10/10/2006 8:03:00 AM 40.2 0.04 10/10/2006 8:14:00 AM 40.24 0.04 10/10/2006 8:21:00 AM 40.28 0.04 10/10/2006 8:25:00 AM 40.31 0.04 10/10/2006 8:27:00 AM 40.35 0.04 10/10/2006 8:34:00 AM 40.39 0.04 10/10/2006 8:38:00 AM 40.43 0.04 10/10/2006 8:41:00 AM 40.47 0.04 10/10/2006 8:44:00 AM 40.51 0.04 10/10/2006 8:47:00 AM 40.55 0.04 10/10/2006 8:49:00 AM 40.59 0.04 10/10/2006 8:50:00 AM 40.63 0.04 10/10/2006 8:50:00 AM 40.63 0.04 I am trying to write a query or view that displays the data in with set 15 minute increments from the available data. So if all the available data for 10/10/06 is the above it would be display like: 10/10/06 RAINFALL 0100 0.00 0115 0.00 0130 0.00 .... .... .... 0800 0.00 0815 0.04 0830 0.04 0845 0.04 0900 0.04 0915 0.00 0930 0.00 .... .... .... 2400 0.00 Thanks for any help or suggestions! RBollinger
[quoted text, click to view] "robboll" <robboll@hotmail.com> wrote in message news:1164343680.564526.168030@l39g2000cwd.googlegroups.com... > SQL Server 2000 > > Here is a data sample: > > DDate Acc Amount > 10/10/2006 8:03:00 AM 40.2 0.04 > 10/10/2006 8:14:00 AM 40.24 0.04 > 10/10/2006 8:21:00 AM 40.28 0.04 > 10/10/2006 8:25:00 AM 40.31 0.04 > 10/10/2006 8:27:00 AM 40.35 0.04 > 10/10/2006 8:34:00 AM 40.39 0.04 > 10/10/2006 8:38:00 AM 40.43 0.04 > 10/10/2006 8:41:00 AM 40.47 0.04 > 10/10/2006 8:44:00 AM 40.51 0.04 > 10/10/2006 8:47:00 AM 40.55 0.04 > 10/10/2006 8:49:00 AM 40.59 0.04 > 10/10/2006 8:50:00 AM 40.63 0.04 > 10/10/2006 8:50:00 AM 40.63 0.04 > > I am trying to write a query or view that displays the data in with set > 15 minute increments from the available data. So if all the available > data for 10/10/06 is the above it would be display like: > > 10/10/06 RAINFALL > 0100 0.00 > 0115 0.00 > 0130 0.00 > ... > ... > ... > 0800 0.00 > 0815 0.04 > 0830 0.04 > 0845 0.04 > 0900 0.04 > 0915 0.00 > 0930 0.00 > ... > ... > ... > 2400 0.00 > > Thanks for any help or suggestions! There might be an easier way, but it should work: SQL Server stores datetime values internally as a binary(8), with the high-order 4 bytes representing the number of days offset from the org date, and the low-order 4 bytes representing the time of day in 3.33~ ms time periods (300 per second), so... To derive the 15 minute period of the day in which a given datetime value occurred, extract the low-order 4 bytes, convert to an integer, and divide by 15 minutes worth of 3.33~ ms increments, i.e., select convert(int, SUBSTRING(convert(binary(8), getdate()), 5, 4)) / (300 * 60 * 15) You can group by that derivation and use the SUM aggregate function to add the rainfall... wait though, wouldn't it be: 0815 0.08 0830 0.12 0845 0.16 0900 0.12 (I'm assuming the last row was a duplicate.) Anyway, you can go back the other way to get the time of day using something like this: Select QtrHours, convert(datetime, convert(binary(8), QtrHours * (300 * 60 * 15))) AS ToD FROM ( select convert(int, SUBSTRING(convert(binary(8), getdate()), 5, 4)) / (300 * 60 * 15) AS QtrHours ) drs Hope it helps... -Mark [quoted text, click to view] > RBollinger >
I would start by deriving a table which lists your times. Use an auxiliary
table of numbers (such as my dbo.nums, with column num starting from 1). Then you can join this to your table of data. select t.starttime, isnull(sum(d.amount),0) as rainfall from (select dateadd(minute,(num-1)*15,'2006-10-10') as starttime, dateadd(minute,num*15,'2006-10-10') as endtime from nums where num <= 24*4) t left join data d on d.ddate >= t.starttime and d.ddate < t.endtime group by t.starttime ; And I'm assuming a structure like: create table data (ddate datetime primary key, acc decimal(18,9), amount decimal(18,9)); insert into data values ('10/10/2006 8:03:00 AM', 40.2, 0.04); .... etc [quoted text, click to view] "robboll" <robboll@hotmail.com> wrote in message news:1164343680.564526.168030@l39g2000cwd.googlegroups.com... > SQL Server 2000 > > Here is a data sample: > > DDate Acc Amount > 10/10/2006 8:03:00 AM 40.2 0.04 > 10/10/2006 8:14:00 AM 40.24 0.04 > 10/10/2006 8:21:00 AM 40.28 0.04 > 10/10/2006 8:25:00 AM 40.31 0.04 > 10/10/2006 8:27:00 AM 40.35 0.04 > 10/10/2006 8:34:00 AM 40.39 0.04 > 10/10/2006 8:38:00 AM 40.43 0.04 > 10/10/2006 8:41:00 AM 40.47 0.04 > 10/10/2006 8:44:00 AM 40.51 0.04 > 10/10/2006 8:47:00 AM 40.55 0.04 > 10/10/2006 8:49:00 AM 40.59 0.04 > 10/10/2006 8:50:00 AM 40.63 0.04 > 10/10/2006 8:50:00 AM 40.63 0.04 > > I am trying to write a query or view that displays the data in with set > 15 minute increments from the available data. So if all the available > data for 10/10/06 is the above it would be display like: > > 10/10/06 RAINFALL > 0100 0.00 > 0115 0.00 > 0130 0.00 > ... > ... > ... > 0800 0.00 > 0815 0.04 > 0830 0.04 > 0845 0.04 > 0900 0.04 > 0915 0.00 > 0930 0.00 > ... > ... > ... > 2400 0.00 > > Thanks for any help or suggestions! > > RBollinger >
Thanks for the responses. In MS Access I am able to do this using two
queries. In Query1 I create a column SetTime that extracts the minutes portion from the time, and then "stamps" the time as 0, 15, 30, 45 depending on which quarter the time falls. In the second query: Query2 I group by using the stamped field and MAX the value field for the maximum rainfall every quarter hour. As follows: Query1: test SELECT Table1.DDate, DateValue([ddate]) AS Dte, Format([ddate],"nn:ss") AS SetTime, Format([ddate],"hh") & ":" & IIf(Val([settime])>=0 And Val([settime])<=14,"00:00",IIf(Val([settime])>=15 And Val([settime])<=29,"15:00",IIf(Val([settime])>=30 And Val([settime])<=44,"30:00",IIf(Val([settime])>=45 And Val([settime])<=60,"45:00","")))) AS TimeGroup, Table1.Acc, Table1.Amount, Table1.Raw FROM Table1 ORDER BY Table1.DDate; Query2: test2 SELECT test.TimeGroup, Max(test.Acc) AS MaxOfAcc, test.Amount FROM test GROUP BY test.TimeGroup, test.Amount; Q: How can I use this approach with SQL Server? [quoted text, click to view] On Nov 24, 12:02 am, "Rob Farley" <rob_far...@hotmail.com> wrote:
> I would start by deriving a table which lists your times. Use an auxiliary > table of numbers (such as my dbo.nums, with column num starting from 1). > Then you can join this to your table of data. > > select t.starttime, isnull(sum(d.amount),0) as rainfall > from > (select dateadd(minute,(num-1)*15,'2006-10-10') as starttime, > dateadd(minute,num*15,'2006-10-10') as endtime from nums where num <= 24*4) > t > left join > data d > on d.ddate >= t.starttime > and d.ddate < t.endtime > group by t.starttime > ; > > And I'm assuming a structure like: > create table data (ddate datetime primary key, acc decimal(18,9), amount > decimal(18,9)); > insert into data values ('10/10/2006 8:03:00 AM', 40.2, 0.04); > ... etc > > "robboll" <robb...@hotmail.com> wrote in messagenews:1164343680.564526.168030@l39g2000cwd.googlegroups.com... > > > > > SQL Server 2000 > > > Here is a data sample: > > > DDate Acc Amount > > 10/10/2006 8:03:00 AM 40.2 0.04 > > 10/10/2006 8:14:00 AM 40.24 0.04 > > 10/10/2006 8:21:00 AM 40.28 0.04 > > 10/10/2006 8:25:00 AM 40.31 0.04 > > 10/10/2006 8:27:00 AM 40.35 0.04 > > 10/10/2006 8:34:00 AM 40.39 0.04 > > 10/10/2006 8:38:00 AM 40.43 0.04 > > 10/10/2006 8:41:00 AM 40.47 0.04 > > 10/10/2006 8:44:00 AM 40.51 0.04 > > 10/10/2006 8:47:00 AM 40.55 0.04 > > 10/10/2006 8:49:00 AM 40.59 0.04 > > 10/10/2006 8:50:00 AM 40.63 0.04 > > 10/10/2006 8:50:00 AM 40.63 0.04 > > > I am trying to write a query or view that displays the data in with set > > 15 minute increments from the available data. So if all the available > > data for 10/10/06 is the above it would be display like: > > > 10/10/06 RAINFALL > > 0100 0.00 > > 0115 0.00 > > 0130 0.00 > > ... > > ... > > ... > > 0800 0.00 > > 0815 0.04 > > 0830 0.04 > > 0845 0.04 > > 0900 0.04 > > 0915 0.00 > > 0930 0.00 > > ... > > ... > > ... > > 2400 0.00 > > > Thanks for any help or suggestions! > > > RBollinger- Hide quoted text -- Show quoted text -
I can't get past: CONVERT (CHAR(8), DATEADD(minute, 0, DATEDIFF(minute,
0, DDate) / 15 * 15), 108) without getting an overflow error in SQL Server. Seems to be an interesting approach well beyond the approach that I was taking. On Nov 26, 4:49 pm, Hugo Kornelis [quoted text, click to view] <h...@perFact.REMOVETHIS.info.INVALID> wrote:
> On 26 Nov 2006 06:52:22 -0800, robboll wrote: > > > > >Thanks for the responses. In MS Access I am able to do this using two > >queries. > (snip) > >Q: How can I use this approach with SQL Server?Hi robboll, > > A: Why would you want to? > > Why use two queries when a single one will suffice? > > SELECT CONVERT(CHAR(8), > DATEADD(minute, > 0, > DATEDIFF(minute, 0, DDate) / 15 * 15), > 108) AS TimeGroup, > MAX(Acc) AS MaxOfAcc, > Amount > FROM Table1 > GROUP BY DATEDIFF(minute, 0, DDate) / 15, Amount; > > (Untested - seewww.aspfaq.com/5006if you prefer a tested reply) > > -- > Hugo Kornelis, SQL Server MVP
DATEADD(minute, 0, DATEDIFF(minute, 0, DDate)) causes
the same error [quoted text, click to view] On Nov 26, 6:35 pm, "robboll" <robb...@hotmail.com> wrote:
> I can't get past: CONVERT (CHAR(8), DATEADD(minute, 0, DATEDIFF(minute, > 0, DDate) / 15 * 15), 108) > without getting an overflow error in SQL Server. Seems to be an > interesting approach well beyond the approach that I was taking. > > On Nov 26, 4:49 pm, Hugo Kornelis > > > > <h...@perFact.REMOVETHIS.info.INVALID> wrote: > > On 26 Nov 2006 06:52:22 -0800, robboll wrote: > > > >Thanks for the responses. In MS Access I am able to do this using two > > >queries. > > (snip) > > >Q: How can I use this approach with SQL Server?Hi robboll, > > > A: Why would you want to? > > > Why use two queries when a single one will suffice? > > > SELECT CONVERT(CHAR(8), > > DATEADD(minute, > > 0, > > DATEDIFF(minute, 0, DDate) / 15 * 15), > > 108) AS TimeGroup, > > MAX(Acc) AS MaxOfAcc, > > Amount > > FROM Table1 > > GROUP BY DATEDIFF(minute, 0, DDate) / 15, Amount; > > > (Untested - seewww.aspfaq.com/5006ifyou prefer a tested reply) > > > -- > > Hugo Kornelis, SQL Server MVP- Hide quoted text -- Show quoted text -
[quoted text, click to view]
On 26 Nov 2006 06:52:22 -0800, robboll wrote: >Thanks for the responses. In MS Access I am able to do this using two >queries. (snip) >Q: How can I use this approach with SQL Server? Hi robboll, A: Why would you want to? Why use two queries when a single one will suffice? SELECT CONVERT(CHAR(8), DATEADD(minute, 0, DATEDIFF(minute, 0, DDate) / 15 * 15), 108) AS TimeGroup, MAX(Acc) AS MaxOfAcc, Amount FROM Table1 GROUP BY DATEDIFF(minute, 0, DDate) / 15, Amount; (Untested - see www.aspfaq.com/5006 if you prefer a tested reply) --
It's only that he's got the parameters in the wrong order in DATEADD.
In his solution, try converting: DATEADD(minute, 0, DATEDIFF(minute, 0, DDate) / 15 * 15), to: DATEADD(minute, DATEDIFF(minute, 0, DDate) / 15 * 15, 0) But I still prefer mine... because it gives you the zero rows. Rob [quoted text, click to view] "robboll" <robboll@hotmail.com> wrote in message news:1164588751.704414.130570@f16g2000cwb.googlegroups.com... > DATEADD(minute, 0, DATEDIFF(minute, 0, DDate)) causes > the same error > > On Nov 26, 6:35 pm, "robboll" <robb...@hotmail.com> wrote: >> I can't get past: CONVERT (CHAR(8), DATEADD(minute, 0, DATEDIFF(minute, >> 0, DDate) / 15 * 15), 108) >> without getting an overflow error in SQL Server. Seems to be an >> interesting approach well beyond the approach that I was taking. >> >> On Nov 26, 4:49 pm, Hugo Kornelis >> >> >> >> <h...@perFact.REMOVETHIS.info.INVALID> wrote: >> > On 26 Nov 2006 06:52:22 -0800, robboll wrote: >> >> > >Thanks for the responses. In MS Access I am able to do this using two >> > >queries. >> > (snip) >> > >Q: How can I use this approach with SQL Server?Hi robboll, >> >> > A: Why would you want to? >> >> > Why use two queries when a single one will suffice? >> >> > SELECT CONVERT(CHAR(8), >> > DATEADD(minute, >> > 0, >> > DATEDIFF(minute, 0, DDate) / 15 * 15), >> > 108) AS TimeGroup, >> > MAX(Acc) AS MaxOfAcc, >> > Amount >> > FROM Table1 >> > GROUP BY DATEDIFF(minute, 0, DDate) / 15, Amount; >> >> > (Untested - seewww.aspfaq.com/5006ifyou prefer a tested reply) >> >> > -- >> > Hugo Kornelis, SQL Server MVP- Hide quoted text -- Show quoted text - >
[quoted text, click to view]
On Mon, 27 Nov 2006 11:46:26 +1030, Rob Farley wrote: >It's only that he's got the parameters in the wrong order in DATEADD. Hi Rob, Thanks for finding thhat. Somehow, I always manage to swap those two parameters. (Though I usually catch and correct the error myself before posting). --
No kidding... I do it all the time myself.
[quoted text, click to view] "Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message news:h2umm2dc3hfevvduooauuov10758obb3gf@4ax.com... > On Mon, 27 Nov 2006 11:46:26 +1030, Rob Farley wrote: > >>It's only that he's got the parameters in the wrong order in DATEADD. > > Hi Rob, > > Thanks for finding thhat. Somehow, I always manage to swap those two > parameters. (Though I usually catch and correct the error myself before > posting). > > -- > Hugo Kornelis, SQL Server MVP
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |