Group by Non-blank Distinct Values

[Renraf]

I have ticket data that creates a new record for every non-consecutive period
of time spent working a ticket. There is a field "TicketComplete" that will
be checked if the ticket is closed by the end of the stop time for that
record and unchecked if more work needs to be performed later.

I want to combine the information from each of the records that applies to
the same ticket. Here is what the data may look like:

Ticket, Start, Stop, TicketComplete
123, 9:00 AM, 9:30 AM, 0
ABC, 9:30 AM, 11:00 AM, -1
123, 11:00 AM, 11:50 AM, 0
(blank), 11:50 AM, 12:15 PM, -1
123, 12:15 PM, 12:50 PM, -1
(blank), 12:50 PM, 2:30 PM, -1

So, in the end, I want the min start time, max stop time, and sum of
TicketComplete for each ticket number, BUT I do not want to aggregate all of
the records with no ticket number. I want each to remain as its own entry
with the given Start, Stop, TicketComplete.

Advice? (Other than changing my data; I know it probably looks horribly
constructed, but it is consistent with the scenario in which we use it).

 

[KARL DEWEY]

Try this --
SELECT Ticket, Min([Start]) AS StartTime, Max([Stop]) AS StopTime,
Sum([TicketComplete]) AS SUMOfTicketComplete
FROM [ticket data]
GROUP BY Ticket
WHERE Ticket Is Not Null AND Ticket <> "";

 

[Renraf]

Does the WHERE statement apply to the whole query or just to the GROUP BY? I
don't know SQL syntax particularly well.

Try this --
SELECT Ticket, Min([Start]) AS StartTime, Max([Stop]) AS StopTime,
Sum([TicketComplete]) AS SUMOfTicketComplete
FROM [ticket data]
GROUP BY Ticket
WHERE Ticket Is Not Null AND Ticket <> "";

--
Build a little, test a little.

I have ticket data that creates a new record for every non-consecutive period
of time spent working a ticket. There is a field "TicketComplete" that will
be checked if the ticket is closed by the end of the stop time for that
record and unchecked if more work needs to be performed later.

I want to combine the information from each of the records that applies to
the same ticket. Here is what the data may look like:

Ticket, Start, Stop, TicketComplete
123, 9:00 AM, 9:30 AM, 0
ABC, 9:30 AM, 11:00 AM, -1
123, 11:00 AM, 11:50 AM, 0
(blank), 11:50 AM, 12:15 PM, -1
123, 12:15 PM, 12:50 PM, -1
(blank), 12:50 PM, 2:30 PM, -1

So, in the end, I want the min start time, max stop time, and sum of
TicketComplete for each ticket number, BUT I do not want to aggregate all of
the records with no ticket number. I want each to remain as its own entry
with the given Start, Stop, TicketComplete.

Advice? (Other than changing my data; I know it probably looks horribly
constructed, but it is consistent with the scenario in which we use it).

 

[KARL DEWEY]

It applys to the whole. You may have both Nulls and zero lenght strings.
--
Build a little, test a little.

Does the WHERE statement apply to the whole query or just to the GROUP BY? I
don't know SQL syntax particularly well.

Try this --
SELECT Ticket, Min([Start]) AS StartTime, Max([Stop]) AS StopTime,
Sum([TicketComplete]) AS SUMOfTicketComplete
FROM [ticket data]
GROUP BY Ticket
WHERE Ticket Is Not Null AND Ticket <> "";

--
Build a little, test a little.

I have ticket data that creates a new record for every non-consecutive period
of time spent working a ticket. There is a field "TicketComplete" that will
be checked if the ticket is closed by the end of the stop time for that
record and unchecked if more work needs to be performed later.

I want to combine the information from each of the records that applies to
the same ticket. Here is what the data may look like:

Ticket, Start, Stop, TicketComplete
123, 9:00 AM, 9:30 AM, 0
ABC, 9:30 AM, 11:00 AM, -1
123, 11:00 AM, 11:50 AM, 0
(blank), 11:50 AM, 12:15 PM, -1
123, 12:15 PM, 12:50 PM, -1
(blank), 12:50 PM, 2:30 PM, -1

So, in the end, I want the min start time, max stop time, and sum of
TicketComplete for each ticket number, BUT I do not want to aggregate all of
the records with no ticket number. I want each to remain as its own entry
with the given Start, Stop, TicketComplete.

Advice? (Other than changing my data; I know it probably looks horribly
constructed, but it is consistent with the scenario in which we use it).

 

[John Spencer]

Syntax error in your posting. WHERE clause should occur BEFORE the GROUP BY
clause.

SELECT Ticket
, Min([Start]) AS StartTime
, Max([Stop]) AS StopTime
, Sum([TicketComplete]) AS SUMOfTicketComplete
FROM [ticket data]
WHERE Ticket Is Not Null AND Ticket <> ""
GROUP BY Ticket

I'm not sure how the poster wants to handle the records where Ticket is
"blank". The above obviously excludes ALL those records.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Renraf]

That's the problem. I want to include each record with a blank ticket number
as its own record, while grouping all other records that have the same ticket
number. That's why I didn't think the code below would do what I wanted.

Syntax error in your posting. WHERE clause should occur BEFORE the GROUP BY
clause.

SELECT Ticket
, Min([Start]) AS StartTime
, Max([Stop]) AS StopTime
, Sum([TicketComplete]) AS SUMOfTicketComplete
FROM [ticket data]
WHERE Ticket Is Not Null AND Ticket <> ""
GROUP BY Ticket

I'm not sure how the poster wants to handle the records where Ticket is
"blank". The above obviously excludes ALL those records.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

It applys to the whole. You may have both Nulls and zero lenght strings.

.