August 19, 2010 at 3:05 pm
Hello,
Our system behaves such that every certain number of records inserted into a table, a checkpoint row is inserted to signal certain system event. each record inserted has a datetime stamp so we can know what rows were already inserted between two checkpoint operations. My task is to get the SUM(fielda) within the ranges between each 2 consecutive checkpoints.
Any idea how to accomplish this without a cursor?
Many Thanks
--Sam
August 19, 2010 at 3:27 pm
Would you mind providing some sample data that reflect table structure and data structure (e.g. How can the checkpoint rows be identified? Is there an identity column? or the like)
August 19, 2010 at 3:37 pm
Hello,
sample data:
RowId uniqueidentifier,
RowType INT (0 - data row, 1-check point row)
Value float (this is the value we want to sum within the range)
DateInserted DATETIME (Date row was inserted for both data and check point)
ex: I need the sum for the values between date1 and date2
SELECT SUM(value)
FROM table
WHERE DAETINSERTED BETWEEN date1 and date2
What I am asking about is if there is a way to avoid using cursor while generating this sum for every two consecutive check point rows (date range where ROWTYPE =1)
Thanks
--Sam
August 19, 2010 at 3:51 pm
Depending on the number of rows and whether the data can be aggregated asynchronously or not I would either use
ROW_NUMBER() OVER(ORDER BY RowId ) - ROW_NUMBER() OVER(PARTITION BY RowType ORDER BY RowId)
in a subquery and aggregate by that column or use the "quirky-update" method to assign a group number using a scheduled job.
The link for the quirky-update method can be found here[/url]
August 19, 2010 at 3:57 pm
You'll get much better answers if you read and follow the instructions in the first article I reference below in my signature block regarding asking for help. In addition to the DDL for the table(s), sample data (readily consumable), the expected results based on the sample data would also assist greatly in providing you with a more complete answer.
Anything else is just guess work by the volunteers that try to help.
August 19, 2010 at 5:16 pm
Another option would be to extract the timestamp data for just the checkpoint rows into a CTE and also add a sequential row counter using ROW_NUMBER() with the sequence numbering defined by the timestamp order. This CTE can then be joined with itself to get the pairs of adjacent checkpoints that define the beginning and end of each time interval, which can then be used to partition the data rows in the original table for aggregation.
;WITH cteCHKPT AS (
SELECT
DateInserted,
ROW_NUMBER() OVER (ORDER BY DateInserted) AS Seq
FROM MyData
WHERE (RowType = 1)
)
SELECT
CP1.DateInserted,
CP2.DateInserted,
SUM(CASE WHEN (D.RowType = 0) THEN D.Value ELSE 0.0 END) AS Sigma
FROM (cteCHKPT CP1 INNER JOIN cteCHKPT CP2 ON (CP1.Seq = CP2.Seq - 1))
INNER JOIN MyData D
ON (D.DateInserted >= CP1.DateInserted AND D.DateInserted < CP2.DateInserted)
GROUP BY CP1.DateInserted, CP2.DateInserted
ORDER BY CP1.DateInserted
The above query obviously doesn't filter by an input date range, and it will also ignore any data before the first checkpoint or after the last checkpoint, but it could be amended to addess these issues.
August 20, 2010 at 7:38 am
Andrew, Thanks that is pretty much what I was looking for.
Lynn, thanks for your input - will note for future posts. Apparently "Forum Newbie" under my Author info is there for a reason 🙂
Lutz, thanks for your prompt help.
Thank you all.
--Sam
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply