November 17, 2017 at 5:46 am
Hi,
Col1
10
20
30
40
Need output like this
10-0=10
20-10=10
30-10=20
40-20=20
Output:
Col1
10
10
20
20
November 17, 2017 at 5:58 am
You can answer query for 2012 .It would be great help
Thanks
November 17, 2017 at 6:00 am
selpoivre - Friday, November 17, 2017 5:58 AMYou can answer query for 2012 .It would be great help
Thanks
But which environment are you using? 2012 has a more Window Functions than 2008. If you're using 2008, there's no point providing a function that will only work on 2012 onwards (because it won't on 2008).
Edit, also, apologies, I have misunderstood your logic. I thought this was a ROWS BETWEEN issue, however, actually, not. The value changes each time, which means the problem is more likely to be a recursive CTE issue. please ignore my previous posts, for the moment.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 17, 2017 at 6:02 am
I have both 2008 and 2012 systems.I can run query on 2012,its not an issue
November 17, 2017 at 6:30 am
Thom A - Friday, November 17, 2017 6:00 AMselpoivre - Friday, November 17, 2017 5:58 AMYou can answer query for 2012 .It would be great help
ThanksBut which environment are you using? 2012 has a more Window Functions than 2008. If you're using 2008, there's no point providing a function that will only work on 2012 onwards (because it won't on 2008).
Edit, also, apologies, I have misunderstood your logic. I thought this was a ROWS BETWEEN issue, however, actually, not. The value changes each time, which means the problem is more likely to be a recursive CTE issue. please ignore my previous posts, for the moment.
I think he just needs LAG, doesn't he? Table DDL and consumable sample data would be helpful, of course, along with expected results that make sense. I don't understand why the output for 30 is 30-10=20 and not 30-20=10.
John
November 17, 2017 at 7:09 am
John Mitchell-245523 - Friday, November 17, 2017 6:30 AMI think he just needs LAG, doesn't he? Table DDL and consumable sample data would be helpful, of course, along with expected results that make sense. I don't understand why the output for 30 is 30-10=20 and not 30-20=10.John
Mmm, i don't really understand either. I've been trying to figure out the logic but it doesn't seem obvious. I thought they were summing the subtractions, but if that were the case, every row would had a -0.
OP, can you elaborate on how you get each figure?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 17, 2017 at 9:42 am
Seems to be an odd recursive thing, where for each row you compute a SubtractionResult, with SubtractionResult=ColumnValue-(SubtractionResult from previous row, or 0 if no previous row).
Fairly simple to implement in whatever is the most appropriate recursive solution here (likely Quirky Update will be most efficient, with all its usual caveats).
Another way of saying the same thing is that you're computing these terms for each row, with RowN standing in for the column value in question for the row N:
Row1-0, or simply Row1
Row2-Row1
Row3-(Row2-Row1), or Row3-Row2+Row1
Row4-(Row3-(Row2-Row1)), or Row4-Row3+Row2-Row1
...
So, the other way you could do it with the allowance of ORDER BY in windowed aggregate functions in 2012 is to do a SUM of the column value in question for all preceding rows inclusive of the current row, switching the signs of rows with a different (row number in specified order)%2 value than the current row (note that pattern in the expanded forms of each expression above).
Of course, you have to have something that defines the order of each row; the OP will have to adapt to whatever defines order for his data (I've just used ascending numerical order as his sample seems to use).
Something like this:
IF OBJECT_ID ('tempdb.dbo.#somenumbers') IS NOT NULL DROP TABLE #somenumbers;
CREATE TABLE #somenumbers (somenumber INT);
INSERT INTO #somenumbers VALUES
(10),
(20),
(30),
(40);
WITH numbered AS
(
SELECT rn=ROW_NUMBER() OVER (ORDER BY somenumber ASC), somenumber
FROM #somenumbers
),
odds_and_evens AS
(
SELECT *,
odds=CASE WHEN rn%2=0 THEN -1*somenumber ELSE somenumber END,
evens=CASE WHEN rn%2=1 THEN -1*somenumber ELSE somenumber END
FROM numbered
)
SELECT
somenumber
,cumulative_subtraction
=CASE WHEN rn%2=0 THEN SUM(evens) OVER (ORDER BY rn ASC) ELSE SUM(odds) OVER (ORDER BY rn ASC) END
FROM odds_and_evens
ORDER BY rn ASC;
EDIT: Tidied up some word choices.
November 17, 2017 at 10:00 am
Jacob Wilkins - Friday, November 17, 2017 9:42 AMSeems to be an odd recursive thing, where for each row you compute a SubtractionResult, with SubtractionResult=ColumnValue-(SubtractionResult from previous row, or 0 if no previous row).Fairly simple to implement in whatever is the most appropriate recursive solution here (likely Quirky Update will likely be most efficient, with all its usual caveats).
Another way of saying the same thing is that you're computing these terms for each row, with RowN standing in for the column value in question for the row N:
Row1-0, or simply Row1
Row2-Row1
Row3-(Row2-Row1), or Row3-Row2+Row1
Row4-(Row3-(Row2-Row1)), or Row4-Row3+Row2-Row1
...So, the other way you could do it with the allowance of ORDER BY in windowed aggregate functions in 2012 is to do a SUM of the column value in question for all preceding rows inclusive of the current row, switching the signs of rows with a different (row number in specified order)%2 value than the current row (note that pattern in the expanded forms of each expression above).
Of course, you have to have something that defines the order of each row; the OP will have to adapt to whatever defines order for his data (I've just used ascending numerical order as his sample seems to use).
Something like this:
IF OBJECT_ID ('tempdb.dbo.#somenumbers') IS NOT NULL DROP TABLE #somenumbers;CREATE TABLE #somenumbers (somenumber INT);
INSERT INTO #somenumbers VALUES
(10),
(20),
(30),
(40);WITH numbered AS
(
SELECT rn=ROW_NUMBER() OVER (ORDER BY somenumber ASC), somenumber
FROM #somenumbers
),
odds_and_evens AS
(
SELECT *,
odds=CASE WHEN rn%2=0 THEN -1*somenumber ELSE somenumber END,
evens=CASE WHEN rn%2=1 THEN -1*somenumber ELSE somenumber END
FROM numbered
)SELECT
somenumber
,cumulative_subtraction
=CASE WHEN rn%2=0 THEN SUM(evens) OVER (ORDER BY rn ASC) ELSE SUM(odds) OVER (ORDER BY rn ASC) END
FROM odds_and_evens
ORDER BY rn ASC;
what is the expected out put from this set?
INSERT INTO #somenumbers VALUES
(10),
(5),
(30),
(40);
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 17, 2017 at 10:10 am
Jacob Wilkins - Friday, November 17, 2017 10:06 AM@j-2 Livingston SQL:Directed to the OP, or to me? 🙂
sorry Jacob.....was supposed to be to the OP.
That said ...what thoughts have you ?
i would expect to see
10 10
5 -5
30 35
40 5
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 17, 2017 at 10:34 am
No worries; I just wanted to make sure I didn't just wait around in the case it was directed to me 🙂
Assuming the desired order for the cumulative piece in that example is the presented order and my hypothesis about the logic desired is correct, then yeah, that's what I'd expect also.
In the OP's court now 🙂
November 17, 2017 at 10:42 am
Jacob Wilkins - Friday, November 17, 2017 10:34 AMNo worries; I just wanted to make sure I didn't just wait around in the case it was directed to me 🙂Assuming the desired order for the cumulative piece in that example is the presented order and my hypothesis about the logic desired is correct, then yeah, that's what I'd expect also.
In the OP's court now 🙂
but your code doesnt deliver those results .
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 17, 2017 at 10:45 am
As expected.
From my initial post:
Of course, you have to have something that defines the order of each row; the OP will have to adapt to whatever defines order for his data (I've just used ascending numerical order as his sample seems to use[/i]).
Hence the qualification in my response to you, since I suspected that you presented them in an order other than ascending numerical for a reason:
Assuming the desired order for the cumulative piece in that example is the presented order...
🙂
November 17, 2017 at 10:51 am
Jacob Wilkins - Friday, November 17, 2017 10:45 AMAs expected.From my initial post:
Of course, you have to have something that defines the order of each row; the OP will have to adapt to whatever defines order for his data (I've just used ascending numerical order as his sample seems to use[/i]).
Hence the qualification in my response to you, since I suspected that you presented them in an order other than ascending numerical for a reason:
Assuming the desired order for the cumulative piece in that example is the presented order...
🙂
fair enough......lets see if the OP responds or maybe this another "Friday special"....post and bugger off for the weekend!
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply