Solved

SELECT STATEMENT NEEDS SUMMARY

Posted on 2011-03-01
7
380 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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

778 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