• Status: Solved
• Priority: Medium
• Security: Public
• Views: 56

# ms access query to find open tickets in queue each friday

I have a table with service ticket #, Enter Date, and Closed date.

What I am trying to query is the quantity of open tickets (tickets entered not closed) for each week.

So If a ticket was entered on last Monday but was closed any time during the week - it would not be counted.

If a ticket was entered on last Monday - is was not closed by Friday - it would count as 1 Ticket in Queue for that week.

I am not sure how to structure the query to return records per week with number of Open tickets in Queue.

Any suggestions?

0
• 4
• 2
1 Solution

MIS LiasonCommented:
Does a "week" include weekends...

And to be clear, are we talking about a Sunday through Saturday "week", ...or the "Week Number" as defined as each "week" in the year.
0

Commented:
try this query

SELECT DatePart("ww",[EnterDate]) AS WeekNumber, Count(DatePart("ww",[EnterDate])) AS NumOfTicketsEntered, Count(DatePart("ww",[ClosedDate])) AS NumOfTicketsClosed, [NumOfTicketsEntered]-[NumOfTicketsClosed] AS CountOfOpenTickets
FROM YOURTABLE
GROUP BY DatePart("ww",[EnterDate])
0

Author Commented:
Yes Sunday to Saturday will work.

I have been working with DatePart - to get the week number and year.  So I can convert the Enter date and Closed date to the week numbers.

I can do a query to eliminate where the tickets were open and closed in the same week.

Having trouble figuring out how to account for tickets that stay open multiple weeks?
0

Author Commented:
Rey,

That worked - is there a way to convert the WeekNumb column to the saturday date?  I have to move these numbers to an existing spreadsheet with saturday dates.

Thanks,
0

Author Commented:
I was looking a little closer and I noticed it does not account for tickets that span a week:

The query returned for week 4 - 80 tic entered, 74 closed = 6 open - that works but ...

Week 5 - 75 tic entered, 73 closed  ,,, showing 2 open  - this include the 6 from week 4 and the 2 from week 5 for a total of 8 open tickets.

Does this make sense?
0

Author Commented:
I have a ticket# 813 that was entered in on 1.21.2015 and was never closed.  So I need to count this ticket for each of the following weeks since this is an open ticket (would count as 1 each week since it is still open)

Not sure how to account for this?

Thanks,
0

Commented:
here is the query with a Saturday date

SELECT [EnterDate] -7 + Weekday([EnterDate]) AS SatDate, Count(DatePart("ww",[EnterDate])) AS NumOfTicketsEntered, Count(DatePart("ww",[ClosedDate])) AS NumOfTicketsClosed, [NumOfTicketsEntered]-[NumOfTicketsClosed] AS CountOfOpenTickets
FROM YOURTABLE
GROUP BY [EnterDate] -7 + Weekday([EnterDate])

as for your other problem, you will need VBA user define function to do that.
and I suggest that you post another question for that. It will be better to add a sample db in that question.
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.