Speeding up query

[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

 

[Paul Overway]

Why nest it at all?

SELECT Equipment, Format([DateTime],"yyyy-mm") AS Month, Max([DateTime]) As
LastReading
FROM tbl1
GROUP BY Equipment, Format([TheDate],"yyyy-mm")
ORDER BY Equipment

If you only want certain months or a certain range or months, you can enter
criteria under LastReading.

 

[lawrencep]

Thanks for the quick reply, Paul.

That certainly works a lot faster but I'm not sure how to handle months
with no data now. When I dump this into a report, I'd like the columns
to line up by month so you can easily see which months are skipped. I
basically need a blank for those months.

Patrick

 

[Paul Overway]

It sounds like you want a crosstab?

TRANSFORM Max(DateTime) AS MaxOfDateTime
SELECT tbl1.Equipment, Year([DateTime]) AS [Year]
FROM tbl1
GROUP BY tbl1.Equipment, Year([DateTime])
ORDER BY tbl1.Equipment, Year([DateTime]) DESC
PIVOT "M" & Format([DateTime],"mm");

 

[lawrencep]

It turns out a crosstab is what I wanted--thanks for dragging it out of
me. I really appreciate the help. I modified it a little more and
pulled some additional info in.

TRANSFORM Max(A.DateTime) AS LastTime
SELECT C.Description, B.Description
FROM (vibtrenddata AS A RIGHT JOIN Equipment AS B ON A.EquipmentKey =
B.EquipmentKey) INNER JOIN Area AS C ON B.AreaKey = C.AreaKey
GROUP BY C.Description, B.Description
ORDER BY C.Description, B.Description
PIVOT Format([DateTime],"yyyy-mm");

One more question: When I added the Right Join (because I wanted to
show that some equipment had no data at all) an extra column called
"<>" popped up. Any reason behind that?

Thanks again,

Patrick

 

[lawrencep]

Also, for each of the Max(DateTime) items in the pivot, there's
additional info in other tables. Can you join a crosstab query to other
tables? I'd like to be able to show 'These are the months with data and
this is the data collected at those times' in the same tabular format
of the crosstab. Not having too much luck so far.

Patrick

 

[Paul Overway]

The <> indicates there is no data

 

[Paul Overway]

If two crosstabs have the same fixed column and each has a unique key as
part of the row heading, yes, you can combine the two in another query.
You'd add both the crosstabs to a new query, do the join, and then bring
down the columns you want...you might need to provide an alias for the
columns if the names are the same in each crosstab.

 

[Paul Overway]

PS...this is why I had year as a row heading....so, the number of columns
would be fixed.