Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Else statement not picking up.

Status
Not open for further replies.

nc297

Programmer
Apr 7, 2010
162
US
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

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

Code:
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
 
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.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
 
Just as a side not, please use the
Code:
 TGML tags when posting code. It makes it much easier to read :-)
 
I'm sorry about that. Here it is again.

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top