sql sub query question with count

[Spark]

Hi,

Situation:
Need a query to return number of ticket records by month of open in a
log table where the ticket open record is older than 24 hours then the
ticket pending or ticket closed record. Tickets can also only have a
closed record with no open record.

Sample data table:
ticket_id date_log status_name status_id
13 8/21/2002 10:11:23 AM Open 1
13 8/21/2002 10:12:06 AM Closed 3
14 8/21/2002 10:16:54 AM Closed 3
14 8/21/2002 10:16:35 AM Open 1
15 8/21/2002 10:22:19 AM Open 1
15 8/21/2002 12:30:15 PM Open 1
15 8/21/2002 2:06:09 PM Open 1
15 8/23/2002 9:34:39 AM Open 1
15 8/23/2002 9:57:38 AM Open 1
15 8/23/2002 10:02:39 AM Closed 3
92 8/22/2002 4:38:24 PM Open 1
92 8/22/2002 4:45:59 PM Closed 3
93 8/23/2002 8:55:41 AM Open 1
93 8/23/2002 9:06:27 AM Closed 3
94 8/23/2002 8:59:17 AM Open 1
94 8/26/2002 9:22:27 AM Closed 3
98 8/23/2002 9:45:48 AM Open 1
98 8/26/2002 9:20:17 AM Closed 3
980 10/1/2002 9:55:25 AM Open 1
980 10/1/2002 9:55:36 AM Closed 3
1183 10/11/2002 9:58:25 AM Closed 3
1184 10/11/2002 10:03:17 AM Closed 3
1185 10/11/2002 10:05:51 AM Closed 3
1225 10/14/2002 10:56:47 AM Open 1
1225 10/16/2002 1:40:06 PM Pending 2
1225 10/17/2002 11:21:48 AM Pending 2
1225 10/17/2002 1:51:30 PM Pending 2
1225 10/18/2002 9:59:49 AM Pending 2
1225 10/18/2002 2:50:39 PM Closed 3
1225 5/11/2003 12:05:29 PM Closed 3

Current query:
SELECT DISTINCT DATEPART(mm, date_clean) AS month_name,
(SELECT count(DISTINCT t_id)
FROM log_tickets t1
WHERE (t_id = t2.t_id) AND (DATEDIFF(hh,
(SELECT TOP 1 date_log
FROM log_tickets t1
WHERE (t_id = t2.t_id) AND (status_id = 1)
)
,
(SELECT TOP 1 date_log
FROM log_tickets t1
WHERE (t_id = t2.t_id) AND (status_id = 2 OR status_id
= 3)
)
)
)>24) AS late_tickets
FROM dbo.log_tickets t2

Current Output:
month_name late_tickets
10 1
8 0
8 1
10 0

This is the output of what I would like:
month_name late_tickets
8 3
10 2

Any help would be appreciated.
Thanks,
Sam

[Erland Sommarskog]

Re: sql sub query question with count

[posted and mailed, please reply in news]

Spark (spark@adsgroup .net) writes:[color=blue]
> Need a query to return number of ticket records by month of open in a
> log table where the ticket open record is older than 24 hours then the
> ticket pending or ticket closed record. Tickets can also only have a
> closed record with no open record.[/color]

First a general piece of advice. When posting a query like this,
it is adviceable to include CREATE TABLE statements for the involved
tables and INSERT statements for the sample data. Had you done this
I would have been able to suggest a tested solution.

What follows here is completely untested:


SELECT Month(coalesce( cl.last_closed, op.first_open)) , COUNT(*)
FROM (SELECT ticked_id, first_open = MIN(date_log)
FROM log_tickets
WHERE status_id IN (1, 2)
GROUP BY ticked_id) AS op
FULL JOIN
(SELECT ticked_id, last_closed = MAX(date_log)
FROM log_tickets
WHERE status_id = 3
GROUP BY ticket_id) AS cl ON op.ticket_id = cl.ticket_id
WHERE datediff(ss, coalesce(op.fir st_open, '19000101'),
coalesce(cl.las t_closed, '99991231') > 86400
GROUP BY Month(coalesce( cl.last_closed, op.first_open))

I like to note that there are a couple of things that were
unspecified in your post:

o If a ticket has more than one Open/Pending entry, which counts,
first or last? (I have assumed first.)
o Likewise, what if a ticket is closed more than once.
o If a ticket is opened one month, and closed another for which
month should it be included? (I have assumed month it was closed.)
o You don't mention the possibility that a ticket is not closed. The
query above will include such tickets in the listing.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Spark]

Re: sql sub query question with count

>Re: sql sub query question with count[color=blue]
>From: Erland Sommarskog
>Date Posted: 8/15/2003 3:23:00 PM[/color]
[color=blue]
>What follows here is completely untested:[/color]

[color=blue]
> SELECT Month(coalesce( cl.last_closed, op.first_open)) , COUNT(*)
> FROM (SELECT ticked_id, first_open = MIN(date_log)
> FROM log_tickets
> WHERE status_id IN (1, 2)
> GROUP BY ticked_id) AS op
> FULL JOIN
> (SELECT ticked_id, last_closed = MAX(date_log)
> FROM log_tickets
> WHERE status_id = 3
> GROUP BY ticket_id) AS cl ON op.ticket_id = cl.ticket_id
> WHERE datediff(ss, coalesce(op.fir st_open, '19000101'),
> coalesce(cl.las t_closed, '99991231') > 86400
> GROUP BY Month(coalesce( cl.last_closed, op.first_open))[/color]

I added an extra closed paranthesis to this line:
"coalesce(cl.la st_closed, '99991231')) > 86400"

I get this error:
Server: Msg 535, Level 16, State 1, Line 1
Difference of two datetime columns caused overflow at runtime.


As per suggestion, I have included more information.
[color=blue]
>o If a ticket has more than one Open/Pending entry, which counts,
> first or last? (I have assumed first.)[/color]

Actually in this case, Pending and Closed mean the same thing. If
there is more than one Pending/Closed entry, the first one counts.
[color=blue]
>o Likewise, what if a ticket is closed more than once.[/color]

Use the first closed entry.
[color=blue]
>o If a ticket is opened one month, and closed another for which
> month should it be included? (I have assumed month it was closed.)[/color]

Yep.
[color=blue]
>o You don't mention the possibility that a ticket is not closed. The
> query above will include such tickets in the listing.[/color]

Forgot about this case. Thanks for including it.



CREATE TABLE [dbo].[log_tickets] (
[ticket_id] [int] NOT NULL ,
[date_log] [datetime] NOT NULL ,
[status_name] [nvarchar] (50) NULL ,
[status_id] [smallint] NULL
) ON [PRIMARY]
GO

INSERT INTO log_tickets VALUES (13,'8/21/2002 10:11:23 AM','Open',1)
INSERT INTO log_tickets VALUES (13,'8/21/2002 10:12:06 AM','Closed',3)
INSERT INTO log_tickets VALUES (14,'8/21/2002 10:16:54 AM','Closed',3)
INSERT INTO log_tickets VALUES (14,'8/21/2002 10:16:35 AM','Open',1)
INSERT INTO log_tickets VALUES (15,'8/21/2002 10:22:19 AM','Open',1)
INSERT INTO log_tickets VALUES (15,'8/21/2002 12:30:15 PM','Open',1)
INSERT INTO log_tickets VALUES (15,'8/21/2002 2:06:09 PM','Open',1)
INSERT INTO log_tickets VALUES (15,'8/23/2002 9:34:39 AM','Open',1)
INSERT INTO log_tickets VALUES (15,'8/23/2002 9:57:38 AM','Open',1)
INSERT INTO log_tickets VALUES (15,'8/23/2002 10:02:39 AM','Closed',3)
INSERT INTO log_tickets VALUES (92,'8/22/2002 4:38:24 PM','Open',1)
INSERT INTO log_tickets VALUES (92,'8/22/2002 4:45:59 PM','Closed',3)
INSERT INTO log_tickets VALUES (93,'8/23/2002 8:55:41 AM','Open',1)
INSERT INTO log_tickets VALUES (93,'8/23/2002 9:06:27 AM','Closed',3)
INSERT INTO log_tickets VALUES (94,'8/23/2002 8:59:17 AM','Open',1)
INSERT INTO log_tickets VALUES (94,'8/26/2002 9:22:27 AM','Closed',3)
INSERT INTO log_tickets VALUES (98,'8/23/2002 9:45:48 AM','Open',1)
INSERT INTO log_tickets VALUES (98,'8/26/2002 9:20:17 AM','Closed',3)
INSERT INTO log_tickets VALUES (980,'8/28/2002 9:30:25 AM','Open',1)
INSERT INTO log_tickets VALUES (980,'9/1/2002 9:40:36 AM','Closed',3)
INSERT INTO log_tickets VALUES (980,'9/1/2002 9:55:25 AM','Open',1)
INSERT INTO log_tickets VALUES (980,'9/1/2002 9:55:36 AM','Closed',3)
INSERT INTO log_tickets VALUES (1183,'9/11/2002 9:58:25
AM','Closed',3)
INSERT INTO log_tickets VALUES (1184,'9/11/2002 10:03:17
AM','Closed',3)
INSERT INTO log_tickets VALUES (1185,'9/11/2002 10:05:51
AM','Closed',3)
INSERT INTO log_tickets VALUES (1225,'9/14/2002 10:56:47 AM','Open',1)
INSERT INTO log_tickets VALUES (1225,'9/16/2002 1:40:06
PM','Pending',2 )
INSERT INTO log_tickets VALUES (1225,'9/17/2002 11:21:48
AM','Pending',2 )
INSERT INTO log_tickets VALUES (1225,'9/17/2002 1:51:30
PM','Pending',2 )
INSERT INTO log_tickets VALUES (1225,'9/18/2002 9:59:49
AM','Pending',2 )
INSERT INTO log_tickets VALUES (1225,'9/18/2002 2:50:39
PM','Closed',3)
INSERT INTO log_tickets VALUES (1225,'5/11/2003 12:05:29
PM','Closed',3)


Thanks for your help.
We are getting closer.
Sam

[--CELKO--]

Re: sql sub query question with count

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. You ask people to help you for free, but you don't
give us any specifications we can use, no sample data, and no DDL.

Your design is wrong. The ticket is an entity and to exist an entity
must have a duration (Einstein). This is one of many reasons I keep
telling people that a row is not a record, as you seem to think. A
row is a complete fact in itself; a record does not have to be. You
modeled the PHYSICAL, written lines from a paper form, not the LOGICAL
fact that you need. Hence, you get a crappy, complex query that runs
like glue.

CREATE TABLE Tickets
(ticket_nbr INTEGER NOT NULL PRIMARY KEY
start_time DATETIME NOT NULL,
finish_time DATE, -- null means still open
status_code INTEGER NOT NULL
CHECK (status_code IN (1, 2, 3)),
CHECK (start_time < finish_time),
-- I am guessing at this business rule
CHECK (CASE WHEN status_code = 1 AND finish_time IS NULL
THEN 1
WHEN status_code IN (2,3) AND finish_time IS NOT NULL
THEN 1 ELSE 0 END = 1)
);
[color=blue][color=green]
>> Need a query to return number of ticket records by month of open in[/color][/color]
a log table where the ticket open record [sic] is older than 24 hours
than the ticket pending or ticket closed record [sic]. <<

Now the anser is easy; remember to COALESCE(finish _time,
CURRENT_TIMESTA MP) in the query to handle things that are still open.
[color=blue][color=green]
>> Tickets can also only have a closed record [sic] with no open[/color][/color]
record [sic]<<

Please write the constraint that enforces that business rule. I think
that you meant that a ticket must be open before it can be closed or
pending. Also use ISO-8601 time formats for portability.

[Erland Sommarskog]

Re: sql sub query question with count

Spark (spark@adsgroup .net) writes:[color=blue]
> I get this error:
> Server: Msg 535, Level 16, State 1, Line 1
> Difference of two datetime columns caused overflow at runtime.[/color]

Oops! This was due the combination the values to cover tickets still
open, or tickets that only were closed, and my zeal to get an accurate
expression of 24 hours. datediff() works by counting boundaries, thus:

select datediff(hh, '20021212 00:56:00', '20021213 00:09:22')

yields 24.

The remaining issue should be easy to sort out on your own, just change
MAX to MIN, and move the two around. But since you included CREATE TABLE
and INSERT statemetns, I was able to test this modified query:

SELECT Month(coalesce( cl.first_closed , op.first_open)) , COUNT(*)
FROM (SELECT ticket_id, first_open = MIN(date_log)
FROM log_tickets
WHERE status_id = 1
GROUP BY ticket_id) AS op
FULL JOIN
(SELECT ticket_id, first_closed = MIN(date_log)
FROM log_tickets
WHERE status_id IN (2, 3)
GROUP BY ticket_id) AS cl ON op.ticket_id = cl.ticket_id
WHERE datediff(ss, coalesce(op.fir st_open,
dateadd(day, -2, cl.first_closed )),
coalesce(cl.fir st_closed,
dateadd(day, 2, cl.first_closed ))) > 86400
GROUP BY Month(coalesce( cl.first_closed , op.first_open))

I got the results:

Month Count
----------- -----------
8 3
9 5

This appears to match the test data.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp