Solved

SELECT STATEMENT NEEDS SUMMARY

Posted on 2011-03-01
7
375 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Lmunilla
  • 4
  • 2
7 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 35006369
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
 

Author Comment

by:Lmunilla
ID: 35006639
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
 
LVL 25

Expert Comment

by:reb73
ID: 35006681
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 25

Accepted Solution

by:
reb73 earned 500 total points
ID: 35006690
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
 
LVL 1

Expert Comment

by:Techyy
ID: 35006709
Try this:

Select EQ as EQID, max(DATETIME) as MoveDate, LOCATION as LastLoc from <Table name> group by EQ
0
 
LVL 25

Expert Comment

by:reb73
ID: 35006760
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
 

Author Closing Comment

by:Lmunilla
ID: 35006981
Thanks!!!!  this worked great
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now