swaggrK
asked on
Stored Procedure resultset
I have inherited the following stored procedure and I need to modify it. The result set is a count of certain types of procedures done by each month by doctors. It provides the name of doctor, current year, total for this year, total from last year and the total from two years ago.
The problem is, "if a doctor has no procedures THIS YEAR then their name does not appear in the result set."
I need them to show, with a zero for the given month, regardless.
The problem is, "if a doctor has no procedures THIS YEAR then their name does not appear in the result set."
I need them to show, with a zero for the given month, regardless.
ALTER PROCEDURE [Physician].[selectYearlyPhysician]
@FacilityID int,
@Year int,
@CaseTypeID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @PhysicianName VARCHAR(100)
DECLARE @PhysicianID int
DECLARE @TempPhysicianID int
DECLARE @TempSpecialtyID int
DECLARE @Specialty VARCHAR(100)
DECLARE @SpecialtyID int
DECLARE @SortOrder int
DECLARE @TempCaseTypeID int
DECLARE @Count int
DECLARE @Month int
DECLARE @1YearAgo int
DECLARE @2YearAgo int
DECLARE @PreviousYearFlag bit
DECLARE @YearUtilization TABLE
(
Physician VARCHAR(100),
PhysicianID int,
Specialty VARCHAR(100),
SpecialtyID int,
SortOrder int,
CaseTypeID int,
Jan int,
Feb int,
March int,
April int,
May int,
June int,
July int,
Aug int,
Sept int,
Oct int,
Nov int,
Dec int,
OneYearAgo int,
TwoYearAgo int
)
IF @CaseTypeID = 99
BEGIN
DECLARE PysUtil_Cursor CURSOR FOR
SELECT
Substring(ISNULL(Physician.FirstName,''),1,1) + '. ' + ISNULL(Physician.LastName,'')
as PhysicianName,
a.PhysicianID, --Specialty.Description,
Alias,
a.SpecialtyID,
SortOrder,
--a.CaseTypeID,
99 as CaseTypeID,
Month,
sum(Count) as count
from PhysicianUtilization a inner join
Physician on
a.PhysicianID= Physician.PhysicianID
inner join
--PhysicianUtilization.SpecialtyID = Specialty.SpecialtyID
Specialty on a.SpecialtyID=Specialty.SpecialtyID
-- SpecialtyAliasTranslation on
-- SpecialtyAliasTranslation.SpecialtyID = a.SpecialtyID inner join
inner join SpecialtyAlias on
SpecialtyAlias.SpecialtyAliasID = Specialty.SpecialtyAliasID
where
a.FacilityID = @FacilityID and
a.Year = @Year and
(a.CaseTypeID = 1 OR a.CaseTypeID = 2 OR a.CaseTypeID = 3)
--and (@Year >= 2015 and a.FacilityID not in (10111))
group by
a.PhysicianID,
Physician.FirstName,
Physician.LastName,
a.SpecialtyID,
SortOrder,
Month,
Alias
order by a.PhysicianID
END
ELSE IF @CaseTypeID = 2
BEGIN
DECLARE PysUtil_Cursor CURSOR FOR
SELECT
-- Physician.FirstName + ' ' + Physician.LastName
-- as PhysicianName,
-- a.PhysicianID, --Specialty.Description,
-- case
-- WHEN CaseTypeID = 3 then
-- 'PAIN'
-- ELSE
-- Alias
-- END
-- as Alias,
-- a.SpecialtyID,
-- case
-- WHEN CaseTypeID = 3 then
-- 5
-- ELSE
-- SortOrder
-- END
-- as SortOrder,
-- CaseTypeID,
-- Month,
-- Count
Substring(ISNULL(Physician.FirstName,''),1,1) + '. ' + ISNULL(Physician.LastName,'') +
CASE
WHEN CaseTypeID = 3 then
' - PAIN'
ELSE
''
END
as PhysicianName,
a.PhysicianID, --Specialty.Description,
Alias,
a.SpecialtyID,
SortOrder,
CaseTypeID,
Month
,sum(Count) as Count
from PhysicianUtilization a inner join
Physician on a.PhysicianID= Physician.PhysicianID inner join
Specialty on a.SpecialtyID=Specialty.SpecialtyID
-- SpecialtyAliasTranslation on
-- SpecialtyAliasTranslation.SpecialtyID = a.SpecialtyID inner join
inner join
SpecialtyAlias on
SpecialtyAlias.SpecialtyAliasID = Specialty.SpecialtyAliasID
where
a.FacilityID = @FacilityID
and a.Year = @Year --Kevin deletion
-- and a.Year IN (@Year, @Year-1) --Kevin addition
and (a.CaseTypeID = 2 or a.CaseTypeID = 3)
--and (@Year >= 2015 and a.FacilityID not in (10111))
group by Physician.FirstName,Physician.LastName,
a.PhysicianID,
Alias,
a.SpecialtyID,
SortOrder,
CaseTypeID,
Month
order by a.PhysicianID
END
ELSE
BEGIN
DECLARE PysUtil_Cursor CURSOR FOR
SELECT
Substring(ISNULL(Physician.FirstName,''),1,1) + '. ' + ISNULL(Physician.LastName,'') as PhysicianName,
a.PhysicianID, --Specialty.Description,
Alias,
a.SpecialtyID,
SortOrder,
CaseTypeID,
Month,
sum(Count) as Count
from PhysicianUtilization a inner join
Physician on a.PhysicianID= Physician.PhysicianID inner join
Specialty on a.SpecialtyID=Specialty.SpecialtyID
-- SpecialtyAliasTranslation on
-- SpecialtyAliasTranslation.SpecialtyID = a.SpecialtyID inner join
inner join
SpecialtyAlias on
SpecialtyAlias.SpecialtyAliasID = Specialty.SpecialtyAliasID
where
a.FacilityID = @FacilityID
and a.Year = @Year
and (a.CaseTypeID = @CaseTypeID)
--and (@Year >= 2015 and a.FacilityID not in (10111))
group by Physician.FirstName,Physician.LastName,
a.PhysicianID,
Alias,
a.SpecialtyID,
SortOrder,
CaseTypeID,
Month
order by a.PhysicianID
END
OPEN PysUtil_Cursor;
FETCH NEXT FROM PysUtil_Cursor into @PhysicianName, @PhysicianID, @Specialty, @SpecialtyID, @SortOrder,@CaseTypeID, @Month, @Count;
WHILE @@FETCH_STATUS = 0
BEGIN
if @TempPhysicianID = @PhysicianID and @TempSpecialtyID = @SpecialtyID and @TempCaseTypeID = @CaseTypeID
BEGIN
if @Month =1
update @YearUtilization set Jan = @Count WHERE PhysicianID =@PhysicianID and SpecialtyID=@SpecialtyID and CaseTypeID = @CaseTypeID
else if @Month =2
update @YearUtilization set Feb = @Count WHERE PhysicianID =@PhysicianID and SpecialtyID=@SpecialtyID and CaseTypeID = @CaseTypeID
else if @Month =3
update @YearUtilization set March = @Count WHERE PhysicianID =@PhysicianID and SpecialtyID=@SpecialtyID and CaseTypeID = @CaseTypeID
else if @Month =4
update @YearUtilization set April = @Count WHERE PhysicianID =@PhysicianID and SpecialtyID=@SpecialtyID and CaseTypeID = @CaseTypeID
else if @Month =5
update @YearUtilization set May = @Count WHERE PhysicianID =@PhysicianID and SpecialtyID=@SpecialtyID and CaseTypeID = @CaseTypeID
else if @Month =6
update @YearUtilization set June = @Count WHERE PhysicianID =@PhysicianID and SpecialtyID=@SpecialtyID and CaseTypeID = @CaseTypeID
else if @Month =7
update @YearUtilization set July = @Count WHERE PhysicianID =@PhysicianID and SpecialtyID=@SpecialtyID and CaseTypeID = @CaseTypeID
else if @Month =8
update @YearUtilization set Aug = @Count WHERE PhysicianID =@PhysicianID and SpecialtyID=@SpecialtyID and CaseTypeID = @CaseTypeID
else if @Month =9
update @YearUtilization set Sept = @Count WHERE PhysicianID =@PhysicianID and SpecialtyID=@SpecialtyID and CaseTypeID = @CaseTypeID
else if @Month =10
update @YearUtilization set Oct = @Count WHERE PhysicianID =@PhysicianID and SpecialtyID=@SpecialtyID and CaseTypeID = @CaseTypeID
else if @Month =11
update @YearUtilization set Nov = @Count WHERE PhysicianID =@PhysicianID and SpecialtyID=@SpecialtyID and CaseTypeID = @CaseTypeID
else if @Month =12
update @YearUtilization set Dec = @Count WHERE PhysicianID =@PhysicianID and SpecialtyID=@SpecialtyID and CaseTypeID = @CaseTypeID
END
ELSE
BEGIN
if @Month =1
insert @YearUtilization (Physician, PhysicianID, Specialty, SpecialtyID, SortOrder,CaseTypeID, Jan) values(@PhysicianName, @PhysicianID,@Specialty,@SpecialtyID, @SortOrder, @CaseTypeID,@Count)
else if @Month =2
insert @YearUtilization (Physician, PhysicianID, Specialty, SpecialtyID, SortOrder,CaseTypeID, Feb) values(@PhysicianName, @PhysicianID,@Specialty,@SpecialtyID, @SortOrder, @CaseTypeID,@Count)
else if @Month =3
insert @YearUtilization (Physician, PhysicianID, Specialty, SpecialtyID, SortOrder,CaseTypeID, March) values(@PhysicianName, @PhysicianID,@Specialty,@SpecialtyID, @SortOrder, @CaseTypeID,@Count)
else if @Month =4
insert @YearUtilization (Physician, PhysicianID, Specialty, SpecialtyID, SortOrder,CaseTypeID, April) values(@PhysicianName, @PhysicianID,@Specialty,@SpecialtyID, @SortOrder, @CaseTypeID,@Count)
else if @Month =5
insert @YearUtilization (Physician, PhysicianID, Specialty, SpecialtyID, SortOrder,CaseTypeID, May) values(@PhysicianName, @PhysicianID,@Specialty,@SpecialtyID, @SortOrder, @CaseTypeID,@Count)
else if @Month =6
insert @YearUtilization (Physician, PhysicianID, Specialty, SpecialtyID, SortOrder, CaseTypeID, June) values(@PhysicianName, @PhysicianID,@Specialty,@SpecialtyID, @SortOrder, @CaseTypeID,@Count)
else if @Month =7
insert @YearUtilization (Physician, PhysicianID, Specialty, SpecialtyID, SortOrder, CaseTypeID, July) values(@PhysicianName, @PhysicianID,@Specialty,@SpecialtyID, @SortOrder, @CaseTypeID,@Count)
else if @Month =8
insert @YearUtilization (Physician, PhysicianID, Specialty, SpecialtyID, SortOrder, CaseTypeID, Aug) values(@PhysicianName, @PhysicianID,@Specialty,@SpecialtyID, @SortOrder, @CaseTypeID,@Count)
else if @Month =9
insert @YearUtilization (Physician, PhysicianID, Specialty, SpecialtyID, SortOrder, CaseTypeID, Sept) values(@PhysicianName, @PhysicianID,@Specialty,@SpecialtyID, @SortOrder, @CaseTypeID,@Count)
else if @Month =10
insert @YearUtilization (Physician, PhysicianID, Specialty, SpecialtyID, SortOrder, CaseTypeID, Oct) values(@PhysicianName, @PhysicianID,@Specialty,@SpecialtyID, @SortOrder, @CaseTypeID,@Count)
else if @Month =11
insert @YearUtilization (Physician, PhysicianID, Specialty, SpecialtyID, SortOrder, CaseTypeID, Nov) values(@PhysicianName, @PhysicianID,@Specialty,@SpecialtyID, @SortOrder, @CaseTypeID,@Count)
else if @Month =12
insert @YearUtilization (Physician, PhysicianID, Specialty, SpecialtyID, SortOrder, CaseTypeID, Dec) values(@PhysicianName, @PhysicianID,@Specialty,@SpecialtyID, @SortOrder, @CaseTypeID,@Count)
IF @CaseTypeID=99
BEGIN
Select @1YearAgo=sum(Count) from PhysicianUtilizationYearTotal where Year=@Year-1 and FacilityID=@FacilityID and PhysicianID=@PhysicianID and (CaseTypeID=1 or CaseTypeID=2 or CaseTypeID=3) and SpecialtyID=@SpecialtyID
Select @2YearAgo=sum(Count) from PhysicianUtilizationYearTotal where Year=@Year-2 and FacilityID=@FacilityID and PhysicianID=@PhysicianID and (CaseTypeID=1 or CaseTypeID=2 or CaseTypeID=3) and SpecialtyID=@SpecialtyID
END
ELSE IF @CaseTypeID=2
BEGIN
Select @1YearAgo=sum(Count) from PhysicianUtilizationYearTotal where Year=@Year-1 and FacilityID=@FacilityID and PhysicianID=@PhysicianID and (CaseTypeID=2) and SpecialtyID=@SpecialtyID
Select @2YearAgo=sum(Count) from PhysicianUtilizationYearTotal where Year=@Year-2 and FacilityID=@FacilityID and PhysicianID=@PhysicianID and (CaseTypeID=2) and SpecialtyID=@SpecialtyID
END
ELSE
BEGIN
Select @1YearAgo=Count from PhysicianUtilizationYearTotal where Year=@Year-1 and FacilityID=@FacilityID and PhysicianID=@PhysicianID and CaseTypeID=@CaseTypeID and SpecialtyID=@SpecialtyID
Select @2YearAgo=Count from PhysicianUtilizationYearTotal where Year=@Year-2 and FacilityID=@FacilityID and PhysicianID=@PhysicianID and CaseTypeID=@CaseTypeID and SpecialtyID=@SpecialtyID
END
update @YearUtilization set OneYearAgo = @1YearAgo, TwoYearAgo = @2YearAgo WHERE PhysicianID = @PhysicianID and SpecialtyID=@SpecialtyID and CaseTypeID = @CaseTypeID
SET @1YearAgo = null
SET @2YearAgo = null
SET @TempPhysicianID = @PhysicianID
SET @TempSpecialtyID = @SpecialtyID
SET @TempCaseTypeID = @CaseTypeID
END
FETCH NEXT FROM PysUtil_Cursor into @PhysicianName, @PhysicianID, @Specialty, @SpecialtyID, @SortOrder,@CaseTypeID, @Month, @Count;
END;
CLOSE PysUtil_Cursor;
DEALLOCATE PysUtil_Cursor;
select
FacilityID=@FacilityID,
Physician ,
a.PhysicianID,
Specialty,
SpecialtyID,
SortOrder,
CaseTypeID,
ISNULL(Jan,0) as Jan,
ISNULL(Feb,0) as Feb,
ISNULL(March,0) as March,
ISNULL(April,0) as April,
ISNULL(May,0) as May,
ISNULL(June,0) as June,
ISNULL(July,0) as July,
ISNULL(Aug,0) as Aug,
ISNULL(Sept,0) as Sept,
ISNULL(Oct,0) as Oct,
ISNULL(Nov,0) as Nov,
ISNULL(Dec,0) as Dec,
Year=@Year,
OneYearAgo,
TwoYearAgo,
YTD=(ISNULL(Jan,0)+ISNULL(Feb,0)+ISNULL(March,0)+ISNULL(April,0)+ISNULL(May,0)+ISNULL(June,0)+ISNULL(July,0)+ISNULL(Aug,0)+ISNULL(Sept,0)+ISNULL(Oct,0)+ISNULL(Nov,0)+ISNULL(Dec,0))
from @YearUtilization a inner join Physician b on a.PhysicianID = b.PhysicianID order by SortOrder, LastName
END
Last Comment
ASKER
@Jim Horn...I did the following, but no change. This is the query that returns the result set.
Changed to left join:
Origina inner joinl:
SELECT
Substring(ISNULL(Physician.FirstName,''),1,1) + '. ' + ISNULL(Physician.LastName,'') +
CASE
WHEN CaseTypeID = 3 then
' - PAIN'
ELSE
''
END
as PhysicianName,
a.PhysicianID,
Alias,
a.SpecialtyID,
a.[Year],
SortOrder,
CaseTypeID,
Month,
sum(Count) as Count
from Physician.PhysicianUtilization a inner join
Physician.Physician as Physician
on a.PhysicianID= Physician.PhysicianID inner join
Physician.Specialty as Specialty
on a.SpecialtyID=Specialty.SpecialtyID
inner join
Physician.SpecialtyAlias on
SpecialtyAlias.SpecialtyAliasID = Specialty.SpecialtyAliasID
where
a.FacilityID = 10020
and (a.CaseTypeID = 2 or a.CaseTypeID = 3)
and a.Year = 2016
and a.PhysicianID = 4333
group by Physician.FirstName,Physician.LastName,
a.PhysicianID,
Alias,
a.SpecialtyID,
a.[Year],
SortOrder,
CaseTypeID,
Month
order by a.PhysicianID
Changed to left join:
SELECT
Substring(ISNULL(Physician.FirstName,''),1,1) + '. ' + ISNULL(Physician.LastName,'') +
CASE
WHEN CaseTypeID = 3 then
' - PAIN'
ELSE
''
END
as PhysicianName,
a.PhysicianID,
Alias,
a.SpecialtyID,
a.[Year],
SortOrder,
CaseTypeID,
Month,
sum(Count) as Count
from Physician.PhysicianUtilization a left join
Physician.Physician as Physician
on a.PhysicianID= Physician.PhysicianID left join
Physician.Specialty as Specialty
on a.SpecialtyID=Specialty.SpecialtyID
left join
Physician.SpecialtyAlias on
SpecialtyAlias.SpecialtyAliasID = Specialty.SpecialtyAliasID
where
a.FacilityID = 10020
and (a.CaseTypeID = 2 or a.CaseTypeID = 3)
and a.Year = 2016
and a.PhysicianID = 4333
group by Physician.FirstName,Physician.LastName,
a.PhysicianID,
Alias,
a.SpecialtyID,
a.[Year],
SortOrder,
CaseTypeID,
Month
order by a.PhysicianID
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you for your assistance.
You're welcome.
Did your problem got solved?
Did your problem got solved?
ASKER
Thank you for your prompt response. Sorry for the delay in closing out but I thought I had already submitted.
No. Question still open.
ASKER
Close.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
For a pretty picture do an images.google.com search for 'SQL JOIN' and you'll see what I mean.