What am I doing wrong?
I tried this and now it is giving me one result but just the top one. The else statement is not picking it up.
ALTER procedure [dbo].[TestDDS]--'s0b'
@doc varchar(3)
as
DECLARE @ddsgroup varchar(3)
SELECT @ddsgroup = DDSGroup FROM NatDocFile2 where doc = @doc
if @doc = @ddsgroup
Begin
WITH Base AS (
SELECT p.DOC as DDS,
p.Reg,
n.RegionAcronym,
n.ddsgroup,
p.FO,
p.CLMS,
--getting age of case when it was filed
DATEDIFF(DAY, Min(p.filedate), GETDATE()) AS Age,
Min(FileDate) AS FileDate,
n.ddsstate ,
--getting ddsage of case when it was receipted into the dds
DDSAge = Datediff(day,max(p.ddsrcpt), getdate()),
CASE WHEN MIN(p.Title) <> MAX(p.Title) THEN 'Concurr' Else
Min(p.title) End As title
FROM pendingdds AS p
JOIN natdocfile2 AS n
ON n.doc = p.doc
where n.ddsgroup=@ddsgroup AND ( Datediff(DAY, filedate, Getdate()) > 300 )
GROUP BY p.fo,
p.Reg,
n.regionacronym,
n.ddsgroup,
p.DOC,
p.CLMS,
n.ddsstate),
--getting results produced by the from clause
Ranked AS (
SELECT *,
-- return only the first rank which is 1
ROW_NUMBER() OVER(PARTITION BY clmssn ORDER BY FileDate DESC) AS rn
FROM Base)
SELECT *
FROM Ranked
WHERE rn = 1
ORDER BY clms, age DESC
end
ELSE
begin
SELECT @ddsgroup = DDSGroup FROM NatDocFile2 where doc = @doc
if @doc = @ddsgroup
begin
WITH Base AS (
SELECT p.DOC as DDS,
p.Reg,
n.RegionAcronym,
n.ddsgroup,
p.FO,
p.CLMS,
--getting age of case when it was filed
DATEDIFF(DAY, Min(p.filedate), GETDATE()) AS Age,
Min(FileDate) AS FileDate,
n.ddsstate ,
--getting ddsage of case when it was receipted into the dds
DDSAge = Datediff(day,max(p.ddsrcpt), getdate()),
CASE WHEN MIN(p.Title) <> MAX(p.Title) THEN 'Concurr' Else Min(p.title) End As title
FROM pendingdds AS p
JOIN natdocfile2 AS n
ON n.doc = p.doc
where p.doc=@doc
GROUP BY p.fo,
p.Reg,
n.regionacronym,
n.ddsgroup,
p.DOC,
p.CLMS,
n.ddsstate),
--getting results produced by the from clause
Ranked AS (
SELECT *,
-- return only the first rank which is 1
ROW_NUMBER() OVER(PARTITION BY clmssn ORDER BY FileDate DESC) AS rn
FROM Base)
SELECT *
FROM Ranked
WHERE rn = 1
ORDER BY clms, age DESC
end
end
I tried this and now it is giving me one result but just the top one. The else statement is not picking it up.
ALTER procedure [dbo].[TestDDS]--'s0b'
@doc varchar(3)
as
DECLARE @ddsgroup varchar(3)
SELECT @ddsgroup = DDSGroup FROM NatDocFile2 where doc = @doc
if @doc = @ddsgroup
Begin
WITH Base AS (
SELECT p.DOC as DDS,
p.Reg,
n.RegionAcronym,
n.ddsgroup,
p.FO,
p.CLMS,
--getting age of case when it was filed
DATEDIFF(DAY, Min(p.filedate), GETDATE()) AS Age,
Min(FileDate) AS FileDate,
n.ddsstate ,
--getting ddsage of case when it was receipted into the dds
DDSAge = Datediff(day,max(p.ddsrcpt), getdate()),
CASE WHEN MIN(p.Title) <> MAX(p.Title) THEN 'Concurr' Else
Min(p.title) End As title
FROM pendingdds AS p
JOIN natdocfile2 AS n
ON n.doc = p.doc
where n.ddsgroup=@ddsgroup AND ( Datediff(DAY, filedate, Getdate()) > 300 )
GROUP BY p.fo,
p.Reg,
n.regionacronym,
n.ddsgroup,
p.DOC,
p.CLMS,
n.ddsstate),
--getting results produced by the from clause
Ranked AS (
SELECT *,
-- return only the first rank which is 1
ROW_NUMBER() OVER(PARTITION BY clmssn ORDER BY FileDate DESC) AS rn
FROM Base)
SELECT *
FROM Ranked
WHERE rn = 1
ORDER BY clms, age DESC
end
ELSE
begin
SELECT @ddsgroup = DDSGroup FROM NatDocFile2 where doc = @doc
if @doc = @ddsgroup
begin
WITH Base AS (
SELECT p.DOC as DDS,
p.Reg,
n.RegionAcronym,
n.ddsgroup,
p.FO,
p.CLMS,
--getting age of case when it was filed
DATEDIFF(DAY, Min(p.filedate), GETDATE()) AS Age,
Min(FileDate) AS FileDate,
n.ddsstate ,
--getting ddsage of case when it was receipted into the dds
DDSAge = Datediff(day,max(p.ddsrcpt), getdate()),
CASE WHEN MIN(p.Title) <> MAX(p.Title) THEN 'Concurr' Else Min(p.title) End As title
FROM pendingdds AS p
JOIN natdocfile2 AS n
ON n.doc = p.doc
where p.doc=@doc
GROUP BY p.fo,
p.Reg,
n.regionacronym,
n.ddsgroup,
p.DOC,
p.CLMS,
n.ddsstate),
--getting results produced by the from clause
Ranked AS (
SELECT *,
-- return only the first rank which is 1
ROW_NUMBER() OVER(PARTITION BY clmssn ORDER BY FileDate DESC) AS rn
FROM Base)
SELECT *
FROM Ranked
WHERE rn = 1
ORDER BY clms, age DESC
end
end