Jump to content
  • 0

Task to pull most recent entry for each device if last entry was 1+ month ago


Question

Hi,  

I have 2 tables (Inventory list, and check in log)  and I am trying to pull a list of all devices that are active and have not been checked in for over 1 month from today. I only want to pull the last check in entry.

I was attempting to do this using a SELECT TOP 1 from check in log in the WHERE clause but it is not giving me the desired results.  Anyone have suggestions on the logic to get this working?  you can see my desired result below.  

 

Example Table 1(Inventory list)

Device ID status
1 Active
2 Active
3 Active
4 Active
5 Active
6 Active
7 Active
8 Active

Table 2 (Running Log)

Device ID Date
1 1/1/2021
2 1/15/2021
3 12/21/2020
4 3/4/2021
1 2/1/2021
1 3/1/2021
2 2/15/2021

 

Desired Result:

Device ID Date
3 12/21/2020
2 2/15/2021

Current Result:

Device ID Date
1 1/1/2021
1 2/1/2021
2 2/15/2021
2 1/15/2021
3 12/21/2020

 

image.thumb.png.7dae69411563a6705191706a40cf7c7d.png

 

Also, is there a trick to getting the order by timestamp to work?  The result seems to order by the deviceID even if I specify timestamp.

 

Link to post
Share on other sites

7 answers to this question

Recommended Posts

  • 0

What I can suggest on this one is to make use of the 'Group' function then aggregate your date field to only get the maximum. 

We can later on set a condition that the record will only be included if their maximum date is 1 month or more. By the way, in regards about this month ago, do you mean like, 30 days before? 

Link to post
Share on other sites
  • 0
On 3/27/2021 at 2:48 AM, NiceDuck said:

What I can suggest on this one is to make use of the 'Group' function then aggregate your date field to only get the maximum. 

We can later on set a condition that the record will only be included if their maximum date is 1 month or more. By the way, in regards about this month ago, do you mean like, 30 days before? 

 

Yes, it would be at least 30+ days ago since the last date.  Thank you so much for taking the time to reply, I will try this method tomorrow and let you know how it goes.  The results you got is what I am looking to capture.

Link to post
Share on other sites
  • 0

Hi @FaeFaeFae,

I ended up giving yours a shot, and it did return the results I was looking for when the fields were limited to the device ID and Date.  Is there a simple way to pull other fields from the record that has the  MAX log date?

When I add them to the select statement, it requires them to be added to the group by which adds more than 1 result for each deviceIID to the results, or requires to be in an aggregate which using max doesnt work as it pulls according to the alphabet and not the record with the newest timestamp.

 

Screenshot_6.jpg

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...