How to get column count?

I would like to know which tickets are still open (those with Open=1 as last entry).  TicketNum is used to track added entries for a still open ticket.  TicketNum entries are just increments of the previous entry for the TicketTypeId.  Often, the same TicketType is used.  Once a ticket is closed and another open, the new ticket starts with TicketNum=1 and the process starts all over.

In the code example, only employeeIds 5 (Id=7) and 6 (Id=12) have open tickets.

 
Id   empId  ticketNum   TicketTypeId   Open   DateCreated
1       2           1     6              0
2       2           1     8              0
3       2           1     9              1
4       2           2     9              0
5       5           1     6              0
6       5           1     9              1
7       5           2     9              1
8       6           1     9              1
9       6           2     9              0
10      6           1     9              1
11      6           2     9              1
12      6           3     9              1

Open in new window

brettrAsked:
Who is Participating?
 
sshah254Connect With a Mentor Commented:
Select count(*) from table a inner join
(select max(Id), ticketNum as maxid from table group by ticketNum) b
on a.id = b.id and a.ticketNum = b.ticketNum
where a.Open = 1

SS
0
 
brettrAuthor Commented:
Thanks but getting a few errors.

No column was specified for column 1 of 'b'

Invalid column name 'id'

Where you have a.id = b.id, b.id is not valid correct?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.