SELECT STATEMENT NEEDS SUMMARY

I am using the following select statement.  I get the info by day by Equipment   I am creating a command to select the Summarized data byequipment, by month, but I also need the total for all areas by month.  I do not know how to do this.


EQ             DATETIME                          LOCATION
BHL005     2/17/2011 12:45:29 PM     YARD
BHL005     2/18/2010 12:45:29 PM    1000000
BHL005     2/19/2009 12:45:29 PM     2009635
BHL005     2/20/2008 12:45:29 PM    2006608
BHL005     2/17/2007 12:45:29 PM     2009635
EXC001     2/15/2008 12:45:29 PM     2006608
EXC001     2/12/2007 12:45:29 PM     YARD


I need a command that will return only the latest date with the latest location for each equipment
Laura MunillaVolunteer - Board MemberAsked:
Who is Participating?
 
reb73Connect With a Mentor Commented:
Sorry, some of the field references in my previous post was incorrect, here's the corrected one -

select A.EQId, B.LatestDate as MoveDate, A.Location as LastLoc
from tbl A
inner join
	(select EQId, max(DateTime) as LatestDate
	 from tbl
	 group by EQId
	) B on B.EQId = A.EQId and B.LatestDate = A.DateTime

Open in new window

0
 
reb73Commented:
Try this -
select A.EQ, B.Mnth, B.LatestDate, A.Yard
from tbl A
inner join
	(select EQ, Year(datetime) * 100 + month(datetime) as Mnth, max(datetime) as LatestDate
	 from tbl
	 group by Year(datetime) * 100 + month(datetime)
	) B on B.EQ = A.EQ and B.LatestDate = A.LatestDate

Open in new window

0
 
Laura MunillaVolunteer - Board MemberAuthor Commented:
Sorry,  I do not need montly info nor area,  just the EQ.   I am creating a command to select the Summarized data by equipment, by latest date.    I do not know how to do this.
EQ           DATETIME                          LOCATION
BHL005     2/17/2011 12:45:29 PM     yard
BHL005     2/18/2010 12:45:29 PM    1000000
BHL005     2/19/2009 12:45:29 PM     2009635
BHL005     2/20/2008 12:45:29 PM    2006608
BHL005     2/17/2007 12:45:29 PM     2009635
EXC001     2/15/2008 12:45:29 PM     2006608
EXC001     2/12/2007 12:45:29 PM     YARD


I need a command that will return only the latest date with the latest location for each equipment .  Like this:

EQID          MoveDate     LastLoc
BHL005     2/17/2011      yard
EXC001     2/15/2008    2006608
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
reb73Commented:
Ok, try this -
select A.EQId, B.LatestDate as MoveDate, A.Location as LastLoc
from tbl A
inner join
	(select EQId, max(datetime) as LatestDate
	 from tbl
	 group by EQId
	) B on B.EQId = A.EQId and B.LatestDate = A.LatestDate

Open in new window

0
 
TechyyCommented:
Try this:

Select EQ as EQID, max(DATETIME) as MoveDate, LOCATION as LastLoc from <Table name> group by EQ
0
 
reb73Commented:
Techyy -> Your query will generate an error as Location is not aggregated or present in the group by clause. A subquery is required in this case.
0
 
Laura MunillaVolunteer - Board MemberAuthor Commented:
Thanks!!!!  this worked great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.