| Author |
 Topic  |
|
|
JJ297
Aged Yak Warrior
940 Posts |
 Posted - 03/26/2012 : 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
India
47069 Posts |
Posted - 03/26/2012 : 15:37:32
|
what does below return?
SELECT DDSGroup FROM NatDocFile2 where doc = 's0b'
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 03/26/2012 : 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 |
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 03/26/2012 : 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? |
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 03/27/2012 : 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
|
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 03/27/2012 : 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/27/2012 : 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/
|
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 03/27/2012 : 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
|
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 03/28/2012 : 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/28/2012 : 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/
|
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 03/28/2012 : 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) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/28/2012 : 15:03:59
|
how were you calling the proc? what value you passed for the parameter @doc?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 03/28/2012 : 19:39:39
|
| Yes I'm passing @doc. I need to get doc='s0b' |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/28/2012 : 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/
|
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 03/29/2012 : 08:42:57
|
| How can I get it to execute? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/29/2012 : 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/
|
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 04/04/2012 : 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
|
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 04/04/2012 : 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 04/04/2012 : 18:14:21
|
great...glad that you got it sorted
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Getting two results
