Else statement not picking up.

[nc297]

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

 

[gmmastros]

Let's remove the big queries and take a look at the logic.

DECLARE @ddsgroup varchar(3)
SELECT @ddsgroup = DDSGroup FROM NatDocFile2 where doc = @doc

if @doc = @ddsgroup
  Begin 

    -- Big Query Here
  end
ELSE
  begin
    SELECT @ddsgroup = DDSGroup FROM NatDocFile2 where doc = @doc

    if @doc = @ddsgroup
      begin
        -- Big query here
      end
end

The problem, as I see it, is that you are assigning the same value to @ddsgroup, and then doing the same check. Your code may be going in to the ELSE part, but then you repeat the conditions causing it to essentially do nothing.

Does this make sense?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nc297]

Thank you this does make sense. I revised it and now getting results from both queries.

ALTER procedure [dbo].[GetDDSInfo]--'s0b'
@doc varchar (3)

as

DECLARE @ddsgroup varchar(3)
--set ddsgroup gets ddsgroup from natdocfile2
SELECT @ddsgroup = DDSGroup FROM NatDocFile2 where doc = @doc

if @doc = @ddsgroup
Begin
WITH Base AS (

SELECT p.DOC as Doc,

p.Reg,

n.RegionAcronym,
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 ,
d.ddscode,
Max(ddsrcpt) AS DDSReceipt,


--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 offices.dbfficeCodes2 AS d
ON d.officecode = p.fo

JOIN natdocfile2 AS n
ON n.doc = p.doc

where n.ddsgroup=@ddsgroup

GROUP BY p.fo,
p.Reg,
n.regionacronym,
p.DOC,
p.CLMS,
n.ddsstate,
d.ddscode
),
--getting results produced by the from clause
Ranked AS (

SELECT *,
-- return only the first rank which is 1
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
end



Begin
Select p.DOC,
p.Reg,
FO,
CLMS,
n.regionacronym,
Age = Datediff(day,min(filedate), getdate()),
FileDate = min(FileDate),
n.ddsstate,
d.ddscode,
max(ddsrcpt) AS DDSReceipt,
DDSAge = Datediff(day,max(ddsrcpt), getdate()),
Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end
From
(select *, row_number() over (Partition by ddsrcpt order by ddsrcpt) as rownum
from pendingdds) p
join offices.dbo.officecodes2 d
on d.officecode = p.fo
join natdocfile2 n
on n.doc = p.fo
where p.doc=@doc
Group By p.FO, p.Reg, p.DOC, CLMS, n.ddsstate, n.regionacronym, d.ddscode
order by p.fo, age , DDSReceipt desc

End

 

[Ascalonian]

Just as a side not, please use the

 TGML tags when posting code. It makes it much easier to read :-)

 

[nc297]

I'm sorry about that. Here it is again.

ALTER procedure [dbo].[GetDDSInfo]--'s0b'
@doc varchar (3)

as

DECLARE @ddsgroup varchar(3)
--set ddsgroup gets ddsgroup from natdocfile2
SELECT @ddsgroup = DDSGroup FROM NatDocFile2 where doc = @doc

if @doc = @ddsgroup
Begin 
WITH Base AS (

SELECT p.DOC as Doc,

       p.Reg, 

     n.RegionAcronym,
       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 ,
        d.ddscode,
            Max(ddsrcpt) AS DDSReceipt,


       --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 offices.dbo.OfficeCodes2 AS d 
  ON d.officecode = p.fo

JOIN natdocfile2 AS n
  ON n.doc = p.doc

where  n.ddsgroup=@ddsgroup

GROUP BY p.fo, 
         p.Reg, 
         n.regionacronym,
         p.DOC, 
         p.CLMS, 
         n.ddsstate,
         d.ddscode
          ),
--getting results produced by the from clause
Ranked AS (

SELECT *, 
-- return only the first rank which is 1 
       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
end



Begin 
Select p.DOC, 
       p.Reg,
    FO,
      CLMS,
       n.regionacronym,
Age = Datediff(day,min(filedate), getdate()),
FileDate = min(FileDate),
n.ddsstate,
d.ddscode,
max(ddsrcpt) AS DDSReceipt,
    DDSAge = Datediff(day,max(ddsrcpt), getdate()),
    Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end
From 
(select *, row_number() over (Partition by ddsrcpt order by ddsrcpt) as rownum 
from pendingdds) p
join offices.dbo.officecodes2 d
on d.officecode = p.fo
join natdocfile2 n
on n.doc = p.fo
where p.doc=@doc
Group By p.FO, p.Reg, p.DOC, CLMS, n.ddsstate, n.regionacronym, d.ddscode
order by p.fo, age , DDSReceipt desc

End