Tough MAX type grouping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Tough MAX type grouping

Author

Topic

Humate
Posting Yak Master

101 Posts

Posted - 2008-08-27 : 04:56:49

Hi All,

I have a query that has been working fine until recently when I needed to make a change.

I have a group of client records in one table, and processes relating to these records in another table.

My previous query:

Select Ref, max(nullif(case when ProcessID = 'p001' then 'ProcessDate' else '' end, '')) as Process 1

From Clientrecords left outer join Processes on Clientrecords.ref = Processes.ref

Group by Ref

This has worked fine so far, but when I make the change to look at 2 possible processes, my query will return the maximum process ID, not the maximum date (e.g. process 1 can have a more recent date than process 2).

Select Ref, max(nullif(case when ProcessID = 'p001' or ProcessID = 'p002' then 'ProcessDate' else '' end, '')) as InitialProcessStage

From Clientrecords left outer join Processes on Clientrecords.ref = Processes.ref

Group by Ref

How do I change the above query to return the max 'ProcessDate' for Process 1 or 2, rather than than the date of the max process?

Hope that makes sense and thanks in advance.
Humate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 05:19:35

include process field also in group by

Humate
Posting Yak Master

101 Posts

Posted - 2008-08-27 : 05:38:52

Thanks for the reply. Adding to the group by duplicates my entries per reference - unless i'm doing it wrong of course.

I ideally wanted just one row only for each reference, with the maximum date of either process 1 or process 2 in the next column (these process may or may not exist yet).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 05:59:07

please provide some sample data and illustrate what you want.

Humate
Posting Yak Master

101 Posts

Posted - 2008-08-27 : 07:16:54

quote:
Originally posted by visakh16

please provide some sample data and illustrate what you want.

OK, I think this is what you need?



declare @t table
(
REF int,
ProcessID int,
ProcessDate datetime
)
INSERT INTO @t VALUES (1234,0001,'02/12/2008')
INSERT INTO @t  VALUES (1234,0002,'02/10/2008')
INSERT INTO @t VALUES (1235,0001,'02/10/2008')
INSERT INTO @t  VALUES (1235,0002,'11/15/2008')

----
declare @s table
(
REF int
)
INSERT INTO @s VALUES (1234)
INSERT INTO @s VALUES (1235)
INSERT INTO @s VALUES (1236)
INSERT INTO @s VALUES (1237)

----

select s.ref,  max(nullif(case when t.processid = '0001' or t.processid= '0002' then t.Processdate ELSE '' END, '')) as ProcessDate from 
@s left outer join @t on @s.ref = @t.ref
group by s.ref

--Here I would expect to see these results, note reference 1234 returns the maximum process date, prcoess 0001.

Ref-------------Date

1234-----------'02/12/2008'
1235-----------'11/15/2008'
1236-------------Null
1237-------------Null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 07:27:00

[code]select s.REF,t.MaxDate
from @s s
left join (select REF,MAX(ProcessDate) AS MaxDate
FROM @t
GROUP BY REF) t
ON t.REF=s.REF[/code]

Humate
Posting Yak Master

101 Posts

Posted - 2008-08-27 : 08:07:43

I think we are getting nearer. How would I specify that the MAX(ProcessDate) has to meet the criteria of processid = 'p001' or 'p002'?

There are many more processes in the table, into the hundreds, so if I just use max it will return the most recent of any of these processes. For this particular column, I just want to filter process 1 and 2.

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources