SELECT widget, MAX(`timestamp`) AS ts
FROM tableX AS t
WHERE state = 'down'
GROUP BY widget
HAVING NOT EXISTS
( SELECT *
FROM tableX AS tt
WHERE tt.widget = t.widget
AND tt.state <> 'down'
AND tt.`timestamp` > MAX(t.`timestamp`)
) ;
I think that you'll need two indices, one on (widget, state, timestamp)
and one on (widget, timestamp, state)
for efficiency.
This will work, too, and will be needing only one index, on (widget, timestamp, state)
:
SELECT t.widget, t.`timestamp`
FROM
tableX AS t
JOIN
( SELECT widget, MAX(`timestamp`) AS ts
FROM tableX
GROUP BY widget
) AS tm
ON tm.widget = t.widget
AND tm.ts = t.`timestamp`
WHERE t.state = 'down' ;
Tested both at SQL-Fiddle: test
Okay, well, since nobody else chimed in and I needed to get it done, I took one last shot at it. Not quite sure on the why of it all, but I used basically the entire query as a subquery in the FROM clause, and then ran the report against that, and that seemed to do it...
SELECT
[VendorCusts].[VendorPaid],
[VendorCusts].[DE?],
[VendorCusts].[IC?],
[VendorCusts].[AG?],
[VendorCusts].[GB?],
COUNT(VendorCustID) AS [CustCount]
FROM
(
SELECT DISTINCT
[Plan Revenue Expense].[Check To] AS [VendorPaid],
[Support Provider].[DE?],
[Support Provider].[IC?],
[Support Provider].[Agency?] AS [AG?],
[Support Provider].[GeneralBus?] AS [GB?],
Customer.CustID AS [VendorCustID],
Customer.LName AS [VendorCustLName],
Customer.FName AS [VendorCustFName]
FROM
(((Customer
INNER JOIN Plan ON Customer.CustID=Plan.CustID)
INNER JOIN [Plan Revenue] ON [Plan].[Plan ID]=[Plan Revenue].[PlanID])
INNER JOIN [Plan Revenue Expense] ON [Plan Revenue].[Rev ID]=[Plan Revenue Expense].[RevID])
LEFT JOIN [Support Provider] ON [Plan Revenue Expense].[SP]=[Support Provider].[ID]
WHERE
(
(
([Plan Revenue Expense].[First Day]>=[Expense Start "MM/DD/YYYY"])
AND
([Plan Revenue Expense].[First Day]<=[Expense End "MM/DD/YYYY"])
)
OR
(
([Plan Revenue Expense].[Last Day]>=[Expense Start "MM/DD/YYYY"])
AND
([Plan Revenue Expense].[Last Day]<=[Expense End "MM/DD/YYYY"])
)
)
AND NOT
(
[Plan Revenue].[Service]='111' OR
[Plan Revenue].[Service]='222' OR
[Plan Revenue].[Service] LIKE '333*'
)
AND NOT Customer.[Inactive?]=TRUE
ORDER BY
[Plan Revenue Expense].[Check To],
Customer.LName,
Customer.FName
) AS [VendorCusts]
GROUP BY
[VendorCusts].[VendorPaid],
[VendorCusts].[DE?],
[VendorCusts].[IC?],
[VendorCusts].[AG?],
[VendorCusts].[GB?]
HAVING
NOT ([VendorCusts].[DE?]=TRUE OR [VendorCusts].[IC?]=TRUE)
ORDER BY
[VendorCusts].[DE?] ASC,
[VendorCusts].[IC?] ASC,
[VendorCusts].[AG?] ASC,
[VendorCusts].[GB?] ASC,
[VendorCusts].[VendorPaid] ASC;
This is more or less what I finally came up with and it appears to get the job done. Checked a few data points and they seemed to agree with the slightly more verbose version w/o a Count() function. So, I think the results are good.
Then I abstracted out the start and end of the date range so I could run a few different versions. And added a few other parameters I needed...
Seems like it's working as intended, if a bit kludge-y. Sometimes it's better to be 'right' than 'pretty.' ;)
Best Answer
As an example
@filterA
and@filterB
are variables/literals with user filters choices inserted/transferred into the query as a parameter or literally (edit WHEN conditional values for to match).