August 31, 2009 at 8:38 am
Hi all,
I've written an query below to display an output of cases are pending over 14 days from the date that cases are received to present. For example, if a case received 8/1/2009 and today is 8/31/2009, then this case is pending over 14 days. Another example, if a case received 8/30/2009 and today is 8/31/2009, then it is not a case pending over 14 days.
The query that I wrote is not displayed an output of what I wanted, it displayed only header names of a report and it did not display the data within a report. For example, below is a partial of header names of an output (report):
socialsecuritynumber dds dereceiveddate.....
ALTER PROCEDURE [dbo].[CasesPendingOver14days]
@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;
select SocialSecurityNumber,' '[Type] ,DDS,DEReceivedDate,DESecondReceivedDate,DEThirdReceivedDate,DEFourthReceivedDate,
SomaticMCReceiveddate,SomaticMCSecondReceiveddate,SomaticMCThirdReceiveddate,SomaticMCFourthReceiveddate,
PsycMCReceiveddate,PsycMCSecondReceiveddate,PsycMCThirdReceiveddate,PsycMCFourthReceiveddate
into #temp
from ROCAPData
delete from #temp;
if @office = 'ALL'
begin
--DE
insert into #temp (SocialSecurityNumber,[Type],DDS,DEReceivedDate)
select SocialSecurityNumber, 'DE' as [Type], DDS as OfficeName,DEReceivedDate from ROCAPData
where DEClearedDate is NULL and DEReceivedDate between @startdate and @enddate and DATEDIFF(day , DEReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,DESecondReceivedDate)
select SocialSecurityNumber, 'DE' as [Type], DDS as OfficeName,DESecondReceivedDate from ROCAPData
where DESecondClearedDate is NULL and DESecondReceivedDate between @startdate and @enddate and DATEDIFF(day , DESecondReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,DEThirdReceivedDate)
select SocialSecurityNumber, 'DE' as [Type], DDS as OfficeName,DEThirdReceivedDate from ROCAPData
where DEThirdClearedDate is NULL and DEThirdReceivedDate between @startdate and @enddate and DATEDIFF(day , DEThirdReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,DEFourthReceivedDate)
select SocialSecurityNumber, 'DE' as [Type], DDS as OfficeName,DEFourthReceivedDate from ROCAPData
where DEFourthClearedDate is NULL and DEFourthReceivedDate between @startdate and @enddate and DATEDIFF(day , DEFourthReceivedDate , GetDate()) > 14
--SM
insert into #temp (SocialSecurityNumber,[Type],DDS,SomaticMCReceivedDate)
select SocialSecurityNumber, 'SM' as [Type], DDS as OfficeName,SomaticMCReceivedDate from ROCAPData
where SomaticMCClearedDate is NULL and SomaticMCReceivedDate between @startdate and @enddate and DATEDIFF(day , SomaticMCReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,SomaticMCSecondReceiveddate)
select SocialSecurityNumber, 'SM' as [Type], DDS as OfficeName,SomaticMCSecondReceiveddate from ROCAPData
where SomaticMCSecondCleareddate is NULL and SomaticMCSecondReceiveddate between @startdate and @enddate and DATEDIFF(day , SomaticMCSecondReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,SomaticMCThirdReceiveddate)
select SocialSecurityNumber, 'SM' as [Type], DDS as OfficeName,SomaticMCThirdReceiveddate from ROCAPData
where SomaticMCThirdCleareddate is NULL and SomaticMCThirdReceiveddate between @startdate and @enddate and DATEDIFF(day , SomaticMCThirdReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,SomaticMCFourthReceiveddate)
select SocialSecurityNumber, 'SM' as [Type], DDS as OfficeName,SomaticMCFourthReceiveddate from ROCAPData
where SomaticMCFourthCleareddate is NULL and SomaticMCFourthReceiveddate between @startdate and @enddate and DATEDIFF(day , SomaticMCFourthReceivedDate , GetDate()) > 14
--PY
insert into #temp (SocialSecurityNumber,[Type],DDS,PsycMCReceivedDate)
select SocialSecurityNumber, 'PY' as [Type], DDS as OfficeName,PsycMCReceivedDate from ROCAPData
where PsycMCClearedDate is NULL and PsycMCReceivedDate between @startdate and @enddate and DATEDIFF(day , PsycMCReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,PsycMCSecondReceiveddate)
select SocialSecurityNumber, 'PY' as [Type], DDS as OfficeName,PsycMCSecondReceiveddate from ROCAPData
where PsycMCSecondCleareddate is NULL and PsycMCSecondReceiveddate between @startdate and @enddate and DATEDIFF(day , PsycMCSecondReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,PsycMCThirdReceiveddate)
select SocialSecurityNumber, 'PY' as [Type], DDS as OfficeName,PsycMCThirdReceiveddate from ROCAPData
where PsycMCThirdCleareddate is NULL and PsycMCThirdReceiveddate between @startdate and @enddate and DATEDIFF(day , PsycMCThirdReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,PsycMCFourthReceiveddate)
select SocialSecurityNumber, 'PY' as [Type], DDS as OfficeName,PsycMCFourthReceiveddate from ROCAPData
where PsycMCFourthCleareddate is NULL and PsycMCFourthReceiveddate between @startdate and @enddate and DATEDIFF(day , PsycMCFourthReceivedDate , GetDate()) > 14
end
else
begin
insert into #temp (SocialSecurityNumber,[Type],DDS,DEReceivedDate)
select SocialSecurityNumber, 'DE' as [Type], DDS as OfficeName,DEReceivedDate from ROCAPData
where DEClearedDate is NULL and DEReceivedDate between @startdate and @enddate and DDS = @office and DATEDIFF(day , DEReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,DESecondReceivedDate)
select SocialSecurityNumber, 'DE' as [Type], DDS as OfficeName,DESecondReceivedDate from ROCAPData
where DESecondClearedDate is NULL and DESecondReceivedDate between @startdate and @enddate and DDS = @office and DATEDIFF(day , DESecondReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,DEThirdReceivedDate)
select SocialSecurityNumber, 'DE' as [Type], DDS as OfficeName,DEThirdReceivedDate from ROCAPData
where DEThirdClearedDate is NULL and DEThirdReceivedDate between @startdate and @enddate and DDS = @office and DATEDIFF(day , DEThirdReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,DEFourthReceivedDate)
select SocialSecurityNumber, 'DE' as [Type], DDS as OfficeName,DEFourthReceivedDate from ROCAPData
where DEFourthClearedDate is NULL and DEFourthReceivedDate between @startdate and @enddate and DDS = @office and DATEDIFF(day , DEFourthReceivedDate , GetDate()) > 14
--SM
insert into #temp (SocialSecurityNumber,[Type],DDS,SomaticMCReceivedDate)
select SocialSecurityNumber, 'SM' as [Type], DDS as OfficeName,SomaticMCReceivedDate from ROCAPData
where SomaticMCClearedDate is NULL and SomaticMCReceivedDate between @startdate and @enddate and DDS = @office and DATEDIFF(day , SomaticMCReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,SomaticMCSecondReceiveddate)
select SocialSecurityNumber, 'SM' as [Type], DDS as OfficeName,SomaticMCSecondReceiveddate from ROCAPData
where SomaticMCSecondCleareddate is NULL and SomaticMCSecondReceiveddate between @startdate and @enddate and DDS = @office and DATEDIFF(day , SomaticMCSecondReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,SomaticMCThirdReceiveddate)
select SocialSecurityNumber, 'SM' as [Type], DDS as OfficeName,SomaticMCThirdReceiveddate from ROCAPData
where SomaticMCThirdCleareddate is NULL and SomaticMCThirdReceiveddate between @startdate and @enddate and DDS = @office and DATEDIFF(day , SomaticMCThirdReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,SomaticMCFourthReceiveddate)
select SocialSecurityNumber, 'SM' as [Type], DDS as OfficeName,SomaticMCFourthReceiveddate from ROCAPData
where SomaticMCFourthCleareddate is NULL and SomaticMCFourthReceiveddate between @startdate and @enddate and DDS = @office and DATEDIFF(day , SomaticMCFourthReceivedDate , GetDate()) > 14
--PY
insert into #temp (SocialSecurityNumber,[Type],DDS,PsycMCReceivedDate)
select SocialSecurityNumber, 'PY' as [Type], DDS as OfficeName,PsycMCReceivedDate from ROCAPData
where PsycMCClearedDate is NULL and PsycMCReceivedDate between @startdate and @enddate and DDS = @office and DATEDIFF(day , PsycMCReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,PsycMCSecondReceiveddate)
select SocialSecurityNumber, 'PY' as [Type], DDS as OfficeName,PsycMCSecondReceiveddate from ROCAPData
where PsycMCSecondCleareddate is NULL and PsycMCSecondReceiveddate between @startdate and @enddate and DDS = @office and DATEDIFF(day , PsycMCSecondReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,PsycMCThirdReceiveddate)
select SocialSecurityNumber, 'PY' as [Type], DDS as OfficeName,PsycMCThirdReceiveddate from ROCAPData
where PsycMCThirdCleareddate is NULL and PsycMCThirdReceiveddate between @startdate and @enddate and DDS = @office and DATEDIFF(day , PsycMCThirdReceivedDate , GetDate()) > 14
insert into #temp (SocialSecurityNumber,[Type],DDS,PsycMCFourthReceiveddate)
select SocialSecurityNumber, 'PY' as [Type], DDS as OfficeName,PsycMCFourthReceiveddate from ROCAPData
where PsycMCFourthCleareddate is NULL and PsycMCFourthReceiveddate between @startdate and @enddate and DDS = @office and DATEDIFF(day , PsycMCFourthReceivedDate , GetDate()) > 14
end
--select * from #temp order by SocialSecurityNumber--where SocialSecurityNumber='005-48-0925'
--Max is used for maximum value of date, sometime value is coming as null. So I have to take maximum value
select SocialSecurityNumber,DDS,max(DEReceivedDate)as DEReceivedDate,max(DESecondReceivedDate)as DESecondReceivedDate,
max(DEThirdReceivedDate)as DEThirdReceivedDate,max(DEFourthReceivedDate)as DEFourthReceivedDate,
max(SomaticMCReceiveddate)as SomaticMCReceiveddate,max(SomaticMCSecondReceiveddate)as SomaticMCSecondReceiveddate,
max(SomaticMCThirdReceiveddate)as SomaticMCThirdReceiveddate,max(SomaticMCFourthReceiveddate)as SomaticMCFourthReceiveddate,
max(PsycMCReceiveddate)as PsycMCReceiveddate,max(PsycMCSecondReceiveddate)as PsycMCSecondReceiveddate,
max(PsycMCThirdReceiveddate)as PsycMCThirdReceiveddate,max(PsycMCFourthReceiveddate)as PsycMCFourthReceiveddate
from #temp
--Group by is used for grouping SocialSecurityNumber and DDS
--Order by is used for ordering SocialSecurityNumber
group by SocialSecurityNumber,DDS
order by SocialSecurityNumber
drop table #temp
End
Thanks
Joseph
August 31, 2009 at 9:08 am
First, I hope the SSN number you have in the code above is either yours or a bogus number.
Second, without the DDL for the tables, sample data, and expected results based on the sample data, there really isn't much we can do except guess at what may be wrong.
You've been on this site long enough, you should know what we need to help you.
August 31, 2009 at 9:11 am
Duplicate post. No replies to this thread please. Direct replies to:http://www.sqlservercentral.com/Forums/Topic779926-338-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2009 at 9:11 am
Also, please replace SSN's with something non-identifying, like a unique integer value or guid.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply