Styjun

How to get cool night-vision without lame drawbacks?

Can we put equal sign after aggregate functions in sql?

Is a single radon-daughter atom in air a solid?

Interaction between Leyline of Anticipation and Teferi, Time Raveler

How much will studying magic in an academy cost?

Folding basket - is there such a thing?

Has there been any indication at all that further negotiation between the UK and EU is possible?

Long term BTC investing

Can someone suggest a path to study Mordell-Weil theorem for someone studying on their own?

Why do some games show lights shine thorugh walls?

First-year PhD giving a talk among well-established researchers in the field

Why is the voltage measurement of this circuit different when the switch is on?

Find the C-factor of a vote

Fedora boot screen shows both Fedora logo and Lenovo logo. Why and How?

3D Crossword, Cryptic, Statue View & Maze

Is my Rep in Stack-Exchange Form?

Why change the size when print a object

expiry or manufactured date?

Why do all the teams that I have worked with always finish a sprint without completion of all the stories?

If you snatch, I trade

MRI maximum field strenght

Can ADFS connect to other SSO services?

Why do even high-end cameras often still include normal (non-cross-type) AF sensors?

When to remove insignificant variables?

Stuck in getting calculations of transaction values

Best way to get identity of inserted row?Add a column with a default value to an existing table in SQL ServerSQL Server: Database stuck in “Restoring” stateWhy is SQL Server 2008 blocking SELECT's on long transaction INSERT's?how to calculate database transaction count per second and database growthGet size of all tables in databaseSelecting max record for each userAdvanced Grouping - Pulling most recent transaction, but maintaining groupingFind Latest Record With Specific ValuesSQL stored procedure query to clear date field if NULL

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I have an event table and want to do calculations on the basis on most recent transaction values.

create table evt (evnt integer not null,
value integer not null,
time datetime not null,
)

and query:

SELECT evnt ,value
FROM evt where time IN (select max(time) from events group by evnt )
 GROUP BY evnt ,value

insert into evt values (2,5,'2018-05-09 12:42:00')
insert into evt values (4,-42,'2018-05-09 13:19:57')
insert into evt values (2,2,'2018-05-09 14:48:30')
insert into evt values (2,7,'2018-05-09 12:54:39')
insert into evt values (3,16,'2018-05-09 13:19:57')
insert into evt values (3,20,'2018-05-09 15:01:09')

Output

evnt | Value
-------------------
2 | -5
3 | 4

sql-server

share|improve this question

edited Mar 25 at 9:46

StepUp

9,14099 gold badges4545 silver badges8080 bronze badges

asked Mar 25 at 9:35

swadswad

2777 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What Calculations you have to do ? And in the Output how did you get value as -5 ?
  
  – Srikar mogaliraju
  Mar 25 at 9:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  For the evnt 2, the latest value is 2 and the second latest value is 7. so the difference between them is -5.
  
  – swad
  Mar 25 at 9:58
  

  
  
  
  

add a comment | 

0

I have an event table and want to do calculations on the basis on most recent transaction values.

create table evt (evnt integer not null,
value integer not null,
time datetime not null,
)

and query:

SELECT evnt ,value
FROM evt where time IN (select max(time) from events group by evnt )
 GROUP BY evnt ,value

insert into evt values (2,5,'2018-05-09 12:42:00')
insert into evt values (4,-42,'2018-05-09 13:19:57')
insert into evt values (2,2,'2018-05-09 14:48:30')
insert into evt values (2,7,'2018-05-09 12:54:39')
insert into evt values (3,16,'2018-05-09 13:19:57')
insert into evt values (3,20,'2018-05-09 15:01:09')

Output

evnt | Value
-------------------
2 | -5
3 | 4

sql-server

share|improve this question

edited Mar 25 at 9:46

StepUp

9,14099 gold badges4545 silver badges8080 bronze badges

asked Mar 25 at 9:35

swadswad

2777 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What Calculations you have to do ? And in the Output how did you get value as -5 ?
  
  – Srikar mogaliraju
  Mar 25 at 9:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  For the evnt 2, the latest value is 2 and the second latest value is 7. so the difference between them is -5.
  
  – swad
  Mar 25 at 9:58
  

  
  
  
  

add a comment | 

I have an event table and want to do calculations on the basis on most recent transaction values.

create table evt (evnt integer not null,
value integer not null,
time datetime not null,
)

and query:

SELECT evnt ,value
FROM evt where time IN (select max(time) from events group by evnt )
 GROUP BY evnt ,value

insert into evt values (2,5,'2018-05-09 12:42:00')
insert into evt values (4,-42,'2018-05-09 13:19:57')
insert into evt values (2,2,'2018-05-09 14:48:30')
insert into evt values (2,7,'2018-05-09 12:54:39')
insert into evt values (3,16,'2018-05-09 13:19:57')
insert into evt values (3,20,'2018-05-09 15:01:09')

Output

evnt | Value
-------------------
2 | -5
3 | 4

sql-server

share|improve this question

edited Mar 25 at 9:46

StepUp

9,14099 gold badges4545 silver badges8080 bronze badges

asked Mar 25 at 9:35

swadswad

2777 bronze badges

create table evt (evnt integer not null,
value integer not null,
time datetime not null,
)

SELECT evnt ,value
FROM evt where time IN (select max(time) from events group by evnt )
 GROUP BY evnt ,value

insert into evt values (2,5,'2018-05-09 12:42:00')
insert into evt values (4,-42,'2018-05-09 13:19:57')
insert into evt values (2,2,'2018-05-09 14:48:30')
insert into evt values (2,7,'2018-05-09 12:54:39')
insert into evt values (3,16,'2018-05-09 13:19:57')
insert into evt values (3,20,'2018-05-09 15:01:09')

evnt | Value
-------------------
2 | -5
3 | 4

sql-server

sql-server

share|improve this question

edited Mar 25 at 9:46

StepUp

9,14099 gold badges4545 silver badges8080 bronze badges

asked Mar 25 at 9:35

swadswad

2777 bronze badges

share|improve this question

edited Mar 25 at 9:46

StepUp

9,14099 gold badges4545 silver badges8080 bronze badges

asked Mar 25 at 9:35

swadswad

2777 bronze badges

edited Mar 25 at 9:46

StepUp

9,14099 gold badges4545 silver badges8080 bronze badges

edited Mar 25 at 9:46

StepUp

9,14099 gold badges4545 silver badges8080 bronze badges

asked Mar 25 at 9:35

swadswad

2777 bronze badges

asked Mar 25 at 9:35

swadswad

2777 bronze badges

1 Answer
1

active

oldest

votes

0

Based on your brief description, i guess this is what you wanted

select evnt, sum(case when rn = 1 then value else -value end)
from
(
 select *, rn = row_number() over (partition by evnt order by time desc)
 from evt
) d
where d.rn <= 2
group by evnt
having count(*) = 2

share|improve this answer

answered Mar 25 at 9:58

SquirrelSquirrel

12.8k22 gold badges2323 silver badges2828 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can we achieve it without row_number()?
  
  – swad
  Mar 25 at 10:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  any particular reason you want to avoid row_number ?
  
  – Squirrel
  Mar 25 at 10:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  these are not allowed in my task..
  
  – swad
  Mar 25 at 10:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  so you are allow to use ?
  
  – Squirrel
  Mar 25 at 10:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can't use row_number() .
  
  – swad
  Mar 25 at 11:14
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55334830%2fstuck-in-getting-calculations-of-transaction-values%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Based on your brief description, i guess this is what you wanted

select evnt, sum(case when rn = 1 then value else -value end)
from
(
 select *, rn = row_number() over (partition by evnt order by time desc)
 from evt
) d
where d.rn <= 2
group by evnt
having count(*) = 2

share|improve this answer

answered Mar 25 at 9:58

SquirrelSquirrel

12.8k22 gold badges2323 silver badges2828 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can we achieve it without row_number()?
  
  – swad
  Mar 25 at 10:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  any particular reason you want to avoid row_number ?
  
  – Squirrel
  Mar 25 at 10:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  these are not allowed in my task..
  
  – swad
  Mar 25 at 10:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  so you are allow to use ?
  
  – Squirrel
  Mar 25 at 10:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can't use row_number() .
  
  – swad
  Mar 25 at 11:14
  

  
  
  
  

add a comment | 

0

Based on your brief description, i guess this is what you wanted

select evnt, sum(case when rn = 1 then value else -value end)
from
(
 select *, rn = row_number() over (partition by evnt order by time desc)
 from evt
) d
where d.rn <= 2
group by evnt
having count(*) = 2

share|improve this answer

answered Mar 25 at 9:58

SquirrelSquirrel

12.8k22 gold badges2323 silver badges2828 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can we achieve it without row_number()?
  
  – swad
  Mar 25 at 10:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  any particular reason you want to avoid row_number ?
  
  – Squirrel
  Mar 25 at 10:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  these are not allowed in my task..
  
  – swad
  Mar 25 at 10:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  so you are allow to use ?
  
  – Squirrel
  Mar 25 at 10:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can't use row_number() .
  
  – swad
  Mar 25 at 11:14
  

  
  
  
  

add a comment | 

Based on your brief description, i guess this is what you wanted

select evnt, sum(case when rn = 1 then value else -value end)
from
(
 select *, rn = row_number() over (partition by evnt order by time desc)
 from evt
) d
where d.rn <= 2
group by evnt
having count(*) = 2

share|improve this answer

answered Mar 25 at 9:58

SquirrelSquirrel

12.8k22 gold badges2323 silver badges2828 bronze badges

select evnt, sum(case when rn = 1 then value else -value end)
from
(
 select *, rn = row_number() over (partition by evnt order by time desc)
 from evt
) d
where d.rn <= 2
group by evnt
having count(*) = 2

share|improve this answer

answered Mar 25 at 9:58

SquirrelSquirrel

12.8k22 gold badges2323 silver badges2828 bronze badges

answered Mar 25 at 9:58

SquirrelSquirrel

12.8k22 gold badges2323 silver badges2828 bronze badges

answered Mar 25 at 9:58

SquirrelSquirrel

12.8k22 gold badges2323 silver badges2828 bronze badges