I don't know why I'm receiving different counts:

This query gives me a count of 16 records that are over 300

    Select   p.doc,
COUNT(distinct CASE WHEN Datediff(DAY, filedate, Getdate()) > 250 THEN clms END) AS [250],
COUNT(distinct CASE WHEN Datediff(DAY, filedate, Getdate()) > 300 THEN clms END) AS [300],           
COUNT(distinct CASE WHEN Datediff(DAY, filedate, Getdate()) > 350 THEN clms END) AS [350],
COUNT(distinct CASE WHEN Datediff(DAY, filedate, Getdate()) > 400 THEN clms END) AS [400]
           
     FROM pendingdds p
join Offices.dbo.OfficeCodes d
on d.officecode = p.doc
join natdocfile n
on n.doc = p.fo

  where d.typecode='7' and d.reportsto='F03' and p.doc ='s09'
Group By p.doc


Now this query will list the clms and it gives me 17 clms.



Select  distinct  p.CLMS, p.DOC,
        p.Reg,
n.RegionAcronym,
        p.FO,

Age = Datediff(day,max(p.filedate), getdate()),
    FileDate = max(FileDate),
o.mailingaddressstate as DDS,
o.ddsofficecode as DDSCode,
Min(ddsrcpt) AS DDSReceipt,
    DDSAge = Datediff(day,min(p.ddsrcpt), getdate()),
    Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end
From pendingdds p
 join Offices.dbo.OfficeCodes d
on d.officecode = p.doc
join natdocfile n
on n.doc = p.fo
join offices.dbo.doorsinfo o
on o.officecode = p.fo
where p.doc ='s09'--@doc
AND ( Datediff(DAY, filedate, Getdate()) > 300 )
Group By  p.fo, p.Reg, n.regionacronym,p.DOC, p.CLMS, o.ddsofficecode,
o.mailingaddressstate
order by clms

Can you see what I am doing wrong in the first query to get one less number?  Do I have it set up right to get the counts?
 

In your second select statement you're joining with 1 extra table (DoorsInfo) which will affect the results. Try commenting out last join with that table (and related fields in select and group by) and see if the results will match.

PluralSight Learning Library

Actually that gave me the same results.

My problem is I have two clms that have the same number but the FO, Filedate and Title are different.  So how could I add to the statement below to say if clms is the same and FO, Fieldate and Title are different take the min(filedate) (which would only show one record) and call that concurr?

Select    p.CLMS, p.DOC,
        p.Reg,
        p.FO,

Age = Datediff(day,max(p.filedate), getdate()),
    FileDate = min(FileDate),
o.mailingaddressstate as DDS,
o.ddsofficecode as DDSCode,
Min(ddsrcpt) AS DDSReceipt,
    DDSAge = Datediff(day,min(p.ddsrcpt), getdate()),
    Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end
From pendingdds p
 join Offices.dbo.OfficeCodes d
on d.officecode = p.doc
join offices.dbo.doorsinfo o
on o.officecode = p.fo
where p.doc ='s09'
AND ( Datediff(DAY, filedate, Getdate()) > 300 )
Group By p.fo,  p.Reg, p.DOC, p.CLMS, o.ddsofficecode,
o.mailingaddressstate
order by clm

What is your SQL Server version? I think you can solve your problem using ROW_NUMBER() function, but it's hard to suggest anything as you didn't provide tables and insert statements to understand your problem better.

PluralSight Learning Library

Take a look at clms - 125987

I would like for only one record to show (out of the two of 125987).  The one I want is the filedate of 05/06/2011.

I tried min(clms) = max(clms) and  min(FO) <> max(FO) and min(Filedate) <> max(filedate) and Title <> title then 'concurr' else title end

This didn't work as it still listed both records.


Here's all of the info:

CREATE TABLE [dbo].[DoorsInfo](
    
    [DDSOfficeCode] [varchar](255) NULL,
    
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


insert into DoorsInfo
select 'S09' union all
select 'S08'


CREATE TABLE [dbo].[natdocfileb](
    
    [RegionAcronym] [char](3) NULL,
    
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

insert into natdocfileb
select 'PHI' union all
select 'BOS'


CREATE TABLE [dbo].[OfficeCodes](
    [OfficeCode] [char](3) NOT NULL,
    
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

insert into officecodes
select '029' union all
select '268' union all
select 'B68' union all
select 'C80' union all
select '001' union all
select '002' union all
select '003' union all
select '009'


CREATE TABLE [dbo].[TestData](
    [fo] [varchar](3) NOT NULL,
    [reg] [varchar](3) NULL,
    [doc] [varchar](4) NOT NULL,
    [clms] [char](6) NOT NULL,
    [cos] [char](6) NOT NULL,
    [FileDate] [datetime] NULL,
    [DDSRcpt] [datetime] NULL,
    [Title] [varchar](3) NOT NULL,
 CONSTRAINT [PK_testdata] 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 testdata
select '029', 'c', 's09', '129698', '129698', '03/24/2011', '03/24/2011', 'T2' union all
select 'C80', 'c', 's09', '129698', '129698', '03/24/2011', '03/25/2011', 'T16' union all
select '268', 'c', 's09', '005747', '005747', '06/20/2011', '06/20/2011', 'T16' union all
select '268', 'c', 's09', '005747', '005747', '06/20/2011', '06/20/2011', 'T2' union all
select 'C80', 'c', 's09', '698745', '698745', '05/11/2011', '05/12/2011', 'T16' union all
select 'B68', 'c', 's09', '978456', '978456', '06/03/2011', '01/18/2012', 'T16' union all
select '029', 'c', 's09', '125987', '125987', '05/05/2011', '06/10/2011', 'T2' union all
select '268', 'c', 's09', '125987', '125987', '05/06/2011', '06/10/2011', 'T16'







Here's the query:



Select p.DOC,
        p.Reg,
n.RegionAcronym,
        p.FO,
      p.CLMS,
    
--getting age of case when it was filed    
Age = Datediff(day,max(p.filedate), getdate()),
    FileDate = max(FileDate),
o.mailingaddressstate as DDS,
o.ddsofficecode as DDSCode,
Min(ddsrcpt) AS DDSReceipt,
--getting ddsage of case when it was receipted into the dds
    DDSAge = Datediff(day,min(p.ddsrcpt), getdate()),
--if
    Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end


From testdata p
 join Offices.dbo.OfficeCodes d
on d.officecode = p.doc
join natdocfile n
on n.doc = p.fo
join offices.dbo.doorsinfo o
on o.officecode = p.fo
where p.doc ='s09'--@doc
  AND ( Datediff(DAY, filedate, Getdate()) > 300 )
Group By p.fo, p.Reg, n.regionacronym,p.DOC, p.CLMS, o.ddsofficecode,
o.mailingaddressstate
order by clms,  age desc



 

Markros you were right someone helped me with this and used row_number.

;WITH Base AS (
SELECT p.DOC,
       p.Reg,
             p.FO,
       p.CLMS,
       --getting age of case when it was filed   
       DATEDIFF(DAY, MAX(p.filedate), GETDATE()) AS Age,
       MAX(FileDate) AS FileDate,
       
       MIN(ddsrcpt) AS DDSReceipt,
       --getting ddsage of case when it was receipted into the dds
       DDSAge = Datediff(day,min(p.ddsrcpt), getdate()),
       --if
       CASE WHEN MIN(p.Title) <> MAX(p.Title) THEN 'Concurr' ELSE MIN(p.Title) END AS title
FROM testdata AS p
JOIN OfficeCodes AS d
  ON d.officecode = p.doc
--JOIN natdocfileb AS n
--  ON n.doc = p.fo
--JOIN DoorsInfo AS o
--  ON o.officecode = p.fo
WHERE p.doc = 's09'--@doc
  AND (DATEDIFF(DAY, filedate, GETDATE()) > 300 )
GROUP BY p.fo,
         p.Reg,
         --n.regionacronym,
         p.DOC,
         p.CLMS,
         
         ),
Ranked AS (
SELECT *,
       ROW_NUMBER() OVER(PARTITION BY clms ORDER BY FileDate DESC) AS rn
FROM Base)
SELECT *
FROM Ranked
WHERE rn = 1
ORDER BY clms, age DESC;

Could you please explain what row_number and ranked are doing in the query?

;WITH Base AS (
SELECT p.DOC,
       p.Reg,
             p.FO,
       p.CLMS,
       --getting age of case when it was filed   
       DATEDIFF(DAY, MAX(p.filedate), GETDATE()) AS Age,
       MAX(FileDate) AS FileDate,
       
       MIN(ddsrcpt) AS DDSReceipt,
       --getting ddsage of case when it was receipted into the dds
       DDSAge = Datediff(day,min(p.ddsrcpt), getdate()),
       --if
       CASE WHEN MIN(p.Title) <> MAX(p.Title) THEN 'Concurr' ELSE MIN(p.Title) END AS title
FROM testdata AS p
JOIN OfficeCodes AS d
  ON d.officecode = p.doc


WHERE p.doc = 's09'
  AND (DATEDIFF(DAY, filedate, GETDATE()) > 300 )
GROUP BY p.fo,
         p.Reg,
        
         p.DOC,
         p.CLMS,
         
         ),
Ranked AS (
SELECT *,
       ROW_NUMBER() OVER(PARTITION BY clms ORDER BY FileDate DESC) AS rn
FROM Base)
SELECT *
FROM Ranked
WHERE rn = 1
ORDER BY clms, age DESC;