Help with Query

[nc297]

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.dbfficeCodes 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.dbfficeCodes 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?

 

[markros]

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

 

[nc297]

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.dbfficeCodes 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

 

[markros]

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

 

[nc297]

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.dbfficeCodes 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

 

[nc297]

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;

 

[nc297]

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;