Select  doc,
	count(doc) as cnt,
        CLMS,
	Age = Datediff(day,(filedate), getdate()),
	FileDate,
        Max(ddsrcpt) AS ddsrcpt,
	DDSAge = Datediff(day,max(ddsrcpt), getdate()),
	Title = case when min(Title) <> max(Title) then 'Concurr' else min(Title) end
from testa
where doc in ('S09') 
Group By doc, clms, filedate
order by age asc

Doc       cnt
S09        5

Doc    cnt      CLMS            Age      FileDate       DDSrcpt      DDSage    title
S09	1	879680   	252	03/31/2011	05/31/2011	191	T16
S09	2	325698   	335	01/07/2011	11/23/2011	15	Concurr
S09	2	222489   	420	10/14/2010	10/23/2011	46	Concurr
S09	1	569489   	427	10/07/2010	10/26/2011	43	T16
S09	1	156489   	435	09/29/2010	08/31/2011	99	T16

CREATE TABLE [dbo].[TestA](
		[doc] [varchar](4) NOT NULL,
	[clms] [char](9) NOT NULL,
	[cos] [char](9) NOT NULL,
	[FileDate] [char](30) NULL,
	[DDSRcpt] [char](30) NULL,
	[Title] [varchar](3) NOT NULL,
 CONSTRAINT [PK_PendingDDS_1] PRIMARY KEY CLUSTERED 
(
	
	[clms] ASC,
	[cos] ASC,
	[Title] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


[code]
insert into TestA
select 'S09', '222489', '222489', '10/14/2010', '10/23/2011', 'T16' union all
select 'S09', '222489', '222489', '10/14/2010', '02/22/2011', 'T2' union all
select 'S09', '569489', '569489', '10/07/2010', '10/26/2011', 'T16' union all
select 'S09', '156489', '156489', '09/29/2010', '08/31/2011', 'T16' union all
select 'S09', '879680', '879680', '03/31/2011', '05/31/2011', 'T16' union all
select 'S09', '325698', '325698', '01/07/2011', '11/23/2011', 'T16' union all
select 'S09', '325698', '325698', '01/07/2011', '01/11/2011', 'T2'

SELECT 
    doc, 
    COUNT(*) AS cnt
FROM
    (
        Select  
            doc
        from 
            testa
        where 
            doc in ('S09') 
        Group By 
            doc, clms, filedate
    ) AS T
GROUP BY 
    doc

quote:

---

Originally posted by JJ297

Thanks so much. What's the difference between count(doc) and your count(*) I know yours came out right but why?

---

SELECT 
    doc, 
    COUNT(*) AS cnt
FROM
    (
        Select  
            doc
        from 
            testa
        where 
            doc in ('S09') 
        Group By 
            doc, clms, filedate
    ) AS T
GROUP BY 
    doc

quote:

---

Originally posted by JJ297

One last thing could you explain the query to me please:

SELECT 
    doc, 
    COUNT(*) AS cnt
FROM
    (
        Select  
            doc
        from 
            testa
        where 
            doc in ('S09') 
        Group By 
            doc, clms, filedate
    ) AS T
GROUP BY 
    doc

---