SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Looking for help with If/else statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Chris Hill
Starting Member

3 Posts

Posted - 08/22/2014 :  17:03:56  Show Profile  Reply with Quote
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 - 08/23/2014 :  14:57:55  Show Profile  Reply with Quote
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
Aged Yak Warrior

804 Posts

Posted - 08/24/2014 :  08:49:08  Show Profile  Reply with Quote
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

Sweden
30242 Posts

Posted - 08/24/2014 :  10:14:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 08/25/2014 :  16:01:43  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
372 Posts

Posted - 08/25/2014 :  17:34:09  Show Profile  Reply with Quote


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
    ...
...

Go to Top of Page

gbritton
Aged Yak Warrior

804 Posts

Posted - 08/25/2014 :  17:41:46  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000