April 30, 2008 at 7:35 am
Hello All,
I need to create stored procedure that will output information created 3
months after the record was created. For example: if the stored procedure was
run today or based on a date parameter I would like it to output all records
created 3 months prior to that day. There are other parameters I need,but I
think I can take care of those,
Thanks in advance.
James Pettigrew
Systems Manager
April 30, 2008 at 7:42 am
jpettigrew (4/30/2008)
Hello All,I need to create stored procedure that will output information created 3
months after the record was created. For example: if the stored procedure was
run today or based on a date parameter I would like it to output all records
created 3 months prior to that day. There are other parameters I need,but I
think I can take care of those,
Thanks in advance.
What's your data structure? Any sample data?
Maybe these basic date manipulation techniques will help you?
select
getdate() as Now,
dateadd(month, -3, getdate()) as ThreeMonthsAgo,
dateadd(day, datediff(day, 0, getdate()), 0) as Today,
dateadd(month, datediff(month, 0, getdate()), 0) as StartOfMonth
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 30, 2008 at 8:05 am
If I wanted to use what you provided in your reply to this sp, where woud I apply? In the where clause?
CREATE PROCEDURE dbo.spFEI_ThreemonthfollowSurvey
@Payor_Keys varchar(256) = '',
@strFEIUsrs varchar (1024) = '',
@dtstartDate DateTime='9/1/2007',
@dtEndDate DateTime='9/30/2007'
AS
CREATE TABLE #PayorNameTable (Payor_Name varchar(50))
CREATE TABLE #OutputTable (
Payor_Keys varchar(256),
Payor_Names varchar(1024),
payor_key int,
Payor_Name varchar(50),
p_patient_id int,
p_ss_number char(11),
p_first_name varchar (40),
p_last_name varchar (40),
p_address1 varchar (30),
p_address2 varchar (30),
p_address3 varchar (30),
p_city varchar (30),
p_state char (2),
p_zip_code char(10),
p_home_phone char(12),
p_work_phone char(12),
p_work_ext char(6),
p_oth_phone char(12),
p_phone_type int,
p_email varchar(50),
p_sex char(1),
p_birthdate Datetime,
MAIL_SURVEY varchar (40),
PHONE_SURVEY varchar (40),
FOLLOW_UP_CALL varchar (40),
LEAVE_MESSAGE varchar (40),
SPECIAL_INSTRUCTIONS varchar(40),
Create_by varchar (30),
RecFound Int,
evSource varchar(30)
)
DECLARE @sQt Varchar (1), @Payor_Key_Insert As Varchar(256), @Payor_Names As Varchar(1024), @Payor_Name As Varchar (50)
-- Initialize
SET @Payor_Names = ''
SET @sQt=Char(34)
SET @dtEndDate = DATEADD(s, -1, DATEADD(mm, 3, @dtEndDate))
IF LEN(@Payor_Keys) >0
BEGIN
-- Determine Payor Names
INSERT INTO #PayorNameTable SELECT Payor_Name FROM dbo.Payor WHERE Payor_Key IN (@Payor_Keys)
DECLARE Payor_Names_Cursor CURSOR FOR
SELECT Payor_Name FROM #PayorNameTable
OPEN Payor_Names_Cursor
FETCH NEXT FROM Payor_Names_Cursor INTO @Payor_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Payor_Names = @Payor_Names + ", " + RTRIM(@Payor_Name)
FETCH NEXT FROM Payor_Names_Cursor INTO @Payor_Name
END
IF LEN(@Payor_Names) > 2
SET @Payor_Names = RIGHT(@Payor_Names, LEN(@Payor_Names) - 2)
CLOSE Payor_Names_Cursor
DEALLOCATE Payor_Names_Cursor
-- Prudential Clinical Intake (Mail Survey, Phone Surver, Follow Up Call, Leave Message)
INSERT INTO #OutputTable (Payor_Keys, Payor_Names, payor_key, payor_Name, p_patient_id, p_ss_number, p_first_name, p_last_name, p_address1, p_address2, p_address3, p_city, p_state, p_zip_code, p_home_phone, p_work_phone, p_work_ext, p_oth_phone, p_phone_type, p_email, p_sex, p_birthdate, MAIL_SURVEY, PHONE_SURVEY, FOLLOW_UP_CALL, LEAVE_MESSAGE, Special_Instructions, Create_by, RecFound, evSource)
SELECT DISTINCT @Payor_Keys, @Payor_Names, contact.insu_id, Payor.Payor_name, contact.xp_patient_id, contact.p_ss_number, contact.p_first_name, contact.p_last_name, contact.p_address1, contact.p_address2, contact.p_address3, contact.p_city, contact.p_state, contact.p_zip_code, contact.p_home_phone, contact.p_work_phone, contact.p_work_ext, contact.p_oth_phone, contact.p_phone_type, contact.p_email, contact.p_sex, contact.p_birthdate, evPRUClinicala.MAIL_SURVEY, NULL, evPRUClinicala.FOLLOW_UP_CALL, evPRUClinicala.LEAVE_MESSAGE, NULL, evPRUClinicala.Create_by, 1, 'evPRUCLINICAL'
FROM dbo.evPRUclinicala evPRUclinicala
INNER JOIN dbo.Contact Contact ON evPRUclinicala.Patient_Key = Contact.p_patient_id
LEFT OUTER JOIN dbo.Payor Payor on Contact.Insu_ID = Payor.Payor_Key
WHERE evPRUclinicala.Create_Date BETWEEN @dtStartDate AND @dtEndDate
AND Contact.Insu_ID IN(@Payor_Keys)
-- EAP Intake ( Phone satisfaction survey, Follow Up Call, Message OK)
INSERT INTO #OutputTable (Payor_Keys, Payor_Names, payor_key, payor_Name, p_patient_id, p_ss_number, p_first_name, p_last_name, p_address1, p_address2, p_address3, p_city, p_state, p_zip_code, p_home_phone, p_work_phone, p_work_ext, p_oth_phone, p_phone_type, p_email, p_sex, p_birthdate, MAIL_SURVEY, PHONE_SURVEY, FOLLOW_UP_CALL, LEAVE_MESSAGE, Special_Instructions, Create_by, RecFound, evSource)
SELECT DISTINCT @Payor_Keys, @Payor_Names, contact.insu_id, Payor.Payor_name, contact.xp_patient_id, contact.p_ss_number, contact.p_first_name, contact.p_last_name, contact.p_address1, contact.p_address2, contact.p_address3, contact.p_city, contact.p_state, contact.p_zip_code, contact.p_home_phone, contact.p_work_phone, contact.p_work_ext, contact.p_oth_phone, contact.p_phone_type, contact.p_email, contact.p_sex, contact.p_birthdate, evEAPintakea.MAIL_SURVEY, NULL , evEAPintakea.FOLLOW_UP_CALL , NULL, NULL, evEAPIntakea.Create_By, 1, 'evEAPIntake'
FROM dbo.evEAPIntakea evEAPIntakea
INNER JOIN erw.dbo.Contact Contact ON evEAPIntakea.Patient_Key = Contact.p_patient_id
LEFT OUTER JOIN dbo.Payor Payor on Contact.Insu_ID = Payor.Payor_Key
WHERE evEAPIntakea.Create_Date BETWEEN @dtStartDate AND @dtEndDate
AND Contact.Insu_ID IN(@Payor_Keys)
-- EAP FOLLOW UP CALL (PHONE_SATSURVEY)
INSERT INTO #OutputTable (Payor_Keys, Payor_Names, payor_key, payor_Name, p_patient_id, p_ss_number, p_first_name, p_last_name, p_address1, p_address2, p_address3, p_city, p_state, p_zip_code, p_home_phone, p_work_phone, p_work_ext, p_oth_phone, p_phone_type, p_email, p_sex, p_birthdate, MAIL_SURVEY, PHONE_SURVEY, FOLLOW_UP_CALL, LEAVE_MESSAGE, Special_Instructions, Create_by, RecFound, evSource)
SELECT DISTINCT @Payor_Keys, @Payor_Names, contact.insu_id , Payor.Payor_name, contact.xp_patient_id, contact.p_ss_number, contact.p_first_name, contact.p_last_name, contact.p_address1, contact.p_address2, contact.p_address3, contact.p_city, contact.p_state, contact.p_zip_code, contact.p_home_phone, contact.p_work_phone, contact.p_work_ext, contact.p_oth_phone, contact.p_phone_type, contact.p_email, contact.p_sex, contact.p_birthdate, NULL, evEAPfollowupcall.PHONE_SATSURVEY, NULL, NULL, NULL, evEAPfollowupcall.Create_by, 1, 'evEAPfollowupcall'
FROM dbo.evEAPfollowupcall evEAPfollowupcall
INNER JOIN erw.dbo.Contact Contact ON evEAPfollowupcall.Patient_Key = Contact.p_patient_id
LEFT OUTER JOIN dbo.Payor Payor on Contact.Insu_ID = Payor.Payor_Key
WHERE evEAPfollowupcall.Create_Date BETWEEN @dtStartDate AND @dtEndDate
AND Contact.Insu_ID IN(@Payor_Keys)
END
ELSE
BEGIN
SET @Payor_Names = 'All Payors'
-- Prudential Clinical Intake (Mail Survey, Phone Surver, Follow Up Call, Leave Message)
INSERT INTO #OutputTable (Payor_Keys, Payor_Names, payor_key, payor_Name, p_patient_id, p_ss_number, p_first_name, p_last_name, p_address1, p_address2, p_address3, p_city, p_state, p_zip_code, p_home_phone, p_work_phone, p_work_ext, p_oth_phone, p_phone_type, p_email, p_sex, p_birthdate, MAIL_SURVEY, PHONE_SURVEY, FOLLOW_UP_CALL, LEAVE_MESSAGE, Special_Instructions, Create_by, RecFound, evSource)
SELECT DISTINCT @Payor_Keys, @Payor_Names, contact.insu_id, Payor.Payor_name, contact.xp_patient_id, contact.p_ss_number, contact.p_first_name, contact.p_last_name, contact.p_address1, contact.p_address2, contact.p_address3, contact.p_city, contact.p_state, contact.p_zip_code, contact.p_home_phone, contact.p_work_phone, contact.p_work_ext, contact.p_oth_phone, contact.p_phone_type, contact.p_email, contact.p_sex, contact.p_birthdate, evPRUClinicala.MAIL_SURVEY, NULL, evPRUClinicala.FOLLOW_UP_CALL, evPRUClinicala.LEAVE_MESSAGE, NULL, evPRUClinicala.Create_by, 1, 'evPRUCLINICAL'
FROM dbo.evPRUclinicala evPRUclinicala
INNER JOIN dbo.Contact Contact ON evPRUclinicala.Patient_Key = Contact.p_patient_id
LEFT OUTER JOIN evEAPfollowupcall ON evEAPfollowupcall.Patient_Key = evPRUclinicala.Patient_Key
LEFT OUTER JOIN dbo.Payor Payor on Contact.Insu_ID = Payor.Payor_Key
WHERE evPRUclinicala.Create_Date BETWEEN @dtStartDate AND @dtEndDate
AND evEAPfollowupcall.Patient_Key IS NULL
-- EAP Intake ( Phone satisfaction survey, Follow Up Call, Message OK)
INSERT INTO #OutputTable (Payor_Keys, Payor_Names, payor_key, payor_Name, p_patient_id, p_ss_number, p_first_name, p_last_name, p_address1, p_address2, p_address3, p_city, p_state, p_zip_code, p_home_phone, p_work_phone, p_work_ext, p_oth_phone, p_phone_type, p_email, p_sex, p_birthdate, MAIL_SURVEY, PHONE_SURVEY, FOLLOW_UP_CALL, LEAVE_MESSAGE, Special_Instructions, Create_by, RecFound, evSource)
SELECT DISTINCT @Payor_Keys, @Payor_Names, contact.insu_id, Payor.Payor_name, contact.xp_patient_id, contact.p_ss_number, contact.p_first_name, contact.p_last_name, contact.p_address1, contact.p_address2, contact.p_address3, contact.p_city, contact.p_state, contact.p_zip_code, contact.p_home_phone, contact.p_work_phone, contact.p_work_ext, contact.p_oth_phone, contact.p_phone_type, contact.p_email, contact.p_sex, contact.p_birthdate, evEAPintakea.MAIL_SURVEY, NULL ,evEAPintakea.FOLLOW_UP_CALL , NULL, NULL, evEAPIntakea.Create_By, 1, 'evEAPIntake'
FROM dbo.evEAPIntakea evEAPIntakea
INNER JOIN erw.dbo.Contact Contact ON evEAPIntakea.Patient_Key = Contact.p_patient_id
LEFT OUTER JOIN evEAPfollowupcall ON evEAPfollowupcall.Patient_Key = evEAPIntakea.Patient_Key
LEFT OUTER JOIN dbo.Payor Payor on Contact.Insu_ID = Payor.Payor_Key
WHERE evEAPIntakea.Create_Date BETWEEN @dtStartDate AND @dtEndDate
AND evEAPfollowupcall.Patient_Key IS NULL
-- EAP FOLLOW UP CALL (PHONE_SATSURVEY)
INSERT INTO #OutputTable (Payor_Keys, Payor_Names, payor_key, payor_Name, p_patient_id, p_ss_number, p_first_name, p_last_name, p_address1, p_address2, p_address3, p_city, p_state, p_zip_code, p_home_phone, p_work_phone, p_work_ext, p_oth_phone, p_phone_type, p_email, p_sex, p_birthdate, MAIL_SURVEY, PHONE_SURVEY, FOLLOW_UP_CALL, LEAVE_MESSAGE, Special_Instructions, Create_by, RecFound, evSource)
SELECT DISTINCT @Payor_Keys, @Payor_Names, contact.insu_id , Payor.Payor_name, contact.xp_patient_id, contact.p_ss_number, contact.p_first_name, contact.p_last_name, contact.p_address1, contact.p_address2, contact.p_address3, contact.p_city, contact.p_state, contact.p_zip_code, contact.p_home_phone, contact.p_work_phone, contact.p_work_ext, contact.p_oth_phone, contact.p_phone_type, contact.p_email, contact.p_sex, contact.p_birthdate, NULL, evEAPfollowupcall.PHONE_SATSURVEY, NULL, NULL, NULL, evEAPfollowupcall.Create_by, 1, 'evEAPfollowupcall'
FROM dbo.evEAPfollowupcall evEAPfollowupcall
INNER JOIN erw.dbo.Contact Contact ON evEAPfollowupcall.Patient_Key = Contact.p_patient_id
LEFT OUTER JOIN dbo.Payor Payor on Contact.Insu_ID = Payor.Payor_Key
WHERE evEAPfollowupcall.Create_Date BETWEEN @dtStartDate AND @dtEndDate
END
-- Return Data
IF LEN(@strFEIUsrs) > 0
EXEC("SELECT * FROM #Outputtable WHERE Create_by IN(" + @strFEIUsrs + ") ")
ELSE
SELECT * FROM #Outputtable
GO
James Pettigrew
Systems Manager
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply