One record each quarter - SQL Server Forums

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)

One record each quarter

Author

Topic

maarten.del
Starting Member

17 Posts

Posted - 2010-03-05 : 07:28:09

Hello,

I have this table
SERVERTIMESTAMP DEBIET_DB1 PEIL_DB1
2009-12-03 06:56:06.290 3295 2442
2009-12-03 06:55:16.260 3285 2444
2009-12-03 06:54:06.250 3300 2444
2009-12-03 06:53:16.230 3295 2444
2009-12-03 06:52:06.210 3285 2444
2009-12-03 06:51:16.200 3290 2444
2009-12-03 06:50:06.180 3288 2444
2009-12-03 06:49:16.150 3283 2444
2009-12-03 06:48:26.060 3285 2445
2009-12-03 06:47:12.840 3292 2446
2009-12-03 06:46:22.700 3281 2445
2009-12-03 06:45:12.530 3300 2445
2009-12-03 06:44:22.400 3283 2444
2009-12-03 06:43:12.210 3286 2445

You see, every minute i have 2 values. This table is very huge. I want to write a query that gives the 2 value each 15 minutes (quarter)(the last value in that quarter).

Can someone put me in the right direction with this.

Thanks in advance.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-05 : 08:43:38

I can not see 2 values for every minute, there is only one value. can you show ?

Vabhav T

maarten.del
Starting Member

17 Posts

Posted - 2010-03-05 : 08:50:11

I know it's difficult toe see.
Example for the first record it is 3295 and 2442

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-05 : 08:56:51

Post your expected results and we might be able to help more.

maarten.del
Starting Member

17 Posts

Posted - 2010-03-05 : 09:14:56

2009-08-29 03:00:10.540 9 3433
2009-08-29 03:01:05.540 12 3433
2009-08-29 03:02:05.540 10 3433
2009-08-29 03:03:05.540 10 3433
2009-08-29 03:04:05.540 10 3433
2009-08-29 03:05:05.540 9 3433
2009-08-29 03:06:05.540 9 3433
2009-08-29 03:07:05.540 9 3433
2009-08-29 03:08:05.560 9 3433
2009-08-29 03:09:05.560 10 3433
2009-08-29 03:10:05.560 9 3433
2009-08-29 03:11:05.560 9 3433
2009-08-29 03:12:05.560 9 3433
2009-08-29 03:13:05.560 12 3433
2009-08-29 03:14:05.560 14 3433
2009-08-29 03:15:10.280 12 3433
2009-08-29 03:16:05.280 7 3433
2009-08-29 03:17:05.280 9 3433
2009-08-29 03:18:05.280 10 3433
2009-08-29 03:19:05.290 14 3433
2009-08-29 03:20:05.290 7 3433
2009-08-29 03:21:05.290 7 3433
2009-08-29 03:22:05.290 12 3433
2009-08-29 03:23:05.290 12 3433
2009-08-29 03:24:05.290 14 3433
2009-08-29 03:25:05.290 12 3433
2009-08-29 03:26:05.290 10 3433
2009-08-29 03:27:05.310 9 3433
2009-08-29 03:28:05.310 12 3433
2009-08-29 03:29:05.310 10 3433
2009-08-29 03:30:10.030 9 3433
2009-08-29 03:31:05.030 12 3433
2009-08-29 03:32:05.030 14 3433
2009-08-29 03:33:05.030 10 3433
2009-08-29 03:34:05.030 7 3433
2009-08-29 03:35:05.030 12 3433
2009-08-29 03:36:05.030 7 3433
2009-08-29 03:37:05.030 10 3433
2009-08-29 03:38:05.040 12 3433
2009-08-29 03:39:05.040 9 3433
2009-08-29 03:40:05.040 9 3433
2009-08-29 03:41:05.040 10 3433
2009-08-29 03:42:05.040 10 3433
2009-08-29 03:43:05.040 12 3433
2009-08-29 03:44:05.040 9 3433
2009-08-29 03:45:09.670 10 3433
2009-08-29 03:46:04.870 12 3433
2009-08-29 03:47:04.870 9 3433
2009-08-29 03:48:04.870 9 3433
2009-08-29 03:49:04.870 12 3433
2009-08-29 03:50:04.870 12 3433
2009-08-29 03:51:04.890 10 3433
2009-08-29 03:52:04.890 9 3433
2009-08-29 03:53:04.890 14 3433
2009-08-29 03:54:04.890 12 3433
2009-08-29 03:55:04.890 9 3433
2009-08-29 03:56:04.890 9 3433
2009-08-29 03:57:04.890 12 3433 <-------------
2009-08-29 04:03:05.230 12 3433 <-------------
2009-08-29 04:04:05.230 12 3433
2009-08-29 04:05:05.230 12 3433
2009-08-29 04:06:05.230 12 3433
2009-08-29 04:07:05.230 12 3433
2009-08-29 04:08:05.230 12 3433
2009-08-29 04:09:05.230 12 3433
2009-08-29 04:10:05.230 10 3433
2009-08-29 04:11:05.250 10 3433
2009-08-29 04:12:05.250 9 3433
2009-08-29 04:13:05.250 9 3433
2009-08-29 04:14:05.250 14 3433
2009-08-29 04:15:09.840 12 3433

result must be

2009-08-29 03:00:10.540 9 3433
2009-08-29 03:15:10.280 12 3433
2009-08-29 03:30:10.030 9 3433
2009-08-29 03:45:09.670 10 3433
2009-08-29 04:03:05.230 12 3433 <----------

explanation of <----------

here my scada-systeem didn't have connection, so it didn't log values. Then the query takes the first values of those 15 minutes.

I have already this. This is a very easy solution and doesn't support the explanation I wrote near <--------

Excuse me for my very bad english :-)

My easy solution:

select Servertimestamp, Debiet_DB1, Peil_DB1
from LembekeOosteeklo
where datepart(minute, Servertimestamp) in ('0','15','30','45')
order by servertimestamp desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 09:47:38

see logic used here

http://visakhm.blogspot.com/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources