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

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?

Thanks for your help.
0
johnmadigan
Asked:
johnmadigan
  • 4
  • 2
1 Solution
 
Jeffrey CoachmanMIS 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
 
Rey Obrero (Capricorn1)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
 
johnmadiganAuthor 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
johnmadiganAuthor 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
 
johnmadiganAuthor 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
 
johnmadiganAuthor 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
 
Rey Obrero (Capricorn1)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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now