I'm trying to get the counts as shown below (ie totals and totals for a date range). Currently I'm experimenting in EDBManager

SELECT
_LastResult, Count(*) AS ProjectCount, DatedCount
FROM Calls                               
JOIN               
(
SELECT
_LastResult,Count(*) AS DatedCount
FROM Calls
WHERE
(_LastResult <> '-' AND _LastResult IS NOT NULL)
AND
_LastAttempt BETWEEN :Starter AND :Ender
GROUP BY _LastResult
) DC ON _LastResult = DC._LastResult
WHERE
_LastResult <> '-' AND _LastResult IS NOT NULL
GROUP BY _LastResult
ORDER BY _LastResult


I was using b9 and with that ProjectCount was correct but the DatedCount figures were incorrect. I installed b13 and now I don't get any figures for either ProjectCount or DatedCount.

I've tried both with and without parameters.

In b13 this

SELECT
_LastResult,Count(*) AS DatedCount
FROM Calls
WHERE
(_LastResult <> '-' AND _LastResult IS NOT NULL)
AND
_LastAttempt BETWEEN DATE '2010-04-01' AND DATE '2010-05-04'
GROUP BY _LastResult

gives zero rows

but

SELECT
_LastResult, COUNT(*) AS DatedCount
FROM Calls
WHERE
(_LastResult <> '-' AND _LastResult IS NOT NULL)
AND
_LastAttempt BETWEEN :Starter AND :Ender
GROUP BY _LastResult

with Starter set to 2010-04-01 and Ender set to 2010-05-04 (ie as above) gives 22 rows

HELP!!

Roy Lambert

I think I may have "lost" a load of records of the end of the table. I'm loading back from the live database and I'll retry.

Roy Lambert

And when I recovered the records I found I, being a moron, was using the wrong table in the derived table. All working now.

Roy Lambert