Solved

SELECT STATEMENT NEEDS SUMMARY

Posted on 2011-03-01
7
385 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
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

 
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

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.

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

713 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