select
TIMEDIFF(endTime,startTime) AS 'usage',
ediss7.opc,
ediss7.dpc,
ediss7.cic,
editrunkgroup.trunkGroup,
editrunkgroup.pointCode2,
editrunkgroup.pointCode1,
ediss7.startTime,
ediss7.endTime from ediss7 left join editrunkgroup
on ((ediss7.cic >= editrunkgroup.ciclow and
ediss7.cic <=editrunkgroup.cichigh)and
((ediss7.opc = editrunkgroup.pointcode1 or ediss7.opc
= editrunkgroup.pointcode2)
and (ediss7.dpc = editrunkgroup.pointcode1 or
ediss7.dpc = editrunkgroup.pointcode2)))
Where
(editrunkgroup.trunkGroup is null) Group by ediss7.cic, ediss7.dpc, ediss7.opc
It selects everything that doesn't fall into a predefined category. I want it to take the duplicate entries (cic, dpc, and opc being the same) and add the usage time of the grouped entries and have it display a total for each group. How would I do this?
SELECT
TIMEDIFF(endTime,startTime) AS 'usage',
ediss7.opc,
ediss7.dpc,
ediss7.cic,
editrunkgroup.trunkGroup,
editrunkgroup.pointCode2,
editrunkgroup.pointCode1,
ediss7.startTime,
ediss7.endTime
FROM
ediss7 LEFT JOIN editrunkgroup
on ((ediss7.cic >= editrunkgroup.ciclow and
ediss7.cic <=editrunkgroup.cichigh) and
((ediss7.opc = editrunkgroup.pointcode1 or
ediss7.opc = editrunkgroup.pointcode2) and
(ediss7.dpc = editrunkgroup.pointcode1 or
ediss7.dpc = editrunkgroup.pointcode2)))
WHERE
(editrunkgroup.trunkGroup is null)
GROUP BY
ediss7.cic,
ediss7.dpc,
ediss7.opc
Could you explain what you're trying to do? I couldn't quite figure it out.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
This query goes into a database, selects some entries that dont fall into a "trunkgroup" that the on statement checks. I want to group these entries that dont fall into a trunkgroup together if they have the same CIC, DPC and OPC. These entries are called "ghost trunks" because the trunkgroup doesnt exist. But I also need to add the "usage" time of the entries in the ghost trunk together to form a total usage time for each "ghost trunk". This prevents duplicates in a list but also shows the correct time each of those groups had. Right now it groups them together, but doesn't add up the times of each in the group so it is way under what it should be.