| Description: |
Hi ALL
Saying if I have a store procedure as below, and I wish to ask someone in here knowing how to write store procedure but in different way (different method such as CTE method, etc.) like the one I wrote. But the output of your store procedure has to have the same outcome as my store procedure.
Below is my store procedure:
CREATE PROCEDURE FYTDCASESCLEAREDBYMC
-- Add the parameters for the stored procedure here
@Startdate Datetime,
@Enddate Datetime,
@Office varchar(5)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Create table #temp
(
[MCNAME] varchar(50),
DDS varchar(50),
[Type] varchar(50),
TOTALMCREVIEWED int
)
If @office = \'ALL\'
Begin
--SM
Insert Into #temp([MCNAME],DDS,[Type],TOTALMCREVIEWED)
Select MCNAME,DDS ,Typ As Type,Sum(isnull(Cnt,0)) As Total from (
select SomaticMCName As MCNAME,DDS ,\'SM\' As Typ,count(*) As Cnt from ROCAPData where SomaticMCClearedDate is Not null and SomaticMCClearedDate between @startdate and @enddate Group By SomaticMCName,DDS
Union All
select SomaticMCNameSecondReturned As MCNAME,DDS ,\'SM\' As Typ,count(*) As Cnt from ROCAPData where SomaticMCSecondClearedDate is Not null and SomaticMCSecondClearedDate between @startdate and @enddate Group By SomaticMCNameSecondReturned,DDS
Union All
select SomaticMCNameThirdReturned As MCNAME,DDS ,\'SM\' As Typ,count(*) As Cnt from ROCAPData where SomaticMCThirdClearedDate is Not null and SomaticMCThirdClearedDate between @startdate and @enddate Group By SomaticMCNameThirdReturned,DDS
Union All
select SomaticMCNameFourthReturned As MCNAME,DDS ,\'SM\' As Typ,count(*) As Cnt from ROCAPData where SomaticMCFourthClearedDate is Not null and SomaticMCFourthClearedDate between @startdate and @enddate Group By SomaticMCNameFourthReturned,DDS
) a where MCNAME is not null Group By MCNAME,DDS,Typ
--PM
Insert Into #temp([MCNAME],DDS,[Type],TOTALMCREVIEWED)
Select MCNAME,DDS ,Typ As Type,Sum(isnull(Cnt,0)) As Total from (
select PsycMCName As MCNAME,DDS ,\'PM\' As Typ,count(*) As Cnt from ROCAPData where PsycMCClearedDate is Not null and PsycMCClearedDate between @startdate and @enddate Group By PsycMCName,DDS
Union All
select PsycMCNameSecondReturned As MCNAME,DDS ,\'PM\' As Typ,count(*) As Cnt from ROCAPData where PsycMCSecondClearedDate is Not null and PsycMCSecondClearedDate between @startdate and @enddate Group By PsycMCNameSecondReturned,DDS
Union All
select PsycMCNameThirdReturned As MCNAME,DDS ,\'PM\' As Typ,count(*) As Cnt from ROCAPData where PsycMCThirdClearedDate is Not null and PsycMCThirdClearedDate between @startdate and @enddate Group By PsycMCNameThirdReturned,DDS
Union All
select PsycMCNameFourthReturned As MCNAME,DDS ,\'PM\' As Typ,count(*) As Cnt from ROCAPData where PsycMCFourthClearedDate is Not null and PsycMCFourthClearedDate between @startdate and @enddate Group By PsycMCNameFourthReturned,DDS
) a where MCNAME is not null Group By MCNAME,DDS,Typ
End
Else
Begin
--SM
Insert Into #temp([MCNAME],DDS,[Type],TOTALMCREVIEWED)
Select MCNAME,DDS ,Typ As Type,Sum(isnull(Cnt,0)) As Total from (
select SomaticMCName As MCNAME,DDS ,\'SM\' As Typ,count(*) As Cnt from ROCAPData where SomaticMCClearedDate is Not null and SomaticMCClearedDate between @startdate and @enddate and DDS=@office Group By SomaticMCName,DDS
Union all
select SomaticMCNameSecondReturned As MCNAME,DDS ,\'SM\' As Typ,count(*) As Cnt from ROCAPData where SomaticMCSecondClearedDate is Not null and SomaticMCSecondClearedDate between @startdate and @enddate and DDS=@office Group By SomaticMCNameSecondReturned,DDS
Union all
select SomaticMCNameThirdReturned As MCNAME,DDS ,\'SM\' As Typ,count(*) As Cnt from ROCAPData where SomaticMCThirdClearedDate is Not null and SomaticMCThirdClearedDate between @startdate and @enddate and DDS=@office Group By SomaticMCNameThirdReturned,DDS
Union all
select SomaticMCNameFourthReturned As MCNAME,DDS ,\'SM\' As Typ,count(*) As Cnt from ROCAPData where SomaticMCFourthClearedDate is Not null and SomaticMCFourthClearedDate between @startdate and @enddate and DDS=@office Group By SomaticMCNameFourthReturned,DDS
) a where MCNAME is not null Group By MCNAME,DDS,Typ
--PM
Insert Into #temp([MCNAME],DDS,[Type],TOTALMCREVIEWED)
Select MCNAME,DDS ,Typ As Type,Sum(isnull(Cnt,0)) As Total from (
select PsycMCName As MCNAME,DDS ,\'PM\' As Typ,count(*) As Cnt from ROCAPData where PsycMCClearedDate is Not null and PsycMCClearedDate between @startdate and @enddate and DDS=@office Group By PsycMCName,DDS
Union all
select PsycMCNameSecondReturned As MCNAME,DDS ,\'PM\' As Typ,count(*) As Cnt from ROCAPData where PsycMCSecondClearedDate is Not null and PsycMCSecondClearedDate between @startdate and @enddate and DDS=@office Group By PsycMCNameSecondReturned,DDS
Union all
select PsycMCNameThirdReturned As MCNAME,DDS ,\'PM\' As Typ,count(*) As Cnt from ROCAPData where PsycMCThirdClearedDate is Not null and PsycMCThirdClearedDate between @startdate and @enddate and DDS=@office Group By PsycMCNameThirdReturned,DDS
Union all
select PsycMCNameFourthReturned As MCNAME,DDS ,\'PM\' As Typ,count(*) As Cnt from ROCAPData where PsycMCFourthClearedDate is Not null and PsycMCFourthClearedDate between @startdate and @enddate and DDS=@office Group By PsycMCNameFourthReturned,DDS
) a where MCNAME is not null Group By MCNAME,DDS,Typ
End
Insert Into #temp(TOTALMCREVIEWED)
Select sum(TOTALMCREVIEWED) from #temp
Select * from #temp
Drop Table #temp
END
Thanks
Joe
|
