writing a query that can work back from now() to now()-1min add collect data
-
writing a query that can work back from now() to now()-1min add collect data
I would like to write a query that can work back from the current time to current time - 1 min and than return the total of a field and count the number of records selected
Any help please
-
This looks a lot like homework or an interview question... please post what you've tried and we'll go from there.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
select ESDSTotal from DEMANDSPREAD WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP)FROM DEMANDSPREAD) to (SELECT MAX(TIMESTAMP-00:01:00)FROM DEMANDSPREAD)
ESDStotal(int) and Timestamp(datetime) are in my table
thats what i am trying so far to get the time at least
-
Maybe:
SELECT SUM(S.ESDS) AS ESDSTotal
,COUNT(S.ESDS) AS ESDSCount
,COUNT(*) AS ESDSCountWithNulls
FROM DEMANDSPREAD S
CROSS JOIN (
SELECT MAX(S1.[TimeStamp]) AS MaxTimeStamp
FROM DEMANDSPREAD S1
) D
WHERE S.[TimeStamp] BETWEEN DATEADD(minute, -1, D.MaxTimeStamp) AND D.MaxTimeStamp
-
Heh... although they both have the same execution plan, I'm not sure why you wrote it as a Cross-Join that works like an Inner Join... why not just write it as the Inner Join that it really is?
SELECT SUM(s.ESDSTotal) AS ESDSTotal,
COUNT(s.ESDSTotal) AS ESDSCount,
COUNT(*) AS ESDSCountWithNulls
FROM dbo.DemandSpread s
INNER JOIN (--==== Derived table "d" finds latest date in DemandSpread
SELECT MAX(s1.[TimeStamp]) AS MaxTimeStamp
FROM dbo.DemandSpread s1) d
ON s.[TimeStamp] BETWEEN DATEADD(mi, -1, d.MaxTimeStamp) AND d.MaxTimeStamp
Jon,
If you want the count for the last minute, we need to make a mod to either the code above or to Ken's good code as follows...
SELECT SUM(s.ESDSTotal) AS ESDSTotal,
COUNT(s.ESDSTotal) AS ESDSCount,
COUNT(*) AS ESDSCountWithNulls
FROM dbo.DemandSpread s
WHERE s.[TimeStamp] BETWEEN DATEADD(mi, -1, GETDATE()) AND GETDATE()
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry about the CROSS JOIN. I do try to suppress my urge to put all the join conditions in the WHERE clause!
-
LOL... I know what you mean... probably a really bad habit but the only time I normally use the ANSI "Join" statements is when an outer join is present.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you guys so much, my knowledge on sql is very small right now,you have a been great help, and I dont want to push it, but i am calling this statement from a 4 pc's and have alot of data running thru it, really new to sql maybe using it a month or so. its really great tool so I got less knowledge on it that and need to get it real fast.
I am posting to it ever 2 seconds is there away of making a new field to calculate this information when data arrives? of course I clear the data base every 2-3d ays or so, so there wont be t-1 when i start sending the data from excel, any help or is this the best way?
Is the data being processed whtin the remote PC if so thats fine
Thanks again
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply