Difference - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Difference

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-26 : 09:39:46

How i can take take difference of a column on the basis of row_number...

mean i want to take a difference of a column rcount...for the value of maximum row_number and maximum-1 row_number..
how i write query for take that Difference??

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-26 : 09:52:28

Post sample data.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-26 : 09:58:38

thanks sir....
Here is some data

Custid Rcount lcount closingid
11200 32 78 4
11200 45 80 9
11200 56 89 10
11200 78 98 15

So i want the difference of RCOUNT on the basis of Closing id mean for last closingid like the difference oh closingid 15 and 10

The Output Must Be like this

Custid Rcount
11200 22 9

Thats All I want///

Thanks In Advance......

quote:
Originally posted by sodeep

Post sample data.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-26 : 11:01:44

try some thing like this

declare @temp table ( Custid int, Rcount int, lcount int, closingid int )

insert into @temp
select 11200, 32, 78, 4 union all
select 11200, 45, 80, 9 union all
select 11200, 56, 89, 10 union all
select 11200, 78, 98, 15

select tmax.custid,(tmax.rcount-tmin.rcount) as Rcount ,(tmax.lcount-tmin.lcount) as lcount
from
(
select
row_number() over ( partition by custid order by closingid desc ) as rno, *
from @temp ) tmax
inner join
(
select
row_number() over ( partition by custid order by closingid desc ) as rno, *
from @temp
) tmin on tmin.custid = tmax.custid
and tmax.rno = 1
and tmin.rno = 2

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-26 : 11:20:28

[code]Select Custid,(MAX(Rcount) - (Select min(Rcount) from(Select top 2 Rcount from Table order by Rcount desc)Z))Rcount,
(MAX(lcount) - (Select min(lcount) from(Select top 2 lcount from Table order by lcount desc)M))Lcount
from Table
Group by Custid[/code]

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-26 : 11:28:27

Thanks,,,,to u Both Guys,,,,,,,,,,,
u just saved my one night.,........
Thank you Very Much.........................

I really appreciate ur help

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-26 : 11:29:25

Welcome.....

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-26 : 11:43:10

you are Welcome

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-26 : 11:58:07

Hey i found one more way to do this task.......
after seeing ur ideas to get these values
Thanks to u both........

with ashish as
(
select rcount,custid,row_number() over(order by closing_id desc) as ROW from Tbl_Pair_Income where custid=@custid
)
select (max(rcount)-min(rcount)) as Rcount,custid from ashish where row between 1 and 2 group by @custid

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-27 : 00:17:42

Again M wid some problem,,,,if i want running difference in this den,,,,,,,,,
mean my output should be like dis.....................

Table Tbl_DATA

Custid Rcount lcount closingid
11200 32 78 4
11200 45 80 9
11200 56 89 10
11200 78 98 15

And Output Will Be like dis

custid rcount lcount
11200 13 2
11200 11 9
11200 22 9

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-27 : 00:53:16

Try this once,

declare @temp table ( Custid int, Rcount int, lcount int, closingid int )

insert into @temp
select 11200, 32, 78, 4 union all
select 11200, 45, 80, 9 union all
select 11200, 56, 89, 10 union all
select 11200, 78, 98, 15

select tmax.custid,(tmax.rcount-tmin.rcount) as Rcount ,(tmax.lcount-tmin.lcount) as lcount
from
(
select
row_number() over ( partition by custid order by closingid desc ) as rno, *
from @temp ) tmax
inner join
(
select
row_number() over ( partition by custid order by closingid desc ) as rno, *
from @temp
) tmin on tmin.custid = tmax.custid
and tmax.rno = tmin.rno - 1
order by lcount,rcount

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-27 : 23:17:21

This will work in your scenerio but won't work if closingid is not in ascending order:

Select Identity(int,1,1)as ROWID,* into #Z from Table

Select z.Custid,(z.Rcount-m.Rcount)as Rcount,(z.lcount - m.lcount)as lcount
from #Z z inner join #Z m
on z.ROWID = m.ROWID +1

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-28 : 09:25:57

Thanks For your help.........
Where glad to have persons like you there to guide us....
Thanks Again......

quote:
Originally posted by sodeep

This will work in your scenerio but won't work if closingid is not in ascending order:

Select Identity(int,1,1)as ROWID,* into #Z from Table

Select z.Custid,(z.Rcount-m.Rcount)as Rcount,(z.lcount - m.lcount)as lcount
from #Z z inner join #Z m
on z.ROWID = m.ROWID +1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources