|
I have a time series data in which a numerical indicator fluctuates over time. If I want to find out for which given day, this indicator has crossed above 30 in the past 3 days. Is it possible to write a SQL query that retrieve this information?
The logic to check for the crossing movement is:
IF indicator(day x) > 30 AND indicator(3 days ago from day x) <30 THEN
Return "YES WE HAVE AN UPWARD CROSSING MOVEMENT THROUGH 30 FOR PAST 3 DAYS!"
END IF
Can this be accomplished using a SQL CASE statement with a HAVING clause or something else?
I'd greatly appreciate any help! If possible please provide a sample code 
By the way I really want to thank you guys for helping me recently... 
|
|
|
|
|
This question will be best suited in the General database forum. Please make sure to post in correct forum from now on.
Just saw you have posted it there as well. Do not cross post. It is considered rude.
Assuming, your database fields are called indicator and date, try this:
select indicator from yourTable where indicator > 30 and datediff(date, getdate(),date) <= 3
if(@@rowcount > 0)
select 1
select 0
(not sure about order of getdate and date in datediff, you can check that through the query though)
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
modified on Tuesday, December 15, 2009 11:08 AM
|
|
|
|
|
Thanks a lot for your help D@nish, sorry about cross-posting.
|
|
|
|
|
Hi D@nish, the above query doesn't seem to take the condition of [indicator from 3 days ago which is less than 30] into account....
For instance, my table contains two columns:
Date-------------Indicator Value
12/16/2009-------45
12/15/2009-------33
12/14/2009-------29
12/13/2009-------23
12/12/2009-------27
12/11/2009-------35
12/10/2009-------29
12/9/2009--------24
...
...
I want to find out for which date, the indicator value had crossed from below 30 three days before to above 30. The answers are: "12/16/2009" and "12/11/2009". How should I contruct the query to do so?
Many thanks again.
|
|
|
|
|
James Shao wrote: The answers are: "12/16/2009" and "12/11/2009".
12/15 should also come. Right? 12/13 is less than 30 and 12/15 is more.
I hope this should work.
select tbl1.date as fromDate,tbl2.date as toDate, tbl1.indicator as fromValue, tbl2.indicator as toValue from yourtable tbl1, yourtable tbl2 where tbl1.indicator < 30 and tb12.indicator > 30 and datediff(date,tbl1.date,tbl2.date) = 2
(I have not tested the query so it may give some error)
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Hi guys,
I have a xml document as below and in Xslt I need to get the person name on the basis of email id as in below xml file , contributor node contain email id and associated name is included in Epic node can any body help me to get the name for each contributor through xpath?
<ONIXMessage>
<Product>
<Contributor>
<Email>Rohit@gmail.com</Email>
</Contributor>
</Product>
<Product>
<Contributor>
<Email>Rohit@gmail.com</Email>
</Contributor>
</Product>
<Epic>
<Person>
<Name>Rohit</Name>
<Email>Rohit@gmail.com</Email>
</Person>
</Epic>
</ONIXMessage>
|
|
|
|
|
Rohit16db wrote: person name on the basis of email id as in below xml
Why Duplicate entry for Product->contributor->Email
XSLT is used to transform your xml into XHTML. You can easily move into some node decendant to the current one using XPath.
Tell me what exactly you require.
See
http://www.w3schools.com/xsl/[^]
for reference.
|
|
|
|
|
I have some controls (placeholders) that I'm trying hide/show, and a button that I want to enable/disable when the button is clicked, but none of that stuff is happening. What am I not doing?
I'm doing this (both panels are hidden by default, and the button is enabled by default:
protected void buttonRetrieveData_OnClick(object sender, EventArgs e)
{
this.WaitPanel.Visible = true;
this.OutputPanel.Visible = false;
this.buttonRetrieveData.Enabled = false;
try
{
blah blah blah...
}
catch (Exception ex)
{
if (ex != null) { }
}
finally
{
this.WaitPanel.Visible = false;
this.OutputPanel.Visible = true;
this.buttonRetrieveData.Enabled = true;
}
}
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Does the code run (is the event being fired?)
Is the button RetrieveData in an Ajax Update panel?
|
|
|
|
|
Eduard Keilholz wrote: Does the code run (is the event being fired?)
Yes, it does. I even tried putting the code (that doesn't work) into the Page_Load method thinking maybe it should go there instead, but that had no effect.
Eduard Keilholz wrote: Is the button RetrieveData in an Ajax Update panel?
No. The panels are PlaceHolder controls.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
John Simmons / outlaw programmer wrote: this.WaitPanel.Visible = true;
this.OutputPanel.Visible = false;
this.buttonRetrieveData.Enabled = false;
John Simmons / outlaw programmer wrote: this.WaitPanel.Visible = false;
this.OutputPanel.Visible = true;
this.buttonRetrieveData.Enabled = true;
Aren't these contradictory? Whatever the case maybe, end result will be what is there in finally block. Isn't it?
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
The intent is that when the button is clicked, the wait panel is displayed, the output panel is hidden, and the button that fired the event is disabled. When the process is finished, the wait panel is hidden, the output panel is displayed, and the button is re-enabled.
The finally block merely ensures that the controls are returned to the desired state, no matter what happens in the try block.
I don't see how the code I posted is contradictory.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
John Simmons / outlaw programmer wrote: I don't see how the code I posted is contradictory
I was thinking the other way. After the event the result will always be the same.
I don't think it will work (not among the even decent ASP.Net guys so may be wrong). Since the page will not be rendered during the phase where event is getting executed. I would rather employ a javascript function which make the panels visible/hidden before the server side event is fired and then in the event hide them.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Well, I did try moving the first block into the Page_Load method, but that didn't help at all...
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
That will not help. In fact if it is a full postback, nothing will. Since during the event is during executed, the page will not be rendered in the browser.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
The process that is contained in the button event is fairly long, and I was considering moving it into a thread, which would be responsible for changing the controls back upon completion. Maybe that's ultimately the answer...
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
I assume you would have, but still I would suggest. Have you thought about having that button in a update panel and and using update panel animator? It would give a better look.
(Here I am assuming that WaitPanel must be something like those loading/working kind gifs)
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
d@nish wrote: Have you thought about having that button in a update panel and and using update panel animator? It would give a better look.
I don't know what that is...
d@nish wrote: (Here I am assuming that WaitPanel must be something like those loading/working kind gifs)
Yeah, it is.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
This[^] should help. And this[^] link for UpdateProgress control (sorry for incorrect control name in earlier post).
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
This is what impossible.
In case of client server env, everytime you request the server, you are actually posting the form with all its data to the server, and until the server processes it and returns it back, you will see a blank screen(If not using AJAX).
So, it will not show you the panels.
What you need to do, is you call the server using AJAX call, and use some javascript to show the panel in the client side.
Panels are rendered as div.
So, design the panel in server side, if you wish... (I would have dynamically created the div using Javascript), use Js to handle like this :
var panel = document.getElementById('<%=this.WaitPanel.ClientId %>');
panel.style.display = 'block'; //To hide the panel..
and to hide it again
panel.style.display = 'none';
Dont use serverside visible property, as otherwise the html will not be sent to the client.
|
|
|
|
|
k
===========================
http://www.nitinsawant.com
===========================
|
|
|
|
|
Fix your user ID. It's displaying HTML and borking up the forum presentation.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Forgive me if I'm wrong, but, as per your sequence, the code in the finally block is getting executed in all cases, which as far as I know, is the way it is supposed to work. I don't see any reason why the code that you have highlighted should not get executed!?
|
|
|
|
|
Dinesh Mani wrote: the code in the finally block is getting executed in all cases, which as far as I know, is the way it is supposed to work.
You are correct. The code in the finally block will get executed no matter what happens above it.
Dinesh Mani wrote: I don't see any reason why the code that you have highlighted should not get executed!?
I don't either, hence my question - why doesn't it work?
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
I'm not an expect in Ajax programming, but wouldn't the flush happen only after the event execution is completed? So, in effect, the working of the first section would not be visible but would have happened.
I'm not sure on this, as said above I'm not an ajax expert either.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
