Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Getting two results

Author  Topic 

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-03-26 : 15:32:55
When I highlight the GetDDS300 's0b' to run the query this should only give me the else results. Instead it is giving me both results. How can I change that to give me the one results? The where clauses are both different.

ALTER procedure [dbo].[GetDDS300] '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 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 ,

       Max(ddsrcpt) AS DDSReceipt,


       --getting ddsage of case when it was receipted into the dds

       DDSAge = Datediff(day,max(p.ddsrcpt), getdate()),

       --if 

      CASE WHEN MIN(p.Title) <> MAX(p.Title) THEN 'Concurr' Else Min(p.title) End As title
 
FROM pendingdds AS p

JOIN offices.dbo.OfficeCodes AS d 

  ON d.officecode = p.doc

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

--set ddsgroup gets ddsgroup from natdocfile2
SELECT @ddsgroup = DDSGroup FROM NatDocFile2 where doc = @doc

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

Age = Datediff(day,min(filedate), getdate()),
FileDate = min(FileDate),
n.ddsstate,

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 natdocfile2 n
on n.doc = p.fo
where p.doc=@doc and ( Datediff(DAY, filedate, Getdate()) > 300 )

Group By p.FO, p.Reg, p.DOC, CLMS, n.ddsstate, n.regionacronym
order by clms
End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-26 : 15:37:32
what does below return?

SELECT DDSGroup FROM NatDocFile2 where doc = 's0b'



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-03-26 : 15:38:22
You need another BEGIN/END pair

WHERE rn = 1

ORDER BY clms, age DESC
end

else
BEGIN
--set ddsgroup gets ddsgroup from natdocfile2
SELECT @ddsgroup = DDSGroup FROM NatDocFile2 where doc = @doc

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

Age = Datediff(day,min(filedate), getdate()),
FileDate = min(FileDate),
n.ddsstate,

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 natdocfile2 n
on n.doc = p.fo
where p.doc=@doc and ( Datediff(DAY, filedate, Getdate()) > 300 )

Group By p.FO, p.Reg, p.DOC, CLMS, n.ddsstate, n.regionacronym
order by clms
END
END
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-03-26 : 17:19:46
Thanks for the replies. I have left work for the day. I will try it in the morning. Question... why do I need another Begin and End when I already have one there in the else statement?
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-03-27 : 08:28:26
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)
--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 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()),

       --if 

      CASE WHEN MIN(p.Title) <> MAX(p.Title) THEN 'Concurr' Else Min(p.title) End As title
 
--WHEN  min(p.fo) <> max(p.fo) and 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()),

       --if 

      CASE WHEN MIN(p.Title) <> MAX(p.Title) THEN 'Concurr' Else Min(p.title) End As title
 
--WHEN  min(p.fo) <> max(p.fo) and 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
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-03-27 : 08:30:50
Visakh16 I tried this

SELECT DDSGroup FROM NatDocFile2 where doc = 's0b'

In place of the other select statement but it still only brought back the first statement where the else statement should have provided the results.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-27 : 11:57:30
you else and if block looks similar. can you explain whats difference you're implementing in them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-03-27 : 15:51:01
Thanks for your help I figured it out. Only thing is there another way to write the if statement where I am hard coding the PC2 and where doc='S0B' (I have it in bold below)? 


@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 DDS,

       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 ,
		n.ddscode,

       Max(ddsrcpt) AS DDSReceipt,


       --getting ddsage of case when it was receipted into the dds

       DDSAge = Datediff(day,max(p.ddsrcpt), getdate()),

       --if 

      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

GROUP BY p.fo, 

         p.Reg, 

         n.regionacronym,

        p.DOC, 
         p.CLMS, 
        n.ddsstate,
	n.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

else if @doc in ('PC2')
Begin


WITH Base AS (
SELECT p.DOC as DDS,

     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 ,
		n.ddscode,

       Max(ddsrcpt) AS DDSReceipt,


       --getting ddsage of case when it was receipted into the dds

       DDSAge = Datediff(day,max(p.ddsrcpt), getdate()),

       --if 

      CASE WHEN MIN(p.Title) <> MAX(p.Title) THEN 'Concurr' Else Min(p.title) End As title
 
from
(select *, row_number() over (Partition by ddsrcpt order by ddsrcpt) as rownum 
from pendingdds) p
inner join natdocfile2 n on n.doc = p.fo
where  p.doc='s0b'
GROUP BY p.fo, 

         p.Reg, 

         n.regionacronym,
         p.DOC, 
         p.CLMS, 
        n.ddsstate,
	n.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

else 
Begin


WITH Base AS (
SELECT p.DOC as DDS,

     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 ,
		n.ddscode,

       Max(ddsrcpt) AS DDSReceipt,


       --getting ddsage of case when it was receipted into the dds

       DDSAge = Datediff(day,max(p.ddsrcpt), getdate()),

       --if 

      CASE WHEN MIN(p.Title) <> MAX(p.Title) THEN 'Concurr' Else Min(p.title) End As title
 
from
(select *, row_number() over (Partition by ddsrcpt order by ddsrcpt) as rownum 
from pendingdds) p
inner join natdocfile2 n on n.doc = p.fo
where  p.doc=@doc

GROUP BY p.fo, 

         p.Reg, 

         n.regionacronym,
         p.DOC, 
         p.CLMS, 
        n.ddsstate,
		n.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
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2012-03-27 : 15:53:20
quote:
Originally posted by sunitabeck

You need a pair



SUNITA!!!!

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-03-28 : 10:50:30
I'm okay with what I have submitted for the last query. Just have to use it with the hardcoding in it.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-28 : 11:31:35
quote:
Originally posted by JJ297

I'm okay with what I have submitted for the last query. Just have to use it with the hardcoding in it.

why do you need to hardcode?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-03-28 : 15:00:56
Because my else statement doesn't work. It is only showing the first statement. Can't get it to work. Had to add the PC2 statement (hardcode) to get it to work as it wasn't coming up in the else statement. Any ideas? When I just put else in the statement I get both queries (results)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-28 : 15:03:59
how were you calling the proc? what value you passed for the parameter @doc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-03-28 : 19:39:39
Yes I'm passing @doc. I need to get doc='s0b'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-28 : 20:37:00
ok...then else wont be executed if below returns s0b

SELECT DDSGroup FROM NatDocFile2 where doc = 's0b'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-03-29 : 08:42:57
How can I get it to execute?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-29 : 12:13:31
why do you need to? as per your logic you need to execute it only if it returns PC2 isnt it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-04-04 : 09:06:44
Sorry for just getting back to you. If I got rid of PC2 then I want S0b to execute. It is excuting but from teh first query. It should come from the second query.

ALTER procedure [dbo].[GetDDSInfo]--'pc2'

@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 DDS,

       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 ,
		n.ddscode,

       Max(ddsrcpt) AS DDSReceipt,


       --getting ddsage of case when it was receipted into the dds

       DDSAge = Datediff(day,max(p.ddsrcpt), getdate()),

       --if 

      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

GROUP BY p.fo, 

         p.Reg, 

         n.regionacronym,

--		n.ddsgroup,
         p.DOC, 
         p.CLMS, 
        n.ddsstate,
		n.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


else 
Begin


WITH Base AS (
SELECT p.DOC as DDS,

     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 ,
		n.ddscode,

       Max(ddsrcpt) AS DDSReceipt,


       --getting ddsage of case when it was receipted into the dds

       DDSAge = Datediff(day,max(p.ddsrcpt), getdate()),

       --if 

      CASE WHEN MIN(p.Title) <> MAX(p.Title) THEN 'Concurr' Else Min(p.title) End As title
 
from
(select *, row_number() over (Partition by ddsrcpt order by ddsrcpt) as rownum 
from pendingdds) p
inner join natdocfile2 n on n.doc = p.fo
where  p.doc=@doc

GROUP BY p.fo, 

         p.Reg, 

         n.regionacronym,
         p.DOC, 
         p.CLMS, 
        n.ddsstate,
		n.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
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts
Posted - 2012-04-04 : 15:05:16
I got it to work by moving the query that belonged to S0B up top in front of the other query. Now I'm getting the correct results to appear.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-04-04 : 18:14:21
great...glad that you got it sorted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site