Dateproblem - 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

General SQL Server Forums

New to SQL Server Programming

Dateproblem

Author

Topic

kfluffie
Posting Yak Master

103 Posts

Posted - 2008-11-14 : 12:17:58

Hello,
I have a database from an errand system.

The (nescessary) columns are (after my INNER JOIN's):
IncidentID (Primary Key towards another table)
CreatedOn (Datefield, when was the errand created)
Status (New, In Progress etc.)
ActionOn (When was it change TO this state)

What I want is how long how the different errand been 'In Progress' before they have gone into any other status.

The different status are:
New
In Progress
Waiting
Resolved
Completed
Closed

I can't figure out how to do it. It would be easier if I hade another column names 'ChangedFrom' or something similar when the status was changed FROM 'In Progress' to something other, but that I can't effect.

I might be able to do a FOR-loop which looks after the next date and validates it towards the status.

Do you have any other, easier ideas?

Best Regards,
KF

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 12:20:34

does it have any audit tables containing history information?

kfluffie
Posting Yak Master

103 Posts

Posted - 2008-11-14 : 12:47:46

quote:
Originally posted by visakh16

does it have any audit tables containing history information?

Hello,
I can show you the current query that we use when we just retrieve history:

SELECT 
	case when INS.CreatedOn = IncidentHistory.ModifiedOn 
		then CAST(INS.IncidentID AS VARCHAR) 
		else '' END Incident_ID,    
	case when INS.CreatedOn = IncidentHistory.ModifiedOn 
		then CAST(Month(INS.CreatedOn) AS VARCHAR) 
		else '' END CreatedMonth,    
	case when INS.CreatedOn = IncidentHistory.ModifiedOn 
		then convert(varchar, INS.CreatedOn, 120) 
		else '' END CreatedOn,            
	USR.FirstName + ' ' + USR.LastName ServiceUser,  
	BU.BusinessUnitName,           
	case when INS.CreatedOn = IncidentHistory.ModifiedOn 
		then INS.Title 
		else '' END Title,            
	case when IncidentHistory.Description IS NULL 
		then '' 
		else IncidentHistory.Description END HistoryDesc,            
	INSTAT.IncidentStatusName Status, 
	PRI.PriorityName,  
	CAT.IncidentCategoryName Category,      
	case when CLOSECAT.ClosureClassificationName IS NOT NULL 
		then CLOSECAT.ClosureClassificationName 
		else '' END ClosureClass,      
	ASSUSER.UserName Assigned,  
	IncidentHistory.ModifiedOn ActionOn,           
	case when IncidentHistory.Resolution IS NULL 
		then '' 
		else IncidentHistory.Resolution END HistoryResolution          
FROM   
Incident INS  
INNER JOIN Users ASSUSER ON ASSUSER.UserID = INS.AssigneeID 
INNER JOIN BusinessUnit BU ON BU.BusinessUnitID = INS.BusinessUnitID 
INNER JOIN Priority PRI ON INS.PriorityID = PRI.PriorityID 
INNER JOIN IncidentCategory CAT ON CAT.IncidentCategoryID = INS.IncidentCategoryID 
INNER JOIN Users USR ON USR.UserID = INS.ServiceUserID   
INNER JOIN IncidentHistory ON INS.IncidentID = IncidentHistory.IncidentID 
INNER JOIN IncidentStatus INSTAT ON INSTAT.IncidentStatusID = IncidentHistory.IncidentStatusID   
LEFT OUTER JOIN ClosureClassification CLOSECAT ON CLOSECAT.ClosureClassificationID = INS.ClosureClassificationID

Here you also have the different column names of the tables:
http://pastebin.com/m94f1c38

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 12:53:35

are you using sql 2005?

kfluffie
Posting Yak Master

103 Posts

Posted - 2008-11-14 : 12:57:38

quote:
Originally posted by visakh16

are you using sql 2005?

Yepp!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 13:06:30

[code]SELECT IncidentID,
DATEDIFF(ss,ih.CreatedOn,COALESCE(ih1.CreatedOn,i.CreatedOn)) as TimeDiffInSeconds
FROM IncidentHistory ih
INNER JOIN IncidentStatus is
ON is.IncidentStatusID=ih.IncidentStatusID
OUTER APPLY (SELECT TOP 1 CreatedOn
FROM IncidentHistory
WHERE IncidentID=ih.IncidentID
AND CreatedOn >ih.CreatedOn
AND IncidentStatusID<>ih.IncidentStatusID
ORDER BY CreatedOn)ih1
OUTER APPLY (SELECT CreatedOn
FROM Incident
WHERE IncidentID=ih.IncidentID )i
WHERE ih.IncidentStatusName='In Progress'
[/code]

kfluffie
Posting Yak Master

103 Posts

Posted - 2008-11-17 : 03:55:26

quote:

Originally posted by visakh16

SELECT IncidentID,
DATEDIFF(ss,ih.CreatedOn,COALESCE(ih1.CreatedOn,i.CreatedOn)) as TimeDiffInSeconds
FROM IncidentHistory ih
INNER JOIN IncidentStatus is
ON is.IncidentStatusID=ih.IncidentStatusID
OUTER APPLY (SELECT TOP 1 CreatedOn
             FROM IncidentHistory
             WHERE IncidentID=ih.IncidentID 
             AND CreatedOn >ih.CreatedOn
             AND IncidentStatusID<>ih.IncidentStatusID
             ORDER BY CreatedOn)ih1
OUTER APPLY (SELECT CreatedOn
             FROM Incident
             WHERE IncidentID=ih.IncidentID )i
WHERE ih.IncidentStatusName='In Progress'

Hello Visakh16,
thank you very much for your assistance.

Your query worked like a charm and I have now read about the 'OUTER APPLY' operator, which was a new operator for me.

Here is a good article about this:
http://iablog.sybase.com/paulley/2008/07/cross-and-outer-apply/

Also, if I would like to do this for SQL 2000, what is the most appropiate way to do that, as I understand CROSS and OUTER APPLY is uniquely for 2005?

Best Regards,
KF

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 04:01:55

In sql 2000. you need to use corelated subqueries for this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 04:05:33

something like

SELECT IncidentID,
DATEDIFF(ss,ih.CreatedOn,COALESCE((SELECT TOP 1 CreatedOn
             FROM IncidentHistory
             WHERE IncidentID=ih.IncidentID 
             AND CreatedOn >ih.CreatedOn
             AND IncidentStatusID<>ih.IncidentStatusID
             ORDER BY CreatedOn),i.CreatedOn)) as TimeDiffInSeconds
FROM IncidentHistory ih
INNER JOIN IncidentStatus is
ON is.IncidentStatusID=ih.IncidentStatusID
INNER JOIN Incident i
ON i.IncidentID=ih.IncidentID 
WHERE ih.IncidentStatusName='In Progress'

kfluffie
Posting Yak Master

103 Posts

Posted - 2008-11-17 : 04:25:03

quote:

Originally posted by visakh16

something like

SELECT IncidentID,
DATEDIFF(ss,ih.CreatedOn,COALESCE((SELECT TOP 1 CreatedOn
             FROM IncidentHistory
             WHERE IncidentID=ih.IncidentID 
             AND CreatedOn >ih.CreatedOn
             AND IncidentStatusID<>ih.IncidentStatusID
             ORDER BY CreatedOn),i.CreatedOn)) as TimeDiffInSeconds
FROM IncidentHistory ih
INNER JOIN IncidentStatus is
ON is.IncidentStatusID=ih.IncidentStatusID
INNER JOIN Incident i
ON i.IncidentID=ih.IncidentID 
WHERE ih.IncidentStatusName='In Progress'

Hello again Visakh16,
I did some changes in the column names, and after that I got the exact output that I wanted.

You are very good at this and almost pinpointed the query out exactly without having the database in front of you.

Thanks for your help and now I am a little more experienced in T-SQL!

Best Regards,
KF

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 04:34:12

Thanks Kfluffie
Glad that i could help you out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources