Row_number function to return multiple rows

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_number function to return multiple rows

Author

Topic

mmunson
Starting Member

10 Posts

Posted - 2009-05-18 : 14:04:41

Hello,

I'm a bit of a sql newbie and I was wondering if I could please get some help.

My original script below, works well except in my result set I get Orders with multiple 'staus' lines and what I wanted was only the status line with the MAX date/time.

use servicedw
SELECT CRM_StatusCodes.TRANSACTION_ID as [Service Order ID]
, CRM_Partners.PARTNER
, CRM_Partners.PARTNER_FCT
, CRM_Partners.DESCRIPTION [Function]
, CRM_StatusCodes.SYSTEM_STATUS as [Confirmation Status]
, CRM_Partners.ADDRESS
, CRM_PartsLabor.DESCRIPTION
, CRM_orders.created_at as [Back Log Date]
, crm_statuscodes.end_date as [LastStatus]
, count(CRM_StatusCodes.TRANSACTION_ID) as [Service Order Count] /* this displays all transactions associated with the FSE */
FROM CRM_Partners
INNER JOIN CRM_Orders ON CRM_Partners.PARTNER_ID = CRM_Orders.PARTNER_ID
INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_PartsLabor ON CRM_Orders.TRANSACTION_ID = CRM_PartsLabor.TRANSACTION_ID

WHERE
CRM_StatusCodes.SYSTEM_STATUS in
('Distribution incorrect','Planned','Incomplete: General','Transferred to bill. document',
'Distribution lock','Lease Fully Activated','Printed','Distributed','Released','Cancelled internally',
'Confirmed','To be distributed','Rejected','Document from OLTP','Billing Cancelled','Contains Errors',
'In Process','Open')
AND CRM_Partners.PARTNER_FCT IN ('ZRSM', '00000052', '00000056')
AND CRM_PartsLabor.ordered_prod IN ('FSE_LABOR')
AND CRM_StatusCodes.TRANSACTION_ID not like ('9%')
AND cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) >=dateadd(day,-5,getdate())

GROUP BY
CRM_StatusCodes.TRANSACTION_ID
, CRM_Partners.ADDRESS, CRM_Partners.PARTNER
, CRM_Partners.PARTNER_FCT
, CRM_Partners.DESCRIPTION
, CRM_StatusCodes.SYSTEM_STATUS
, CRM_orders.created_at
, CRM_PartsLabor.DESCRIPTION
, crm_statuscodes.end_date

Result Set:
Service Order LastStatus ADDRESS ConfirmationStatus
3000004782 2009-05-15 10:25:32.000 Fever A. Confirmed
3000004782 2009-05-15 10:25:32.000 Matteo A. Confirmed
3000004782 2009-05-15 10:25:32.000 T - SEFIFy Confirmed
3000004785 2009-05-14 16:17:47.000 Edward A. Confirmed
3000004785 2009-05-14 16:17:47.000 Randy A. Confirmed
3000004785 2009-05-14 16:17:47.000 T - CSAWZR Confirmed
3000004786 2009-05-14 00:32:41.000 James A. Confirmed
3000004786 2009-05-14 00:32:00.000 James A. Released
3000004786 2009-05-14 00:32:41.000 Robert A. Confirmed
3000004786 2009-05-14 00:32:00.000 Robert A. Released
3000004786 2009-05-14 00:32:41.000 T - CSA Confirmed
3000004786 2009-05-14 00:32:00.000 T - CSA5 Released

I created a new script using the row_number () function to get the first row, and this works swell too but it doesn't give me the exta data lines I need.

;with
CTEMain as
(SELECT CRM_StatusCodes.TRANSACTION_ID as [Service Order ID]
, CRM_Partners.PARTNER
, CRM_Partners.PARTNER_FCT
, CRM_Partners.DESCRIPTION as [Function]
, CRM_StatusCodes.SYSTEM_STATUS as [Confirmation Status]
, CRM_Partners.ADDRESS
, CRM_PartsLabor.DESCRIPTION
, CRM_orders.created_at as [Back Log Date]
, crm_statuscodes.end_date as [LastStatus]
, count(CRM_StatusCodes.TRANSACTION_ID) as [Service Order Count] /* this displays all transactions associated with the FSE */

FROM
CRM_Partners
INNER JOIN CRM_Orders ON CRM_Partners.PARTNER_ID = CRM_Orders.PARTNER_ID
INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_PartsLabor ON CRM_Orders.TRANSACTION_ID = CRM_PartsLabor.TRANSACTION_ID

WHERE
CRM_StatusCodes.SYSTEM_STATUS in
('Distribution incorrect','Planned','Incomplete: General','Transferred to bill. document',
'Distribution lock','Lease Fully Activated','Printed','Distributed','Released','Cancelled internally',
'Confirmed','To be distributed','Rejected','Document from OLTP','Billing Cancelled','Contains Errors',
'In Process','Open')
AND CRM_Partners.PARTNER_FCT IN ('ZRSM', '00000052', '00000056')
AND CRM_PartsLabor.ordered_prod IN ('FSE_LABOR')
AND CRM_StatusCodes.TRANSACTION_ID not like ('9%')
AND cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) >=dateadd(day,-5,getdate())
GROUP BY
CRM_StatusCodes.TRANSACTION_ID
, CRM_Partners.ADDRESS
, CRM_Partners.PARTNER
, CRM_Partners.PARTNER_FCT
, CRM_Partners.DESCRIPTION
, CRM_StatusCodes.SYSTEM_STATUS
, CRM_orders.created_at
, CRM_PartsLabor.DESCRIPTION
, crm_statuscodes.end_date),
CTE2 as
(select *,
row_number() over (partition by [Service Order ID] order by [LastStatus]) as Row
from CTEMain)
select *
from CTE2
where Row = 1;

Result Set:
Service Order ID LastStatus ADDRESS Confirmation Status
3000004782 2009-05-15 10:25:32.000 Fever A. Confirmed
3000004785 2009-05-14 16:17:47.000 Edward A. Confirmed
3000004786 2009-05-14 00:32:00.000 James A Released

With the first line '3000004782' I needed it to include,
3000004782 2009-05-15 10:25:32.000 Fever A. Confirmed
3000004782 2009-05-15 10:25:32.000 Matteo A. Confirmed

but not include the line if the ADDRESS (name field) is duplicated. I changed the row_number line to:
row_number() over (partition by [Function] order by [LastStatus]) as Row

and this is what I got which is almost perfect, but I need this for every line.
Service Order LastStatus ADDRESS Confirmation Status
3000004802 2009-05-13 14:07:06.000 Do A Released
3000004802 2009-05-13 14:07:06.000 Jeffrey A Released
3000004802 2009-05-13 14:07:06.000 T - CSAEZR6 Released

This is the result set I have with my other fields my script. ( I didn't include it above for the sake of trying to be neat:)

PARTNER PARTNER_FCT Function DESCRIPTION Back Log Date Service Order Count
0004000139 00000056 Service Employee Group FSE Labor 20090514212513 2
0050000717 00000052 Exec. Service Employee FSE Labor 20090514215309 1
0050000717 00000052 Exec. Service Employee FSE Labor 20090514215309 1

I hope I explained my dilemma clearly and concisely.

Thank you in advance for any advice you can provide. (Since I'm a newbie I need things kinda' spelled out for me :he he:)

Michelle

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-18 : 14:27:01

My head hurts today, so I'm not following you, but does this get what you want?

 row_number() over (partition by [Service Order ID], [Address] order by [LastStatus]) as Row

You might want to use Books Online to help better understand the ROW_NUMBER function that was provided to you on SQL Server Central.

mmunson
Starting Member

10 Posts

Posted - 2009-05-18 : 15:53:39

Oh Duh! Thanks Lamprey... Why didn't I think of that?! That was easy.

Michelle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources