Stored Procedure or View? - 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

Stored Procedure or View?

Author

Topic

xcas08
Starting Member

13 Posts

Posted - 2007-03-14 : 10:13:43

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[/b]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 10:17:55

Dupe http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80576

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 10:21:31

Peter you were slower by 21 secs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 10:26:54

[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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 10:29:17

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 10:29:58

so this is how you boost your post count

?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80576

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 10:30:59

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 10:33:11

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-14 : 10:38:10

That's not fair, Peter !!

You are encouraging posters to duplicate their questions.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

xcas08
Starting Member

13 Posts

Posted - 2007-03-14 : 10:43:32

sorry, i wasn't sure if i'd get the results from this section. Thanks so much for the quick reply. In this example I need to know the amount of hours. Also I couldn't tell if you subtracted the wait time from the total time? Thanks again

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 10:43:37

quote:
Originally posted by harsh_athalye

That's not fair, Peter !!

You are encouraging posters to duplicate their questions.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

You are referring to the post count ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-14 : 10:56:51

quote:
Originally posted by khtan

quote:
Originally posted by harsh_athalye

That's not fair, Peter !!

You are encouraging posters to duplicate their questions.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

You are referring to the post count ?

KH

No..Post count doesn't matter for me.
He is already way too ahead of me in terms of no. of posts.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 10:57:27

[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(HOUR, w.[Open], w.[Closed]) AS Hours
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

xcas08
Starting Member

13 Posts

Posted - 2007-03-14 : 10:58:13

Again, forgive me for lack of knowledge. I'm going to be using this in a report, so how do i write this so that it uses all the data in the specified table (aka ActivityTable)? Would I be creating a stored procedure, or a view? Thanks for the help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 11:01:56

Create a VIEW with this code

-- Show the expected result
SELECT		w.CallID,
		w.[Open],
		w.[Closed],
		DATEDIFF(HOUR, w.[Open], w.[Closed]) AS Hours
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	ActivityTable
					) AS x
			GROUP BY	x.CallID
		) AS w
--ORDER BY	w.CallID

Peter Larsson
Helsingborg, Sweden

xcas08
Starting Member

13 Posts

Posted - 2007-03-14 : 11:05:40

Does this code you wrote tell me the amount of time of the entire call? it would be perfect if it subtracted the total waiting time as well from the total time? (i.e. Waiting/Pending Time)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 11:07:34

Have you tried the query yet?
How should WAITING time be calculated?
How should PENDING time be calculated?

What about you, with the sample code above, post the FULL expected output?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 11:10:27

What happens if PENDING and AWAITING overlaps, just as OPEN do?

Peter Larsson
Helsingborg, Sweden

xcas08
Starting Member

13 Posts

Posted - 2007-03-14 : 11:19:26

If i take the above...

SELECT w.CallID,
w.[Open],
w.[Closed],
DATEDIFF(HOUR, w.[Open], w.[Closed]) AS Hours
FROM (
SELECT CallID,
MAX(CASE WHEN CallStatus = 'Open' THEN [Stop] END) AS [Open],
MAX(CASE WHEN CallStatus = 'Awaiting' THEN [Stop] END) AS [Closed]
FROM @Sample
GROUP BY CallID
) AS w
ORDER BY w.CallID
**Total Awaiting = 118 hours

then...

SELECT w.CallID,
w.[Open],
w.[Closed],
DATEDIFF(HOUR, w.[Open], w.[Closed]) AS Hours
FROM (
SELECT CallID,
MIN(CASE WHEN CallStatus = 'Open' THEN [Stop] END) AS [Open],
MAX(CASE WHEN CallStatus = 'Pending' THEN [Stop] END) AS [Closed]
FROM @Sample
GROUP BY CallID
) AS w
ORDER BY w.CallID
**Total Pending time = 780 hours

Total wait time 898 hours
Total ticket duration 2143-898 = 1245 hours.

I guess these would be the expected results, but who's to say that there aren't more statuses throughout that would affect the time. how could i loop through the records to make sure I have accounted for all the statuses? Thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 11:33:30

select callstatus, count(*)
from activitytable
group by callstatus

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 11:40: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.[CaseOpen],
w.[CaseClosed],
DATEDIFF(HOUR, w.[CaseOpen], w.[CaseClosed]) -
DATEDIFF(HOUR, w.[CaseOpen], w.[PendingClosed]) -
DATEDIFF(HOUR, w.[AwaitingOpen], w.[AwaitingClosed]) AS CaseHours
FROM (
SELECT x.CallID,
MIN(CASE WHEN x.CallStatus = 'Open' THEN x.[Stop] END) AS [CaseOpen],
MAX(CASE WHEN x.CallStatus = 'Closed' THEN x.[Stop] END) AS [CaseClosed],
MAX(CASE WHEN x.CallStatus = 'Pending' THEN x.[Stop] END) AS [PendingClosed],
MAX(CASE WHEN x.CallStatus = 'Open' THEN x.[Stop] END) AS [AwaitingOpen],
MAX(CASE WHEN x.CallStatus = 'Awaiting' THEN x.[Stop] END) AS [AwaitingClosed]
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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources