help modifying query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

help modifying query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-08-25 : 17:19:14

Hi,

I have the following query

alter PROCEDURE dbo.select_honors
(
@userID int
)
AS SET NOCOUNT ON

SELECT top 100 rank, honorDate, honorTypeID FROM tblHonors H

JOIN tblUserDetails UD on UD.userID = H.userID

WHERE h.userID = @userID ORDER BY date asc

GO

which brings back the following results.

27 2006-08-23 17:52:05.607 2
25 2006-08-23 18:36:26.920 2
23 2006-08-23 23:37:42.013 2
19 2006-08-24 03:02:41.373 2
19 2006-08-24 03:04:07.577 2
19 2006-08-24 08:05:22.233 2
21 2006-08-24 13:06:36.123 2
23 2006-08-24 18:07:54.873 2
18 2006-08-24 23:09:09.217 2
21 2006-08-25 03:02:43.717 2
19 2006-08-25 08:04:01.467 2
23 2006-08-25 13:05:19.467 2

What I would like to do is bring back a 4th column with a "rankChange" value. This would be determined by the difference between the current row and the row after it.

Example:

27 2006-08-23 17:52:05.607 2 (rankChange= -2)
25 2006-08-23 18:36:26.920 2 (rankChange= -2)
23 2006-08-23 23:37:42.013 2 (rankChange= -4)
19 2006-08-24 03:02:41.373 2 (rankChange= 0 )

The query is pretty instant right now. Hopefully its not some major overhead to do this.

Any help is much appreciated.

Thanks once again!!
mike123

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-08-25 : 17:44:54

Try this for the calculated column

isnull((SELECT H1.rank - H.rank FROM tblHonors H1 WHERE H1.honordate = (SELECT min(H2.honordate) FROM tblHonors H2 WHERE H2.honorDate > H.honorDate)), 0)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-08-25 : 18:43:43

hi,

I get the following error

Server: Msg 512, Level 16, State 1, Procedure select_honors, Line 8
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

any idea?

Thanks again !:)
mike123

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-08-25 : 23:56:26

Use a top to get just the first one,

isnull((SELECT TOP 1 H1.rank - H.rank FROM tblHonors H1 WHERE H1.honordate = (SELECT min(H2.honordate) FROM tblHonors H2 WHERE H2.honorDate > H.honorDate)), 0)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-08-27 : 21:06:44

thansk for the help, but I am getting some unexpected results with that query.. here is an example

rank/date/typeID/rankChange

27 2006-08-23 17:52:05.607 2 1
25 2006-08-23 18:36:26.920 2 3
23 2006-08-23 23:37:42.013 2 1
19 2006-08-24 03:02:41.373 2 1
19 2006-08-24 03:04:07.577 2 3
19 2006-08-24 08:05:22.233 2 3
21 2006-08-24 13:06:36.123 2 2
23 2006-08-24 18:07:54.873 2 1
18 2006-08-24 23:09:09.217 2 3
21 2006-08-25 03:02:43.717 2 1
19 2006-08-25 08:04:01.467 2 3
23 2006-08-25 13:05:19.467 2 1
19 2006-08-27 03:02:10.530 2 1
19 2006-08-27 03:03:38.467 2 3
24 2006-08-27 08:04:54.030 2 2
28 2006-08-27 13:06:13.060 2 2
28 2006-08-27 18:07:31.357 2 1
19 2006-08-25 18:06:37.623 2 3
18 2006-08-25 23:07:52.827 2 2
17 2006-08-26 03:02:37.903 2 1
16 2006-08-26 08:03:57.560 2 1
20 2006-08-26 13:05:13.450 2 1

any ideas? thx a bunch!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-27 : 23:33:43

Post your table structure, some sample data and the query that you used.

panthagani
Yak Posting Veteran

58 Posts

Posted - 2006-08-28 : 03:08:05

Okay, here is a pointer on how I went about it

declare @RankDet table
(Rank int,
Hdate datetime)
insert into @RankDet
select 27, '2006-08-23 17:52:05.607' union all
select 25, '2006-08-23 18:36:26.920' union all
select 23, '2006-08-23 23:37:42.013' union all
select 19, '2006-08-24 03:02:41.373' union all
select 19, '2006-08-24 03:04:07.577' union all
select 19, '2006-08-24 08:05:22.233' union all
select 21, '2006-08-24 13:06:36.123'

select R1.Rank,R1.Hdate,(R2.Rank-R1.Rank) as [RankDiff] from @RankDet R1 INNER JOIN @RankDet R2 on
R2.Rank < R1.Rank
where R2.Hdate in
(select min(R2.Hdate)from @RankDet R2 where Hdate >
(select min(R2.Hdate) from @RankDet R2))

/* 7 row(s) affected)

Rank Hdate RankDiff
----------- ------------------------------------------------------ -----------
27 2006-08-23 17:52:05.607 -2

(1 row(s) affected)*/

The above query gives you the first record of your result set. Need to put in the inner "min" query as a correlated subquery, I think.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-28 : 03:12:57

SnSQL Query should work fine with your requirement....


Declare @var Table 
( Rank int,HonorDate smalldatetime,honortypeid varchar(10) )


Insert @var
Select '27' Rank ,'2006-08-23 17:52:05.607' honorDate,'2' honorTypeID  Union All 
Select '25','2006-08-23 18:36:26.920','2'Union All 
Select '23','2006-08-23 23:37:42.013','2'Union All
Select '19','2006-08-24 03:02:41.373','2'Union All
Select '19','2006-08-24 03:04:07.577','2'Union All
Select '19','2006-08-24 08:05:22.233','2'Union All
Select '21','2006-08-24 13:06:36.123','2'Union All
Select '23','2006-08-24 18:07:54.873','2'Union All
Select '18','2006-08-24 23:09:09.217','2'Union All
Select '21','2006-08-25 03:02:43.717','2'Union All
Select '19','2006-08-25 08:04:01.467','2'Union All
Select '23','2006-08-25 3:05:19.467','2'

Select *, 
isnull((SELECT H1.rank - H.rank FROM @var H1 WHERE 
H1.honordate = (SELECT min(H2.honordate) FROM @var H2 
WHERE H2.honorDate > H.honorDate)), 0) as RankChange  From @var H

Chirag

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-08-28 : 17:14:52

Mike, I noticed that you are ordering by [Date], but showing honorDate in your query. The expression I gave you uses honorDate because I don't know the structure of your table, but you may have to replace honorDate in the expression with [Date] to get the comparison to the previous row correctly.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-08-30 : 00:27:50

Hey Guys,

I haven't been able to get this to work with the right results. I am only getting positive values for the "rankChange" column, and they are incorrect.

If I can post anything else please let me know ..

Any help is much appreciated.

Thanks again,
Mike123

Here is my data and SPROCS

sample data of tblHonors

49 100 48 2 2006-08-23 17:52:05.733
345 100 47 2 2006-08-23 18:36:27.310
442 100 45 2 2006-08-23 23:37:42.187
633 100 38 2 2006-08-24 03:02:45.373
732 100 38 2 2006-08-24 03:04:07.700
930 100 38 2 2006-08-24 08:05:22.360
1130 100 40 2 2006-08-24 13:06:36.233

sample data of userdetails

100 bob

CREATE TABLE [dbo].[tblUserDetails] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[NameOnline] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblHonors] (
[honorID] [int] IDENTITY (1, 1) NOT NULL ,
[userID] [int] NOT NULL ,
[rank] [smallint] NOT NULL ,
[honorTypeID] [tinyint] NOT NULL ,
[honorDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE PROCEDURE dbo.select_honors
(
@userID int
)
AS SET NOCOUNT ON

SELECT rank, honorDate, honorTypeID,

isnull((SELECT TOP 1 H1.rank - H.rank FROM tblHonors H1 WHERE H1.honordate = (SELECT min(H2.honordate) FROM tblHonors H2 WHERE H2.honorDate > H.honorDate)), 0) as rankChange

FROM tblHonors H

JOIN tblUserDetails UD on UD.userID = H.userID

WHERE h.userID = @userID ORDER BY honordate desc

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 06:15:04

[code]
select r.*,
(select t.rank from @rankdet t where hdate = (select min(a.hdate) from @rankdet a where a.hdate > r.hdate)) - r.rank
from @rankdet r
order by r.hdate[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 06:35:28

quote:
Originally posted by mike123

I am only getting positive values for the "rankChange" column, and they are incorrect.

sample data of tblHonors
49 100 48 2 2006-08-23 17:52:05.733
345 100 47 2 2006-08-23 18:36:27.310
442 100 45 2 2006-08-23 23:37:42.187
633 100 38 2 2006-08-24 03:02:45.373
732 100 38 2 2006-08-24 03:04:07.700
930 100 38 2 2006-08-24 08:05:22.360
1130 100 40 2 2006-08-24 13:06:36.233

Why is that incorrect? The data is sorted and coincidentally, the Ranks are ascending.

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-08-30 : 12:33:03

Hi Peter,

This data is incorrect becuase the "rankChange" column is not right.
It is "2" across every row, however the rank value does not change by 2 for every row. The values should be

49 100 48 2 (should be -1) 2006-08-23 17:52:05
345 100 47 2 (should be -2) 2006-08-23 18:36:27
442 100 45 2 (should be -7) 2006-08-23 23:37:42
633 100 38 2 (should be 0) 2006-08-24 03:02:45
732 100 38 2 (should be 0) 2006-08-24 03:04:07
930 100 38 2 (should be 0) 2006-08-24 08:05:22
1130 100 40 2 (should be 0) 2006-08-24 13:06:36

Does this make sense to you?

Thanks again !:)

Mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 13:16:29

So what is wrog with the query I posted earlier?

declare	@rankdet table (rank int, hdate datetime)

insert	@rankdet
select	49, '2006-08-23 17:52:05' union all
select	345, '2006-08-23 18:36:27' union all
select	442, '2006-08-23 23:37:42' union all
select	633, '2006-08-24 03:02:45' union all
select	732, '2006-08-24 03:04:07' union all
select	930, '2006-08-24 08:05:22' union all
select	1130, '2006-08-24 13:06:36'

select		r.rank,
		(select t.rank from @rankdet t where hdate = (select min(a.hdate) from @rankdet a where a.hdate > r.hdate)) - r.rank RankChange,
		r.hdate
from		@rankdet r
order by 	r.hdate

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-08-30 : 20:26:36

Hi Peter,

So I misread your post. I thought you were quoting an older reply but I did not see you were quoting a newer post.

Your query is perfect and returns the correct data with what you set up. I just integrated finished integrating it into my query

Thanks again:)
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources