DECLARE @WRKTBL_Main AS VARCHAR(30) DECLARE @CMD_WHERE_Common AS VARCHAR(8000) DECLARE @CMD_EVALDATE AS VARCHAR(30) DECLARE @UserUGUID As VARCHAR(38) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET @WRKTBL_Main = 'zrpt903_admin' SET @CMD_WHERE_Common = ' ( (Persons.Transferred = 0) AND (Persons.Deleted = 0))' SET @UserUGUID = '{D43C7126-0FF6-11D5-A9EB-006008D10013}' SET @CMD_EVALDATE = '10/26/07' /* sp_helptext usp_rpt_ImmunDetails_903 select * from zrpt903_admin select * from zrpt90301_admin select * from zrpt90302_admin select * from zrpt90303_admin select * from zrpt90304_admin select * from zrpt90305_admin select 'Meningococcal' [Vaccine], Column1 [Current - Up To Date], Column2 [Current - Provisional], Column3 [Exempt - Conscientious], Column4 [Exempt - Medial], Column5 [Delinquent], Column6 [Totals] from zrpt90304_admin where seriesord = 11 select CASE SeriesORD WHEN 2 THEN 'HepA' WHEN 7 THEN 'Pneumococccal' END [Vaccine], Column1 [Current - Up To Date], Column2 [Current - Provisional], Column3 [Exempt - Conscientious], Column4 [Exempt - Medial], Column5 [Delinquent], Column6 [Totals] from zrpt90305_admin select personpguid, lastname, firstname, middlename from persons where personpguid in (select personpguid from vw_Immunizationreport where serieslid = 251789345) select * from vw_immunizationreport where personpguid = '85336F48-2FD5-4F8E-8428-E7A8497D2566' select * from #zrptImmunDetails where serieslid = 251789345 */ --select * from organizations /* PURPOSE: HO2RPT: 903 - Texas Immunization Compliance Report ARGUMENTS: All args are optional: @CMD_WHERE arguments are set to default values that include all records @WRKTBL_Main - name of the persistent worktable with records for the main body of the report @CMD_WHERE_Common - Criteria specific to fields @CMD_EVALDATE - The date the status of each series is evaluated. RETURNS: UPDATED BY: pparks LAST UPD ON: 11/1/02 UPDATED BY: Laura Davis LAST UPD ON: 09/25/2006 UPDATED: Added a data pull to get the first district name & the school year and put them in a header table. Added OTHER as an exemption type. LAST UPD ON: 09/28/2006 UPDATED: Gave the exemption "other" it's own status so that "other" would show up on the report with a due date. LAST UPD ON: 09/29/2006 UPDATED: Removed @CMD_ORDER_BY because it is not used and cause problems passing in @UserUGUID. LAST UPD ON: 10/04/2006 UPDATED: Made Section 1 fields effected by selection criteria. Moved the fields to usp_rpt_903TximmuneList_ddl #header. LAST UPD ON: 10/16/2006 UPDATED: If the only thing that gets passed in in the @CMD_WHERE_Common is transfered & deleted, it got an error. LAST UPD ON: 10/19/2006 UPDATED: Changed medical & other exemptions to expire after the expiration date. They were expiring on the expiration date. Now the expiration code matches the code in HO. LAST UPD ON: 10/24/2006 UPDATED: If there is no district name, add a blank in the table so that the row exists. LAST UPD ON: 10/24/2006 UPDATED: Changed the diptheria most recent date calculation. LAST UPD ON: 11/03/2006 UPDATED: Changed the EXEMPTION type OTHER expiration date calculation. LAST UPD ON: 11/06/2006 UPDATED: Changed the # of students with conciencious exemptions (field F ) to only look at students. UPDATED BY: Laura Davis LAST UPD ON: 04/27/2007 for SP5 - Call a sp_Immunization_Card to get all the immunization history detail istead of using table ImmunizationHistory. Pass paramters to sp_Immunization_Card LAST UPD ON: 04/29/2007 for SP5 - Combined 903_list (makes header and detail by kid). Build a temp table of series we don’t want in audit list (Deletes certain series). Build temp table for detail by kid – inner join so that we don’t get series we don’t want. Do what vb code does - do now & do next. Do what 903_calcstatus does. LAST UPD ON: 05/08/2007 for SP5 - Call view dbo.vw_ImmunizationReport to get all the immunization history detail istead of using table ImmunizationHistory. LAST UPD ON: 05/14/2007 - Use @CMD_EVALDATE to evaluate the status of each series. Is it current or delinquent? LAST UPD ON: 05/17/2007 - Measles1 - if the kid got at least one dose under 1year of age, they are complete. Measles2 - Follows the measles rules. LAST UPD ON: 08/20/2007 - usp_rpt_ImmunDetails to calculate exemption,admission,immune data. LAST UPD ON: 08/27/2007 - Fix F & K & N because F & K should be 1 & they are 0. Fix L & O because L & O should be more than 0 & they are 0. LAST UPD ON: 08/31/2007 - The Measles Series should say Measles First Dose or Measles Second Dose, not just Measles. Students in the audit section should be listed by grade in alphabetical order for all those with a Complete or Current status. So Kindergarten students are listed first, in alphabetical of all students with a Current or Complete status (show all dose dates). Then list all 7th graders, who are Complete or Current for dose 1 in alphabetical order. After all students who are current or complete in status are listed, then list all students with a delinquent or with any other status other than Current or Complete, by grade in alphabetical order. LAST UPD ON: 09/10/2007 - Line F: All student in the district in grades Kindergarten-12th with a Religious or Other exemption. LAST UPD ON: 09/17/2007 - We were stripping any values for Transfered & Deleted from the string passed in from the report program. We need to stop stripping that out. LAST UPD ON: 09/17/2007 - For measles: if a valid first dose is in place and the status of the student is changed by entering an Exemption or Admission, the first dose should count as complete within the Section 2 grid. The audit should show as Complete (for the first valid dose of measles) and the Second Dose Measles audit report should show the current status with the appropriate exemption or admission entered. LAST UPD ON: 09/25/2007 - Replace "SchoolOGUID is Null" from the where clause with "Persons.SchoolOGUID is Null" because otherwise SchoolOGUID is ambigious. NOTES: DESIGN NOTES: OUTLINE: DEFINITIONS: execute usp_rpt_903TximmuneList_ddl @WRKTBL_Main = 'zrpt903_main', @CMD_WHERE_COMMON = '((Persons.Transferred = 0) AND (Persons.Deleted = 0)) AND (Persons.SchoolOGUID IN (''{D4246F3B-7392-4BC8-9FCA-18E2567F3D29}'', ''{F068DDA3-97A5-11D5-9347-00C04F04F753}'', ''{A1A3E9A9-89AC-48E2-A126-2 168CE67864F}'', ''{F068DDB2-97A5-11D5-9347-00C04F04F753}'') OR SchoolOGUID is Null)', @CMD_EVALDATE = '2006-09-27' */ -- DECLARE -- @WRKTBL_Main AS VARCHAR(30), -- @CMD_WHERE_Common AS VARCHAR(8000), -- @CMD_EVALDATE AS VARCHAR(30), -- @UserUGUID AS VARCHAR(38) -- -- SET @WRKTBL_Main = 'zrpt903_main' -- SET @CMD_WHERE_COMMON = ' ( (Persons.Transferred = 0) AND (Persons.Deleted = 0)) AND (Persons.SchoolOGUID IN (SELECT GUIDID FROM dbo.parSchoolchood) OR SchoolOGUID is Null) ' -- -- SET @CMD_WHERE_COMMON = '(PersonPGUID = ''dec09124-5e3d-49d8-ae43-55e712fd9c1f'')' -- SET @UserUGUID = 'D43C7126-0FF6-11D5-A9EB-006008D10013' -- SET @CMD_EVALDATE = GETDATE() SET NOCOUNT ON -------------------------------------------------------------------------------------------------------------------- -- Declare Constants & Variables -------------------------------------------------------------------------------------------------------------------- -- General ---------------------------------------------------------------------------------------------------- -- DECLARE @True BIT -- SET @True = 1 -- DECLARE @False BIT -- SET @False = 0 DECLARE @conCR VARCHAR(1) SET @conCR = char(13) -- DECLARE @conTab VARCHAR(1) -- SET @conTab = char(9) -- DECLARE @conSpc VARCHAR(1) -- SET @conSpc = char(32) DECLARE @conQt CHAR(1) SET @conQt = char(39) -- DECLARE @RetVal INT -- SET @RetVal = 0 DECLARE @nError int DECLARE @Retcount INT -- routine specific SQL variables ---------------------------------------------------------------------------------------------------- -- DECLARE @ErrorOccurred BIT -- SET @ErrorOccurred = @False DECLARE @CMD VARCHAR(7500) -- DECLARE @Religious INT -- SET @Religious = 252313605 -- DECLARE @Medical INT -- SET @Medical = 252313606 -- DECLARE @Other INT -- SET @Other = 252313607 -- DECLARE @Provisional INT -- SET @Provisional = 252313609 -- DECLARE @TETANUS INT -- SET @TETANUS = 251789414 -- DECLARE @DIPHTHERIA INT -- SET @DIPHTHERIA = 251789413 -- DECLARE @TDBOOSTER INT -- SET @TDBOOSTER = 251789412 -- DECLARE @Measles INT -- SET @Measles = 251789416 -- DECLARE @PGUID VARCHAR(38) DECLARE @RunDate VARCHAR(50) -- DECLARE @RunDateAsDate datetime DECLARE @LogInID VARCHAR(12) DECLARE @WRKTBL1 VARCHAR(30) DECLARE @WRKTBL2 VARCHAR(30) DECLARE @WRKTBL3 VARCHAR(30) DECLARE @WRKTBL4 VARCHAR(30) DECLARE @WRKTBL5 VARCHAR(30) SET @LogInID = (Select userlogin from users where useruguid = @UserUGUID) SET @WRKTBL1 = 'zrpt90301_' + @LogInID SET @WRKTBL2 = 'zrpt90302_' + @LogInID SET @WRKTBL3 = 'zrpt90303_' + @LogInID SET @WRKTBL4 = 'zrpt90304_' + @LogInID SET @WRKTBL5 = 'zrpt90305_' + @LogInID DECLARE @CMD_WHERE_Common_PK_K_7 AS VARCHAR(8000) SET NOCOUNT ON -------------------------------------------------------------------------------------------------------------------- -- Preprocess -------------------------------------------------------------------------------------------------------------------- SET @CMD = 'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Tables WHERE Table_Name = ' + @conQT + @WRKTBL_Main + @conQT + ') ' + @conCR + 'DROP TABLE ' + @WRKTBL_Main --PRINT @CMD EXECUTE (@CMD) SET @CMD = 'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Tables WHERE Table_Name = ' + @conQT + @WRKTBL1 + @conQT + ') ' + @conCR + 'DROP TABLE ' + @WRKTBL1 EXECUTE (@CMD) SET @CMD = 'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Tables WHERE Table_Name = ' + @conQT + @WRKTBL2 + @conQT + ') ' + @conCR + 'DROP TABLE ' + @WRKTBL2 EXECUTE (@CMD) SET @CMD = 'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Tables WHERE Table_Name = ' + @conQT + @WRKTBL3 + @conQT + ') ' + @conCR + 'DROP TABLE ' + @WRKTBL3 EXECUTE (@CMD) SET @CMD = 'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Tables WHERE Table_Name = ' + @conQT + @WRKTBL4 + @conQT + ') ' + @conCR + 'DROP TABLE ' + @WRKTBL4 EXECUTE (@CMD) SET @CMD = 'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Tables WHERE Table_Name = ' + @conQT + @WRKTBL5 + @conQT + ') ' + @conCR + 'DROP TABLE ' + @WRKTBL5 EXECUTE (@CMD) -------------------------------------------------------------------------------------------------------------------- -- DECLARE @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED AS VARCHAR(8000) -- DECLARE @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED2 AS VARCHAR(8000) SELECT @CMD_WHERE_Common = REPLACE(@CMD_WHERE_Common,'OR SchoolOGUID is Null)','OR Persons.SchoolOGUID is Null)') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = @CMD_WHERE_Common -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_Common,' (Persons.Deleted = 0) AND ','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,' (Persons.Deleted = ''0'') AND ','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,'AND (Persons.Deleted = ''0'')','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,'AND (Persons.Deleted = ''0'')','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,' (Persons.Deleted = ''1'') AND ','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,'AND (Persons.Deleted = ''1'')','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,'(Persons.Transferred = ''0'')','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,'(Persons.Transferred = ''1'')','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,'AND (Persons.Deleted = 0)','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,'AND (Persons.Deleted = 0)','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,' (Persons.Deleted = 1) AND ','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,'AND (Persons.Deleted = 1)','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,'(Persons.Transferred = 0)','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,'(Persons.Transferred = 1)','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,' () AND ','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,'( ) AND ','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,'( ) AND ','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,'( ) AND ','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,' ( ) AND ','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,' AND )',')') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,' AND )',')') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,'() AND ','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED2 = @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,'Persons.SchoolOGUID','Schools.OrganizationOGUID') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,' OR SchoolOGUID is Null','') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,'( )','(1=1)') -- SELECT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED = REPLACE(@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED,' (Persons.PersonPGUID = ''{87CBF937-9A86-11D6-B3E3-00A0C96168DC}'') ','(1=1)') -- select @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED as 'CMD_WHERE_INCLUDETRANSFERED_AND_DELETED' -- PRINT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED --SET @CMD_WHERE_Common = @CMD_WHERE_Common + ' AND GradeLID in (84082694,84082711,84082701)' --K1, K2, 7 SET @CMD_WHERE_Common_PK_K_7 = @CMD_WHERE_Common + ' AND GradeLID in (84082693,84082694,84082711,84082701)' --PreSchool(PK), K1, K2, 7 --select @CMD_EVALDATE as date --into evaldate -- SELECT @RunDate = REPLACE(@CMD_EVALDATE,'( (Events.ExamDate = ','') -- SELECT @RunDate = REPLACE(@RunDate,'))','') -- SELECT @RunDate = left(right(@RunDate,6),5) + '-' + right(left(@RunDate,5),4) SET @RunDate = convert(varchar,convert(datetime,@CMD_EVALDATE), 101) --print @RunDate -- SELECT @RunDateAsDate = convert(datetime,@RunDate) -- SELECT @RunDateAsDate = convert(datetime,'2007-05-14') -------------------------------------------------------------------------------------------------------------------- -- -------------------------------------------------------------------------------------------------------------------- IF OBJECT_ID('TempDB..#tmp_criteria') IS NOT NULL DROP TABLE #tmp_criteria -- Create empty temp table SELECT PersonPGUID INTO #tmp_criteria FROM Persons WHERE 1 = 0 CREATE CLUSTERED INDEX ak_tmp_criteria ON #tmp_criteria( PersonPGUID ) -- Get a list of EventGUIDs based on argument criteria SET @CMD = '' + 'INSERT INTO #tmp_criteria ' + @conCR + 'SELECT distinct vw_ImmunizationReport.PersonPGUID ' + @conCR + 'FROM Persons INNER JOIN dbo.vw_ImmunizationReport ' + @conCR + 'ON Persons.PersonPGUID = vw_ImmunizationReport.PersonPGUID ' + @conCR + 'WHERE ' + @CMD_WHERE_Common_PK_K_7 -- PRINT @CMD EXECUTE (@CMD) --select * --from #tmp_criteria --where PersonPGUID = 'b34205a2-df37-4776-b46d-987b22b4e7e9' SET ANSI_WARNINGS OFF -------------------------------------------------------------------------------------------------------------------- -- Create Immunization detail table -------------------------------------------------------------------------------------------------------------------- IF OBJECT_ID('TempDB..#zrptImmunDetails') IS NOT NULL DROP TABLE #zrptImmunDetails --print 'Immunization Summary' CREATE TABLE #zrptImmunDetails( [PersonPGUID] [uniqueidentifier] NOT NULL, [Series] [varchar](255) NULL, [SeriesLID] INT NULL, [AdmAuthBy] [varchar](100) NULL, [Admissiondate] [datetime] NULL, [AdmissionExpire] [datetime] NULL, [ExemptAuthBy] [varchar](100) NULL, [Exemptiondate] [datetime] NULL, [ExemptionExpire] [datetime] NULL, [ExemptionOtherInfo] [varchar](400) NULL, [SeriesMaintComment] [text] NULL, [SeriesMaintComment1] [varchar](1) NULL, [SeriesStatus] [varchar](64) NULL, [SeriesStatus1] [varchar](64) NULL, [Locale] [varchar](64) NULL, [ExemptionType] [int] NULL, [ImmAuthBy] [varchar](100) NULL, [ImmuneDate] [datetime] NULL, [Immunecomment] [varchar](400) NULL, [Immunecomment1] [varchar](1) NULL, [TiterResultString] [varchar](10) NULL, [TiterComment] [varchar](400) NULL, [TiterDate] [datetime] NULL, TiterResultImmune bit NULL ) --Get list of display values for the report INSERT INTO #zrptImmunDetails SELECT --1 as TblID, vw_ImmunizationReport.PersonPGUID, -- (P.LastName + ', ' + P.FirstName + ' ' + P.MiddleName) AS FullName, vw_ImmunizationReport.SeriesName AS Series, vw_ImmunizationReport.SeriesLID, rtrim(ltrim(vw_ImmunizationReport.AdmAuthBy)) AS AdmAuthBy, vw_ImmunizationReport.Admissiondate, vw_ImmunizationReport.AdmissionExpire, rtrim(ltrim(vw_ImmunizationReport.ExemptAuthBy)) AS ExemptAuthBy, vw_ImmunizationReport.Exemptiondate, vw_ImmunizationReport.ExemptionExpire, vw_ImmunizationReport.ExemptionOtherInfo, vw_ImmunizationReport.SeriesMaintComment, CAST(vw_ImmunizationReport.SeriesMaintComment AS Varchar(1)) AS SeriesMaintComment1, vw_ImmunizationReport.SeriesStatus, CASE WHEN SeriesStatus = 'Immune' THEN SeriesStatus WHEN SeriesStatus like 'Complete%' THEN SeriesStatus WHEN vw_ImmunizationReport.SeriesStatus like 'Religious%' AND ExemptionDate IS NOT NULL THEN CASE WHEN (convert(datetime,@RunDate) >= ExemptionDate) THEN vw_ImmunizationReport.SeriesStatus ELSE 'Delinquent' END WHEN (vw_ImmunizationReport.SeriesStatus like 'Medical%' OR vw_ImmunizationReport.SeriesStatus like 'Other%') AND (ExemptionDate IS NOT NULL AND ExemptionExpire IS NOT NULL) THEN CASE WHEN (convert(datetime,@RunDate) between ExemptionDate AND ExemptionExpire) THEN vw_ImmunizationReport.SeriesStatus ELSE 'Delinquent' END WHEN (vw_ImmunizationReport.SeriesStatus like 'Provisional%') AND (AdmissionDate IS NOT NULL AND AdmissionExpire IS NOT NULL) THEN CASE WHEN (convert(datetime,@RunDate) between AdmissionDate AND AdmissionExpire) THEN vw_ImmunizationReport.SeriesStatus ELSE 'Delinquent' END WHEN (vw_ImmunizationReport.SeriesStatus like 'Denied%') AND (AdmissionDate IS NOT NULL) THEN CASE WHEN (convert(datetime,@RunDate) >= AdmissionDate) THEN vw_ImmunizationReport.SeriesStatus ELSE 'Delinquent' END WHEN (vw_ImmunizationReport.SeriesStatus like 'Delinquent%') AND (AdmissionDate IS NOT NULL) AND (vw_ImmunizationReport.AdmissionTypeLID = 252313608) THEN CASE WHEN (convert(datetime,@RunDate) >= AdmissionDate) THEN 'Denied' ELSE 'Delinquent' END ELSE CASE WHEN (convert(datetime,@RunDate) < getdate()) THEN CASE WHEN (convert(datetime,@RunDate) >= vw_ImmunizationReport.DelinquentDate) THEN 'Delinquent' ELSE vw_ImmunizationReport.SeriesStatus END ELSE CASE WHEN vw_ImmunizationReport.ExemptionTypeLID = 252313605 AND ExemptionDate IS NOT NULL THEN CASE WHEN (convert(datetime,@RunDate) >= ExemptionDate) THEN 'Religious' ELSE vw_ImmunizationReport.SeriesStatus END WHEN vw_ImmunizationReport.ExemptionTypeLID = 252313606 AND (ExemptionDate IS NOT NULL AND ExemptionExpire IS NOT NULL) THEN CASE WHEN (convert(datetime,@RunDate) between ExemptionDate AND ExemptionExpire) THEN 'Medical' ELSE vw_ImmunizationReport.SeriesStatus END WHEN vw_ImmunizationReport.ExemptionTypeLID = 252313607 AND (ExemptionDate IS NOT NULL AND ExemptionExpire IS NOT NULL) THEN CASE WHEN (convert(datetime,@RunDate) between ExemptionDate AND ExemptionExpire) THEN 'Other' ELSE vw_ImmunizationReport.SeriesStatus END WHEN (vw_ImmunizationReport.AdmissionTypeLID = 252313609) AND (AdmissionDate IS NOT NULL AND AdmissionExpire IS NOT NULL) THEN CASE WHEN (convert(datetime,@RunDate) between AdmissionDate AND AdmissionExpire) THEN 'Provisional' ELSE vw_ImmunizationReport.SeriesStatus END WHEN (vw_ImmunizationReport.AdmissionTypeLID = 252313608) AND (AdmissionDate IS NOT NULL) THEN CASE WHEN (convert(datetime,@RunDate) >= AdmissionDate) THEN 'Denied' ELSE vw_ImmunizationReport.SeriesStatus END ELSE CASE WHEN (convert(datetime,@RunDate) >= vw_ImmunizationReport.DelinquentDate) THEN 'Delinquent' ELSE vw_ImmunizationReport.SeriesStatus END END END END AS SeriesStatus1, vw_ImmunizationReport.Locale, CASE WHEN SeriesStatus = 'Immune' THEN 1 WHEN vw_ImmunizationReport.SeriesStatus like 'Religious%' AND ExemptionDate IS NOT NULL THEN CASE WHEN (convert(datetime,@RunDate) >= ExemptionDate) THEN 3 ELSE 0 END WHEN (vw_ImmunizationReport.SeriesStatus like 'Medical%' OR vw_ImmunizationReport.SeriesStatus like 'Other%') AND (ExemptionDate IS NOT NULL AND ExemptionExpire IS NOT NULL) THEN CASE WHEN (convert(datetime,@RunDate) between ExemptionDate AND ExemptionExpire) THEN 3 ELSE 0 END WHEN (vw_ImmunizationReport.SeriesStatus like 'Provisional%') AND (AdmissionDate IS NOT NULL AND AdmissionExpire IS NOT NULL) THEN CASE WHEN (convert(datetime,@RunDate) between AdmissionDate AND AdmissionExpire) THEN 2 ELSE 0 END WHEN (vw_ImmunizationReport.SeriesStatus like 'Denied%') AND (AdmissionDate IS NOT NULL) THEN CASE WHEN (convert(datetime,@RunDate) >= AdmissionDate) THEN 2 ELSE 0 END WHEN (vw_ImmunizationReport.SeriesStatus like 'Delinquent%') AND (AdmissionDate IS NOT NULL) AND (vw_ImmunizationReport.AdmissionTypeLID = 252313608) THEN CASE WHEN (convert(datetime,@RunDate) >= AdmissionDate) THEN 2 ELSE 0 END ELSE CASE WHEN (convert(datetime,@RunDate) < getdate()) THEN 0 ELSE CASE WHEN vw_ImmunizationReport.ExemptionTypeLID = 252313605 AND ExemptionDate IS NOT NULL THEN CASE WHEN (convert(datetime,@RunDate) >= ExemptionDate) THEN 3 ELSE 0 END WHEN vw_ImmunizationReport.ExemptionTypeLID = 252313606 AND (ExemptionDate IS NOT NULL AND ExemptionExpire IS NOT NULL) THEN CASE WHEN (convert(datetime,@RunDate) between ExemptionDate AND ExemptionExpire) THEN 3 ELSE 0 END WHEN vw_ImmunizationReport.ExemptionTypeLID = 252313607 AND (ExemptionDate IS NOT NULL AND ExemptionExpire IS NOT NULL) THEN CASE WHEN (convert(datetime,@RunDate) between ExemptionDate AND ExemptionExpire) THEN 3 ELSE 0 END WHEN (vw_ImmunizationReport.AdmissionTypeLID = 252313609) AND (AdmissionDate IS NOT NULL AND AdmissionExpire IS NOT NULL) THEN CASE WHEN (convert(datetime,@RunDate) between AdmissionDate AND AdmissionExpire) THEN 2 ELSE 0 END WHEN (vw_ImmunizationReport.AdmissionTypeLID = 252313608) AND (AdmissionDate IS NOT NULL) THEN CASE WHEN (convert(datetime,@RunDate) >= AdmissionDate) THEN 2 ELSE 0 END ELSE 0 END END END AS ExemptionType, vw_ImmunizationReport.ImmAuthBy, vw_ImmunizationReport.ImmuneDate, vw_ImmunizationReport.Immunecomment, CAST(vw_ImmunizationReport.Immunecomment AS VARCHAR(1)) AS Immunecomment1, vw_ImmunizationReport.TiterResultString, vw_ImmunizationReport.TiterComment, vw_ImmunizationReport.TiterDate, vw_ImmunizationReport.TiterResultImmune FROM #tmp_criteria -- INNER JOIN Persons AS P ON #tmp_criteria.PersonPGUID = P.PersonPGUID INNER JOIN vw_ImmunizationReport ON #tmp_criteria.PersonPGUID = vw_ImmunizationReport.PersonPGUID UPDATE #zrptImmunDetails SET AdmAuthBy = ( CASE WHEN len(ltrim(rtrim(AdmAuthBy))) = 0 THEN ExemptAuthBy ELSE AdmAuthBy END), Admissiondate = ISNULL(Admissiondate,Exemptiondate), AdmissionExpire = ISNULL(AdmissionExpire,ExemptionExpire) FROM #zrptImmunDetails WHERE #zrptImmunDetails.SeriesStatus LIKE 'Provisional%' UPDATE #zrptImmunDetails SET AdmissionExpire = NULL WHERE #zrptImmunDetails.SeriesStatus1 LIKE 'Denied%' CREATE CLUSTERED INDEX IDX_Person2 ON #zrptImmunDetails (PersonPGUID) -------------------------------------------------------------------------------------------------------------------- -- Section 2: Immunization Summary for the sub report -------------------------------------------------------------------------------------------------------------------- --print 'Immunization Summary' ----------------------------------------------------------------------------------------------------------------------- -- Create temp table #Series ----------------------------------------------------------------------------------------------------------------------- IF OBJECT_ID('TempDB..#Series') IS NOT NULL DROP TABLE #Series CREATE TABLE #Series ( ActionLID INT, LidType VARCHAR(32), LIDID VARCHAR(32) ) CREATE NONCLUSTERED INDEX ak_Lids ON #Series ( LIDID ) CREATE NONCLUSTERED INDEX ak_ActionLID ON #Series ( ActionLID ) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- #Series INSERT INTO #Series SELECT 251789416,'Immunization','Measles' UNION ALL SELECT 251789417,'Immunization','Mumps' UNION ALL SELECT 251789418,'Immunization','Rubella' UNION ALL SELECT 251789413,'Immunization','Diptheria' UNION ALL SELECT 251789357,'Immunization','Polio' UNION ALL SELECT 251789330,'Immunization','HepA' UNION ALL SELECT 251789333,'Immunization','HepB' UNION ALL SELECT 251789336,'Immunization','HIB' UNION ALL SELECT 251789366,'Immunization','Varicella' UNION ALL SELECT 251789354,'Immunization','Pneumococccal' UNION ALL SELECT 251789345,'Immunization','Meningococcal' SET ANSI_WARNINGS OFF --PRINT @RunDate IF OBJECT_ID('TempDB..#tmp_main') IS NOT NULL DROP TABLE #tmp_main --Get list of display values for the report SELECT -- IH.ID, IH.PersonPGUID, (P.LastName + ', ' + P.FirstName + ' ' + P.MiddleName) AS FullName, Convert(datetime, P.BirthDate, 1) as DOB, (Select Text From ListItems Where ListItemLID = P.GradeLID) AS Grade, (select Ord from listitems where listitemlid = P.gradeLID) as GradeOrd, P.GradeLID, IH.SeriesLID, IH.SeriesName AS Series, IH.SeriesOrd, Convert(DateTime, IH.Dose1Date, 1) as Dose1Date, Convert(DateTime, IH.Dose2Date, 1)as Dose2Date, Convert(DateTime, IH.Dose3Date, 1)as Dose3Date, Convert(DateTime, IH.Dose4Date, 1) as Dose4Date, Convert(DateTime, IH.Dose5Date, 1) as Dose5Date, Convert(DateTime, IH.Dose6Date, 1) as Dose6Date, Convert(DateTime, IH.Dose7Date, 1) as Dose7Date, Convert(DateTime, IH.Dose8Date, 1) as Dose8Date, Convert(DateTime, IH.Dose9Date, 1) as Dose9Date, Convert(DateTime, IH.Dose10Date, 1) as Dose10Date, Convert(DateTime, IH.Dose11Date, 1) as Dose11Date, Convert(DateTime, IH.Dose12Date, 1) as Dose12Date, Convert(DateTime, IH.Dose13Date, 1) as Dose13Date, Convert(DateTime, IH.Dose14Date, 1) as Dose14Date, IH.Dose15Date, [Status1], [Status2], [Status3], [Status4], [Status5], [Status6], [Status7], [Status8], [Status9], [Status10], [Status11], [Status12], [Status13], [Status14], IH.Scheduled , #zrptImmunDetails.[SeriesStatus1] as SeriesStatus, CASE WHEN IH.SeriesStatus = 'Complete' THEN 0 WHEN IH.SeriesStatus = 'Current' THEN 0 ELSE 1 END AS SeriesStatusORD, -- IH.SeriesStatus, -- CASE -- WHEN IH.SeriesStatus like 'Religious%' AND ExemptionDate IS NOT NULL THEN -- CASE WHEN (convert(datetime,@RunDate,101) > convert(datetime,ExemptionDate,101)) THEN IH.SeriesStatus -- ELSE 'Delinquent' END -- WHEN (IH.SeriesStatus like 'Medical%' OR IH.SeriesStatus like 'Other%') AND (ExemptionDate IS NOT NULL AND ExemptionExpire IS NOT NULL) THEN -- CASE WHEN (convert(datetime,@RunDate,101) between ExemptionDate AND ExemptionExpire) THEN IH.SeriesStatus -- ELSE 'Delinquent' END -- WHEN (IH.SeriesStatus like 'Provisional%' OR IH.SeriesStatus like 'Denied%') AND (AdmissionDate IS NOT NULL AND AdmissionExpire IS NOT NULL) THEN -- CASE WHEN (convert(datetime,@RunDate,101) between AdmissionDate AND AdmissionExpire) THEN IH.SeriesStatus -- ELSE 'Delinquent' END -- ELSE IH.SeriesStatus -- END AS SeriesStatus, ' ' AS Section -- IH.[ExemptionTypeLID], -- IH.[AdmissionTypeLID], -- IH.Immune, -- IH.Complete, -- [DelinquentDate] INTO #tmp_main FROM #tmp_criteria INNER JOIN Persons AS P ON #tmp_criteria.PersonPGUID = P.PersonPGUID INNER JOIN vw_ImmunizationReport AS IH ON P.PersonPGUID = IH.PersonPGUID LEFT OUTER JOIN #zrptImmunDetails ON #zrptImmunDetails.PersonPGUID = IH.PersonPGUID AND IH.SeriesLID = #zrptImmunDetails.SeriesLID INNER JOIN #Series ON IH.SeriesLID = #Series.ActionLID -- FROM Persons AS P INNER JOIN #Persons ON -- P.PersonPGUID = #Persons.PersonPGUID -- INNER JOIN dbo.vw_ImmunizationReport AS IH ON -- P.PersonPGUID = IH.PersonPGUID INNER JOIN #Series ON -- IH.SeriesLID = #Series.ActionLID -- select * -- FROM #tmp_Main --WHERE [SeriesStatus] like 'relig%' -- WHERE seriesLID = 251789413 SET ANSI_WARNINGS ON ----------------------------------------------------------------------------------------- -- I don't get the Measles 2 idea. I'm just updating what was already there. Laura 05/17/2007 ----------------------------------------------------------------------------------------- Insert #tmp_main SELECT -- IH.ID, PersonPGUID, FullName, DOB, Grade, GradeOrd, GradeLID, SeriesLID, 'Measles Second Dose', SeriesOrd, Dose1Date, Dose2Date, Dose3Date, Dose4Date, Dose5Date, Dose6Date, Dose7Date, Dose8Date, Dose9Date, Dose10Date, Dose11Date, Dose12Date, Dose13Date, Dose14Date, Dose15Date, [Status1], [Status2], [Status3], [Status4], [Status5], [Status6], [Status7], [Status8], [Status9], [Status10], [Status11], [Status12], [Status13], [Status14], Scheduled , SeriesStatus, SeriesStatusORD, 'MEAS2' AS Section -- [ExemptionTypeLID], -- [AdmissionTypeLID], -- Immune, -- Complete, -- [DelinquentDate] FROM #tmp_Main WHERE seriesLID = 251789416 --select * --FROM #tmp_Main --WHERE seriesLID = 251789416 ---- and FullName like 'nava%' ----and 'MEAS2' <> Section -- AND (Dose1Date IS NOT NULL) -- AND (Status1 <> 'I') --and SeriesStatus <> 'Current' --and SeriesStatus <> 'Complete' IF OBJECT_ID('TempDB..#MeaslesSeriesStatus') IS NOT NULL DROP TABLE #MeaslesSeriesStatus CREATE TABLE #MeaslesSeriesStatus ( Status VARCHAR(50), ) INSERT INTO #MeaslesSeriesStatus SELECT 'Provisional' UNION ALL SELECT 'Religious' UNION ALL SELECT 'Religious (Global Exmpt)' UNION ALL SELECT 'Other' UNION ALL SELECT 'Medical' UNION ALL SELECT 'Medical (Global Exmpt)' UNION ALL SELECT 'Delinquent' UNION ALL SELECT 'Denied' UPDATE #tmp_Main SET SeriesStatus = 'Complete', SeriesStatusORD =0 FROM #tmp_Main INNER JOIN #MeaslesSeriesStatus ON #tmp_Main.SeriesStatus = #MeaslesSeriesStatus.Status WHERE (SeriesLID = 251789416) AND (LEN(RTrim(Section)) = 0) AND (Status1 <> 'I') AND (SeriesStatusORD <> 0) AND (Dose1Date IS NOT NULL) --select FullName, SeriesStatus, * -- FROM #tmp_Main INNER JOIN -- #MeaslesSeriesStatus ON #tmp_Main.SeriesStatus = #MeaslesSeriesStatus.Status -- WHERE (SeriesLID = 251789416) -- AND (LEN(RTrim(Section)) = 0) -- AND (Status1 <> 'I') -- AND (SeriesStatusORD <> 0) -- AND (Dose1Date IS NOT NULL) --select * -- FROM #tmp_Main -- WHERE seriesLID = 251789416 -- Insert #tmp_main -- SELECT PersonPGUID, School, FullName, IDNum, DOB, county, Grade, gradelid, GradeOrd, SeriesLID, 'Measles Second Dose', 350, -- Dose1Admin, Dose1Date, Dose2Admin, Dose2Date, Dose3Admin, Dose3Date, Dose4Admin, Dose4Date, Dose5Admin, Dose5Date, Dose6Admin, -- Dose6Date, Immune, Complete, ImmuneDate, AdmissionTypeLID, Admission, Admissiondate, AdmissionExpire, -- ExemptionTypeLID, Exemption, Exemptiondate, ExemptionExpire, -- Status1, Status2, 'MEAS2', LocaleLID, Locale, tdRecent from #tmp_Main where seriesLID = 251789416 -- UPDATE #tmp_main -- SET SeriesStatus = 'Complete' -- FROM #tmp_Main -- WHERE (seriesLID = 251789416) and Section <> 'MEAS2' -- AND ( -- DateDiff(D, dose1date, DATEADD(YYYY, 1, DOB)) < 0 OR DateDiff(D, dose2date, DATEADD(YYYY, 1, DOB)) < 0 OR -- DateDiff(D, dose3date, DATEADD(YYYY, 1, DOB)) < 0 OR DateDiff(D, dose4date, DATEADD(YYYY, 1, DOB)) < 0 OR -- DateDiff(D, dose5date, DATEADD(YYYY, 1, DOB)) < 0 OR DateDiff(D, dose6date, DATEADD(YYYY, 1, DOB)) < 0 OR -- DateDiff(D, dose7date, DATEADD(YYYY, 1, DOB)) < 0 OR DateDiff(D, dose8date, DATEADD(YYYY, 1, DOB)) < 0 OR -- DateDiff(D, dose9date, DATEADD(YYYY, 1, DOB)) < 0 OR DateDiff(D, dose10date, DATEADD(YYYY, 1, DOB)) < 0 OR -- DateDiff(D, dose11date, DATEADD(YYYY, 1, DOB)) < 0 OR DateDiff(D, dose12date, DATEADD(YYYY, 1, DOB)) < 0 OR -- DateDiff(D, dose13date, DATEADD(YYYY, 1, DOB)) < 0 OR DateDiff(D, dose14date, DATEADD(YYYY, 1, DOB)) < 0 -- ) ----------------------------------------------------------------------------------------- -- end of Measles 2 ----------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------- -- Section Header -------------------------------------------------------------------------------------------------------------------- --DROP TABLE [#Header] IF OBJECT_ID('TempDB..#Header') IS NOT NULL DROP TABLE #Header CREATE TABLE [#Header] ( [District] varchar(100) NULL , [SchoolYear] varchar(100) NULL , ReligiousTot INT NULL , NumPreKindSchools INT NULL , NumKindSchools INT NULL , Num7Schools INT NULL , NumStud INT NULL ) ON [PRIMARY] -- Get a list based on argument criteria SET @CMD = '' + 'INSERT INTO #Header ([District]) ' + @conCR + 'SELECT TOP 1 District ' + @conCR + -- 'FROM Schools ' + @conCR + 'FROM Schools INNER JOIN Persons ON Schools.OrganizationOGUID = Persons.SchoolOGUID ' + @conCR + 'WHERE (LEN(District) > 1) AND ' + @CMD_WHERE_Common -- PRINT @CMD EXECUTE (@CMD) -- PRINT @CMD_WHERE_INCLUDETRANSFERED_AND_DELETED --print '616' IF NOT EXISTS (select * from #Header) BEGIN INSERT INTO #Header ([District]) VALUES ('') END UPDATE #Header SET SchoolYear=(SELECT (CAST(DATEPART(yy, min(DaysOffSchedule.SchoolYearStartDate)) AS varchar(4)) + '-' + CAST(DATEPART(yy, max(DaysOffSchedule.SchoolYearEndDate)) AS varchar(4))) FROM DaysOffSchedule WHERE getDate() between SchoolYearStartDate and SchoolYearEndDate) -- SET @CMD = '' + -- 'SELECT DISTINCT CAST(dbo.vw_ImmunizationReport.PersonPGUID AS varchar(40)), Persons.GRadeLID -- FROM dbo.vw_ImmunizationReport INNER JOIN #Series ON -- dbo.vw_ImmunizationReport.SeriesLID = #Series.ActionLID INNER JOIN Persons ON -- Persons.PersonPGUID = dbo.vw_ImmunizationReport.PersonPGUID -- WHERE (([SeriesStatus] like ''Religious%'') OR ([SeriesStatus] like ''Other%'') ) AND ((Persons.gradelid BETWEEN 84082694 AND 84082706) or (Persons.gradelid = 84082711)) AND ' + @CMD_WHERE_Common -- -- PRINT @CMD -- EXECUTE (@CMD) SET @CMD = '' + 'UPDATE #Header SET ReligiousTot =(' + @conCR + 'SELECT Count (DISTINCT CAST(dbo.vw_ImmunizationReport.PersonPGUID AS varchar(40))) FROM dbo.vw_ImmunizationReport INNER JOIN #Series ON dbo.vw_ImmunizationReport.SeriesLID = #Series.ActionLID INNER JOIN Persons ON Persons.PersonPGUID = dbo.vw_ImmunizationReport.PersonPGUID WHERE (([SeriesStatus] like ''Religious%'') OR ([SeriesStatus] like ''Other%'') ) AND ((Persons.gradelid BETWEEN 84082694 AND 84082706) or (Persons.gradelid = 84082711)) AND ' + @CMD_WHERE_Common + ')' -- WHERE (([SeriesStatus] like ''Religious%'') OR ([SeriesStatus] like ''Other%'') ) AND Persons.gradelid in (84082694, 84082711) AND ' + @CMD_WHERE_Common + ')' -- PRINT @CMD EXECUTE (@CMD) -- UPDATE #Header -- SET ReligiousTot= (SELECT Count (DISTINCT CAST(PersonPGUID AS varchar(40))) -- FROM dbo.vw_ImmunizationReport INNER JOIN #Series ON -- dbo.vw_ImmunizationReport.SeriesLID = #Series.ActionLID INNER JOIN Persons AS P ON -- P.PersonPGUID = dbo.vw_ImmunizationReport.PersonPGUID -- WHERE (([SeriesStatus] like 'Religious%') OR ([SeriesStatus] like 'Other%') ) -- AND (@CMD_WHERE_INCLUDETRANSFERED_AND_DELETED)) -- Create empty temp table IF OBJECT_ID('TempDB..#NumSchools') IS NOT NULL DROP TABLE #NumSchools CREATE TABLE #NumSchools ( [ident] [int] IDENTITY (1, 1) NOT NULL , [schoologuid] uniqueidentifier ROWGUIDCOL NOT NULL , ) SET @CMD = '' + 'INSERT INTO #NumSchools ([schoologuid]) ' + @conCR + 'SELECT Distinct(Schools.schoologuid) ' + @conCR + 'FROM Schools inner join schoolgrades ' + @conCR + ' on Schools.schoologuid = schoolgrades.schoologuid INNER JOIN ' + @conCR + ' Persons ON Schools.OrganizationOGUID = Persons.SchoolOGUID ' + @conCR + 'WHERE schoolgrades.gradelid in (84082693) AND ' + @CMD_WHERE_Common + @conCR -- PRINT @CMD EXECUTE (@CMD) UPDATE #Header SET NumPreKindSchools = (SELECT COUNT(ident) FROM #NumSchools) TRUNCATE TABLE #NumSchools SET @CMD = '' + 'INSERT INTO #NumSchools ([schoologuid]) ' + @conCR + 'SELECT Distinct(Schools.schoologuid) ' + @conCR + 'FROM Schools inner join schoolgrades ' + @conCR + ' on Schools.schoologuid = schoolgrades.schoologuid INNER JOIN ' + @conCR + ' Persons ON Schools.OrganizationOGUID = Persons.SchoolOGUID ' + @conCR + 'WHERE schoolgrades.gradelid in (84082694, 84082711) AND ' + @CMD_WHERE_Common + @conCR -- PRINT @CMD EXECUTE (@CMD) UPDATE #Header SET NumKindSchools = (SELECT COUNT(ident) FROM #NumSchools) TRUNCATE TABLE #NumSchools SET @CMD = '' + 'INSERT INTO #NumSchools ([schoologuid]) ' + @conCR + 'SELECT Distinct(Schools.schoologuid) ' + @conCR + 'FROM Schools inner join schoolgrades ' + @conCR + ' on Schools.schoologuid = schoolgrades.schoologuid INNER JOIN ' + @conCR + ' Persons ON Schools.OrganizationOGUID = Persons.SchoolOGUID ' + @conCR + 'WHERE schoolgrades.gradelid in (84082701) AND ' + @CMD_WHERE_Common + @conCR -- PRINT @CMD EXECUTE (@CMD) UPDATE #Header SET Num7Schools = (SELECT COUNT(ident) FROM #NumSchools) SET @CMD = 'UPDATE #Header '+ @conCR + 'SET NumStud = '+ @conCR + '(SELECT COUNT(Deleted) from Persons where (Persons.PositionLID = 84213806) AND ((gradelid BETWEEN 84082694 AND 84082706) or (gradelid = 84082711)) AND ' + @CMD_WHERE_Common + ')' --PRINT @CMD Execute (@CMD) -- SET @CMD = 'Select Distinct (PersonPGUID) from Persons where (Persons.PositionLID = 84213806) AND ((gradelid BETWEEN 84082694 AND 84082706) or (gradelid = 84082711)) AND ' + @CMD_WHERE_Common -- --PRINT @CMD -- Execute (@CMD) -- SET @RetCount = @@RowCount -- -- UPDATE #Header -- SET NumStud = @RetCount --SELECT * FROM #Header --select * from #tmp_main ----------------------------------------------------------------------------------------------------------------------- -- Create temp table #SeriesSummary ----------------------------------------------------------------------------------------------------------------------- IF OBJECT_ID('TempDB..#SeriesSummary') IS NOT NULL DROP TABLE #SeriesSummary CREATE TABLE #SeriesSummary ( SeriesLID INT, Series VARCHAR(50), SeriesORD INT, Students INT default 0, GradeLID INT , Grade VARCHAR(50), OLDStatus INT , [Column] INT ) CREATE NONCLUSTERED INDEX SS_SeriesLID ON #SeriesSummary ( SeriesLID ) CREATE NONCLUSTERED INDEX SS_GradeLID ON #SeriesSummary ( GradeLID ) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- #SeriesSummary INSERT INTO #SeriesSummary SELECT 251789416,'Measles1', 4, 0, 84082701, '7', 1, 1 UNION ALL SELECT 251789416,'Measles1', 4, 0, 84082701, '7', 5, 2 UNION ALL SELECT 251789416,'Measles1', 4, 0, 84082701, '7', 4, 3 UNION ALL SELECT 251789416,'Measles1', 4, 0, 84082701, '7', 3, 4 UNION ALL SELECT 251789416,'Measles1', 4, 0, 84082701, '7', 6, 5 UNION ALL SELECT 251789416,'Measles1', 4, 0, 84082694, 'K', 1, 1 UNION ALL SELECT 251789416,'Measles1', 4, 0, 84082694, 'K', 5, 2 UNION ALL SELECT 251789416,'Measles1', 4, 0, 84082694, 'K', 4, 3 UNION ALL SELECT 251789416,'Measles1', 4, 0, 84082694, 'K', 3, 4 UNION ALL SELECT 251789416,'Measles1', 4, 0, 84082694, 'K', 6, 5 UNION ALL SELECT 251789416,'Measles1', 4, 0, 84082711, 'K', 1, 1 UNION ALL SELECT 251789416,'Measles1', 4, 0, 84082711, 'K', 5, 2 UNION ALL SELECT 251789416,'Measles1', 4, 0, 84082711, 'K', 4, 3 UNION ALL SELECT 251789416,'Measles1', 4, 0, 84082711, 'K', 3, 4 UNION ALL SELECT 251789416,'Measles1', 4, 0, 84082711, 'K', 6, 5 UNION ALL SELECT 251789416,'Measles Second Dose', 5, 0, 84082701, '7', 1, 1 UNION ALL SELECT 251789416,'Measles Second Dose', 5, 0, 84082701, '7', 5, 2 UNION ALL SELECT 251789416,'Measles Second Dose', 5, 0, 84082701, '7', 4, 3 UNION ALL SELECT 251789416,'Measles Second Dose', 5, 0, 84082701, '7', 3, 4 UNION ALL SELECT 251789416,'Measles Second Dose', 5, 0, 84082701, '7', 6, 5 UNION ALL SELECT 251789416,'Measles Second Dose', 5, 0, 84082694, 'K', 1, 1 UNION ALL SELECT 251789416,'Measles Second Dose', 5, 0, 84082694, 'K', 5, 2 UNION ALL SELECT 251789416,'Measles Second Dose', 5, 0, 84082694, 'K', 4, 3 UNION ALL SELECT 251789416,'Measles Second Dose', 5, 0, 84082694, 'K', 3, 4 UNION ALL SELECT 251789416,'Measles Second Dose', 5, 0, 84082694, 'K', 6, 5 UNION ALL SELECT 251789416,'Measles Second Dose', 5, 0, 84082711, 'K', 1, 1 UNION ALL SELECT 251789416,'Measles Second Dose', 5, 0, 84082711, 'K', 5, 2 UNION ALL SELECT 251789416,'Measles Second Dose', 5, 0, 84082711, 'K', 4, 3 UNION ALL SELECT 251789416,'Measles Second Dose', 5, 0, 84082711, 'K', 3, 4 UNION ALL SELECT 251789416,'Measles Second Dose', 5, 0, 84082711, 'K', 6, 5 UNION ALL SELECT 251789417,'Mumps', 6, 0, 84082701, '7', 1, 1 UNION ALL SELECT 251789417,'Mumps', 6, 0, 84082701, '7', 5, 2 UNION ALL SELECT 251789417,'Mumps',6, 0, 84082701, '7', 4, 3 UNION ALL SELECT 251789417,'Mumps', 6, 0, 84082701, '7', 3, 4 UNION ALL SELECT 251789417,'Mumps', 6, 0, 84082701, '7', 6, 5 UNION ALL SELECT 251789417,'Mumps', 6, 0, 84082694, 'K', 1, 1 UNION ALL SELECT 251789417,'Mumps', 6, 0, 84082694, 'K', 5, 2 UNION ALL SELECT 251789417,'Mumps', 6, 0, 84082694, 'K', 4, 3 UNION ALL SELECT 251789417,'Mumps', 6, 0, 84082694, 'K', 3, 4 UNION ALL SELECT 251789417,'Mumps', 6, 0, 84082694, 'K', 6, 5 UNION ALL SELECT 251789417,'Mumps', 6, 0, 84082711, 'K', 1, 1 UNION ALL SELECT 251789417,'Mumps', 6, 0, 84082711, 'K', 5, 2 UNION ALL SELECT 251789417,'Mumps', 6, 0, 84082711, 'K', 4, 3 UNION ALL SELECT 251789417,'Mumps', 6, 0, 84082711, 'K', 3, 4 UNION ALL SELECT 251789417,'Mumps', 6, 0, 84082711, 'K', 6, 5 UNION ALL SELECT 251789418,'Rubella', 9, 0, 84082701, '7', 1, 1 UNION ALL SELECT 251789418,'Rubella', 9, 0, 84082701, '7', 5, 2 UNION ALL SELECT 251789418,'Rubella', 9, 0, 84082701, '7', 4, 3 UNION ALL SELECT 251789418,'Rubella', 9, 0, 84082701, '7', 3, 4 UNION ALL SELECT 251789418,'Rubella', 9, 0, 84082701, '7', 6, 5 UNION ALL SELECT 251789418,'Rubella', 9, 0, 84082694, 'K', 1, 1 UNION ALL SELECT 251789418,'Rubella', 9, 0, 84082694, 'K', 5, 2 UNION ALL SELECT 251789418,'Rubella', 9, 0, 84082694, 'K', 4, 3 UNION ALL SELECT 251789418,'Rubella', 9, 0, 84082694, 'K', 3, 4 UNION ALL SELECT 251789418,'Rubella', 9, 0, 84082694, 'K', 6, 5 UNION ALL SELECT 251789418,'Rubella', 9, 0, 84082711, 'K', 1, 1 UNION ALL SELECT 251789418,'Rubella', 9, 0, 84082711, 'K', 5, 2 UNION ALL SELECT 251789418,'Rubella', 9, 0, 84082711, 'K', 4, 3 UNION ALL SELECT 251789418,'Rubella', 9, 0, 84082711, 'K', 3, 4 UNION ALL SELECT 251789418,'Rubella', 9, 0, 84082711, 'K', 6, 5 UNION ALL SELECT 251789413,'Diptheria', 1, 0, 84082701, '7', 1, 1 UNION ALL SELECT 251789413,'Diptheria', 1, 0, 84082701, '7', 5, 2 UNION ALL SELECT 251789413,'Diptheria', 1, 0, 84082701, '7', 4, 3 UNION ALL SELECT 251789413,'Diptheria', 1, 0, 84082701, '7', 3, 4 UNION ALL SELECT 251789413,'Diptheria', 1, 0, 84082701, '7', 6, 5 UNION ALL SELECT 251789413,'Diptheria', 1, 0, 84082694, 'K', 1, 1 UNION ALL SELECT 251789413,'Diptheria', 1, 0, 84082694, 'K', 5, 2 UNION ALL SELECT 251789413,'Diptheria', 1, 0, 84082694, 'K', 4, 3 UNION ALL SELECT 251789413,'Diptheria', 1, 0, 84082694, 'K', 3, 4 UNION ALL SELECT 251789413,'Diptheria', 1, 0, 84082694, 'K', 6, 5 UNION ALL SELECT 251789413,'Diptheria', 1, 0, 84082711, 'K', 1, 1 UNION ALL SELECT 251789413,'Diptheria', 1, 0, 84082711, 'K', 5, 2 UNION ALL SELECT 251789413,'Diptheria', 1, 0, 84082711, 'K', 4, 3 UNION ALL SELECT 251789413,'Diptheria', 1, 0, 84082711, 'K', 3, 4 UNION ALL SELECT 251789413,'Diptheria', 1, 0, 84082711, 'K', 6, 5 UNION ALL SELECT 251789357,'Polio', 8, 0, 84082701, '7', 1, 1 UNION ALL SELECT 251789357,'Polio', 8, 0, 84082701, '7', 5, 2 UNION ALL SELECT 251789357,'Polio', 8, 0, 84082701, '7', 4, 3 UNION ALL SELECT 251789357,'Polio', 8, 0, 84082701, '7', 3, 4 UNION ALL SELECT 251789357,'Polio', 8, 0, 84082701, '7', 6, 5 UNION ALL SELECT 251789357,'Polio', 8, 0, 84082694, 'K', 1, 1 UNION ALL SELECT 251789357,'Polio', 8, 0, 84082694, 'K', 5, 2 UNION ALL SELECT 251789357,'Polio', 8, 0, 84082694, 'K', 4, 3 UNION ALL SELECT 251789357,'Polio', 8, 0, 84082694, 'K', 3, 4 UNION ALL SELECT 251789357,'Polio', 8, 0, 84082694, 'K', 6, 5 UNION ALL SELECT 251789357,'Polio', 8, 0, 84082711, 'K', 1, 1 UNION ALL SELECT 251789357,'Polio', 8, 0, 84082711, 'K', 5, 2 UNION ALL SELECT 251789357,'Polio', 8, 0, 84082711, 'K', 4, 3 UNION ALL SELECT 251789357,'Polio', 8, 0, 84082711, 'K', 3, 4 UNION ALL SELECT 251789357,'Polio', 8, 0, 84082711, 'K', 6, 5 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082693, 'PK', 1, 1 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082693, 'PK', 5, 2 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082693, 'PK', 4, 3 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082693, 'PK', 3, 4 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082693, 'PK', 6, 5 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082701, '7', 1, 1 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082701, '7', 5, 2 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082701, '7', 4, 3 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082701, '7', 3, 4 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082701, '7', 6, 5 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082694, 'K', 1, 1 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082694, 'K', 5, 2 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082694, 'K', 4, 3 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082694, 'K', 3, 4 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082694, 'K', 6, 5 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082711, 'K', 1, 1 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082711, 'K', 5, 2 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082711, 'K', 4, 3 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082711, 'K', 3, 4 UNION ALL SELECT 251789330,'HepA', 2, 0, 84082711, 'K', 6, 5 UNION ALL SELECT 251789333,'HepB', 3, 0, 84082701, '7', 1, 1 UNION ALL SELECT 251789333,'HepB', 3, 0, 84082701, '7', 5, 2 UNION ALL SELECT 251789333,'HepB', 3, 0, 84082701, '7', 4, 3 UNION ALL SELECT 251789333,'HepB', 3, 0, 84082701, '7', 3, 4 UNION ALL SELECT 251789333,'HepB', 3, 0, 84082701, '7', 6, 5 UNION ALL SELECT 251789333,'HepB', 3, 0, 84082694, 'K', 1, 1 UNION ALL SELECT 251789333,'HepB', 3, 0, 84082694, 'K', 5, 2 UNION ALL SELECT 251789333,'HepB', 3, 0, 84082694, 'K', 4, 3 UNION ALL SELECT 251789333,'HepB', 3, 0, 84082694, 'K', 3, 4 UNION ALL SELECT 251789333,'HepB', 3, 0, 84082694, 'K', 6, 5 UNION ALL SELECT 251789333,'HepB', 3, 0, 84082711, 'K', 1, 1 UNION ALL SELECT 251789333,'HepB', 3, 0, 84082711, 'K', 5, 2 UNION ALL SELECT 251789333,'HepB', 3, 0, 84082711, 'K', 4, 3 UNION ALL SELECT 251789333,'HepB', 3, 0, 84082711, 'K', 3, 4 UNION ALL SELECT 251789333,'HepB', 3, 0, 84082711, 'K', 6, 5 UNION ALL -- SELECT 251789336,'HIB', 0, 84082701, '7', 1, 1 UNION ALL -- SELECT 251789336,'HIB', 0, 84082701, '7', 5, 2 UNION ALL -- SELECT 251789336,'HIB', 0, 84082701, '7', 4, 3 UNION ALL -- SELECT 251789336,'HIB', 0, 84082701, '7', 3, 4 UNION ALL -- SELECT 251789336,'HIB', 0, 84082701, '7', 6, 5 UNION ALL -- SELECT 251789336,'HIB', 0, 84082694, 'K', 1, 1 UNION ALL -- SELECT 251789336,'HIB', 0, 84082694, 'K', 5, 2 UNION ALL -- SELECT 251789336,'HIB', 0, 84082694, 'K', 4, 3 UNION ALL -- SELECT 251789336,'HIB', 0, 84082694, 'K', 3, 4 UNION ALL -- SELECT 251789336,'HIB', 0, 84082694, 'K', 6, 5 UNION ALL -- SELECT 251789336,'HIB', 0, 84082711, 'K', 1, 1 UNION ALL -- SELECT 251789336,'HIB', 0, 84082711, 'K', 5, 2 UNION ALL -- SELECT 251789336,'HIB', 0, 84082711, 'K', 4, 3 UNION ALL -- SELECT 251789336,'HIB', 0, 84082711, 'K', 3, 4 UNION ALL -- SELECT 251789336,'HIB', 0, 84082711, 'K', 6, 5 UNION ALL -- SELECT 251789366,'Varicella', 10, 0, 84082701, '7', 1, 1 UNION ALL SELECT 251789366,'Varicella', 10, 0, 84082701, '7', 5, 2 UNION ALL SELECT 251789366,'Varicella', 10, 0, 84082701, '7', 4, 3 UNION ALL SELECT 251789366,'Varicella', 10, 0, 84082701, '7', 3, 4 UNION ALL SELECT 251789366,'Varicella', 10, 0, 84082701, '7', 6, 5 UNION ALL SELECT 251789366,'Varicella', 10, 0, 84082694, 'K', 1, 1 UNION ALL SELECT 251789366,'Varicella', 10, 0, 84082694, 'K', 5, 2 UNION ALL SELECT 251789366,'Varicella', 10, 0, 84082694, 'K', 4, 3 UNION ALL SELECT 251789366,'Varicella', 10, 0, 84082694, 'K', 3, 4 UNION ALL SELECT 251789366,'Varicella', 10, 0, 84082694, 'K', 6, 5 UNION ALL SELECT 251789366,'Varicella', 10, 0, 84082711, 'K', 1, 1 UNION ALL SELECT 251789366,'Varicella', 10, 0, 84082711, 'K', 5, 2 UNION ALL SELECT 251789366,'Varicella', 10, 0, 84082711, 'K', 4, 3 UNION ALL SELECT 251789366,'Varicella', 10, 0, 84082711, 'K', 3, 4 UNION ALL SELECT 251789366,'Varicella', 10, 0, 84082711, 'K', 6, 5 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082693, 'PK', 1, 1 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082693, 'PK', 5, 2 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082693, 'PK', 4, 3 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082693, 'PK', 3, 4 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082693, 'PK', 6, 5 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082701, '7', 1, 1 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082701, '7', 5, 2 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082701, '7', 4, 3 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082701, '7', 3, 4 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082701, '7', 6, 5 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082694, 'K', 1, 1 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082694, 'K', 5, 2 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082694, 'K', 4, 3 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082694, 'K', 3, 4 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082694, 'K', 6, 5 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082711, 'K', 1, 1 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082711, 'K', 5, 2 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082711, 'K', 4, 3 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082711, 'K', 3, 4 UNION ALL SELECT 251789354,'Pneumococccal' , 7, 0, 84082711, 'K', 6, 5 UNION ALL SELECT 251789345,'Meningococcal', 11, 0, 84082701, '7', 1, 1 UNION ALL SELECT 251789345,'Meningococcal', 11, 0, 84082701, '7', 5, 2 UNION ALL SELECT 251789345,'Meningococcal', 11, 0, 84082701, '7', 4, 3 UNION ALL SELECT 251789345,'Meningococcal', 11, 0, 84082701, '7', 3, 4 UNION ALL SELECT 251789345,'Meningococcal', 11, 0, 84082701, '7', 6, 5 ------------------------------------------------------------------------------------------------------------------- -- pulled from usp_rpt_903TexasImmune_CalcStatus_ddl -------------------------------------------------------------------------------------------------------------------- IF OBJECT_ID('TempDB..#tmp_mainpage') IS NOT NULL DROP TABLE #tmp_mainpage CREATE TABLE #tmp_mainpage (ReligiousTotPK INT DEFAULT(0), ReligiousTotK INT DEFAULT(0), ReligiousTot7 INT DEFAULT(0), NoImmunePK INT DEFAULT(0), NoImmuneK INT DEFAULT(0), NoImmune7 INT DEFAULT(0), NumPKStud INT DEFAULT(0), NumKStud INT DEFAULT(0), Num7Stud INT DEFAULT(0)) INSERT INTO #tmp_mainpage (ReligiousTotPK, ReligiousTotK, ReligiousTot7, NoImmunePK, NoImmuneK, NoImmune7, NumPKStud, NumKStud, Num7Stud) VALUES (0, 0, 0, 0, 0, 0, 0, 0, 0) UPDATE #tmp_mainpage SET ReligiousTot7 = ( SELECT Count (DISTINCT CAST(PersonPGUID AS varchar(40))) FROM #tmp_main WHERE GradeLID = 84082701 AND ((SeriesStatus like 'Religious%') OR (SeriesStatus like 'Other%') )), ReligiousTotK = ( SELECT Count (DISTINCT CAST(PersonPGUID AS varchar(40))) FROM #tmp_main WHERE GradeLID in (84082694,84082711) AND ((SeriesStatus like 'Religious%') OR (SeriesStatus like 'Other%') )), ReligiousTotPK = ( SELECT Count (DISTINCT CAST(PersonPGUID AS varchar(40))) FROM #tmp_main WHERE GradeLID = 84082693 AND ((SeriesStatus like 'Religious%') OR (SeriesStatus like 'Other%') )) IF OBJECT_ID('TempDB..#tmpnoimmune') IS NOT NULL DROP TABLE #tmpnoimmune CREATE TABLE #tmpnoimmune ( cnt int NOT NULL, GradeLID int NOT NULL ) INSERT #tmpnoimmune (cnt, GradeLID) SELECT COUNT(DISTINCT CAST(PersonPGUID AS varchar(40))) as cnt, GradeLID--, CAST(PersonPGUID AS varchar(40)), COUNT(Scheduled) AS required -- INTO #tmpnoimmune2 FROM #tmp_main WHERE (Dose1Date IS NULL) AND (Dose2Date IS NULL) AND (Dose3Date IS NULL) AND (Dose4Date IS NULL) AND (Dose5Date IS NULL) AND (Dose6Date IS NULL) AND (Dose7Date IS NULL) AND (Dose8Date IS NULL) AND (Dose9Date IS NULL) AND (Dose10Date IS NULL) AND (Dose11Date IS NULL) AND (Dose12Date IS NULL) AND (Dose13Date IS NULL) AND (Dose14Date IS NULL) AND (Scheduled = 1) AND (SeriesStatus IN ('Delinquent')) GROUP BY CAST(PersonPGUID AS varchar(40)), GradeLID -- GROUP BY GradeLID having COUNT(Scheduled) >(SELECT COUNT(Immunization_Series.Immunization_SeriesID)-1 AS Required FROM Immunization_Local INNER JOIN Immunization_Series ON Immunization_Local.Immunization_LocalID = Immunization_Series.Immunization_LocalID INNER JOIN #series on #series.ActionLID= Immunization_Series.Immunization_SeriesID AND #series.LIDID <> 'Pneumococccal' AND #series.LIDID <> 'HIB' WHERE (Immunization_Local.DefaultLocal = 1)) UPDATE #tmp_mainpage SET NoImmunePK = ISNULL(( SELECT ISNULL(cnt,0) FROM #tmpnoimmune WHERE gradelid = 84082693),0), NoImmuneK = ISNULL(( SELECT ISNULL(cnt,0) FROM #tmpnoimmune WHERE gradelid in (84082694, 84082711)),0), NoImmune7 = ISNULL(( SELECT ISNULL(cnt,0) FROM #tmpnoimmune WHERE gradelid = 84082701),0) UPDATE #tmp_mainpage SET NumPKStud = ISNULL(( SELECT Count (DISTINCT CAST(PersonPGUID AS varchar(40))) AS num FROM #tmp_main WHERE (GradeLID = 84082693)),0), NumKStud = ISNULL(( SELECT Count (DISTINCT CAST(PersonPGUID AS varchar(40))) AS num FROM #tmp_main WHERE (GradeLID in (84082694, 84082711))),0), Num7Stud = ISNULL(( SELECT Count (DISTINCT CAST(PersonPGUID AS varchar(40))) AS num FROM #tmp_main WHERE (GradeLID = 84082701)) ,0) --SELECT DISTINCT CAST(PersonPGUID AS varchar(40)) AS Num7Stud -- FROM #tmp_main -- WHERE (GradeLID = 84082701) --select Num7Stud from #tmp_mainpage ----------------------------------- #SeriesStatus -------------------------------------------------------------------------- IF OBJECT_ID('TempDB..#Summary') IS NOT NULL DROP TABLE #Summary IF OBJECT_ID('TempDB..#SeriesStatus') IS NOT NULL DROP TABLE #SeriesStatus IF OBJECT_ID('TempDB..#SeriesSummary2') IS NOT NULL DROP TABLE #SeriesSummary2 IF OBJECT_ID('TempDB..#SeriesSummary3') IS NOT NULL DROP TABLE #SeriesSummary3 IF OBJECT_ID('TempDB..#SeriesSummary4') IS NOT NULL DROP TABLE #SeriesSummary4 IF OBJECT_ID('TempDB..#SeriesSummary5') IS NOT NULL DROP TABLE #SeriesSummary5 IF OBJECT_ID('TempDB..#SeriesSummary2A') IS NOT NULL DROP TABLE #SeriesSummary2A IF OBJECT_ID('TempDB..#SeriesSummary2B') IS NOT NULL DROP TABLE #SeriesSummary2B CREATE TABLE #SeriesStatus ( OldStatus INT, NewStatus VARCHAR(50), ) -- CREATE NONCLUSTERED INDEX ak_OldStatus -- ON #SeriesStatus ( OldStatus ) -- -- CREATE NONCLUSTERED INDEX ak_NewStatus -- ON #SeriesStatus ( NewStatus ) INSERT INTO #SeriesStatus SELECT 1,'Current' UNION ALL SELECT 1,'Complete' UNION ALL SELECT 1,'Immune' UNION ALL SELECT 1,'Immune (Global Exmpt)' UNION ALL SELECT 5,'Provisional' UNION ALL SELECT 4,'Religious' UNION ALL SELECT 4,'Religious (Global Exmpt)' UNION ALL SELECT 4,'Other' UNION ALL SELECT 3,'Medical' UNION ALL SELECT 3,'Medical (Global Exmpt)' UNION ALL SELECT 6,'Delinquent' UNION ALL SELECT 6,'Denied' UNION ALL SELECT 1,'Unscheduled' SELECT COUNT(SCHEDULED) AS Students, SeriesLID, Series, Section, gradelid, #SeriesStatus.OldStatus INTO #Summary FROM #tmp_main INNER JOIN #SeriesStatus ON #tmp_main.SeriesStatus = #SeriesStatus.NewStatus WHERE #tmp_main.SeriesLID <> 251789345 GROUP BY SeriesLID, Series, Section, gradelid, #SeriesStatus.OldStatus INSERT INTO #Summary SELECT COUNT(SCHEDULED) AS Students, SeriesLID, Series, Section, gradelid, #SeriesStatus.OldStatus FROM #tmp_main INNER JOIN #SeriesStatus ON #tmp_main.SeriesStatus = #SeriesStatus.NewStatus WHERE #tmp_main.SeriesLID = 251789345 AND Dose1Date IS NOT NULL GROUP BY SeriesLID, Series, Section, gradelid, #SeriesStatus.OldStatus -- SELECT COUNT(SCHEDULED) AS Students, SeriesLID, Series, SeriesStatus, Section, gradelid, #SeriesStatus.OldStatus -- FROM #tmp_main INNER JOIN -- #SeriesStatus ON #tmp_main.SeriesStatus = #SeriesStatus.NewStatus -- WHERE (SeriesLID = 251789416) -- GROUP BY SeriesLID, Series, SeriesStatus, Section, gradelid, #SeriesStatus.OldStatus -- SELECT ISNULL(COUNT(SCHEDULED), 0) AS Move, GradeLID, SeriesStatus, -- CASE -- WHEN SeriesStatus = 'provisional' THEN 5 -- WHEN SeriesStatus = 'medical' THEN 3 -- WHEN SeriesStatus = 'other' THEN 4 -- ELSE 0 -- END AS OldStatus -- INTO #SummaryMeasles1 -- FROM #tmp_main -- WHERE (SeriesLID = 251789416) -- AND (LEN(RTrim(Section)) = 0) -- AND (Status1 <> 'I') -- AND (SeriesStatusORD <> 0) -- AND (SeriesStatus IN ('provisional', 'medical', 'other')) -- AND (Dose1Date IS NOT NULL) -- GROUP BY GradeLID, SeriesStatus -- -- SELECT Sum(Move) AS Move, GradeLID -- INTO #SummaryMeasles2 -- FROM #SummaryMeasles1 -- GROUP BY GradeLID -- -- UPDATE #Summary -- SET -- Students = Students - Move -- FROM #SummaryMeasles1 -- INNER JOIN #Summary ON (#SummaryMeasles1.OldStatus=#Summary.OldStatus) AND (#SummaryMeasles1.GradeLID=#Summary.GradeLID) -- WHERE (#Summary.SeriesLID = 251789416) -- AND (LEN(RTrim(Section)) = 0) -- -- UPDATE #Summary -- SET -- Students = Students + Move -- FROM #SummaryMeasles2 -- INNER JOIN #Summary ON #SummaryMeasles2.GradeLID=#Summary.GradeLID -- WHERE (#Summary.SeriesLID = 251789416) -- AND (#Summary.OldStatus =1) -- AND (LEN(RTrim(Section)) = 0) -- -- UPDATE #Summary -- SET -- Students = 0 -- WHERE (#Summary.SeriesLID = 251789416) -- AND (Students < 0) -- AND (LEN(RTrim(Section)) = 0) UPDATE #SeriesSummary SET Students = #Summary.Students FROM #Summary WHERE #SeriesSummary.SeriesLID = #Summary.SeriesLID AND #SeriesSummary.GradeLID = #Summary.GradeLID AND #SeriesSummary.OLDStatus = #Summary.OLDStatus AND (LEN(RTrim(#Summary.Section)) = 0) UPDATE #SeriesSummary SET Students = #Summary.Students FROM #Summary WHERE #SeriesSummary.Series = 'Measles Second Dose' AND #SeriesSummary.GradeLID = #Summary.GradeLID AND #SeriesSummary.OLDStatus = #Summary.OLDStatus -- AND (len(#Summary.Section) >0) AND (LEN(RTrim(#Summary.Section)) >0) AND #Summary.SeriesLID = 251789416 SELECT SUM(Students) as Students, SeriesORD, [Column] INTO #SeriesSummary2 FROM #SeriesSummary WHERE Grade='K' GROUP BY SeriesORD, [Column] ORDER BY SeriesORD, [Column] SELECT distinct SeriesORD, (Select Students FROM #SeriesSummary2 as Col1 WHERE ([Column] = 1) AND Col1.SeriesORD =#SeriesSummary2.SeriesORD)AS Column1, (Select Students FROM #SeriesSummary2 as Col WHERE ([Column] = 2) AND Col.SeriesORD =#SeriesSummary2.SeriesORD)AS Column2, (Select Students FROM #SeriesSummary2 as Col WHERE ([Column] = 3) AND Col.SeriesORD =#SeriesSummary2.SeriesORD)AS Column3, (Select Students FROM #SeriesSummary2 as Col WHERE ([Column] = 4) AND Col.SeriesORD =#SeriesSummary2.SeriesORD)AS Column4, (Select Students FROM #SeriesSummary2 as Col WHERE ([Column] = 5) AND Col.SeriesORD =#SeriesSummary2.SeriesORD)AS Column5, (Select sum(Students) FROM #SeriesSummary2 as Col WHERE Col.SeriesORD =#SeriesSummary2.SeriesORD)AS Column6 INTO #SeriesSummary3 FROM #SeriesSummary2 SELECT SUM(Students) as Students, SeriesORD, [Column] INTO #SeriesSummary2A FROM #SeriesSummary WHERE Grade='7' GROUP BY SeriesORD, [Column] ORDER BY SeriesORD, [Column] SELECT distinct SeriesORD, (Select Students FROM #SeriesSummary2A as Col1 WHERE ([Column] = 1) AND Col1.SeriesORD =#SeriesSummary2A.SeriesORD)AS Column1, (Select Students FROM #SeriesSummary2A as Col WHERE ([Column] = 2) AND Col.SeriesORD =#SeriesSummary2A.SeriesORD)AS Column2, (Select Students FROM #SeriesSummary2A as Col WHERE ([Column] = 3) AND Col.SeriesORD =#SeriesSummary2A.SeriesORD)AS Column3, (Select Students FROM #SeriesSummary2A as Col WHERE ([Column] = 4) AND Col.SeriesORD =#SeriesSummary2A.SeriesORD)AS Column4, (Select Students FROM #SeriesSummary2A as Col WHERE ([Column] = 5) AND Col.SeriesORD =#SeriesSummary2A.SeriesORD)AS Column5, (Select sum(Students) FROM #SeriesSummary2A as Col WHERE Col.SeriesORD =#SeriesSummary2A.SeriesORD)AS Column6 INTO #SeriesSummary4 FROM #SeriesSummary2A SELECT SUM(Students) as Students, SeriesORD, [Column] INTO #SeriesSummary2B FROM #SeriesSummary WHERE Grade='PK' GROUP BY SeriesORD, [Column] ORDER BY SeriesORD, [Column] SELECT distinct SeriesORD, (Select Students FROM #SeriesSummary2B as Col1 WHERE ([Column] = 1) AND Col1.SeriesORD =#SeriesSummary2B.SeriesORD)AS Column1, (Select Students FROM #SeriesSummary2B as Col WHERE ([Column] = 2) AND Col.SeriesORD =#SeriesSummary2B.SeriesORD)AS Column2, (Select Students FROM #SeriesSummary2B as Col WHERE ([Column] = 3) AND Col.SeriesORD =#SeriesSummary2B.SeriesORD)AS Column3, (Select Students FROM #SeriesSummary2B as Col WHERE ([Column] = 4) AND Col.SeriesORD =#SeriesSummary2B.SeriesORD)AS Column4, (Select Students FROM #SeriesSummary2B as Col WHERE ([Column] = 5) AND Col.SeriesORD =#SeriesSummary2B.SeriesORD)AS Column5, (Select sum(Students) FROM #SeriesSummary2B as Col WHERE Col.SeriesORD =#SeriesSummary2B.SeriesORD)AS Column6 INTO #SeriesSummary5 FROM #SeriesSummary2B --SELECT * --FROM #SeriesSummary -- SELECT SUM(Students) as Students, SeriesLID, Series, Grade, [Column] -- FROM #SeriesSummary -- GROUP BY SeriesLID, Series, Grade, [Column] -- -- SELECT ISNULL(Students,0) AS DTPKComp, gradelid -- FROM #Summary -- WHERE OldStatus = 1 -- AND (SeriesLID = 251789413) -- GROUP BY ISNULL(Students,0), gradelid -- --AND gradelid in (84082694,84082711) --SELECT * FROM #tmp_mainpage ------------------------------------------------------------------------------------------------------------------- -- -------------------------------------------------------------------------------------------------------------------- SET @CMD = 'SELECT 1 as tblID, * ' + @conCR + 'INTO ' + @WRKTBL2 + @conCR + 'FROM #Header' + @conCR --PRINT @CMD EXECUTE (@CMD) IF @@ERROR <> 0 BEGIN SET @nERROR = @@ERROR PRINT 'An error occurred copying data into table ' + @WRKTBL2 + '.' --RETURN(@nERROR) END -- create work table using table name = argument: @WRKTBL_Main and Order by criteria SET @CMD = 'SELECT 1 as tblID, * ' + @conCR + 'INTO ' + @WRKTBL_Main + @conCR + 'FROM #tmp_mainpage ' + @conCR -- + 'ORDER BY ' + @CMD_ORDER_BY + ', PersonPGUID, seriesORD' --PRINT @CMD EXECUTE (@CMD) IF @@ERROR <> 0 BEGIN SET @nERROR = @@ERROR PRINT 'An error occurred copying data into table ' + @WRKTBL_Main + '.' --RETURN(@nERROR) END -- UPDATE #tmp_main -- SET [Series] = 'Measles Second Dose' -- WHERE (len(Section) >0) UPDATE #tmp_main SET [Series] = 'Measles First Dose' WHERE (len(Section) =0) AND SeriesLID =251789416 --select * from #tmp_main SET @CMD = 'SELECT 1 as tblID, * ' + 'INTO ' + @WRKTBL1 + @conCR + ' FROM #tmp_main ' + @conCR + 'ORDER BY SeriesOrd, Section, GradeOrd, SeriesStatusORD, FullName' -- SET @CMD = 'SELECT 1 as tblID, -- CASE -- WHEN (len(Section) >0) THEN ''Measles2'' -- ELSE [Series] -- END AS Series, FullName,* ' + -- 'INTO ' + @WRKTBL1 + @conCR + -- ' FROM #tmp_main ' + @conCR + -- 'ORDER BY FullName, seriesORD' --PRINT @CMD EXECUTE (@CMD) IF @@ERROR <> 0 BEGIN SET @nERROR = @@ERROR PRINT 'An error occurred copying data into table ' + @WRKTBL1 + '.' --RETURN(@nERROR) END SET @CMD = 'SELECT 1 as tblID, * ' + @conCR + 'INTO ' + @WRKTBL3 + @conCR + 'FROM #SeriesSummary3 ' --PRINT @CMD EXECUTE (@CMD) IF @@ERROR <> 0 BEGIN SET @nERROR = @@ERROR PRINT 'An error occurred copying data into table ' + @WRKTBL3 + '.' --RETURN(@nERROR) END SET @CMD = 'SELECT 1 as tblID, * ' + @conCR + 'INTO ' + @WRKTBL4 + @conCR + 'FROM #SeriesSummary4 ' --PRINT @CMD EXECUTE (@CMD) IF @@ERROR <> 0 BEGIN SET @nERROR = @@ERROR PRINT 'An error occurred copying data into table ' + @WRKTBL4 + '.' --RETURN(@nERROR) END SET @CMD = 'SELECT 1 as tblID, * ' + @conCR + 'INTO ' + @WRKTBL5 + @conCR + 'FROM #SeriesSummary5 ' --PRINT @CMD EXECUTE (@CMD) IF @@ERROR <> 0 BEGIN SET @nERROR = @@ERROR PRINT 'An error occurred copying data into table ' + @WRKTBL5 + '.' --RETURN(@nERROR) END -------------------------------------------------------------------------------------------------------------------- -- Drop temp tables -------------------------------------------------------------------------------------------------------------------- -- DROP TABLE #Persons --DROP TABLE #Header --DROP TABLE #NumSchools --DROP TABLE #tmp_main --DROP TABLE #tmp_mainpage --DROP TABLE #tmpnoimmune --DROP TABLE #SeriesSummary2 --DROP TABLE #SeriesSummary2A --DROP TABLE #SeriesSummary2B --DROP TABLE #SeriesSummary3 --DROP TABLE #SeriesSummary4 --DROP TABLE #SeriesSummary5 --DROP TABLE #tmp_criteria --DROP TABLE #zrptImmunDetails --DROP TABLE #MeaslesSeriesStatus -------------------------------------------------------------------------------------------------------------------- -- Return result -------------------------------------------------------------------------------------------------------------------- -- -- IF @@ERROR = 0 -- RETURN @RetVal -- ELSE -- RETURN @@ERROR -- select '(J) Pre-K enrollment' Title, convert(varchar(100), NumPKStud) Value from zrpt903_admin union select '(K) Total number of PK students with conscientious exemption', convert(varchar(100), ReligiousTotPK) from zrpt903_admin UNION select '(L) Total number of Pre-K students without an immunization record', convert(varchar(100), NoImmunePK) from zrpt903_admin union select '(Grade 7) Meningococcal Current - Up To Date', convert(varchar(100),Column1) from zrpt90304_admin where seriesord = 11 union select '(Grade 7) Meningococcal Current - Provisional', convert(varchar(100),Column2) from zrpt90304_admin where seriesord = 11 union select '(Grade 7) Meningococcal Exempt - Conscientious', convert(varchar(100),Column3) from zrpt90304_admin where seriesord = 11 union select '(Grade 7) Meningococcal Exempt - Medical', convert(varchar(100),Column4) from zrpt90304_admin where seriesord = 11 union select '(Grade 7) Meningococcal Delinquent', convert(varchar(100),Column5) from zrpt90304_admin where seriesord = 11 union select '(Grade 7) Meningococcal Totals', convert(varchar(100),Column6) from zrpt90304_admin where seriesord = 11 union select CASE SeriesORD WHEN 2 THEN '(Pre-K) HepA' WHEN 7 THEN '(Pre-K) Pneumococccal' END + ' Current - Up To Date', convert(varchar(100),Column1) from zrpt90305_admin union select CASE SeriesORD WHEN 2 THEN '(Pre-K) HepA' WHEN 7 THEN '(Pre-K) Pneumococccal' END + ' Current - Provisional', convert(varchar(100),Column2) from zrpt90305_admin union select CASE SeriesORD WHEN 2 THEN '(Pre-K) HepA' WHEN 7 THEN '(Pre-K) Pneumococccal' END + ' Exempt - Conscientious', convert(varchar(100),Column3) from zrpt90305_admin union select CASE SeriesORD WHEN 2 THEN '(Pre-K) HepA' WHEN 7 THEN '(Pre-K) Pneumococccal' END + ' Exempt - Medical', convert(varchar(100),Column4) from zrpt90305_admin union select CASE SeriesORD WHEN 2 THEN '(Pre-K) HepA' WHEN 7 THEN '(Pre-K) Pneumococccal' END + ' Delinquent', convert(varchar(100),Column5) from zrpt90305_admin union select CASE SeriesORD WHEN 2 THEN '(Pre-K) HepA' WHEN 7 THEN '(Pre-K) Pneumococccal' END + ' Totals', convert(varchar(100),Column6) from zrpt90305_admin