February 9, 2006 at 12:16 pm
Probably a simple question:
In a ticket support system I need the client name, and a count of the number of tickets they had open in the last month by ticket type.
The trick is, I want to show a zero when no tickets of that type were opened. For instance:
Client_Name Ticket_Type
Client A 1
Client A 3
So how do I get the result set that shows '0' for Ticket_Type 2 for Client A?
Thanks in advance
February 9, 2006 at 1:10 pm
I had to play around a little, but substitue your table and column names appropriately and away you go
select
Zeros.[name]
, ticket
, CASE WHEN ticket_count > 0 THEN ticket_count ELSE ticket_zero END
FROM
(
select
[name]
, ticket_type
, count(ticket_type) AS ticket_count
from
Client
left join ClientTicket
on client_name = [name]
group by
[name]
, ticket_type
) AS Counts
right JOIN
(
select
[name]
, ticket
, 0 AS ticket_zero
from
Client
cross join TicketType
) AS Zeros
ON Zeros.[name] = Counts.[name]
AND Zeros.ticket = Counts.ticket_type
February 9, 2006 at 11:04 pm
Post your table structure, sample data and expected result.
Without detail of your table structure, i can only guess that you will need a LEFT JOIN between the Client Table and the ticket table
February 9, 2006 at 11:18 pm
Hi,
You can use the following Query !!!
select client_name,count(*) from <table>
group by dateadd(mm,-1,ticket_date)
order by ticket_name
i hope this would be your answer!!!
which you are trying to ask.
February 10, 2006 at 10:51 am
Hopefully this will work for you:
create table TicketType
(
TicketTypeID int,
TicketTypeDescription varchar(255)
)
create table Client
(
ClientID int,
ClientName varchar(255)
)
create table Ticket
(
TicketID int,
ClientID int,
TicketTypeID int,
TicketDate datetime
)
insert TicketType values(1, 'Ticket_1')
insert TicketType values(2, 'Ticket_2')
insert TicketType values(3, 'Ticket_3')
insert Client values(1, 'Client_1')
insert Client values(2, 'Client_2')
insert Ticket values(1, 1, 1, '02 Jan 2006') --Should be excluded because > 1 month ago
insert Ticket values(2, 1, 1, '02 Feb 2006')
insert Ticket values(3, 1, 1, '03 Feb 2006')
insert Ticket values(4, 1, 3, '04 Feb 2006')
select c.ClientName, tt.TicketTypeDescription, count(t.ticketID)
from Client c
cross join TicketType tt
left outer join Ticket t
on tt.TicketTypeID = t.TicketTypeID
and c.ClientID = t.ClientID
and t.TicketDate > dateadd(mm, -1, getDate())
group by c.ClientName, tt.TicketTypeDescription
order by c.ClientName, tt.TicketTypeDescription
February 10, 2006 at 1:07 pm
Chris,
Jeff is right on. The LEFT JOIN is needed to include Clients w/o Tickets. COUNTing ( t.TicketID ) as opposed to (*) is the other trick.
February 10, 2006 at 3:59 pm
Finally got, had some issues with weird design, but your help got me where I needed to be.
Thanks All!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply