ALTER PROCEDURE [dbo].[WklyCasesClearedWithNoReturns] 
	-- Add the parameters for the stored procedure here
	@Start Datetime,
	@End Datetime,
	@Office varchar(5)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    --select * from ROCAP


create table #temp
(
SocialSecurityNumber varchar(9),
ClearedDate datetime,
[Type] varchar(5),
DDS varchar(50),
TotalCleared int 
)

if @office = 'ALL'
begin

--DE
insert into #temp
select SocialSecurityNumber, DEClearedDate as ClearedDate, 'DE' as [Type], DDS, NULL 
from ROCAPData where
DEClearedDate between @start and @End
and DESecondClearedDate  is NULL
and DEThirdClearedDate  is NULL
and DEFourthClearedDate  is NULL
Order BY ISNULL( DEClearedDate, '31-Dec-2090')

--Somatic
insert into #temp
select SocialSecurityNumber, SomaticMCClearedDate as ClearedDate, 'SM' as [Type], DDS, NULL 
from ROCAPData where 
SomaticMCClearedDate between @start and @End
and SomaticMCSecondClearedDate  is NULL
and SomaticMCThirdClearedDate  is NULL
and SomaticMCFourthClearedDate  is NULL
Order BY ISNULL( SomaticMCClearedDate, '31-Dec-2090')
--Psyc
insert into #temp
select SocialSecurityNumber, PsycMCClearedDate as ClearedDate, 'PM' as [Type], DDS, NULL 
from ROCAPData where 
PsycMCClearedDate between @Start and @End
and PsycMCSecondClearedDate  is NULL
and PsycMCThirdClearedDate  is NULL
and PsycMCFourthClearedDate  is NULL
Order BY ISNULL( PsycMCClearedDate, '31-Dec-2090')


end
else
begin
--DE
insert into #temp
select SocialSecurityNumber, DEClearedDate as ClearedDate, 'DE' as [Type], DDS, NULL  
from ROCAPData where
DEClearedDate between @Start and @End
and DESecondClearedDate  is NULL
and DEThirdClearedDate  is NULL
and DEFourthClearedDate  is NULL
and DDS = @office
Order BY ISNULL( DEClearedDate, '31-Dec-2090')
--Somatic
insert into #temp
select SocialSecurityNumber, SomaticMCClearedDate as ClearedDate, 'SM' as [Type], DDS, NULL 
from ROCAPData where 
SomaticMCClearedDate between @Start and @End
and SomaticMCSecondClearedDate  is NULL
and SomaticMCThirdClearedDate  is NULL
and SomaticMCFourthClearedDate  is NULL
and DDS = @office
Order BY ISNULL( SomaticMCClearedDate, '31-Dec-2090')
--Psyc
insert into #temp
select SocialSecurityNumber, PsycMCClearedDate as ClearedDate, 'PM' as [Type], DDS, NULL 
from ROCAPData where 
PsycMCClearedDate between @Start and @End
and PsycMCSecondClearedDate  is NULL
and PsycMCThirdClearedDate  is NULL
and PsycMCFourthClearedDate  is NULL
and DDS = @office
Order BY ISNULL( PsycMCClearedDate, '31-Dec-2090')

end

Select SocialSecurityNumber, ClearedDate, [Type], DDS, Count(*) As TotalCleared
from #temp
Group By SocialSecurityNumber, ClearedDate, [Type], DDS
Union
Select Null, Null, Null, Null, count(*)as TotalCleared 
from #temp

END