L
lawrencep
I know it looks convoluted, but it works--kinda. 100 records is no
problem--10k records drags on forever. Each piece of equipment could
have multiple readings (DateTime) but I'm just interested in the latest
one for each month. I can string together more of the nested select
statements and create a table of sorts showing this month, last month,
two months ago, etc. by changing the '-0' to whatever I want.
Where did I go wrong?
SELECT DISTINCT T.Equipment, (SELECT MAX(A.DateTime) FROM tbl1 AS A
WHERE A.DateTime BETWEEN DATESERIAL(YEAR(DATEADD("M", -0, DATE())),
MONTH(DATEADD("M", -0, DATE())), 1) AND DATESERIAL(YEAR(DATEADD("M",
-0, DATE())), MONTH(DATEADD("M", -0, DATE())) + 1, 1) - 1 AND
T.Equipment=A.Equipment) AS NOW_0
FROM tbl1 AS T
ORDER BY T.Equipment;
Thanks,
Patrick
problem--10k records drags on forever. Each piece of equipment could
have multiple readings (DateTime) but I'm just interested in the latest
one for each month. I can string together more of the nested select
statements and create a table of sorts showing this month, last month,
two months ago, etc. by changing the '-0' to whatever I want.
Where did I go wrong?
SELECT DISTINCT T.Equipment, (SELECT MAX(A.DateTime) FROM tbl1 AS A
WHERE A.DateTime BETWEEN DATESERIAL(YEAR(DATEADD("M", -0, DATE())),
MONTH(DATEADD("M", -0, DATE())), 1) AND DATESERIAL(YEAR(DATEADD("M",
-0, DATE())), MONTH(DATEADD("M", -0, DATE())) + 1, 1) - 1 AND
T.Equipment=A.Equipment) AS NOW_0
FROM tbl1 AS T
ORDER BY T.Equipment;
Thanks,
Patrick