Help with Query

[nc297]

Here's some table information

This query:

SELECT
doc,
COUNT(*) AS cnt
FROM
(
Select
doc
FROM testpend p

Group By
doc, clms ) AS T
GROUP BY
doc



Produces this:


doc cnt
S0L 3
S11 2
S23 2



This query:



select doc, [250]=SUM([250]), [300]=SUM([300]), [350]=SUM([350]), [400]=SUM([400])
from
(
SELECT
doc,
[250] = case when [Days] > 250 then cnt else 0 end,
[300] = case when [days] > 300 then cnt else 0 end,
[350] = case when [days] > 350 then cnt else 0 end,
[400] = case when [days] > 400 then cnt else 0 end
from (
SELECT
doc, count(*) as cnt,
[days]=Datediff(Day, filedate, Getdate())
FROM testpend p
group by doc, Datediff(Day, filedate, Getdate())
) as a
)as b
group by doc



Produces this output:



doc 250 300 350 400
S0L 4 0 0 0
S11 2 2 2 2
S23 3 0 0 0




Since there are duplicate clms in the table but the title's are different then they should be counted as one pending claim not two pending claims



CREATE TABLE [dbo].[TestPend](
[fo] [varchar](3) NOT NULL,
[reg] [varchar](3) NULL,
[doc] [varchar](4) NOT NULL,
[clms] [char](9) NOT NULL,
[cos] [char](9) NOT NULL,
[FileDate] [datetime] NULL,
[DDSRcpt] [datetime] NULL,
[Title] [varchar](3) NOT NULL,
CONSTRAINT [PK_TestPend] PRIMARY KEY CLUSTERED
(
[fo] ASC,
[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






insert into TestPend
select '016', 'C', 'S0L', '589642', '589642', '05/17/2011', '08/18/2011', 'T16' union all
select '016', 'C', 'S0L', '589642', '589642', '05/17/2011', '08/18/2011', 'T2' union all
select '019', 'C', 'S0L', '549875', '549875', '06/17/2011', '12/22/2011', 'T2' union all
select '019', 'C', 'S23', '587456', '587456', '06/03/2011', '01/13/2012', 'T16' union all
select '019', 'C', 'S0L', '236542', '236542', '05/20/2011', '07/13/2011', 'T2' union all
select '196', 'C', 'S11', '145697', '145697', '11/24/2010', '09/16/2011', 'T16' union all
select '196', 'C', 'S11', '147756', '147756', '11/24/2010', '09/16/2011', 'T2' union all
select '196', 'C', 'S23', '456987', '456987', '06/08/2011', '06/08/2011', 'T16' union all
select '196', 'C', 'S23', '456987', '456987', '06/08/2011', '06/08/2011', 'T2'


How can I get this query:

select doc, [250]=SUM([250]), [300]=SUM([300]), [350]=SUM([350]), [400]=SUM([400])
from
(
SELECT
doc,
[250] = case when [Days] > 250 then cnt else 0 end,
[300] = case when [days] > 300 then cnt else 0 end,
[350] = case when [days] > 350 then cnt else 0 end,
[400] = case when [days] > 400 then cnt else 0 end
from (
SELECT
doc, count(*) as cnt,
[days]=Datediff(Day, filedate, Getdate())
FROM testpend p
group by doc, Datediff(Day, filedate, Getdate())
) as a
)as b
group by doc



Produces this output:



doc 250 300 350 400
S0L 3 0 0 0
S11 2 2 2 2
S23 3 0 0 0



Ex, S0L should come out as one count but it's coming out as two.

select '016', 'C', 'S0L', '589642', '589642', '05/17/2011', '08/18/2011', 'T16' union all
select '016', 'C', 'S0L', '589642', '589642', '05/17/2011', '08/18/2011', 'T2' union all