Group by Non-blank Distinct Values

  • Thread starter Thread starter Renraf
  • Start date Start date
R

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).
 
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 <> "";
 
Does the WHERE statement apply to the whole query or just to the GROUP BY? I
don't know SQL syntax particularly well.

KARL DEWEY said:
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.


Renraf said:
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).
Click to expand...
Click to expand...
 
It applys to the whole. You may have both Nulls and zero lenght strings.
--
Build a little, test a little.


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

KARL DEWEY said:
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.


Renraf said:
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).
Click to expand...
Click to expand...
Click to expand...
 
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
 
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.

John Spencer said:
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

KARL said:
It applys to the whole. You may have both Nulls and zero lenght strings.
Click to expand...
.
Click to expand...
 
Back
Top