Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

SQL question - Pad fields

Please note, the SQL is handled dynamically by the SQL server, therefore the coding in my WHERE clauses will look odd to you - please disregard this.

I have a question pertaining to this field:

LEFT(pp.PatientId, 17) AS PatientControlNumber ,

In my database, the patient ID typically is around 4 characters (depending on size of client this can grow in size). If this ID is 4 characters in length, I need to report back 13 blank spaces first then the 4 ID numbers that make up the Id #.

Would I use SPACE(13)? How do I code it to auto ajust to the size of the field? I imagine I'll have this same issue with the PatientMRN field as well.  
SET NOCOUNT ON
 
SELECT  pv.TicketNumber ,
        dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS PatientName ,
        FLOOR(DATEDIFF(DAY, pp.birthdate, GETDATE()) / 365.25) AS [Patient Age] ,
        LEFT(f.FederalTaxId, 10) AS FacilityId ,
        LEFT(pp.PatientId, 17) AS PatientControlNumber ,
        LEFT(pp.MedicalRecordNumber, 17) AS PatientMRN ,
        CASE WHEN pp.SSN IS NULL THEN '300'
             ELSE LEFT(pp.SSN, 9)
        END AS SSN ,
        LEFT(pp.Zip, 5) AS Zip ,
        REPLACE(CONVERT(VARCHAR(10), pp.birthdate, 101), '/', '') AS DOB ,
        pp.Sex ,
        REPLACE(CONVERT(VARCHAR(10), pvf.HospitalizationFrom, 1), '/', '') AS AdmissionDate ,
        ml.Code AS [Source of Admission] , --  Admission Source (Filing 3 Tab)
        mll.Code AS PatientDischargeStatus ,  -- Patient Status (Filing 3 Tab)
        REPLACE(CONVERT(VARCHAR(10), pvf.HospitalizationTo, 1), '/', '') AS DischargeDate ,
        LEFT(REPLACE(pvd1.ICD9Code, '.', ''), 5) AS PrimaryDiag ,
        LEFT(REPLACE(pvd2.ICD9Code, '.', ''), 5) AS SecondDiag ,
        LEFT(REPLACE(pvd3.ICD9Code, '.', ''), 5) AS ThirdDiag ,
        LEFT(REPLACE(pvd4.ICD9Code, '.', ''), 5) AS FourthDiag ,
        LEFT(REPLACE(pvd5.ICD9Code, '.', ''), 5) AS FifthDiag ,
        LEFT(REPLACE(pvd6.ICD9Code, '.', ''), 5) AS SixthDiag ,
        LEFT(REPLACE(pvd7.ICD9Code, '.', ''), 5) AS SeventhDiag ,
        LEFT(REPLACE(pvd8.ICD9Code, '.', ''), 5) AS EighthDiag ,
        LEFT(REPLACE(pvd9.ICD9Code, '.', ''), 5) AS NinthDiag ,
        LEFT(pvp.CPTCode, 5) + ISNULL(CONVERT(VARCHAR(4), ml4.Code), '  ')
        + ISNULL(CONVERT(VARCHAR(4), ml5.Code), ' ') AS PrincipleCode ,
        LEFT(pvp2.CPTCode, 5) + ISNULL(CONVERT(VARCHAR(4), ml6.Code), '  ')
        + ISNULL(CONVERT(VARCHAR(4), ml7.Code), ' ') AS SecondCode ,
        LEFT(pvp3.CPTCode, 5) + ISNULL(CONVERT(VARCHAR(4), ml8.Code), '  ')
        + ISNULL(CONVERT(VARCHAR(4), ml9.Code), ' ') AS ThirdCode ,
        LEFT(pvp4.CPTCode, 5) + ISNULL(CONVERT(VARCHAR(4), ml10.Code), '  ')
        + ISNULL(CONVERT(VARCHAR(4), ml11.Code), ' ') AS FourthCode ,
        LEFT(pvp5.CPTCode, 5) + ISNULL(CONVERT(VARCHAR(4), ml12.Code), '  ')
        + ISNULL(CONVERT(VARCHAR(4), ml13.Code), ' ') AS FifthCode ,
        LEFT(pvp6.CPTCode, 5) + ISNULL(CONVERT(VARCHAR(4), ml14.Code), '  ')
        + ISNULL(CONVERT(VARCHAR(4), ml15.Code), ' ') AS SixthCode ,
        REPLACE(CONVERT(VARCHAR(10), pvp.DateOfServiceFrom, 1), '/', '') AS [Primary CPT Date] ,
        '4' AS [Coding Method Used] ,
        REPLACE(CONVERT(VARCHAR(10), pvp.DateOfServiceFrom, 1), '/', '')
        + REPLACE(CONVERT(VARCHAR(10), pvp.DateOfServiceTo, 1), '/', '') AS [Statement Covers Period] ,
        pva.OrigInsAllocation ,
        pva.OrigPatAllocation ,
        CAST(( pva.OrigInsAllocation + pva.OrigPatAllocation ) * 100 AS INTEGER) AS [Total Charges] ,
        LEFT(ic.ListName, 25) AS [Primary Payer] ,
        LEFT(ic2.ListName, 25) AS [Secondary Payer] ,
        LEFT(ic3.ListName, 25) AS [Third Payer] ,
        LEFT(od.StateLicenseNo, 12) AS [Performing Physician ID] ,
        LEFT(oth.StateLicenseNo, 12) AS [Other Physician ID] ,
        '999' AS [Type of Bill]
INTO    #Tmp
FROM    PatientVisit pv
        INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
        LEFT OUTER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
        LEFT JOIN PatientVisitFiling pvf ON pv.PatientVisitId = pvf.PatientVisitId
        LEFT JOIN MedLists ml ON pvf.AdmissionSourceMId = ml.MedListsId
        LEFT JOIN Medlists mll ON pvf.PatientStatusMId = mll.MedListsId
        LEFT JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
        LEFT JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
        LEFT JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId
        LEFT JOIN DoctorFacility od ON pv.OperatingDoctorId = od.DoctorFacilityId
        LEFT JOIN DoctorFacility oth ON pv.OtherDoctorId = oth.DoctorFacilityId
        LEFT JOIN PatientVisitInsurance pvi ON pv.PatientVisitID = pvi.PatientVisitID
                                               AND pvi.OrderForClaims = 1
        LEFT JOIN PatientInsurance pi ON pvi.PatientInsuranceID = pi.PatientInsuranceID
        LEFT JOIN InsuranceCarriers ic ON pi.InsuranceCarriersID = ic.InsuranceCarriersID
        LEFT JOIN PatientVisitInsurance pvi2 ON pv.PatientVisitID = pvi2.PatientVisitID
                                                AND pvi2.OrderForClaims = 2
        LEFT JOIN PatientInsurance pi2 ON pvi2.PatientInsuranceID = pi2.PatientInsuranceID
        LEFT JOIN InsuranceCarriers ic2 ON pi2.InsuranceCarriersID = ic2.InsuranceCarriersID
        LEFT JOIN PatientVisitInsurance pvi3 ON pv.PatientVisitID = pvi3.PatientVisitID
                                                AND pvi3.OrderForClaims = 3
        LEFT JOIN PatientInsurance pi3 ON pvi3.PatientInsuranceID = pi3.PatientInsuranceID
        LEFT JOIN InsuranceCarriers ic3 ON pi3.InsuranceCarriersID = ic3.InsuranceCarriersID
        LEFT JOIN PatientVisitDiags pvd1 ON pv.PatientVisitId = pvd1.PatientVisitId
                                            AND pvd1.listorder = 1
        LEFT JOIN PatientVisitDiags pvd2 ON pv.PatientVisitId = pvd2.PatientVisitId
                                            AND pvd2.listorder = 2
        LEFT JOIN PatientVisitDiags pvd3 ON pv.PatientVisitId = pvd3.PatientVisitId
                                            AND pvd3.listorder = 3
        LEFT JOIN PatientVisitDiags pvd4 ON pv.PatientVisitId = pvd4.PatientVisitId
                                            AND pvd4.listorder = 4
        LEFT JOIN PatientVisitDiags pvd5 ON pv.PatientVisitId = pvd5.PatientVisitId
                                            AND pvd5.listorder = 5
        LEFT JOIN PatientVisitDiags pvd6 ON pv.PatientVisitId = pvd6.PatientVisitId
                                            AND pvd6.listorder = 6
        LEFT JOIN PatientVisitDiags pvd7 ON pv.PatientVisitId = pvd7.PatientVisitId
                                            AND pvd7.listorder = 7
        LEFT JOIN PatientVisitDiags pvd8 ON pv.PatientVisitId = pvd8.PatientVisitId
                                            AND pvd8.listorder = 8
        LEFT JOIN PatientVisitDiags pvd9 ON pv.PatientVisitId = pvd9.PatientVisitId
                                            AND pvd9.listorder = 9
        LEFT JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
                                           AND pvp.ListOrder = 1
        LEFT JOIN PatientVisitProcs pvp2 ON pv.PatientVisitId = pvp2.PatientVisitId
                                            AND pvp2.Listorder = 2
        LEFT JOIN PatientVisitProcs pvp3 ON pv.PatientVisitId = pvp3.PatientVisitId
                                            AND pvp3.Listorder = 3
        LEFT JOIN PatientVisitProcs pvp4 ON pv.PatientVisitId = pvp4.PatientVisitId
                                            AND pvp4.ListOrder = 4
        LEFT JOIN PatientVisitProcs pvp5 ON pv.PatientVisitId = pvp5.PatientVisitId
                                            AND pvp5.ListOrder = 5
        LEFT JOIN PatientVisitProcs pvp6 ON pv.PatientVisitId = pvp6.PatientVisitId
                                            AND pvp6.ListOrder = 6
        LEFT OUTER JOIN MedLists ml4 ON pvp.Modifier1MId = ml4.MedListsId
        LEFT OUTER JOIN MedLists ml5 ON pvp.Modifier2MId = ml5.MedListsId
        LEFT OUTER JOIN MedLists ml6 ON pvp2.Modifier1MId = ml6.MedListsId
        LEFT OUTER JOIN MedLists ml7 ON pvp2.Modifier2MId = ml7.MedListsId
        LEFT OUTER JOIN MedLists ml8 ON pvp3.Modifier1MId = ml8.MedListsId
        LEFT OUTER JOIN MedLists ml9 ON pvp3.Modifier2MId = ml9.MedListsId
        LEFT OUTER JOIN MedLists ml10 ON pvp4.Modifier1MId = ml10.MedListsId
        LEFT OUTER JOIN MedLists ml11 ON pvp4.Modifier2MId = ml11.MedListsId
        LEFT OUTER JOIN MedLists ml12 ON pvp5.Modifier1MId = ml12.MedListsId
        LEFT OUTER JOIN MedLists ml13 ON pvp5.Modifier2MId = ml13.MedListsId
        LEFT OUTER JOIN MedLists ml14 ON pvp6.Modifier1MId = ml14.MedListsId
        LEFT OUTER JOIN MedLists ml15 ON pvp6.Modifier2MId = ml15.MedListsId
WHERE   --Filter on Date
        ( ( 1 = 1
            AND ( pv.Entered >= ISNULL(NULL, '1/1/1900')
                  AND pv.Entered < DATEADD(d, 1, ISNULL(NULL, '1/1/3000'))
                )
          )
          OR ( 1 = 2
               AND ( pvp.DateOfServiceFrom >= ISNULL(NULL, '1/1/1900')
                     AND pvp.DateOfServiceFrom < DATEADD(d, 1,
                                                         ISNULL(NULL,
                                                              '1/1/3000'))
                   )
             )
        )
        AND --Filter on Doctor
        ( ( NULL IS NOT NULL
            AND pv.DoctorID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on Operating Doctor
        ( ( NULL IS NOT NULL
            AND pv.OperatingDoctorId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on Company
        ( ( NULL IS NOT NULL
            AND pv.CompanyID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on Facility
        ( ( NULL IS NOT NULL
            AND pv.FacilityID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on CPT Code
        ( ( NULL IS NOT NULL
            AND pvp.ProceduresId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on Diagnosis
        ( ( NULL IS NOT NULL
            AND pvd1.DiagnosisId IN ( NULL )
          )
          OR ( NULL IS NOT NULL
               AND pvd2.DiagnosisId IN ( NULL )
             )
          OR ( NULL IS NOT NULL
               AND pvd3.DiagnosisId IN ( NULL )
             )
          OR ( NULL IS NOT NULL
               AND pvd4.DiagnosisId IN ( NULL )
             )
          OR ( NULL IS NOT NULL
               AND pvd5.DiagnosisId IN ( NULL )
             )
          OR ( NULL IS NOT NULL
               AND pvd6.DiagnosisId IN ( NULL )
             )
          OR ( NULL IS NOT NULL
               AND pvd7.DiagnosisId IN ( NULL )
             )
          OR ( NULL IS NOT NULL
               AND pvd8.DiagnosisId IN ( NULL )
             )
          OR ( NULL IS NOT NULL
               AND pvd9.DiagnosisId IN ( NULL )
             )
          OR ( NULL IS NULL )
        )
        AND --Filter on Patient
        ( ( NULL IS NOT NULL
            AND pp.PatientProfileID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on Patient Sex
        ( ( NULL IS NOT NULL
            AND pp.sex IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on Insurance Carrier
        ( ( NULL IS NOT NULL
            AND pv.PrimaryInsuranceCarriersId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
 
SELECT  *
FROM    #Tmp
WHERE   --Filter on Age
        ( ([Patient Age] >= ( '-1' )
          AND [Patient Age] <= ( '125' ))
        )
 
DROP TABLE #Tmp

Open in new window

0
Jeff S
Asked:
Jeff S
  • 3
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Hope this helps:

LEFT(pp.PatientId + '                 ', 17) AS PatientControlNumber

Included 17 spaces now manually to your field.
So now the length is 4 + 17 and fetching only the first 17 would help you out.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
If you have 1 character 1 + 17 = 18 and first 17 can be taken out.
If you have 2 characters 2 + 17 = 19 and first 17 can be taken out.
If you have 3 characters 3 + 17 = 20 and first 17 can be taken out.
If you have 4 characters 4 + 17 = 21 and first 17 can be taken out.

P.S: If ID is an integer field, you have to use the below to make it work

LEFT(cast(pp.PatientId as varchar(4)) + '                 ', 17) AS PatientControlNumber

Hope this explains
0
 
Jeff SAuthor Commented:
rrjegan17 -
The total size of this field has to be 17 characters only. Will this auto-ajust it self and report back the 4 from my db plus the blank spaces? If my Id goes to 5 characters (due to database growth) will this adjust to print the 5 characters plus 12 blanks? Sorry for the questions, just trying to learn this one. I don't need 17 + 4 characters ... i need it to equal only 17.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Jeff SAuthor Commented:
Change of plans ... it actually needs to be RIGHT justified not LEFT. So how would I account for that now?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
This should help you out.

RIGHT('                 ' + cast(pp.PatientId as varchar(4)), 17) AS PatientControlNumber

>> I don't need 17 + 4 characters ... i need it to equal only 17.  

I just explained you how it works. If you run the query you will be able to see 17 characters only.
Hope this helps
0
 
Anthony PerkinsCommented:
Or simply:
CAST(pp.PatientId as char(17)) AS PatientControlNumber ,
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now