February 13, 2013 at 11:01 am
Trying to return just a single record for the max audit date for an agent. I thought this would do it:
select completed, Max(auditDate) from tblAudit_AuditSchedule where agentNumber = '54321' group by auditdate, completed HAVING auditDate = MAX(auditDate)
But I get:
02011-12-24 00:00:00
02012-11-07 00:00:00
02013-02-04 07:30:00
02013-02-13 00:00:00
02013-02-15 00:00:00
02013-02-20 00:00:00
12013-02-22 00:00:00
Not sure why the HAVING clause doesn't limit it to just the last record. Any help on what I am doing wrong would be appreciated.
Sean
February 13, 2013 at 11:45 am
Hi Sean
The MAX is applied to each group and since the auditdate column is in the GROUP BY clause you will get a group for each auditdate. This means that your HAVING clause is effectively just doing auditdate = auditdate and you will get a returned row for each.
If you remove the auditdate from you GROUP BY clause you will get up to 2 returned rows in your query, since you also have the completed column in your GROUP BY clause and this appears to be a bit flag.
I would say that you don't need to have a GROUP BY clause, but you will need to determine how you want the completed column handled, eg MAX or some other aggregate function.
I suspect that you are looking for the last audit record for the agent, so you could try something like
SELECT TOP 1 completed, auditdate
FROM tblAudit_AuditSchedule
WHERE agentNumber = '54321'
ORDER BY auditdate DESC
February 13, 2013 at 12:24 pm
SQL told me I couldn't have auditdate in the HAVING CLAUSE without it being in the GROUP BY CLAUSE which is the only reason I added it. And the MAX(auditdate) can't be in the WHERE because it is an aggregate, so I'm confused about how to do it.
February 13, 2013 at 12:30 pm
You need to remove the auditdate from the grouping and in your HAVING clause you need max(auditdate) = xxxx (depends on what you want it to equal?)
February 13, 2013 at 12:33 pm
infact looking again at your post you dont even need a HAVING clause. you just need the max
select Max(auditDate) from tblAudit_AuditSchedule where agentNumber = '54321'
if you need it per agent
select AgentNumber, Max(auditDate) from tblAudit_AuditSchedule group by AgentNumber
and if you need it per agent and competed status
select completed, AgentNumber, Max(auditDate) from tblAudit_AuditSchedule group by Completed, AgentNumber
February 13, 2013 at 12:52 pm
Sean Grebey (2/13/2013)
Trying to return just a single record for the max audit date for an agent. I thought this would do it:select completed, Max(auditDate) from tblAudit_AuditSchedule where agentNumber = '54321' group by auditdate, completed HAVING auditDate = MAX(auditDate)
But I get:
02011-12-24 00:00:00
02012-11-07 00:00:00
02013-02-04 07:30:00
02013-02-13 00:00:00
02013-02-15 00:00:00
02013-02-20 00:00:00
12013-02-22 00:00:00
Not sure why the HAVING clause doesn't limit it to just the last record. Any help on what I am doing wrong would be appreciated.
Sean
What are you trying to return? Without having DDL (CREATE TABLE statement), sample data (INSERT INTO statements), expected results based on sample data; all we can give you are shots in the dark.
February 13, 2013 at 12:56 pm
Let me explain it a little bit better. Every agentNumber will have dozens of audits, so for example:
agentNumber auditdate completed
54321 1/1/2013 1
54321 1/15/2013 1
54321 2/1/2013 0
12345 1/1/2013 1
12345 1/8/2013 1
12345 1/17/2013 1
I need to know the value of completed for each agentNumber on their Max(auditdate), i.e.
54321 0
12345 1
SO really all I need to get back is an agentNumber matched with if their last audit was completed. I don't even care what the last audit date is, just if it was completed or not.
February 13, 2013 at 12:57 pm
And thanks for the help by the way. Much appreciated.
February 13, 2013 at 1:00 pm
Animal Magic (2/13/2013)
infact looking again at your post you dont even need a HAVING clause. you just need the max
select Max(auditDate) from tblAudit_AuditSchedule where agentNumber = '54321'if you need it per agent
select AgentNumber, Max(auditDate) from tblAudit_AuditSchedule group by AgentNumberand if you need it per agent and competed status
select completed, AgentNumber, Max(auditDate) from tblAudit_AuditSchedule group by Completed, AgentNumber
Kind of the other way around, I need the completed value by agent only for their last auditdate. Thanks for the input though.
February 13, 2013 at 1:23 pm
There are a number of ways of doing this. Here's a few ways of writing essentially the same query.
SELECT completed, auditdate, a.agentNumber
FROM tblAudit_AuditSchedule a
INNER JOIN (
SELECT agentNumber, MAX(auditdate) maxdate
FROM tblAudit_AuditSchedule
GROUP BY agentNumber
) b ON a.agentNumber = b.agentNumber and a.auditdate = b.maxdate
-- or
SELECT completed, auditdate, agentNumber
FROM tblAudit_AuditSchedule a
WHERE auditdate = (
SELECT MAX(auditdate)
FROM tblAudit_AuditSchedule b
WHERE a.agentNumber = b.agentNumber
)
-- or
;with LastAuditDatePerAgent AS (
SELECT agentNumber, MAX(auditdate) maxdate
FROM tblAudit_AuditSchedule
GROUP BY agentNumber
)
SELECT completed, auditdate, a.agentNumber
FROM tblAudit_AuditSchedule a
INNER JOIN LastAuditDatePerAgent b ON a.agentNumber = b.agentNumber and a.auditdate = b.maxdate
The previous query I put in would have worked for a single agentNumber.
February 13, 2013 at 1:32 pm
mickyT (2/13/2013)
There are a number of ways of doing this. Here's a few ways of writing essentially the same query.
SELECT completed, auditdate, a.agentNumber
FROM tblAudit_AuditSchedule a
INNER JOIN (
SELECT agentNumber, MAX(auditdate) maxdate
FROM tblAudit_AuditSchedule
GROUP BY agentNumber
) b ON a.agentNumber = b.agentNumber and a.auditdate = b.maxdate
-- or
SELECT completed, auditdate, agentNumber
FROM tblAudit_AuditSchedule a
WHERE auditdate = (
SELECT MAX(auditdate)
FROM tblAudit_AuditSchedule b
WHERE a.agentNumber = b.agentNumber
)
-- or
;with LastAuditDatePerAgent AS (
SELECT agentNumber, MAX(auditdate) maxdate
FROM tblAudit_AuditSchedule
GROUP BY agentNumber
)
SELECT completed, auditdate, a.agentNumber
FROM tblAudit_AuditSchedule a
INNER JOIN LastAuditDatePerAgent b ON a.agentNumber = b.agentNumber and a.auditdate = b.maxdate
The previous query I put in would have worked for a single agentNumber.
Thank you, that got me what I needed.
February 13, 2013 at 11:13 pm
Forgive me but isn't this easily done as follows?
;WITH AuditDates (AgentID, AuditDate, Completed) AS (
SELECT 54321, '2013-01-01', 1
UNION ALL SELECT 54321,'2013-01-15', 1
UNION ALL SELECT 54321,'2013-02-01', 0
UNION ALL SELECT 12345,'2013-01-01', 1
UNION ALL SELECT 12345,'2013-01-08', 1
UNION ALL SELECT 12345,'2013-01-17', 1
)
SELECT AgentID, Completed
FROM (
SELECT AgentID, AuditDate, Completed
,n=ROW_NUMBER() OVER (PARTITION BY AgentID ORDER BY AuditDate DESC)
FROM AuditDates) a
WHERE n = 1
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply