?
Solved

SELECT STATEMENT NEEDS SUMMARY

Posted on 2011-03-01
7
Medium Priority
?
399 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:Laura Munilla
  • 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:Laura Munilla
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 25

Accepted Solution

by:
reb73 earned 2000 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:Laura Munilla
ID: 35006981
Thanks!!!!  this worked great
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses
Course of the Month14 days, 16 hours left to enroll

840 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