Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Max date record for each location

Posted on 2012-09-04
7
Medium Priority
?
674 Views
Last Modified: 2012-09-05
I have the following code and it gives me what I need as long as I don't add the Dept field...but I need Dept.  If I add department then it shows multiple locations with multiple dates instead of the Max date for each location.  It does this even if the department is null.  I think I need an inner select statement but I can't get anything to work.  I could really use some help.

SELECT        tblIncidents.Location, CASE WHEN tblIncidents.Location = 'Corporate' THEN Dept ELSE '' END AS Deptmt, MAX(tblIncidents.Date) AS MaxDate
FROM            tblIncidents INNER JOIN
                         tblLocation ON tblIncidents.Location = tblLocation.Location
WHERE        (tblLocation.Active = 'True') AND (tblIncidents.Date <= @Date)
GROUP BY tblIncidents.Location, tblIncidents.Dept
ORDER BY tblIncidents.Location

Sample results the code above gives me...
Location   Deptmt    MaxDate
Burland            10/16/2010 00:00:00
Burand            09/15/2009 00:00:00
Burland            06/29/2007 00:00:00
Burland            05/25/2006 00:00:00

even tho I just want the first one with the max date.
0
Comment
Question by:BobRosas
  • 4
  • 2
7 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 38366108
Have you tried

select top 1 tblIncidents.Location, CASE WHEN tblIncidents.Location = 'Corporate' THEN Dept ELSE '' END AS Deptmt, MAX(tblIncidents.Date) AS MaxDate
FROM            tblIncidents INNER JOIN
                         tblLocation ON tblIncidents.Location = tblLocation.Location
WHERE        (tblLocation.Active = 'True') AND (tblIncidents.Date <= @Date)
GROUP BY tblIncidents.Location, tblIncidents.Dept
ORDER BY tblIncidents.Location
0
 

Author Comment

by:BobRosas
ID: 38366120
Thank you for your quick response.  The code you gave me did resulted in the max date but only of the first location.  I was hoping to get the max date of all locations.  I'm sorry that my sample data did not show that.  I could still use some help. Thanks.
0
 
LVL 29

Expert Comment

by:QPR
ID: 38366155
how about
select distinct tblIncidents.location......

The problem is that you will be satisfying the inner join more than once
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:BobRosas
ID: 38366163
Thanks again for your help.  I added your code like this...
SELECT DISTINCT tblIncidents.Location, CASE WHEN tblIncidents.Location = 'Corporate' THEN Dept ELSE '' END AS Deptmt, MAX(tblIncidents.Date) AS MaxDate
FROM            tblIncidents INNER JOIN
                         tblLocation ON tblIncidents.Location = tblLocation.Location
WHERE        (tblLocation.Active = 'True') AND (tblIncidents.Date <= @Date)
GROUP BY tblIncidents.Location, tblIncidents.Dept
ORDER BY tblIncidents.Location

and the result is the same as my initial post.  It now shows all locations but it's also showing multiple dates instead of max dates.  Any more thoughts would be appreciated.
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 38366792
You are grouping by Dept, but you use a case statement in your select list. This is probably why you get the same location more than once. Start with this:
SELECT        tblIncidents.Location, CASE WHEN tblIncidents.Location = 'Corporate' THEN Dept ELSE '' END AS Deptmt, MAX(tblIncidents.Date) AS MaxDate
FROM            tblIncidents INNER JOIN
                         tblLocation ON tblIncidents.Location = tblLocation.Location
WHERE        (tblLocation.Active = 'True') AND (tblIncidents.Date <= @Date)
GROUP BY tblIncidents.Location, CASE WHEN tblIncidents.Location = 'Corporate' THEN Dept ELSE '' END
ORDER BY tblIncidents.Location

Open in new window

But this shows the max date for each Dptmt, but you need it for each Location. I think using the OVER clause can solve that, like this:
SELECT        tblIncidents.Location, CASE WHEN tblIncidents.Location = 'Corporate' THEN Dept ELSE '' END AS Deptmt, MAX(tblIncidents.Date) over (partition by tblIncidents.Location) AS MaxDate
FROM            tblIncidents INNER JOIN
                         tblLocation ON tblIncidents.Location = tblLocation.Location
WHERE        (tblLocation.Active = 'True') AND (tblIncidents.Date <= @Date)
GROUP BY tblIncidents.Location, CASE WHEN tblIncidents.Location = 'Corporate' THEN Dept ELSE '' END
ORDER BY tblIncidents.Location

Open in new window

Since I don't have your data I can't test it. But this seems to work:
create table #Incident(Location varchar(10),Department varchar(10), [Date] datetime)
insert into #Incident values('loc1','dep1.1','20120901')
insert into #Incident values('loc1','dep1.1','20120902')
insert into #Incident values('loc1','dep1.2','20120902')
insert into #Incident values('loc1','dep1.2','20120903')
insert into #Incident values('loc2','dep2.1','20120904')
insert into #Incident values('loc2','dep2.2','20120905')

select 
    Location,
    Department,
    MAX([date]) over (partition by Location) as maxdate
from 
    #Incident 

drop table #Incident 

Open in new window


More info about the OVER clause: http://msdn.microsoft.com/en-us/library/ms189461.aspx
0
 

Author Comment

by:BobRosas
ID: 38368549
Nicobo
Thank you so much!  Your initial code is working.  No over clause needed.  There is only one location with departments and I need the max date for each dept in that case.  I apologize for not being more clear.  In my code (without the CASE statement in the group by) it was showing all dates for every location even tho there were no depts.  Your code is just what I need.  Thank you again!
0
 

Author Closing Comment

by:BobRosas
ID: 38368564
This code gave me just want I needed.  Thank you so much!

SELECT DISTINCT tblIncidents.Location, CASE WHEN tblIncidents.Location = 'Corporate' THEN Dept ELSE '' END AS Deptmt, MAX(tblIncidents.Date) AS MaxDate
FROM            tblIncidents INNER JOIN
                         tblLocation ON tblIncidents.Location = tblLocation.Location
WHERE        (tblLocation.Active = 'True') AND (tblIncidents.Date <= @Date)
GROUP BY tblIncidents.Location, CASE WHEN tblIncidents.Location = 'Corporate' THEN Dept ELSE '' END
ORDER BY tblIncidents.Location
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Screencast - Getting to Know the Pipeline

810 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