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 2008 Forums
 Transact-SQL (2008)
 Looking for help with If/else statement

Author  Topic 

Chris Hill
Starting Member

3 Posts

Posted - 2014-08-22 : 17:03:56
I am not very savvy with SQL so I hope I am not asking for too much. I need a string of code that will query data and then look forward and backward in the data, on 15 minute intervals. First it would start looking backward 15 minutes then forward 15 minutes. Then backward 30 minutes then forward 30 minutes until it finally finds the first number that is not blank. then 45 min back then forth, then 1 hr both ways. Zeros are acceptable. Its just that when there is no data or no data entry to report that is not good.

Thank you for any help in advance.

Chris Hill
Starting Member

3 Posts

Posted - 2014-08-23 : 14:57:55
To be a bit clearer....I have an energy monitoring system that takes information from am API and deposits data in a db once per 15 minutes. If there is any issue with data getting back to the server there will be no data in the system for a given 15 minute period. Therefore we need to look at the pevious 15 min for data. If nothing, we need to look ahead 15 min. . Then back 30, then forward 30. This will repeat on until a value is found. The vaule can even be zero.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-24 : 08:49:08
Post create table stmts and insert into stmts so we can see the structure and data samples
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-24 : 10:14:30
SELECT TOP(1) *
FROM dbo.MyTable
WHERE thaTime >= @v
ORDER BY thaTime


SELECT TOP(1) *
FROM dbo.MyTable
WHERE thaTime <= @v
ORDER BY thaTime DESC



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Chris Hill
Starting Member

3 Posts

Posted - 2014-08-25 : 16:01:43
quote:
Originally posted by gbritton

Post create table stmts and insert into stmts so we can see the structure and data samples

Can you be clearer please.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-08-25 : 17:34:09
[code]

SELECT COALESCE(dt_minus15.data, dt_plus15.data, dt_minus30.data, dt_plus30.data, ...)
FROM dbo.data_table dt
LEFT OUTER JOIN dbo.data_table dt_minus15 ON
dt.datetime >= DATEADD(MINUTE, -15, @starting_time) AND
dt.datetime < @starting_time
LEFT OUTER JOIN dbo.data_table dt_plus15 ON
dt.datetime >= @starting_time AND
dt.datetime < DATEADD(MINUTE, +15, @starting_time)
LEFT OUTER JOIN dbo.data_table dt_minus30 ON
...
...

[/code]
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-25 : 17:41:46
quote:
Originally posted by Chris Hill

quote:
Originally posted by gbritton

Post create table stmts and insert into stmts so we can see the structure and data samples

Can you be clearer please.



Like this..http://www.sqlservercentral.com/articles/Best+Practices/61537/
Go to Top of Page
   

- Advertisement -