?
Solved

Getting 1900-01-01 in Date Fields where value should be NULL

Posted on 2011-04-18
4
Medium Priority
?
333 Views
Last Modified: 2012-05-11
In my 'EligibilityStart' and 'EligibilityEnd', I am getting back '1900-01-01 00:00:00.000' when the value should in fact be NULL on some rows. Any insight and help is appreciated.
USE [CentricityPS]
GO
/****** Object:  StoredProcedure [dbo].[cusMMAuthMstPrntRecsGet]    Script Date: 04/18/2011 11:32:17 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


--SC 03/17/06 MMBS 2.8.7 - converted fr cusPatAuthAuthPrntRecsGet
-- Jeff CYS 04/18/2011 Add in PCP and RP NPI - Update Narrative Notes for CPS 9 

ALTER PROCEDURE [dbo].[cusMMAuthMstPrntRecsGet_OC] @lcACodeFr VARCHAR(60)
AS 
BEGIN

      SET NOCOUNT ON

      DECLARE @lcACodeTo VARCHAR(30)
      DECLARE @nBrkVar INT,
              @nCodeLen AS INT

      IF @lcACodeFr = '' 
         SET @lcACodeFr = ''     -- do nothing - nothing was passed
      ELSE 
         IF LEFT(@lcACodeFr, 2) = '::' 
            BEGIN
                  SET @lcACodeTo = RIGHT(@lcACodeFr, LEN(@lcACodeFr) - 2)        
                  SET @lcACodeFr = ''
            END
         ELSE 
            BEGIN
                  SET @nCodeLen = LEN(@lcACodeFr)
                  SET @nBrkVar = 0
                  WHILE ( @nBrkVar <= ( @nCodeLen ) )
                        BEGIN  
                              IF @nBrkVar = @nCodeLen 
                                 BEGIN       
                                       SET @nBrkVar = -1
                                       BREAK
                                 END
                              ELSE 
                                 IF SUBSTRING(@lcACodeFr, @nBrkVar, 2) = '::' 
                                    BREAK       
                                 ELSE 
                                    BEGIN
                                          SET @nBrkVar = @nBrkVar + 1
                                          CONTINUE
                                    END 
                        END -- While
                
                  IF @nBrkVar = -1 
                     SET @lcACodeTo = ''
                  ELSE 
                     BEGIN
                           SET @lcACodeTo = RIGHT(@lcACodeFr, @nCodeLen - ( @nBrkVar + 1 ))
                           SET @lcACodeFr = LEFT(@lcACodeFr, @nBrkVar - 1)
                     END
            END
    
      IF @lcACodeFr = '' 
         SET @lcACodeFr = NULL
    
      IF @lcACodeTo = '' 
         SET @lcACodeTo = NULL

      CREATE TABLE #AuthPrnt ( cusMMAuthMstID INT,
                               Code VARCHAR(12),
                               AuthDte DATETIME,
                               ExpDte DATETIME,
                               RcvdDte DATETIME,
                               ActionDte DATETIME,
                               ApptDte DATETIME,
                               ExtndDte DATETIME,
                               HPTrack VARCHAR(30),
                               TicketNumber VARCHAR(20),
                               FaxPCPDte DATETIME,
                               FaxPrvDte DATETIME,
                               FaxFacDte DATETIME,
                               CnsltRptReviewDte DATETIME,
                               CnsltRptRcvDte DATETIME,
                               CnsltRptReviewBy VARCHAR(60),
                               Created DATETIME,
                               CreatedBy VARCHAR(60),
                               LastModified DATETIME,
                               LastModifiedBy VARCHAR(60),
                               AuthStatus VARCHAR(20),
                               AStatDescr VARCHAR(200),
                               bApproved BIT,
                               PatientID VARCHAR(15),
                               MName VARCHAR(60),
                               MAddress VARCHAR(50),
                               MCity VARCHAR(30),
                               MState VARCHAR(2),
                               MZip VARCHAR(10),
                               MPhone1 VARCHAR(14),
                               MDOB DATETIME,
                               MSex VARCHAR(1),
                               MMedicalRecordNumber VARCHAR(15),
                               MSSN VARCHAR(9),
                               HPName VARCHAR(30),
                               HPType CHAR(1),
                               DenyCode VARCHAR(20),
                               RPName VARCHAR(60),
                               RP_NPI VARCHAR(80),
                               RPFName VARCHAR(15),
                               RPMI CHAR(1),
                               RPLName VARCHAR(40),
                               RPAddress VARCHAR(30),
                               RPCity VARCHAR(30),
                               RPState VARCHAR(2),
                               RPZip VARCHAR(10),
                               RPPhone VARCHAR(14),
                               RPFax VARCHAR(14),
                               bRPNoFax BIT,
                               RPSpec VARCHAR(30),
                               PCPName VARCHAR(60),
                               PCP_NPI VARCHAR(80),
                               PCPFName VARCHAR(15),
                               PCPMI CHAR(1),
                               PCPLName VARCHAR(40),
                               PCPPhone VARCHAR(14),
                               PCPFax VARCHAR(14),
                               bPCPNoFax BIT,
                               PCPAddress VARCHAR(30),
                               PCPCity VARCHAR(30),
                               PCPState VARCHAR(2),
                               PCPZip VARCHAR(10),
                               PCPSpec VARCHAR(30),
                               POSCode VARCHAR(20),
                               POSDescr VARCHAR(100),
                               Pos_ELOS TINYINT,
                               Pos_Admit DATETIME,
                               Pos_Dischg DATETIME,
                               FacName VARCHAR(60),
                               FacAddress VARCHAR(50),
                               FacCity VARCHAR(30),
                               FacZip VARCHAR(10),
                               FacState VARCHAR(2),
                               FacPhone VARCHAR(14),
                               FacFax VARCHAR(14),
                               OutCode VARCHAR(20),
                               OutDescr VARCHAR(100),
                               OutFac VARCHAR(60),
                               ICD9_1 VARCHAR(10),
                               ICD9_1Txt VARCHAR(50),
                               ICD9_2 VARCHAR(10),
                               ICD9_2Txt VARCHAR(50),
                               ICD9_3 VARCHAR(10),
                               ICD9_3Txt VARCHAR(50),
                               ICD9_4 VARCHAR(10),
                               ICD9_4Txt VARCHAR(50),
                               Qty INT,
                               CoPay MONEY,
                               ProcCode VARCHAR(10),
                               ProcDescr VARCHAR(255),
                               ANotes VARCHAR(MAX),
							   NoteLastModified DATETIME, 
                               SNotes VARCHAR(100),
                               CompanyName VARCHAR(100),
                               CompanyAddress VARCHAR(100),
                               CompanyCSZ VARCHAR(100),
							   EligibilityStart DATETIME, 
							   EligibilityEnd DATETIME	
								 ) 

      DECLARE p_AuthMst CURSOR
              FOR SELECT
						  am.cusMMAuthMstID,
                          am.PatientProfileId,
                          am.ACode,
                          am.PatientInsuranceID,
                          am.AuthDte,
                          am.ExpDte,
                          am.RcvdDte,
                          am.ActionDte,
                          am.ApptDte,
                          am.ExtndDte,
                          am.HPTrack,
                          am.FaxPCPDte,
                          am.FaxPrvDte,
                          am.FaxFacDte,
                          am.CnsltRptReviewDte,
                          am.CnsltRptRcvDte,
                          am.CnsltRptReviewBy,
                          am.AppointmentsID,
                          am.PatientVisitID,
                          am.Created,
                          am.CreatedBy,
                          am.LastModified,
                          am.LastModifiedBy,
                          ISNULL(ML_AStatus.Code, '') AS AuthStatus,
                          ISNULL(ML_AStatus.Description, '') AS AStatDescr,
                          PP.PatientId AS PatientId,
                          PP.[Last] + ', ' + PP.[First] + CASE WHEN PP.Middle IS NULL THEN ''
                                                               ELSE ' ' + PP.Middle
                                                          END AS MName,
                          ISNULL(pp.Address1, '') AS MAddress,
                          ISNULL(pp.City, '') AS MCity,
                          ISNULL(pp.State, '') AS MState,
                          ISNULL(pp.Zip, '') AS MZip,
                          ISNULL(pp.Phone1, '') AS MPhone1,
                          PP.Birthdate AS MDOB,
                          ISNULL(pp.Sex, '') AS MSex,
                          ISNULL(pp.MedicalRecordNumber, '') AS MMedicalRecordNumber,
                          ISNULL(PP.SSN, '') AS MSSN,
                          ISNULL(ML_Deny.Code, '') AS DenyCode,
                          DF_RDr.ListName AS RPName,
                          ISNULL(DF_RDr.NPI, '') AS RP_NPI,
                          ISNULL(DF_RDr.First, '') AS RPFName,
                          ISNULL(DF_RDr.Middle, '') AS RPMI,
                          ISNULL(DF_RDr.Last, '') AS RPLName,
                          ISNULL(DF_RDr.Address1, '') AS RPAddress,
                          ISNULL(DF_RDr.City, '') AS RPCity,
                          ISNULL(DF_RDr.State, '') AS RPState,
                          ISNULL(DF_RDr.Zip, '') AS RPZip,
                          ISNULL(DF_RDr.Phone1, '') AS RPPhone,
                          ISNULL(DF_RDr.Phone2Type, 'None') AS RPPhone2Type,
                          ISNULL(DF_RDr.Phone2, '') AS RPPhone2,
                          ISNULL(ml_RDrSpc.Description, '') AS RPSpec,
                          DF_RDr.Type AS RPType,
                          DF_SDr.ListName AS PCPName,
                          ISNULL(DF_SDr.NPI, '') AS PCP_NPI,
                          ISNULL(DF_SDr.First, '') AS PCPFName,
                          ISNULL(DF_SDr.Middle, '') AS PCPMI,
                          ISNULL(DF_SDr.Last, '') AS PCPLName,
                          ISNULL(DF_SDr.Address1, '') AS PCPAddress,
                          ISNULL(DF_SDr.City, '') AS PCPCity,
                          ISNULL(DF_SDr.State, '') AS RPState,
                          ISNULL(DF_SDr.Zip, '') AS PCPZip,
                          ISNULL(DF_SDr.Phone1, '') AS PCPPhone,
                          ISNULL(DF_SDr.Phone2Type, 'None') AS PCPPhone2Type,
                          ISNULL(DF_SDr.Phone2, '') AS PCPPhone2,
                          ISNULL(ml_SDrSpc.Description, '') AS PCPSpec,
                          DF_SDr.Type AS PCPType,
                          ML_Pos.Code AS POSCode,
                          ML_Pos.Description AS POSDescr,
                          am.Pos_ELOS,
                          am.Pos_Admit,
                          am.Pos_Dischg,
                          am.PosMID,
                          ISNULL(DF_PosFac.ListName, '') AS FacName,
                          ISNULL(DF_PosFac.Address1, '') AS FacAddress,
                          ISNULL(DF_PosFac.City, '') AS FacCity,
                          ISNULL(DF_PosFac.State, '') AS FacState,
                          ISNULL(DF_PosFac.Zip, '') AS FacZip,
                          ISNULL(DF_PosFac.Phone1, '') AS FacPhone,
                          ISNULL(DF_PosFac.Phone2Type, 'None') AS FacPhone2Type,
                          ISNULL(DF_PosFac.Phone2, '') AS FacPhone2,
                          ISNULL(ML_Out.Code, '') AS OutCode,
                          ISNULL(ML_Out.Description, '') AS OutDescr,
                          ISNULL(DF_OutFac.ListName, '') AS OutFac,
                          --CAST(am.ANotes AS VARCHAR(1000)) AS ANotes
						  Nte.ANote as ANotes,
						  Nte.LastModified as NoteLastModified --- to pull top last note 

                  FROM    cusMMAuthMst am
                          LEFT OUTER JOIN cusCRIMedLists ML_AStatus ON am.StatusMID = ML_AStatus.MedListsId
                          LEFT OUTER JOIN DoctorFacility DF_OutFac ON am.OutFacID = DF_OutFac.DoctorFacilityId
                          LEFT OUTER JOIN MedLists ML_Out ON am.OutMID = ML_Out.MedListsId
                          LEFT OUTER JOIN DoctorFacility DF_PosFac ON am.PosFacID = DF_PosFac.DoctorFacilityId
                          LEFT OUTER JOIN DoctorFacility DF_SDr ON am.SvcDoctorID = DF_SDr.DoctorFacilityId
                          LEFT OUTER JOIN DoctorFacility DF_RDr ON am.RefDoctorId = DF_RDr.DoctorFacilityId
                          LEFT OUTER JOIN PatientProfile PP ON am.PatientProfileID = PP.PatientProfileId
                          LEFT OUTER JOIN MedLists ML_Pos ON am.PosMID = ML_Pos.MedListsId
                          LEFT OUTER JOIN MedLists ML_Deny ON am.DenyMID = ML_Deny.MedListsId
                          LEFT OUTER JOIN PatientInsurance PatIns ON PatIns.PatientInsuranceId = am.PatientInsuranceID
                          LEFT OUTER JOIN MedLists ml_SDrSpc ON DF_SDr.SpecialtyMID = ml_SDrSpc.MedListsId
                          LEFT OUTER JOIN MedLists ml_RDrSpc ON DF_RDr.SpecialtyMID = ml_RDrSpc.MedListsId
                          LEFT OUTER JOIN cusMMAuthNote Nte ON am.cusMMAuthMstID = Nte.cusMMAuthMstID
                          
                  WHERE   ( @lcACodeFr IS NOT NULL
                            AND am.ACode >= @lcACodeFr
                            OR @lcACodeFr IS NULL )
                          AND ( @lcACodeTo IS NOT NULL
                                AND am.ACode <= @lcACodeTo
                                OR @lcACodeTo IS NULL )

      DECLARE @cusMMAuthMstID INT,
              @PatientProfileId INT,
              @ACode VARCHAR(30),
              @PatientInsuranceID INT,
              @AuthDte DATETIME,
              @ExpDte DATETIME,
              @RcvdDte DATETIME,
              @ActionDte DATETIME,
              @ApptDte DATETIME,
              @ExtndDte DATETIME,
              @HPTrack VARCHAR(30),
              @TicketNumber VARCHAR(20),
              @FaxPCPDte DATETIME,
              @FaxPrvDte DATETIME,
              @FaxFacDte DATETIME,
              @CnsltRptReviewDte DATETIME,
              @CnsltRptRcvDte DATETIME,
              @CnsltRptReviewBy VARCHAR(60),
              @AppointmentsID INT,
              @PatientVisitID INT,
              @Created DATETIME,
              @CreatedBy VARCHAR(60),
              @LastModified DATETIME,
              @LastModifiedBy VARCHAR(30),
              @AuthStatus VARCHAR(20),
              @AStatDescr VARCHAR(200),
              @bApproved BIT,
              @PatientID VARCHAR(15),
              @MName VARCHAR(50),
              @MAddress VARCHAR(50),
              @MCity VARCHAR(30),
              @MState VARCHAR(3),
              @MZip VARCHAR(10),
              @MPhone1 VARCHAR(15),
              @MDOB DATETIME,
              @MSex VARCHAR(1),
              @MMedicalRecordNumber VARCHAR(15),
              @MSSN VARCHAR(9),
              @HPName VARCHAR(30),
              @HPType CHAR(1),
              @DenyCode VARCHAR(20),
              @CompanyName VARCHAR(75),
              @CompanyAddress VARCHAR(75),
              @CompanyCSZ VARCHAR(75)
	  DECLARE @EligibilityStart DATETIME, 
			  @EligibilityEnd DATETIME			  
      DECLARE @RPName VARCHAR(60),
			  @RP_NPI VARCHAR(80),
              @RPFName VARCHAR(15),
              @RPMI CHAR(1),
              @RPLName VARCHAR(40),
              @RPAddress VARCHAR(30),
              @RPCity VARCHAR(30),
              @RPState VARCHAR(2),
              @RPZip VARCHAR(10),
              @RPPhone VARCHAR(14),
              @RPFax VARCHAR(14),
              @bRPNoFax BIT,
              @RPSpec VARCHAR(30),
              @RPPhone2Type VARCHAR(25),
              @RPPhone2 VARCHAR(14),
              @RPType SMALLINT        
      DECLARE @PCPName VARCHAR(60),
			  @PCP_NPI VARCHAR(80), 
              @PCPFName VARCHAR(15),
              @PCPMI CHAR(1),
              @PCPLName VARCHAR(40),
              @PCPAddress VARCHAR(30),
              @PCPCity VARCHAR(30),
              @PCPState VARCHAR(2),
              @PCPZip VARCHAR(10),
              @PCPPhone VARCHAR(14),
              @PCPFax VARCHAR(14),
              @bPCPNoFax BIT,
              @PCPSpec VARCHAR(30),
              @PCPPhone2Type VARCHAR(25),
              @PCPPhone2 VARCHAR(14),
              @PCPType SMALLINT
      DECLARE @POSCode VARCHAR(20),
              @POSDescr VARCHAR(100),
              @Pos_ELOS TINYINT,
              @Pos_Admit DATETIME,
              @Pos_Dischg DATETIME,
              @PosMID INT

      DECLARE @FacName VARCHAR(60),
              @FacAddress VARCHAR(50),
              @FacCity VARCHAR(30),
              @FacState VARCHAR(2),
              @FacZip VARCHAR(10),
              @FacPhone VARCHAR(14),
              @FacFax VARCHAR(14),
              @FacPhone2Type VARCHAR(25),
              @FacPhone2 VARCHAR(14)    
      DECLARE @OutCode VARCHAR(20),
              @OutDescr VARCHAR(100),
              @OutFac VARCHAR(60),
              @Anotes VARCHAR(MAX),
			  @NoteLastModified DATETIME

      DECLARE @SNotes VARCHAR(100)
      DECLARE @HPPatientInsuranceId INT,
              @HPOrderForClaims CHAR(1),
              @HPCarrierName VARCHAR(50)

      DECLARE @DiagCode VARCHAR(10),
              @DiagDescr VARCHAR(50)
      DECLARE @ICD9_1 VARCHAR(10),
              @ICD9_1Txt VARCHAR(50),
              @ICD9_2 VARCHAR(10),
              @ICD9_2Txt VARCHAR(50),
              @ICD9_3 VARCHAR(10),
              @ICD9_3Txt VARCHAR(50),
              @ICD9_4 VARCHAR(10),
              @ICD9_4Txt VARCHAR(50),
              @liCount INT
      DECLARE @Qty INT,
              @CoPay MONEY,
              @ProcCode VARCHAR(10),
              @ProcDescr VARCHAR(255)
    
    
      OPEN p_AuthMst

      FETCH NEXT FROM p_AuthMst INTO @cusMMAuthMstID, @PatientProfileId, @ACode, @PatientInsuranceID, @AuthDte, @ExpDte, @RcvdDte, @ActionDte, @ApptDte,
            @ExtndDte, @HPTrack, @FaxPCPDte, @FaxPrvDte, @FaxFacDte, @CnsltRptReviewDte, @CnsltRptRcvDte, @CnsltRptReviewBy, @AppointmentsID, @PatientVisitID,
            @Created, @CreatedBy, @LastModified, @LastModifiedBy, @AuthStatus, @AStatDescr, @PatientID, @MName, @MAddress, @MCity, @MState, @MZip, @MPhone1,
            @MDOB, @MSex, @MMedicalRecordNumber, @MSSN, @DenyCode, @RPName, @RP_NPI, @RPFName, @RPMI, @RPLName, @RPAddress, @RPCity, @RPState, @RPZip, @RPPhone,
            @RPPhone2Type, @RPPhone2, @RPSpec, @RPType, @PCPName, @PCP_NPI, @PCPFName, @PCPMI, @PCPLName, @PCPAddress, @PCPCity, @PCPState, @PCPZip, @PCPPhone,
            @PCPPhone2Type, @PCPPhone2, @PCPSpec, @PCPType, @POSCode, @POSDescr, @Pos_ELOS, @Pos_Admit, @Pos_Dischg, @PosMID, @FacName, @FacAddress, @FacCity,
            @FacState, @FacZip, @FacPhone, @FacPhone2Type, @FacPhone2, @OutCode, @OutDescr, @OutFac, @Anotes, @NoteLastModified --, @EligibilityStart, @EligibilityEnd
            
      WHILE ( @@fetch_status <> -1 )
            BEGIN

                  SET @TicketNumber = ''
                  IF @PatientVisitID <> 0 
                     SELECT @ApptDte = Visit,
                            @TicketNumber = TicketNumber
                     FROM   PatientVisit
                     WHERE  PatientVisitID = @PatientVisitID
                  ELSE 
                     IF @AppointmentsID <> 0 
                        SELECT  @ApptDte = ApptStart
                        FROM    Appointments
                        WHERE   AppointmentsID = @AppointmentsID

                  IF @AuthStatus = 'APR'
                     OR @AuthStatus = 'MOD'
                     OR @AuthStatus = 'TRT' 
                     SET @bApproved = 1
                  ELSE 
                     SET @bApproved = 0
    
        -- * Health Plan 
                  SET @HPName = ''
                  SET @HPType = ''
                  DECLARE p_PatIns CURSOR
                          FOR SELECT  pi.PatientInsuranceId,
                                      CAST(pi.OrderForClaims AS CHAR(1)) AS HPOrderForClaims,
                                      ic.ListName AS HPCarrierName
                              FROM    PatientInsurance pi
                                      INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersID = ic.InsuranceCarriersId
                              WHERE   PatientProfileId = @PatientProfileId
                                      AND ( ( pi.Inactive IS NULL )
                                            OR ( pi.Inactive = 0 ) )
                              ORDER BY OrderForClaims
                  OPEN p_PatIns
                  FETCH NEXT FROM p_PatIns INTO @HPPatientInsuranceId, @HPOrderForClaims, @HPCarrierName
                  WHILE ( @@fetch_status <> -1 )
                        BEGIN
                              IF @PatientInsuranceID = @HPPatientInsuranceId
                                 AND @HPName = '' 
                                 BEGIN
                                       SET @HPName = @HPCarrierName
                                       SET @HPType = @HPOrderForClaims
                    --Set @@fetch_status<>-1
                                 END    
                              FETCH NEXT FROM p_PatIns INTO @HPPatientInsuranceId, @HPOrderForClaims, @HPCarrierName
                        END
                  DEALLOCATE p_PatIns
		
		-- * Eligibility Dates
			SET @EligibilityStart = ''
			SET @EligibilityEnd = ''
			DECLARE p_EligDte CURSOR 
						FOR SELECT  
								d.EligibilityPeriodStartDate AS EligibilityStart,
								d.EligibilityPeriodEndDate AS EligibilityEnd
						FROM    cusMMEligibilityProfile p
								JOIN cusMMEligibilityApplicationInstance ai ON p.pkid = ai.fkMMEligibilityProfile
								JOIN cusMMEligibilityApplication app ON app.fkMMEligibilityApplicationInstance = ai.pkid
								JOIN cusMMEligibilityDecision d ON app.fkMMEligibilityDecision = d.pkid
								JOIN PatientProfile pp on p.fkPatientProfileID = pp.PatientProfileId
						WHERE   p.fkPatientProfileID = @PatientProfileId
								AND p.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
								AND ai.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
								AND app.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
								AND d.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
								AND d.fkEligibilityDecisionID = ( SELECT TOP 1
																		  MedListsid
																  FROM    cusCRIMedLists
																  WHERE   TableName = 'EligDecision'
																		  AND Description = 'Granted' )
						OPEN p_EligDte 
						FETCH NEXT FROM p_EligDte INTO @EligibilityStart, @EligibilityEnd
						WHILE ( @@fetch_status <> -1 )
							BEGIN
								SET @EligibilityStart = @EligibilityStart
								SET @EligibilityEnd =	@EligibilityEnd
						FETCH NEXT FROM p_EligDte INTO @EligibilityStart, @EligibilityEnd
						END
				DEALLOCATE p_EligDte

        -- * Diagnosis Codes
                  SET @ICD9_1 = ''
                  SET @ICD9_1Txt = ''
                  SET @ICD9_2 = ''
                  SET @ICD9_2Txt = ''
                  SET @ICD9_3 = ''
                  SET @ICD9_3Txt = ''
                  SET @ICD9_4 = ''
                  SET @ICD9_4Txt = ''
                  SET @liCount = 0
                  DECLARE p_Diags CURSOR
                          FOR SELECT  d.Code AS DiagCode,
                                      d.Description AS DiagDescr
                              FROM    cusMMAuthDiag ad
                                      INNER JOIN Diagnosis d ON ad.DiagnosisID = d.DiagnosisId
                              WHERE   ad.cusMMAuthMstID = @cusMMAuthMstID
                              ORDER BY nDiagNo
                  OPEN p_Diags         
                  FETCH NEXT FROM p_Diags INTO @DiagCode, @DiagDescr
                  WHILE ( @@fetch_status <> -1 )
                        BEGIN
                              SET @liCount = @liCount + 1
                              IF @liCount = 1 
                                 BEGIN
                                       SET @ICD9_1 = @DiagCode
                                       SET @ICD9_1Txt = @DiagDescr
                                 END
            --End if
                              IF @liCount = 2 
                                 BEGIN
                                       SET @ICD9_2 = @DiagCode
                                       SET @ICD9_2Txt = @DiagDescr
                                 END
            --End if    
                              IF @liCount = 3 
                                 BEGIN
                                       SET @ICD9_3 = @DiagCode
                                       SET @ICD9_3Txt = @DiagDescr
                                 END
            --End if    
                              IF @liCount = 4 
                                 BEGIN
                                       SET @ICD9_4 = @DiagCode
                                       SET @ICD9_4Txt = @DiagDescr
                                 END
            --End if    
                              FETCH NEXT FROM p_Diags INTO @DiagCode, @DiagDescr
                        END
                  DEALLOCATE p_Diags

        -- * Referring Dr
                  IF @RPType = 6          -- Resource
                     SET @RPLName = @RPName
                  IF @RPPhone2Type = 'Fax' 
                     BEGIN
                           SET @RPFax = @RPPhone2
                           SET @bRPNoFax = 0
                     END
                  ELSE 
                     BEGIN
                           SET @RPFax = ''
                           SET @bRPNoFax = 1
                     END
        
        -- * Servicing Dr
                  IF @PCPType = 6 
                     SET @PCPLName = @PCPName
                  IF @PCPPhone2Type = 'Fax' 
                     BEGIN
                           SET @PCPFax = @PCPPhone2
                           SET @bPCPNoFax = 0
                     END
                  ELSE 
                     BEGIN
                           SET @PCPFax = ''
                           SET @bPCPNoFax = 1
                     END

        -- * POS Facility
                  IF @FacPhone2Type = 'Fax' 
                     BEGIN
                           SET @FacFax = @FacPhone2
                     END
                  ELSE 
                     BEGIN
                           SET @FacFax = ''
                     END
        
        -- * POS / SNotes
                  SET @SNotes = ''
                  IF NOT ( @PosMID IS NULL
                           OR @PosMID = 0 ) 
                     BEGIN
                           DECLARE @Code AS VARCHAR(20)
                           DECLARE p_POS CURSOR
                                   FOR SELECT Code
                                       FROM   MedLists ml
                                       WHERE  ml.MedListsId = @PosMID
                           OPEN p_POS
                           FETCH NEXT FROM p_POS INTO @Code
                           IF @Code = '32' 
                              SET @SNotes = 'CALIFORNIA CHILDREN''S SERVICES HAS FINANCIAL ' + 'RESPONSIBILITY FOR THIS TREATMENT'
                           DEALLOCATE p_POS
                     END

        -- * Process Procedure Codes
                  SET @liCount = 0
                  DECLARE p_Procs CURSOR
                          FOR SELECT  ad.LastModifiedBy,
                                      ISNULL(ad.Qty, 0) AS Qty,
                                      ISNULL(ad.CoPay, 0) AS CoPay,
                                      p.Code AS Code,
                                      p.Description AS Description
                              FROM    cusMMAuthDtl ad
                                      INNER JOIN Procedures p ON ad.ProceduresId = p.ProceduresId
                              WHERE   cusMMAuthMstID = @cusMMAuthMstID
                  OPEN p_Procs
            
                  FETCH NEXT FROM p_Procs INTO @LastModifiedBy, @Qty, @CoPay, @ProcCode, @ProcDescr

                  WHILE ( @@fetch_status <> -1 )
                        BEGIN

                              SET @liCount = @liCount + 1

                              INSERT  INTO #AuthPrnt ( cusMMAuthMstID,
                                                       Code,
                                                       AuthDte,
                                                       ExpDte,
                                                       RcvdDte,
                                                       ActionDte,
                                                       ApptDte,
                                                       ExtndDte,
                                                       HPTrack,
                                                       TicketNumber,
                                                       FaxPCPDte,
                                                       FaxPrvDte,
                                                       FaxFacDte,
                                                       CnsltRptReviewDte,
                                                       CnsltRptRcvDte,
                                                       CnsltRptReviewBy,
                                                       Created,
                                                       CreatedBy,
                                                       LastModified,
                                                       LastModifiedBy,
                                                       AuthStatus,
                                                       AStatDescr,
                                                       bApproved,
                                                       PatientID,
                                                       MName,
                                                       MAddress,
                                                       MCity,
                                                       MState,
                                                       MZip,
                                                       MPhone1,
                                                       MDOB,
                                                       MSex,
                                                       MMedicalRecordNumber,
                                                       MSSN,
                                                       HPName,
                                                       HPType,
                                                       DenyCode,
                                                       RPName,
                                                       RP_NPI, 
                                                       RPFName,
                                                       RPMI,
                                                       RPLName,
                                                       RPAddress,
                                                       RPCity,
                                                       RPState,
                                                       RPZip,
                                                       RPPhone,
                                                       RPFax,
                                                       bRPNoFax,
                                                       RPSpec,
                                                       PCPName,
                                                       PCP_NPI, 
                                                       PCPFName,
                                                       PCPMI,
                                                       PCPLName,
                                                       PCPAddress,
                                                       PCPCity,
                                                       PCPState,
                                                       PCPZip,
                                                       PCPPhone,
                                                       PCPFax,
                                                       bPCPNoFax,
                                                       PCPSpec,
                                                       POSCode,
                                                       POSDescr,
                                                       Pos_ELOS,
                                                       Pos_Admit,
                                                       Pos_Dischg,
                                                       FacName,
                                                       FacAddress,
                                                       FacCity,
                                                       FacState,
                                                       FacZip,
                                                       FacPhone,
                                                       FacFax,
                                                       OutCode,
                                                       OutDescr,
                                                       OutFac,
                                                       ICD9_1,
                                                       ICD9_1Txt,
                                                       ICD9_2,
                                                       ICD9_2Txt,
                                                       ICD9_3,
                                                       ICD9_3Txt,
                                                       ICD9_4,
                                                       ICD9_4Txt,
                                                       Qty,
                                                       CoPay,
                                                       ProcCode,
                                                       ProcDescr,
                                                       Anotes,
													   NoteLastModified,
                                                       SNotes,
													   EligibilityStart,
													   EligibilityEnd
													 )
                              VALUES  (
                                        @cusMMAuthMstID,
                                        @ACode,
                                        @AuthDte,
                                        @ExpDte,
                                        @RcvdDte,
                                        @ActionDte,
                                        @ApptDte,
                                        @ExtndDte,
                                        @HPTrack,
                                        @TicketNumber,
                                        @FaxPCPDte,
                                        @FaxPrvDte,
                                        @FaxFacDte,
                                        @CnsltRptReviewDte,
                                        @CnsltRptRcvDte,
                                        @CnsltRptReviewBy,
                                        @Created,
                                        @CreatedBy,
                                        @LastModified,
                                        @LastModifiedBy,
                                        @AuthStatus,
                                        @AStatDescr,
                                        @bApproved,
                                        @PatientID,
                                        @MName,
                                        @MAddress,
                                        @MCity,
                                        @MState,
                                        @MZip,
                                        @MPhone1,
                                        @MDOB,
                                        @MSex,
                                        @MMedicalRecordNumber,
                                        @MSSN,
                                        @HPName,
                                        @HPType,
                                        @DenyCode,
                                        @RPName,
                                        @RP_NPI, 
                                        @RPFName,
                                        @RPMI,
                                        @RPLName,
                                        @RPAddress,
                                        @RPCity,
                                        @RPState,
                                        @RPZip,
                                        @RPPhone,
                                        @RPFax,
                                        @bRPNoFax,
                                        @RPSpec,
                                        @PCPName,
                                        @PCP_NPI, 
                                        @PCPFName,
                                        @PCPMI,
                                        @PCPLName,
                                        @PCPAddress,
                                        @PCPCity,
                                        @PCPState,
                                        @PCPZip,
                                        @PCPPhone,
                                        @PCPFax,
                                        @bPCPNoFax,
                                        @PCPSpec,
                                        @POSCode,
                                        @POSDescr,
                                        @Pos_ELOS,
                                        @Pos_Admit,
                                        @Pos_Dischg,
                                        @FacName,
                                        @FacAddress,
                                        @FacCity,
                                        @FacState,
                                        @FacZip,
                                        @FacPhone,
                                        @FacFax,
                                        @OutCode,
                                        @OutDescr,
                                        @OutFac,
                                        @ICD9_1,
                                        @ICD9_1Txt,
                                        @ICD9_2,
                                        @ICD9_2Txt,
                                        @ICD9_3,
                                        @ICD9_3Txt,
                                        @ICD9_4,
                                        @ICD9_4Txt,
                                        @Qty,
                                        @CoPay,
                                        @ProcCode,
                                        @ProcDescr,
                                        @Anotes,
										@NoteLastModified,
                                        @SNotes, 
										@EligibilityStart,
										@EligibilityEnd
										)

                              FETCH NEXT FROM p_Procs INTO @LastModifiedBy, @Qty, @CoPay, @ProcCode, @ProcDescr

                        END

                  DEALLOCATE p_Procs
        
                  IF @liCount = 0    -- need 1 rec for Prnt rec
                     BEGIN
                           SET @Qty = 0
                           SET @CoPay = 0
                           SET @ProcCode = ''
                           SET @ProcDescr = ''
                           INSERT INTO #AuthPrnt ( cusMMAuthMstID,
                                                   Code,
                                                   AuthDte,
                                                   ExpDte,
                                                   RcvdDte,
                                                   ActionDte,
                                                   ApptDte,
                                                   ExtndDte,
                                                   HPTrack,
                                                   TicketNumber,
                                                   FaxPCPDte,
                                                   FaxPrvDte,
                                                   FaxFacDte,
                                                   CnsltRptReviewDte,
                                                   CnsltRptRcvDte,
                                                   CnsltRptReviewBy,
                                                   Created,
                                                   CreatedBy,
                                                   LastModified,
                                                   LastModifiedBy,
                                                   AuthStatus,
                                                   AStatDescr,
                                                   bApproved,
                                                   PatientID,
                                                   MName,
                                                   MAddress,
                                                   MCity,
                                                   MState,
                                                   MZip,
                                                   MPhone1,
                                                   MDOB,
                                                   MSex,
                                                   MMedicalRecordNumber,
                                                   MSSN,
                                                   HPName,
                                                   HPType,
                                                   DenyCode,
                                                   RPName,
                                                   RP_NPI,
                                                   RPFName,
                                                   RPMI,
                                                   RPLName,
                                                   RPAddress,
                                                   RPCity,
                                                   RPState,
                                                   RPZip,
                                                   RPPhone,
                                                   RPFax,
                                                   bRPNoFax,
                                                   RPSpec,
                                                   PCPName,
                                                   PCP_NPI,
                                                   PCPFName,
                                                   PCPMI,
                                                   PCPLName,
                                                   PCPAddress,
                                                   PCPCity,
                                                   PCPState,
                                                   PCPZip,
                                                   PCPPhone,
                                                   PCPFax,
                                                   bPCPNoFax,
                                                   PCPSpec,
                                                   POSCode,
                                                   POSDescr,
                                                   Pos_ELOS,
                                                   Pos_Admit,
                                                   Pos_Dischg,
                                                   FacName,
                                                   FacAddress,
                                                   FacCity,
                                                   FacState,
                                                   FacZip,
                                                   FacPhone,
                                                   FacFax,
                                                   OutCode,
                                                   OutDescr,
                                                   OutFac,
                                                   ICD9_1,
                                                   ICD9_1Txt,
                                                   ICD9_2,
                                                   ICD9_2Txt,
                                                   ICD9_3,
                                                   ICD9_3Txt,
                                                   ICD9_4,
                                                   ICD9_4Txt,
                                                   Qty,
                                                   CoPay,
                                                   ProcCode,
                                                   ProcDescr,
                                                   Anotes,
												   NoteLastModified,
                                                   SNotes,
												   EligibilityStart, 
												   EligibilityEnd
												 )
                           VALUES (
                                    @cusMMAuthMstID,
                                    @ACode,
                                    @AuthDte,
                                    @ExpDte,
                                    @RcvdDte,
                                    @ActionDte,
                                    @ApptDte,
                                    @ExtndDte,
                                    @HPTrack,
                                    @TicketNumber,
                                    @FaxPCPDte,
                                    @FaxPrvDte,
                                    @FaxFacDte,
                                    @CnsltRptReviewDte,
                                    @CnsltRptRcvDte,
                                    @CnsltRptReviewBy,
                                    @Created,
                                    @CreatedBy,
                                    @LastModified,
                                    @LastModifiedBy,
                                    @AuthStatus,
                                    @AStatDescr,
                                    @bApproved,
                                    @PatientID,
                                    @MName,
                                    @MAddress,
                                    @MCity,
                                    @MState,
                                    @MZip,
                                    @MPhone1,
                                    @MDOB,
                                    @MSex,
                                    @MMedicalRecordNumber,
                                    @MSSN,
                                    @HPName,
                                    @HPType,
                                    @DenyCode,
                                    @RPName,
                                    @RP_NPI, 
                                    @RPFName,
                                    @RPMI,
                                    @RPLName,
                                    @RPAddress,
                                    @RPCity,
                                    @RPState,
                                    @RPZip,
                                    @RPPhone,
                                    @RPFax,
                                    @bRPNoFax,
                                    @RPSpec,
                                    @PCPName,
                                    @PCP_NPI, 
                                    @PCPFName,
                                    @PCPMI,
                                    @PCPLName,
                                    @PCPAddress,
                                    @PCPCity,
                                    @PCPState,
                                    @PCPZip,
                                    @PCPPhone,
                                    @PCPFax,
                                    @bPCPNoFax,
                                    @PCPSpec,
                                    @POSCode,
                                    @POSDescr,
                                    @Pos_ELOS,
                                    @Pos_Admit,
                                    @Pos_Dischg,
                                    @FacName,
                                    @FacAddress,
                                    @FacCity,
                                    @FacState,
                                    @FacZip,
                                    @FacPhone,
                                    @FacFax,
                                    @OutCode,
                                    @OutDescr,
                                    @OutFac,
                                    @ICD9_1,
                                    @ICD9_1Txt,
                                    @ICD9_2,
                                    @ICD9_2Txt,
                                    @ICD9_3,
                                    @ICD9_3Txt,
                                    @ICD9_4,
                                    @ICD9_4Txt,
                                    @Qty,
                                    @CoPay,
                                    @ProcCode,
                                    @ProcDescr,
                                    @Anotes,
									@NoteLastModified,
                                    @SNotes, 
									@EligibilityStart,
									@EligibilityEnd	
								)
                     END
            
                  FETCH NEXT FROM p_AuthMst INTO @cusMMAuthMstID, @PatientProfileId, @ACode, @PatientInsuranceID, @AuthDte, @ExpDte, @RcvdDte, @ActionDte,
                        @ApptDte, @ExtndDte, @HPTrack, @FaxPCPDte, @FaxPrvDte, @FaxFacDte, @CnsltRptReviewDte, @CnsltRptRcvDte, @CnsltRptReviewBy,
                        @AppointmentsID, @PatientVisitID, @Created, @CreatedBy, @LastModified, @LastModifiedBy, @AuthStatus, @AStatDescr, @PatientID, @MName,
                        @MAddress, @MCity, @MState, @MZip, @MPhone1, @MDOB, @MSex, @MMedicalRecordNumber, @MSSN, @DenyCode, @RPName, @RP_NPI, @RPFName, @RPMI, @RPLName,
                        @RPAddress, @RPCity, @RPState, @RPZip, @RPPhone, @RPPhone2Type, @RPPhone2, @RPSpec, @RPType, @PCPName, @PCP_NPI, @PCPFName, @PCPMI, @PCPLName,
                        @PCPAddress, @PCPCity, @PCPState, @PCPZip, @PCPPhone, @PCPPhone2Type, @PCPPhone2, @PCPSpec, @PCPType, @POSCode, @POSDescr, @Pos_ELOS,
                        @Pos_Admit, @Pos_Dischg, @PosMID, @FacName, @FacAddress, @FacCity, @FacState, @FacZip, @FacPhone, @FacPhone2Type, @FacPhone2, @OutCode,
                        @OutDescr, @OutFac, @Anotes, @NoteLastModified  --, @EligibilityStart, @EligibilityEnd  

            END

      DEALLOCATE p_AuthMst

-- 07/07/05 WJY Add Company Name and Address
-- 04/18/2011 Jeff --  Change from ListName to OrgName (Orange County Request)

      SELECT TOP 1
              --@CompanyName = ListName,
              @CompanyName = OrgName,
              @CompanyAddress = Address1,
              @CompanyCSZ = RTRIM(City) + ', ' + State + ' ' + Zip
      FROM    DoctorFacility
      WHERE   Type = 5
              AND ISNULL(Inactive, 0) = 0
      ORDER BY DoctorFacilityID 

      UPDATE  #AuthPrnt
      SET     CompanyName = @CompanyName,
              CompanyAddress = @CompanyAddress,
              CompanyCSZ = @CompanyCSZ 
--		
--	SELECT * 
--	FROM #AuthPrnt
--	ORDER BY Code

-- 04/18/2011 Jeff - Added to return most recent note entry

      SELECT  *
	  FROM (
		SELECT	*
				,ROW_NUMBER() over (partition by cusMMAuthMstID, proccode order by NoteLastModified desc) as rn
		FROM    #AuthPrnt
			) as x
	  WHERE rn=1
      ORDER BY Code
    
      DROP TABLE #AuthPrnt
    
END

Open in new window

0
Comment
Question by:Jeff S
4 Comments
 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 664 total points
ID: 35420800
Are you passing NULL or are you passing an empty string? If you pass empty string, it gets converted into the minimum value for a date/time field.
0
 
LVL 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 668 total points
ID: 35420877
According to this page: http://msdn.microsoft.com/en-us/library/bb630352.aspx  that is the default value for a 'Date' field.  That is what it will return if there is nothing in the database, it will not return a NULL.  Most databases do something similar.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 668 total points
ID: 35421134
You need to replace this:
            SET @EligibilityStart = ''
            SET @EligibilityEnd = ''

With this:
            SET @EligibilityStart = NULL
            SET @EligibilityEnd = NULL
0
 
LVL 7

Author Comment

by:Jeff S
ID: 35421141
All got this one right. I needed to change the empty strings to NULL. Will split the points to be fair.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question