October 20, 2011 at 3:29 pm
Hi Everyone,
Hi I currently have 6 columns in my database that are counters(from a flow meter counting liters / min). I need to take that column and get the difference from the previous value and make that its own column. then i need to take that column and make a running total column for it. I cannot write to the database.
ID counter counterdif counterdifsum
1 387 0 0
2 387 0 0
3 389 2 2
4 392 3 5
5 396 4 9
6 396 0 9
Thats my desired output. I have tried and succeeded in making that entire process for just one of the counters in a query but it takes forever. 1 minute to return 200 rows. it will need to return 40,000 total 🙁 . I was told try and use a cross apply but not quite getting it was just giving me the final total down the entire column.
code i am using below (sorry its terrible lol)
Thanks!
WITH rows AS
(
SELECT no1aliquidcounter,
ROW_NUMBER() OVER (ORDER BY ID) AS rn,
ID
FROM datatable
WHERE ringnum = 120
) ,
M as
(
SELECT
(mp.no1aliquidcounter - mc.no1aliquidcounter) /1000 AS no1aliquidcountdiff,
mc.ID
FROM rows mc
JOIN rows mp
ON mc.rn = mp.rn - 1
),
RUNNER(
ID,
no1aliquidcountdiff,
no1aliquidcountdiffttl)
AS
(
SELECT
a.ID,
a.no1aliquidcountdiff,
(SELECT
SUM(b.no1aliquidcountdiff)
FROM M AS b
WHERE b.ID <= a.ID)
FROM M AS a
)
SELECT * FROM RUNNER
October 20, 2011 at 3:43 pm
If you can't write to the database, then and only then your best option is a recursive CTE, as follows:
DROP TABLE #MySample
CREATE TABLE #MySample (ID INT, [counter] INT)
INSERT INTO #MySample (ID, [counter])
SELECT 1, 387 UNION ALL
SELECT 2, 387 UNION ALL
SELECT 3, 389 UNION ALL
SELECT 4, 392 UNION ALL
SELECT 5, 396 UNION ALL
SELECT 6, 396
;WITH OrderedData AS (
SELECT *,seq = ROW_NUMBER() OVER(ORDER BY ID) FROM #MySample),
Calculator AS (
SELECT
seq,
ID,
[counter],
counterdif = 0,
counterdifsum = 0
FROM OrderedData
WHERE seq = 1
UNION ALL
SELECT
tr.seq,
tr.ID,
tr.[counter],
counterdif = tr.[counter] - lr.[counter],
counterdifsum = lr.counterdifsum + tr.[counter] - lr.[counter]
FROM Calculator lr
INNER JOIN OrderedData tr ON tr.seq = lr.seq+1
) SELECT ID, [counter], counterdif, counterdifsum
FROM Calculator -- check out option maxrecursion
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 20, 2011 at 3:54 pm
Is this being done inside a stored procedure or what? Hard to give a good answer without knowing all the facts.
October 20, 2011 at 4:07 pm
This is a single query I am using to get a grout volume actually. database is stored every 10 seconds always. ID column counts +1. I have a date column that gives me a time stamp (03/03/2011 19:00:00.000). then the counters and a bunch of other sensor data. I am using sql 2008 r2. pretty simple database i would imagine, no nulls always 10 seconds different. rows never get deleted.
I tried the code posted above using my current table and columns and I get seq is an invalid column name. Im new to this but doesnt that say seq = ..... right off the bat 😛
had to toss in the edit here forgot to say thanks for the quick repsonses wasnt up long and got 2 responses 🙂
October 20, 2011 at 4:49 pm
Hello again,
Got my seq fixed but i get these errors:
Msg 240, Level 16, State 1, Line 2
Types don't match between the anchor and the recursive part in column "counterdif" of recursive query "Calculator".
Msg 240, Level 16, State 1, Line 2
Types don't match between the anchor and the recursive part in column "counterdifsum" of recursive query "Calculator".
The counter columns are reals and id is int. Currently looking up but not sure what its saying doesnt match lol. possibly cause counterdif =0 and countersum = 0 line?
WITH OrderedData AS (
SELECT *,seq = ROW_NUMBER() OVER(ORDER BY ID) FROM datatable),
Calculator AS (
SELECT
seq,
ID,
[no1aliquidcounter],
counterdif =0,
counterdifsum = 0
FROM OrderedData
WHERE seq = 1
UNION ALL
SELECT
tr.seq,
tr.ID,
tr.[no1aliquidcounter],
counterdif = tr.[no1aliquidcounter] - lr.[no1aliquidcounter],
counterdifsum = lr.counterdifsum + tr.[no1aliquidcounter] - lr.[no1aliquidcounter]
FROM Calculator lr
INNER JOIN OrderedData tr ON tr.seq = lr.seq+1
) SELECT ID, [no1aliquidcounter], counterdif, counterdifsum
FROM Calculator -- check out option maxrecursion
October 20, 2011 at 5:40 pm
So it works!
counterdif =CAST(0 AS REAL),
counterdifsum = CAST(0 AS REAL)
all i needed to change 🙂
Thanks so much for that code.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply