Query Help for Distinct/Group By

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)

Query Help for Distinct/Group By

Author

Topic

deadtrees
Starting Member

26 Posts

Posted - 2012-02-23 : 14:58:14

I've got a complex query (for me anyway) in which I'm trying to track cases through their lifecycle and count the time between them.

The English description and relationships are as follows.

Each case can have multiple requests (1 to Many), multiple tasks (1 to Many), and Multiple Examination events (Exam and Verify) (1 to Many). All tasks, and Examination events are tagged with the request with which they are associated.

Cases are either assigned if they are new or revisited if they are already created. I'd like to take a case and for each request in the case, I'd like to find the most recent end date if there are multiple 'assigned' tasks per request id OR most recent Revisit EndDate if it exists . I'd then like to link that Assigned or Revisit Event to it's associated Examination event (consisting of an Examination and a Verification)

Can you help?

Select    RQ.RequestID
		,CD.[Case Number]
		,E3.[First Name]+' '+ISNULL(E3.[Last Name],'') as [Owner]
		,CD.[Status]
		,CD.Reason
		,RQ.DateOpen
		,RT.[Type]
		,RQ.DateClosed
		,TK.RequestNumber
		,TK.TaskName
		,MAX(TK.[End Date]) as ENDDATE
		,DATEDIFF("D",RQ.DateOpen,Tk.[End Date]) as TTASSIGN
		,EX.RequestNumber
		,E1.[Last Name] as EXAMINER
		,EX.ExamStart
		,ISNULL(DATEDIFF("D",RQ.DateOpen,EX.ExamStart), 12345) as TTEXAM
		,EX.ExamEnd
		,ISNULL(DATEDIFF("D",EX.ExamStart,EX.ExamEnd),12345) as [EXAM-TTC]
		,E2.[Last Name] as VERIFIER
		,EX.VerifyStart
		,ISNULL(DATEDIFF("D",RQ.DateOpen,EX.VerifyStart),12345) as TTVERIFY
		,EX.VerifyEnd	
		,ISNULL(DATEDIFF("D",EX.VerifyStart,EX.VerifyEnd),12345) as [VERIFY-TTC]
		,ISNULL(DATEDIFF("D",RQ.DateOpen,EX.VerifyEnd),12345) as [CaseTTC]			
FROM LIMS_DATA_Production.dbo.Requests RQ
INNER
   JOIN LIMS_DATA_Production.dbo.RequestTypes RT
      ON RQ.RequestType = RT.ID
INNER
   JOIN LIMS_DATA_Production.dbo.CaseDetails CD
      ON RQ.CaseID = CD.ID
INNER
   JOIN LIMS_DATA_Production.dbo.Tasks TK
      ON RQ.RequestID = TK.RequestNumber
INNER
   JOIN LIMS_DATA_Production.dbo.Examination EX
      ON TK.RequestNumber = EX.RequestNumber
INNER
   JOIN LIMS_DATA_Production.dbo.Employees E1
      ON EX.Examiner = E1.ID
INNER
   JOIN LIMS_DATA_Production.dbo.Employees E2
      ON EX.Examiner = E2.ID
INNER
   JOIN LIMS_DATA_Production.dbo.Employees E3
      ON CD.[Owner] = E3.ID
WHERE ((TK.TaskName = 'Assigned' OR TK.TaskName ='Revisit') AND NOT TK.TaskName = 'Preval') AND RQ.DateClosed between '1/1/2012' and '1/31/2012' AND RQ.SDelete = 0 AND EX.[Eval Only]=0 AND EX.SDelete = 0
GROUP BY CD.[Case Number]
		,E3.[First Name]
		,E3.[Last Name]
		,CD.[Status]
		,CD.Reason
		,RQ.RequestID
		,RQ.DateOpen
		,RT.[Type]
		,RQ.DateClosed
		,TK.RequestNumber
		,TK.TaskName
		,TK.[End Date]
		,EX.RequestNumber
		,E1.[Last Name]
		,EX.ExamStart
		,EX.ExamEnd
		,E2.[Last Name] 
		,EX.VerifyStart
		,EX.VerifyEnd

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 15:34:42

it would be much better if you can post some data and explain how you want distinct to be pulled out of them

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

deadtrees
Starting Member

26 Posts

Posted - 2012-02-23 : 17:25:32

Here is a case of data returned

RequestID	Case Number	Owner	Status	 Reason	DateOpe     Type	DateClosed	RequestNumber	TaskName	ENDDATE	     TTASSIGN	RequestNumber	EXAMINER	ExamStart	TTEXAM	ExamEnd	     EXAM-TTC	VERIFIER	VerifyStart	TTVERIFY	VerifyEnd	VERIFY-TTC	CaseTTC
10147	       XX11000001	_Admin 	Complete Filed	2011-12-20  Callout	2012-01-27 	10147	        Assigned	2012-01-05 	16	10147	       Smith	2012-01-23 	34	2012-01-23 	0       Jones	2012-01-26 	37		2012-01-26 	0		37
10147	       XX11000001	_Admin 	Complete Filed	2011-12-20  Callout	2012-01-27 	10147	        Assigned	2012-01-12 	23	10147	       Smith	2012-01-23 	34	2012-01-23 	0       Jones	2012-01-26 	37		2012-01-26 	0		37
10147	       XX11000001	_Admin 	Complete Filed	2011-12-20  Callout	2012-01-27 	10147	        Assigned	2012-01-17 	28	10147	       Smith	2012-01-23 	34	2012-01-23 	0	Jones	2012-01-26 	37		2012-01-26 	0		37

This case in actuality has one request (10147) three 'assigned' tasks and one examination event (EX.Examiner = E2.ID should Read EX.Verifier = E2.ID in the join of the original post btw)

So, I want the query to query the tasks table first, For a distinct Request ID I want to see if there is more than one Assigned task : If count >1 take max end date and distinct that row. If count = 0 then count revisits. If revisit count >1 Max end date and distinct that row, if count =0 'there's a problem because this scenario shouldn't exist' so output some text if there's a null here.

Hope that makes sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 18:23:35

which is table corresponding to revisits?

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

deadtrees
Starting Member

26 Posts

Posted - 2012-02-23 : 20:30:00

LIMS_DATA_Production.dbo.Tasks TK is the table TK.TaskName is the field that would have 'assigned' or 'revisit' in it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 21:01:53

so you want recent revisted and assigned tasks corresponding to each Requestid if exists?

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

deadtrees
Starting Member

26 Posts

Posted - 2012-02-23 : 22:34:53

Yes, that's correct

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 23:33:52

[code]
Select RQ.RequestID
,CD.[Case Number]
,E3.[First Name]+' '+ISNULL(E3.[Last Name],'') as [Owner]
,CD.[Status]
,CD.Reason
,RQ.DateOpen
,RT.[Type]
,RQ.DateClosed
,TK.RequestNumber
,TK.TaskName
,MAX(TK.[End Date]) as ENDDATE
,DATEDIFF("D",RQ.DateOpen,Tk.[End Date]) as TTASSIGN
,EX.RequestNumber
,E1.[Last Name] as EXAMINER
,EX.ExamStart
,ISNULL(DATEDIFF("D",RQ.DateOpen,EX.ExamStart), 12345) as TTEXAM
,EX.ExamEnd
,ISNULL(DATEDIFF("D",EX.ExamStart,EX.ExamEnd),12345) as [EXAM-TTC]
,E2.[Last Name] as VERIFIER
,EX.VerifyStart
,ISNULL(DATEDIFF("D",RQ.DateOpen,EX.VerifyStart),12345) as TTVERIFY
,EX.VerifyEnd
,ISNULL(DATEDIFF("D",EX.VerifyStart,EX.VerifyEnd),12345) as [VERIFY-TTC]
,ISNULL(DATEDIFF("D",RQ.DateOpen,EX.VerifyEnd),12345) as [CaseTTC]
FROM LIMS_DATA_Production.dbo.Requests RQ
INNER
JOIN LIMS_DATA_Production.dbo.RequestTypes RT
ON RQ.RequestType = RT.ID
INNER
JOIN LIMS_DATA_Production.dbo.CaseDetails CD
ON RQ.CaseID = CD.ID
INNER
JOIN (SELECT *,ROW_NUMBER() OVER (PARTITION BY RequestNumber,TaskName ORDER BY [End Date] DESC) AS Rn
FROM LIMS_DATA_Production.dbo.Tasks) TK
ON RQ.RequestID = TK.RequestNumber
AND TK.Rn =1
INNER
JOIN LIMS_DATA_Production.dbo.Examination EX
ON TK.RequestNumber = EX.RequestNumber
INNER
JOIN LIMS_DATA_Production.dbo.Employees E1
ON EX.Examiner = E1.ID
INNER
JOIN LIMS_DATA_Production.dbo.Employees E2
ON EX.Examiner = E2.ID
INNER
JOIN LIMS_DATA_Production.dbo.Employees E3
ON CD.[Owner] = E3.ID
WHERE ((TK.TaskName = 'Assigned' OR TK.TaskName ='Revisit') AND NOT TK.TaskName = 'Preval') AND RQ.DateClosed between '1/1/2012' and '1/31/2012' AND RQ.SDelete = 0 AND EX.[Eval Only]=0 AND EX.SDelete = 0
GROUP BY CD.[Case Number]
,E3.[First Name]
,E3.[Last Name]
,CD.[Status]
,CD.Reason
,RQ.RequestID
,RQ.DateOpen
,RT.[Type]
,RQ.DateClosed
,TK.RequestNumber
,TK.TaskName
,TK.[End Date]
,EX.RequestNumber
,E1.[Last Name]
,EX.ExamStart
,EX.ExamEnd
,E2.[Last Name]
,EX.VerifyStart
,EX.VerifyEnd
[/code]

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

deadtrees
Starting Member

26 Posts

Posted - 2012-02-24 : 11:25:38

That worked perfectly, thanks for the syntax lesson. I'm going to read up on those commands you used.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-25 : 13:16:34

ok...you're welcome

read more on ROW_NUMBER() below

http://msdn.microsoft.com/en-us/library/ms186734.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources