Calculations on query results

[Steve@trop]

I need to do a calculation on the RESULTS of a query. Each record in the table I'm querying has a date stamp. I have a query that produces a list of the newest records for each machine. I want to calculate how old each of those records is based on today's date.

So far when I try to include the calculation in that query, it returns all the records instead of the newest ones. This makes me think I'm doing something fundamentally incorrect. The calculation is affecting the query results where I just want to do the calculation on the results.

What I'm trying to ultimately do is have a way for the users to see a list of machines that are overdue for a certain procedure. I was thinking of putting a button on a form that will run the query and present the list, perhaps with some conditional formatting highlighting the machines that are overdue, etc.

Do I need to just use 2 queries? Or is there a way to do this with one?

Thanks,

Steve

 

[CJ_London]

can you post your query sql - it is difficult to advise without knowing what you are doing at the moment.

Depending on what you are trying to achieve it should be doable in one query

 

[Steve@trop]

Sure CJ, here is the query without the failed calculation attempt:

SELECT Max(NorthCompressorRoundT.TimeStamp) AS MaxOfTimeStamp, NorthCompressorRoundT.CompressorID
FROM NorthCompressorRoundT
GROUP BY NorthCompressorRoundT.CompressorID;

The query results are below:

MaxOfTimeStampCompressor ID5/16/2013 2:52:12 PMCMP #15/15/2013 12:41:47 PMCMP #105/17/2013 9:41:11 AMCMP #125/15/2013 12:53:56 PMCMP #145/15/2013 8:38:28 AMCMP #25/15/2013 12:50:09 PMCMP #35/7/2013 8:02:27 AMCMP #45/17/2013 9:34:41 AMCMP #55/16/2013 2:55:28 PMCMP #65/15/2013 9:45:27 AMCMP #75/15/2013 12:53:13 PMCMP #8

What I'm looking to do is have another column that shows the amount of days since the MaxOfTimeStamp for each one of the compressors.

 

[Brianwarnock]

You are correct to assume that you will need a second query to do the calculation, though some may use a subquery approach I prefer the simple life.

Brian

 

[Steve@trop]

Thanks,

I'm new to queries. When you run a query on a query, does the original query update automatically or will I need to program the first query to run before the final query each time the user runs it?

Thanks,

Steve

 

[Brianwarnock]

If you have a series of stacked or cascading queries, i'm unsure of the correct term as I have seen both used, then you merely run the last, the system takes care of the rest, and I believe it actually optimizes the code generated when it can.

Brian

 

[CJ_London]

I'm a subquery kinda guy!

This SQL should give you what you want - you may need to format the age column to numeric if it presents itself in a date format. It also does not account wor the working week, holidays etc

SELECT CompressoriD, TimeStamp, Date()-TimeStamp as Age
FROM  NorthCompressorRoundT
WHERE TimeStamp=(SELECT Max(TimeStamp) FROM NorthCompressorRoundT AS Tmp 
WHERE CompressorID=NorthCompressorRoundT.CompressorID)

 

[CJ_London]

When you run a query on a query, does the original query update automatically

Yes! - providing it is either a select or crosstab query

 

[Brianwarnock]

I prefer the cascading approach as I think it is simpler to code, test, modify. I did wonder about performance until a group of MVPs discussed it on here and said that the cascading approach would always be as efficient as subqueries and may actually be better if the subquery design compromised the code optimisation. I wish I could find the thread.
I do of course use subqueries when you have to , such as for ranking.

Brian

 

[Steve@trop]

Thanks CJ and Brian,

I think I can see what you did. You calculated the age on all the rows before selecting the newest (max) records. I was stuck on getting the newest records first, then calculating the age. I'll have to keep this in mind when constructing other queries.

Very helpful, thanks again!

Steve

 

[CJ_London]

You calculated the age on all the rows before selecting the newest (max) records

No - it only calculates the age for the newest record

I don't disagree with whatr Brian is saying - I think it depends on the situation as to which is better and which performs better.

For me the problem with cascading queries is that if you make a change at the beginning you may have to modify all the other queries in the chain. There is also the risk that you will use a query for something else, make a change to suit the new query and the old query no longer works as expected.

 

[Steve@trop]

OK, well I'm getting better at interpreting SQL code but I guess I'm not THAT good yet .

This is still helpful though!

Steve

 

[Brianwarnock]

The sub query equates to the first query of a stack or cascade, so think of it as being executed first although you come across it second.

CJ naming conventions and professional approach avoid the problems you mentioned, and a change to the first query would equate to a change in the subquery so no difference there, but it's a case of horses for courses and/or personal preference no point arguing.

Brian

 

[Steve@trop]

CJ,

That query is so good I plan to use it in another place in the database. How can I edit the SQL query you created above so that it calculates the time in hours instead of days? I tried changing Date() to Hour() but that didn't work. I think I just need to figure out which function and syntax to use.

Thanks again!

Steve

 

[CJ_London]

Easiest way would be

SELECT CompressoriD, TimeStamp, Date()-TimeStamp as Age[COLOR=red],
(Now()-TimeStamp)*24 AS Hrs[/COLOR]

 

[Brianwarnock]

Won't that only give the number of days *24, the calculation of the number of hours is far more complicated than that.

Brian

 

[CJ_London]

It will, but that is what the poster has asked for - I've already said in an earlier post that it does not account for the working week, holidays etc.

 

[Brianwarnock]

I was not thinking of that but things like the start and end being on the same day as a simple example.
I think he needs to look at DateDiff.

Brian

 

[Steve@trop]

I think this will serve my purposes quite nicely. It's a 24x7 operation so weekdays, weekends and holidays are not an issue. Gotta keep the OJ cold on weekends too .

I did need to change date() to now() to get the calculation to be more accurate though. I think date() uses 12:00 AM as a default time where now() uses the current time.

Thanks again!

Steve

 

[CJ_London]

Glad to hear it - I wondered about date/now options but you have something that works for you which is the main thing