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 2000 Forums
 SQL Server Development (2000)
 Stored Procedure or a View??

Author  Topic 

xcas08
Starting Member

13 Posts
Posted - 2007-03-14 : 10:14:55
Hello all I am not quite a beginner but not an expert at SQL. I'm kind of in a bind and need some help. I have a table that shows me statuses of tickets (open, pending, closed), some tickets could have as much as 25 rows/ticket. I want to try to avoid that but at the same time keep track of the time. Here's what I need to happen...

with the data example below I need to take the ((closed date - first open date) - total of Waiting time). This will give me total time duration of the ticket. I'd like to either write a stored procedure or create a view that would do this for me. Any one have ideas?

CallID DateStop TimeStop CallStatus
00216177 2006-01-20 05:39:24 Open
00216177 2006-01-20 05:39:27 Open
00216177 2006-01-20 05:40:13 Open
00216177 2006-01-20 05:40:24 Pending
00216177 2006-02-07 16:05:47 Pending
00216177 2006-02-21 17:26:22 Pending
00216177 2006-02-21 17:29:06 Pending
00216177 2006-02-21 17:29:08 Open
00216177 2006-03-03 16:35:10 Open
00216177 2006-04-05 15:12:26 Open
00216177 2006-04-05 15:17:09 Open
00216177 2006-04-14 14:37:49 Open
00216177 2006-04-14 14:37:54 Awaiting
00216177 2006-04-19 11:20:30 Awaiting
00216177 2006-04-19 12:12:34 Awaiting
00216177 2006-04-19 12:12:37 Awaiting
00216177 2006-04-19 12:12:58 Awaiting
00216177 2006-04-19 12:13:00 Closed

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-03-14 : 10:17:34
Duplicate post: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80575[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-03-14 : 10:25:04
Total time in what?
Seconds? Days? Hours?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-03-14 : 10:26:32
[code]-- Prepare sample data
DECLARE @Sample TABLE (CallID VARCHAR(8), DateStop VARCHAR(10), TimeStop VARCHAR(8), CallStatus VARCHAR(8))

INSERT @Sample
SELECT '00216177', '2006-01-20', '05:39:24', 'Open' UNION ALL
SELECT '00216177', '2006-01-20', '05:39:27', 'Open' UNION ALL
SELECT '00216177', '2006-01-20', '05:40:13', 'Open' UNION ALL
SELECT '00216177', '2006-01-20', '05:40:24', 'Pending' UNION ALL
SELECT '00216177', '2006-02-07', '16:05:47', 'Pending' UNION ALL
SELECT '00216177', '2006-02-21', '17:26:22', 'Pending' UNION ALL
SELECT '00216177', '2006-02-21', '17:29:06', 'Pending' UNION ALL
SELECT '00216177', '2006-02-21', '17:29:08', 'Open' UNION ALL
SELECT '00216177', '2006-03-03', '16:35:10', 'Open' UNION ALL
SELECT '00216177', '2006-04-05', '15:12:26', 'Open' UNION ALL
SELECT '00216177', '2006-04-05', '15:17:09', 'Open' UNION ALL
SELECT '00216177', '2006-04-14', '14:37:49', 'Open' UNION ALL
SELECT '00216177', '2006-04-14', '14:37:54', 'Awaiting' UNION ALL
SELECT '00216177', '2006-04-19', '11:20:30', 'Awaiting' UNION ALL
SELECT '00216177', '2006-04-19', '12:12:34', 'Awaiting' UNION ALL
SELECT '00216177', '2006-04-19', '12:12:37', 'Awaiting' UNION ALL
SELECT '00216177', '2006-04-19', '12:12:58', 'Awaiting' UNION ALL
SELECT '00216177', '2006-04-19', '12:13:00', 'Closed'

-- Show the expected result
SELECT w.CallID,
w.[Open],
w.[Closed],
DATEDIFF(DAY, w.[Open], w.[Closed]) AS Days
FROM (
SELECT x.CallID,
MIN(CASE WHEN x.CallStatus = 'Open' THEN x.[Stop] END) AS [Open],
MAX(CASE WHEN x.CallStatus = 'Closed' THEN x.[Stop] END) AS [Closed]
FROM (
SELECT CallID,
CAST(DateStop + ' ' + TimeStop AS DATETIME) AS [Stop],
CallStatus
FROM @Sample
) AS x
GROUP BY x.CallID
) AS w
ORDER BY w.CallID[/code]

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-03-14 : 10:29:25
If you are not interested in the time, use this
-- Prepare sample data
DECLARE	@Sample TABLE (CallID VARCHAR(8), DateStop VARCHAR(10), TimeStop VARCHAR(8), CallStatus VARCHAR(8))

INSERT	@Sample
SELECT	'00216177', '2006-01-20', '05:39:24', 'Open' UNION ALL
SELECT	'00216177', '2006-01-20', '05:39:27', 'Open' UNION ALL
SELECT	'00216177', '2006-01-20', '05:40:13', 'Open' UNION ALL
SELECT	'00216177', '2006-01-20', '05:40:24', 'Pending' UNION ALL
SELECT	'00216177', '2006-02-07', '16:05:47', 'Pending' UNION ALL
SELECT	'00216177', '2006-02-21', '17:26:22', 'Pending' UNION ALL
SELECT	'00216177', '2006-02-21', '17:29:06', 'Pending' UNION ALL
SELECT	'00216177', '2006-02-21', '17:29:08', 'Open' UNION ALL
SELECT	'00216177', '2006-03-03', '16:35:10', 'Open' UNION ALL
SELECT	'00216177', '2006-04-05', '15:12:26', 'Open' UNION ALL
SELECT	'00216177', '2006-04-05', '15:17:09', 'Open' UNION ALL
SELECT	'00216177', '2006-04-14', '14:37:49', 'Open' UNION ALL
SELECT	'00216177', '2006-04-14', '14:37:54', 'Awaiting' UNION ALL
SELECT	'00216177', '2006-04-19', '11:20:30', 'Awaiting' UNION ALL
SELECT	'00216177', '2006-04-19', '12:12:34', 'Awaiting' UNION ALL
SELECT	'00216177', '2006-04-19', '12:12:37', 'Awaiting' UNION ALL
SELECT	'00216177', '2006-04-19', '12:12:58', 'Awaiting' UNION ALL
SELECT	'00216177', '2006-04-19', '12:13:00', 'Closed'

-- Show the expected result
SELECT		w.CallID,
		w.[Open],
		w.[Closed],
		DATEDIFF(DAY, w.[Open], w.[Closed]) AS Days
FROM		(
			SELECT		CallID,
					MIN(CASE WHEN CallStatus = 'Open' THEN CAST(DateStop AS DATETIME) END) AS [Open],
					MAX(CASE WHEN CallStatus = 'Closed' THEN CAST(DateStop AS DATETIME) END) AS [Closed]
			FROM		@Sample
			GROUP BY	CallID
		) AS w
ORDER BY	w.CallID


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-03-14 : 10:31:05
And if DateStop already is DATETIME (which it really should be)
-- Prepare sample data
DECLARE	@Sample TABLE (CallID VARCHAR(8), DateStop DATETIME, TimeStop VARCHAR(8), CallStatus VARCHAR(8))

INSERT	@Sample
SELECT	'00216177', '2006-01-20', '05:39:24', 'Open' UNION ALL
SELECT	'00216177', '2006-01-20', '05:39:27', 'Open' UNION ALL
SELECT	'00216177', '2006-01-20', '05:40:13', 'Open' UNION ALL
SELECT	'00216177', '2006-01-20', '05:40:24', 'Pending' UNION ALL
SELECT	'00216177', '2006-02-07', '16:05:47', 'Pending' UNION ALL
SELECT	'00216177', '2006-02-21', '17:26:22', 'Pending' UNION ALL
SELECT	'00216177', '2006-02-21', '17:29:06', 'Pending' UNION ALL
SELECT	'00216177', '2006-02-21', '17:29:08', 'Open' UNION ALL
SELECT	'00216177', '2006-03-03', '16:35:10', 'Open' UNION ALL
SELECT	'00216177', '2006-04-05', '15:12:26', 'Open' UNION ALL
SELECT	'00216177', '2006-04-05', '15:17:09', 'Open' UNION ALL
SELECT	'00216177', '2006-04-14', '14:37:49', 'Open' UNION ALL
SELECT	'00216177', '2006-04-14', '14:37:54', 'Awaiting' UNION ALL
SELECT	'00216177', '2006-04-19', '11:20:30', 'Awaiting' UNION ALL
SELECT	'00216177', '2006-04-19', '12:12:34', 'Awaiting' UNION ALL
SELECT	'00216177', '2006-04-19', '12:12:37', 'Awaiting' UNION ALL
SELECT	'00216177', '2006-04-19', '12:12:58', 'Awaiting' UNION ALL
SELECT	'00216177', '2006-04-19', '12:13:00', 'Closed'

-- Show the expected result
SELECT		w.CallID,
		w.[Open],
		w.[Closed],
		DATEDIFF(DAY, w.[Open], w.[Closed]) AS Days
FROM		(
			SELECT		CallID,
					MIN(CASE WHEN CallStatus = 'Open' THEN DateStop END) AS [Open],
					MAX(CASE WHEN CallStatus = 'Closed' THEN DateStop END) AS [Closed]
			FROM		@Sample
			GROUP BY	CallID
		) AS w
ORDER BY	w.CallID


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-03-14 : 10:33:04
If you want your table to be maintainable in the long run, you should not separate the date and the time information.
-- Prepare sample data
DECLARE	@Sample TABLE (CallID VARCHAR(8), [Stop] DATETIME, CallStatus VARCHAR(8))

INSERT	@Sample
SELECT	'00216177', '2006-01-20 05:39:24', 'Open' UNION ALL
SELECT	'00216177', '2006-01-20 05:39:27', 'Open' UNION ALL
SELECT	'00216177', '2006-01-20 05:40:13', 'Open' UNION ALL
SELECT	'00216177', '2006-01-20 05:40:24', 'Pending' UNION ALL
SELECT	'00216177', '2006-02-07 16:05:47', 'Pending' UNION ALL
SELECT	'00216177', '2006-02-21 17:26:22', 'Pending' UNION ALL
SELECT	'00216177', '2006-02-21 17:29:06', 'Pending' UNION ALL
SELECT	'00216177', '2006-02-21 17:29:08', 'Open' UNION ALL
SELECT	'00216177', '2006-03-03 16:35:10', 'Open' UNION ALL
SELECT	'00216177', '2006-04-05 15:12:26', 'Open' UNION ALL
SELECT	'00216177', '2006-04-05 15:17:09', 'Open' UNION ALL
SELECT	'00216177', '2006-04-14 14:37:49', 'Open' UNION ALL
SELECT	'00216177', '2006-04-14 14:37:54', 'Awaiting' UNION ALL
SELECT	'00216177', '2006-04-19 11:20:30', 'Awaiting' UNION ALL
SELECT	'00216177', '2006-04-19 12:12:34', 'Awaiting' UNION ALL
SELECT	'00216177', '2006-04-19 12:12:37', 'Awaiting' UNION ALL
SELECT	'00216177', '2006-04-19 12:12:58', 'Awaiting' UNION ALL
SELECT	'00216177', '2006-04-19 12:13:00', 'Closed'

-- Show the expected result
SELECT		w.CallID,
		w.[Open],
		w.[Closed],
		DATEDIFF(DAY, w.[Open], w.[Closed]) AS Days
FROM		(
			SELECT		CallID,
					MIN(CASE WHEN CallStatus = 'Open' THEN [Stop] END) AS [Open],
					MAX(CASE WHEN CallStatus = 'Closed' THEN [Stop] END) AS [Closed]
			FROM		@Sample
			GROUP BY	CallID
		) AS w
ORDER BY	w.CallID


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site