Solved

SQL question - Pad fields

Posted on 2009-06-29
6
209 Views
Last Modified: 2012-05-07
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
Comment
Question by:Jeff S
  • 3
  • 2
6 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24741136
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24741145
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
 
LVL 7

Author Comment

by:Jeff S
ID: 24741160
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Author Comment

by:Jeff S
ID: 24741237
Change of plans ... it actually needs to be RIGHT justified not LEFT. So how would I account for that now?
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24741251
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24741552
Or simply:
CAST(pp.PatientId as char(17)) AS PatientControlNumber ,
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Passing Parameter to Stored Procedure 4 24
performance query 4 24
SQL - Use results of SELECT DISTINCT in a JOIN 4 23
Proper Case SQL Command 2 12
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

803 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