Row Numbering - 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 2005 Forums

Transact-SQL (2005)

Row Numbering

Author

Topic

jpulizzo
Starting Member

4 Posts

Posted - 2008-05-30 : 09:16:41

Hello,

I am having trouble getting the results I am looking for from this query:

select ROW_NUMBER() OVER(PARTITION BY TransTime, DriverNo ORDER BY TransTime) as CustCount
, ROW_NUMBER() OVER(PARTITION BY Left(ToName,2), TransTime, DriverNo ORDER BY TransTime) As DelCount
, DriverNo
, Convert(VarChar(8), TransTime, 108) As TransTime
, Left(ToName,2) As AirPortCode
from transactions

The results I am trying to get would be like this:

CustCount DelCount DriverNo TransTime AirPortCode
-------------------- -------------------- -------- --------- -----------
1 1 042000 04:00:00 NW
1 2 042000 04:00:00 NW
1 1 042000 06:51:00 AA
1 1 042000 08:45:00 NW
1 1 042000 08:52:00 NW
1 1 042000 10:30:00 NW
1 1 042000 10:45:00 NW
2 1 042000 10:45:00 AA
1 1 042000 11:30:00 NW
1 2 042000 11:30:00 NW

But the closest I have gotten is:

CustCount DelCount DriverNo TransTime AirPortCode
-------------------- -------------------- -------- --------- -----------
1 1 042000 04:00:00 NW
2 2 042000 04:00:00 NW
1 1 042000 06:51:00 AA
1 1 042000 08:45:00 NW
1 1 042000 08:52:00 NW
1 1 042000 10:30:00 NW
1 1 042000 10:45:00 NW
2 1 042000 10:45:00 AA
1 1 042000 11:30:00 NW
2 2 042000 11:30:00 NW

The CustCount field should read 1 if there is only one unique AirPortCode during the TransTime with the specific DriverNo, but the query is counting both rows during that transtime. Any ideas?

Thanks.

raja_saminathan
Starting Member

12 Posts

Posted - 2008-05-30 : 09:49:32

Hi,

Can You Provide Table Schema along with Sample Datas

The Values of some rows you shown here are identical except the Row_number columns,so we need exact table schema

Rajesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 10:32:54

[code]select ROW_NUMBER() OVER(PARTITION BY TransTime, DriverNo,DelCount ORDER BY TransTime) as CustCount,*
FROM
(SELECT
ROW_NUMBER() OVER(PARTITION BY Left(ToName,2), TransTime, DriverNo ORDER BY TransTime) As DelCount
, DriverNo
, Convert(VarChar(8), TransTime, 108) As TransTime
, Left(ToName,2) As AirPortCode
from transactions)t[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-30 : 10:38:41

See if this works

select ROW_NUMBER() OVER(PARTITION BY delcount,TransTime, DriverNo ORDER BY TransTime) as CustCount,*
from
(
select ROW_NUMBER() OVER(PARTITION BY Left(ToName,2), TransTime, DriverNo ORDER BY TransTime) As DelCount
, DriverNo
, Convert(VarChar(8), TransTime, 108) As TransTime
, Left(ToName,2) As AirPortCode
from transactions
) as t

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-30 : 10:40:05

quote:

Originally posted by visakh16

select ROW_NUMBER() OVER(PARTITION BY TransTime, DriverNo,DelCount ORDER BY TransTime) as CustCount,*
FROM
(SELECT
 ROW_NUMBER() OVER(PARTITION BY Left(ToName,2), TransTime, DriverNo ORDER BY TransTime) As DelCount
, DriverNo
, Convert(VarChar(8), TransTime, 108) As TransTime
, Left(ToName,2) As AirPortCode
from transactions)t

You are very fast vishak

Madhivanan

Failing to plan is Planning to fail

jpulizzo
Starting Member

4 Posts

Posted - 2008-05-30 : 10:52:24

That works perfect!!! I thank both of you for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 12:37:17

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

select ROW_NUMBER() OVER(PARTITION BY TransTime, DriverNo,DelCount ORDER BY TransTime) as CustCount,*
FROM
(SELECT
ROW_NUMBER() OVER(PARTITION BY Left(ToName,2), TransTime, DriverNo ORDER BY TransTime) As DelCount
, DriverNo
, Convert(VarChar(8), TransTime, 108) As TransTime
, Left(ToName,2) As AirPortCode
from transactions)t

You are very fast vishak

Madhivanan

Failing to plan is Planning to fail

May be your refresh button is slow Madhi

jpulizzo
Starting Member

4 Posts

Posted - 2008-06-02 : 09:13:30

Well, the query seemed liked it was working. I ran it for three different days and the results were exactly what I was looking for, but now I am getting some strange returns like this:

CustCount DelCount DriverNo TransTime AirPortCode
-------------------- -------------------- -------- --------- -----------
1 1 042004 00:30:00 NW
1 1 042004 06:30:00 NW
1 1 000099 09:00:00 DL
1 1 042000 10:00:00 NW
1 1 042004 10:00:00 NW
1 1 042003 10:35:00 CO
1 1 042000 11:00:00 NW
1 2 042000 11:00:00 NW
1 1 042004 11:00:00 NW
1 1 042003 11:30:00 NW
2 2 042003 11:30:00 NW
2 1 042003 11:30:00 UA
1 2 042003 11:30:00 UA
1 3 042003 11:30:00 UA
1 1 042003 12:00:00 AA
2 1 042003 12:00:00 NW
1 2 042003 12:00:00 NW
1 3 042003 12:00:00 NW
1 4 042003 12:00:00 NW
1 5 042003 12:00:00 NW
1 6 042003 12:00:00 NW
1 7 042003 12:00:00 NW
1 1 042003 12:30:00 NW
1 2 042003 12:30:00 NW
1 1 042004 12:45:00 NW
2 1 042003 14:00:00 AA
1 2 042003 14:00:00 AA
1 1 042003 14:00:00 NW
1 1 042004 14:00:00 NW

The data should look like this:

CustCount DelCount DriverNo TransTime AirPortCode
-------------------- -------------------- -------- --------- -----------
1 1 042004 00:30:00 NW
1 1 042004 06:30:00 NW
1 1 000099 09:00:00 DL
1 1 042000 10:00:00 NW
1 1 042004 10:00:00 NW
1 1 042003 10:35:00 CO
1 1 042000 11:00:00 NW
1 2 042000 11:00:00 NW
1 1 042004 11:00:00 NW
1 1 042003 11:30:00 NW
1 2 042003 11:30:00 NW
2 1 042003 11:30:00 UA
2 2 042003 11:30:00 UA
2 3 042003 11:30:00 UA
1 1 042003 12:00:00 AA
2 1 042003 12:00:00 NW
2 2 042003 12:00:00 NW
2 3 042003 12:00:00 NW
2 4 042003 12:00:00 NW
2 5 042003 12:00:00 NW
2 6 042003 12:00:00 NW
2 7 042003 12:00:00 NW
1 1 042003 12:30:00 NW
1 2 042003 12:30:00 NW
1 1 042004 12:45:00 NW
1 1 042003 14:00:00 AA
1 2 042003 14:00:00 AA
2 1 042003 14:00:00 NW
1 1 042004 14:00:00 NW

The problem seems to occur when the DelCount is greater than one for each AirPortCode during a transtime belonging to a DriverNo. I have had no luck resolving the issue. Any ideas?

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-02 : 09:26:24

Don't get hung up about ROW_NUMBER() function. There is DENSE_RANK() function too.

DECLARE	@Sample TABLE
	(
		DriverNo CHAR(6),
		TransTime CHAR(8),
		AirPortCode CHAR(2)
	)

INSERT	@Sample
SELECT	'042004', '00:30:00', 'NW' UNION ALL
SELECT	'042004', '06:30:00', 'NW' UNION ALL
SELECT	'000099', '09:00:00', 'DL' UNION ALL
SELECT	'042000', '10:00:00', 'NW' UNION ALL
SELECT	'042004', '10:00:00', 'NW' UNION ALL
SELECT	'042003', '10:35:00', 'CO' UNION ALL
SELECT	'042000', '11:00:00', 'NW' UNION ALL
SELECT	'042000', '11:00:00', 'NW' UNION ALL
SELECT	'042004', '11:00:00', 'NW' UNION ALL
SELECT	'042003', '11:30:00', 'NW' UNION ALL
SELECT	'042003', '11:30:00', 'NW' UNION ALL
SELECT	'042003', '11:30:00', 'UA' UNION ALL
SELECT	'042003', '11:30:00', 'UA' UNION ALL
SELECT	'042003', '11:30:00', 'UA' UNION ALL
SELECT	'042003', '12:00:00', 'AA' UNION ALL
SELECT	'042003', '12:00:00', 'NW' UNION ALL
SELECT	'042003', '12:00:00', 'NW' UNION ALL
SELECT	'042003', '12:00:00', 'NW' UNION ALL
SELECT	'042003', '12:00:00', 'NW' UNION ALL
SELECT	'042003', '12:00:00', 'NW' UNION ALL
SELECT	'042003', '12:00:00', 'NW' UNION ALL
SELECT	'042003', '12:00:00', 'NW' UNION ALL
SELECT	'042003', '12:30:00', 'NW' UNION ALL
SELECT	'042003', '12:30:00', 'NW' UNION ALL
SELECT	'042004', '12:45:00', 'NW' UNION ALL
SELECT	'042003', '14:00:00', 'AA' UNION ALL
SELECT	'042003', '14:00:00', 'AA' UNION ALL
SELECT	'042003', '14:00:00', 'NW' UNION ALL
SELECT	'042004', '14:00:00', 'NW'

SELECT		*,
		ROW_NUMBER() OVER (PARTITION BY AirportCode, DriverNo, TransTime ORDER BY TransTime) AS DelCount,
		DENSE_RANK() OVER (PARTITION BY DriverNo, TransTime ORDER BY AirportCode) AS CustCount
FROM		@Sample
ORDER BY	DriverNo,
		TransTime

E 12°55'05.25"
N 56°04'39.16"

jpulizzo
Starting Member

4 Posts

Posted - 2008-06-02 : 10:24:44

Point taken. Thanks for your help Peso!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources