Rank Latest Value from Two Columns

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 2008 Forums

Transact-SQL (2008)

Rank Latest Value from Two Columns

Author

Topic

secuchalan
Starting Member

19 Posts

Posted - 2011-09-09 : 03:14:22

I have one main ticket table and an owner history table that I am joining in order to identify the last owner and last owner group that worked on a ticket.

Our system only allows a ticket to be assigned to an owner or a group at a given time, never both.
I want to see two columns per ticket - one for last owner group and the other for last owner, all in one row.

If a ticket was never assigned to a **group OR an owner** I want it to return NULL in the corresponding column.

The query I am using to pull (for example) these 3 particular tickets uses rank and partition to group the records by their ticket ID then date.
However, I am stuck as to how I can only make it return the latest record for both the owner group and owner and ignore nulls only if it was assigned.
See Ticket ID 1417, it was never assigned to a group but was assigned to an owner, I still want it to return NULL in the Owner Group column and LES in the Owner column.

Any kind of help will be appreciated.

Thanks.


***TICKET HISTORY RECORDS***

ticketid             owndate                    ownergroup        owner              OwnerName          
-------------------- ----------------------- ----------------- --------------   ---------------------  
1409              2008-12-26 16:46:23.507 	SD                NULL                NULL                  
1409              2008-12-26 16:46:10.907 	CS                NULL                NULL                    
1409              2008-12-26 16:45:59.393 	SD                NULL                NULL                   
1409              2008-12-07 18:36:29.277 	SE                NULL                NULL                   
1409              2008-12-07 18:31:45.640 	NULL              VAS                 Smith, Valerie        
1409              2008-12-07 18:31:24.253 	SE                NULL                NULL                  
1409              2008-12-07 18:21:41.447 	NULL              VAS                 Smith, Valerie        
1411              2008-12-28 16:46:23.507 	SD                NULL                NULL                  
1411              2008-12-25 16:46:10.907 	SE                NULL                NULL                   
1417              2008-12-29 16:46:23.507 	NULL              LES                 Morris, Lance         
1417              2008-12-25 16:46:10.907 	NULL 		  NULL                NULL


****RANK QUERY FILTER FOR RANK = 1****
SELECT	OHR.*
FROM	ticket as T
LEFT OUTER JOIN 
	(SELECT	ownerhistory.ticketid
	, ownerhistory.owndate
	, ownerhistory.ownergroup
	, ownerhistory.owner
	, person.displayname AS OwnerName
	, RANK() OVER (PARTITION BY ticketid ORDER BY owndate DESC) AS OHRank
	FROM ownerhistory
	LEFT OUTER JOIN	person 
	ON	ownerhistory.owner = person.personid
	) AS OHR	
	ON T.ticketid = OHR.ticketid
WHERE OHR.ticketid = '1409'
OR OHR.ticketid = '1411'
OR OHR.ticketid = '1417'
AND OHR.OHRank = 1
ORDER BY OHR.ticketid


****INCORRECT QUERY RESULT****

ticketid             owndate                    ownergroup        owner              OwnerName            OHRank
-------------------- ----------------------- ----------------- --------------   ---------------------  ------------------- 
1409              2008-12-26 16:46:23.507 	SD                NULL                NULL                  1
1411              2008-12-28 16:46:23.507 	SD                NULL                NULL                  1
1417              2008-12-29 16:46:23.507 	NULL              LES                 Morris, Lance         1


****DESIRED QUERY RESULT****

ticketid               ownergroup                 ownername       
-------------------- ----------------------- ---------------------
1409			SD			Smith, Valerie 
1411			SD			NULL
1417			NULL			Morris, Lance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 03:31:32

[code]select h.ticketid,h1.ownergroup,h2.ownername
from (select distinct ticketid from tickethistory) h
outer apply (select top 1 ownergroup
from tickethistory
where ownergroup is not null
and ticketid = h.ticketid
order by owndate desc)h1
outer apply (select top 1 ownername
from tickethistory
where ownername is not null
and ticketid = h.ticketid
order by owndate desc)h2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

secuchalan
Starting Member

19 Posts

Posted - 2011-09-12 : 13:46:34

That worked, thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 14:19:09

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources