Microsoft Access - SQL Coding Question

[rob2093124]

Good Morning!
I'm new to SQL coding and am trying to find a way to run a query and look to need some help
Table: Last Month Incidents
Used Columns:Assignment Group, Business duration
Issue:I have a table populated by incident tickets along with a field for Business Duration that shows how long they've been opened. I am trying to run a query that groups by the Assignment Group and gives counts for Total Completed, Same Day Completions, Within 3 Days Completions, etc. So far, I've come up with:

SELECT [Last Month Incidents].[Assignment Group], Count(*) AS MonthlyCount,
(SELECT COUNT(*) FROM [Last Month Incidents] WHERE [Last Month Incidents].[Business duration] >432000) AS Over5Days
FROM [Last Month Incidents]
GROUP BY [Last Month Incidents].[Assignment Group];

This gives me Total Completed by each group, but then give me the same number for "Over5Days" in all groups that represents the amount of tickets open for all days for ALL groups. I don't know of a way to keep the "Over5Days" counts specific to their Assignment Groups as well. Is this possible?

 

[SkipVought]

Hi,

WOW! You have a table of Last Month Incidents that has a field named Business Duration that is populated in SECONDS, in this particular case 432,000 seconds,...

Oops, the clock's ticking. So was that 5 day equivalent calculated at the beginning of August or at the end of August or somewhere in the middle or, to quote an infamous politician, "What difference does it make!"

This sure sounds like a homework assignment to me. Can you state the business case for this exersize?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[rob2093124]

Hey Skip,

I'm actually refining the reporting we do by creating an Access Database that will calculate certain numbers that we regularly report on. I pull the information from out ticketing system which gives us Business Duration in seconds 5 days is our SLA on these tickets. By pulling the amount of tickets that took longer than 5 days to close, I'm able to report on what percentage of our tickets we are completing within SLA vs outside of SLA.

To go a step further, I'd include pulling same-day closures, etc to give us more of an idea on how we are doing as a group.

The Business Duration is the total amount of time that a ticket is open before being resolved. Thanks in advance for any assistance!

 

[rob2093124]

The example in my coding is me trying to add a column that will give me the total amount tickets closed by that group that exceeded 5 days opened. The Over5Days is giving me the amount of tickets Over5Days for all groups, not each individual group.

 

[rob2093124]

I can do this by creating new fields and altering data, but since others may need to fill in for me, I'd like to keep this as seamless as possible, so I'm willing to do more work (SQL coding) to minimize manual work.

 

[SkipVought]

SELECT [Assignment Group], Count(*) AS MonthlyCount
FROM [Last Month Incidents]
WHERE [Business duration] >432000
GROUP BY [Assignment Group];

Why wouldn't that work?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[rob2093124]

Yea, that works. I guess I was just hoping to get that output, Total amount closed, and then other "WHERE" statements all in one query. I'd end up using 5 different queries for Totals, same day closures, 3 day closures, 5 day closures.

 

[SkipVought]

If it were me, I'd import the Last Month Incidents table into an Excel workbook via a query in Excel that can be refreshed each month by command, and construct a report table that would calculate the stats.

I assume that your management wants to see a composite report anyhow.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[rob2093124]

SELECT
[Last Month Incidents].[Assignment Group],
Count(*) AS MonthlyCount,
Sum(IIf ([Last Month Incidents].[Business duration] >432000,1,0)) AS Over5Days
FROM [Last Month Incidents]
GROUP BY [Last Month Incidents].[Assignment Group];

I actually heard back from a friend that figured it out. The above code as able to give me counts based of a calculation while keeping the count grouped by Assignment Group.

 

[SkipVought]

What you should have done is post a sample of your data with a statement of the results you would expect. Otherwise we're just speculating.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[rob2093124]

Will do next time. I thought I explained it well enough, along with the sample coding.

 

[SkipVought]

I would think that a bunch of IIFs you might be able to calculate all your stat elements.

But I would forget your last month table a query against your master data. That way, you might also be able to generate trends and comparison stats.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]