Solved

SQL Query Advice

Posted on 2009-07-06
15
255 Views
Last Modified: 2012-05-07
I need some SQL advice. I formatted a field as such:

ISNULL(LEFT(ic3.ListName,25),'                         ') AS [Third Payer] ,

The intent was if NULL then produce 25 blank space characters. I need to have it like this for a specific report. If any of my values is NULL then X number of characters. Am I correct in this?
SELECT 

		LEFT(f.FederalTaxId , 10) AS FacilityId ,

    		RIGHT('00000000000000000' + pp.PatientId, 17) AS PatientControlNumber ,

		RIGHT('00000000000000000' + COALESCE(pp.MedicalRecordNumber, '0'), 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 ,

    		CASE  	

			WHEN pvf.HospitalizationFrom IS NULL THEN REPLACE(CONVERT(VARCHAR(10) , pvp.DateOfServiceFrom , 1) , '/' , '') 

			ELSE REPLACE(CONVERT(VARCHAR(10) , pvf.HospitalizationFrom , 1) , '/' , '')

		END AS AdmissionDate ,

		ISNULL(ml.Code,'9') AS [Source of Admission] , 

		ISNULL(mll.Code,'09') AS PatientDischargeStatus ,

		CASE

			WHEN pvf.HospitalizationTo IS NULL THEN REPLACE(CONVERT(VARCHAR(10) , pvp.DateOfServiceTo , 1) , '/' , '')

			ELSE REPLACE(CONVERT(VARCHAR(10) , pvf.HospitalizationTo , 1) , '/' , '') 

		END AS DischargeDate ,

		ISNULL(LEFT(replace(pvd1.ICD9Code , '.' , '') , 5), '     ') AS PrimaryDiag ,

		ISNULL(LEFT(replace(pvd2.ICD9Code , '.' , '') , 5), '     ') AS SecondDiag ,

		ISNULL(LEFT(replace(pvd3.ICD9Code , '.' , '') , 5), '     ') AS ThirdDiag ,

		ISNULL(LEFT(replace(pvd4.ICD9Code , '.' , '') , 5), '     ') AS FourthDiag ,

		ISNULL(LEFT(replace(pvd5.ICD9Code , '.' , '') , 5), '     ') AS FifthDiag ,

		ISNULL(LEFT(replace(pvd6.ICD9Code , '.' , '') , 5), '     ')AS SixthDiag ,

		ISNULL(LEFT(replace(pvd7.ICD9Code , '.' , '') , 5), '     ') AS SeventhDiag ,

		ISNULL(LEFT(replace(pvd8.ICD9Code , '.' , '') , 5), '     ') AS EighthDiag ,

		ISNULL(LEFT(replace(pvd9.ICD9Code , '.' , '') , 5), '     ') AS NinthDiag ,

		ISNULL(LEFT(pvp.CPTCode, 5) + ISNULL(CONVERT(varchar(4), ml4.Code), '  ') + ISNULL(CONVERT(varchar(4), ml5.Code), ' '),'         ') AS PrincipleCode , 

		ISNULL(LEFT(pvp2.CPTCode, 5) + ISNULL(CONVERT(varchar(4), ml6.Code), '  ') + ISNULL(CONVERT(varchar(4), ml7.Code), ' '),'         ') AS SecondCode ,  

		ISNULL(LEFT(pvp3.CPTCode, 5) + ISNULL(CONVERT(varchar(4), ml8.Code), '  ') + ISNULL(CONVERT(varchar(4), ml9.Code), ' '), '         ') AS ThirdCode ,

		ISNULL(LEFT(pvp4.CPTCode, 5) + ISNULL(CONVERT(varchar(4), ml10.Code), '  ') + ISNULL(CONVERT(varchar(4), ml11.Code), ' '), '         ') AS FourthCode ,

		ISNULL(LEFT(pvp5.CPTCode, 5) + ISNULL(CONVERT(varchar(4), ml12.Code), '  ') + ISNULL(CONVERT(varchar(4), ml13.Code), ' '),'         ') AS FifthCode ,

		ISNULL(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 ,*/

    		pva.OrigInsAllocation + pva.OrigPatAllocation AS [Total Charges] ,

		ISNULL(LEFT(ic.ListName,25),'                         ') AS [Primary Payer] , 

		ISNULL(LEFT(ic2.ListName,25),'                         ') AS [Secondary Payer] ,

		ISNULL(LEFT(ic3.ListName,25),'                         ') AS [Third Payer] ,

		ISNULL(LEFT(od.StateLicenseNo,12),'            ') AS [Performing Physician ID] ,

		ISNULL(LEFT(oth.StateLicenseNo,12),'            ') AS [Other Physician ID] ,

		'999' AS [Type of Bill] ,

		ISNULL(LEFT(replace(pvd1.ICD9Code , '.' , '') , 5), '     ') AS Primary_Diag ,

		ISNULL(LEFT(replace(pvd2.ICD9Code , '.' , '') , 5), '     ') AS Second_Diag ,

		ISNULL(LEFT(replace(pvd3.ICD9Code , '.' , '') , 5), '     ') AS Third_Diag ,

		ISNULL(LEFT(replace(pvd4.ICD9Code , '.' , '') , 5), '     ') AS Fourth_Diag ,

		ISNULL(LEFT(replace(pvd5.ICD9Code , '.' , '') , 5), '     ') AS Fifth_Diag ,

		ISNULL(LEFT(replace(pvd6.ICD9Code , '.' , '') , 5), '     ') AS Sixth_Diag 
 

FROM

    		PatientVisit pv
 

Rest of my query ............. (not important for this question).

Open in new window

0
Comment
Question by:Jeff S
  • 4
  • 4
  • 3
  • +3
15 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24785563
it seems ok
do you have any problem with it?
0
 
LVL 7

Author Comment

by:Jeff S
ID: 24785635
momi sabag -
Yes, when i get it into crystal reports it seems to ignore it. I just wanted to double check my SQL.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24785677
Why is it like this:

ISNULL(ic3.ListName,25,'                         ') AS [Third Payer] ,

If it can be NULL, then LEFT() is just a overhead.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24785703
Meant "Why is it like that" and the modified one below
0
 
LVL 7

Author Comment

by:Jeff S
ID: 24785732
rrjegan17 -
confused, how should it be?
0
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
ID: 24785788
Are you writing this as a SQL Command in crystal reports or part of stored procdure that you will be calling?
Either way you should take care of this in crystal because crystal I think won't retain all the blanks. What I would do is in your sql query default the null values to some thing unique: ISNULL(ic3.ListName,'xxxxxxxxxxxxxxxxx') AS [Third Payer] and in the the crystal instead of just pulling the database field create a formula to evaluate the field and then print the results. Something like this:
WhilePritingRecords;
Stringvar val;
If {TableName.ListName} = "xxxxxxxxxxxxxxxx" Then "                         "
Else {TableName.ListName}
This is untested but hopefully this should work. Are you trying to print blanks in Crystal or using crystal to export date?
P.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24785835
>> confused, how should it be?

It should be like

ISNULL(ic3.ListName,25,'                         ') AS [Third Payer]

ignoring the LEFT() function.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Expert Comment

by:quzzie
ID: 24786169
I guess what no-one seems to be asking is why you need 25 spaces in your report ?  Is it for some form of padding \ indent in the report ?  if so then I am sure there are probably internal reporting options in the report that you could use to carry out the formatting.  Please could you clarify the need in the report for the spaces ?

0
 
LVL 7

Author Comment

by:Jeff S
ID: 24786232
pssandhu -
I like your way of handling this ... I am going to try that.
 
quzzie -
Yes the padding is needed. Its a goofy state required report and this is how they want it. I don't like it but thats government for you!
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24786298
rrjegan17 left the 25 in there... s/b ISNULL(ic3.ListName,'                         ') AS [Third Payer] ,

reality is, if you really want 25 character lengths, then use Crystal to format, or, have to make it a char (ie trailing spaces)

convert(char(25),isnull(ic3.listname,'')) as [third player],


0
 
LVL 1

Expert Comment

by:quzzie
ID: 24786408
I think in crystal there is a space function which you can use.  you might wanna look into that.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24786632
mark_wills and quzzie,
   I am not that familiar with Crystal Reports Functions.
If you have formatting function for that, then it can be implemented for better performance of the query.

I have one question here..

Does formatting all records (assuming its huge) will be done faster compared to the SQL query approach which is mentioned above.

Kindly confirm
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24786638
Well, to be perfectly honest, it is all a bit moot. Because the overall size is "drawn" and depends on things like variable font (versus fixed font), "can grow" and other properties for the field once placed on a report. But, one thing is for sure, unless you tell it in SQL it is a fixed length type field like CHAR, crystal will interpret as STRING (same as it will for varchar), except varchar will have variable length unless explicitly padded with spaces. So, unless being exported, the trailing spaces are not entirely important...
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24786767
Well, one advantage doing it in crystal is that it is only dealing with the data it is reporting on. Ideally it only selects the data that it wants to report on, and at a detail level is probably not much difference - there is obviously extra bandwidth with spaces, but, if the source / origins of the data is always presented the correcct way, then it doesn't matter if it is Crystal or Excel then.


try this quick quiz in a query window...


declare @v1 varchar(20)
declare @v2 char(20)
declare @v3 varchar(20)

set @v1 = '12345'
set @v2 = '12345'
set @v3 = '12345               '

select '|'+@v1+'|' as v1,'|'+@v2+'|' as v2,'|'+@v3+'|' as v3, '|'+convert(char(25),@v1)+'|' as cv1
select datalength(@v1),datalength(@v2),datalength(@v3), datalength(convert(char(25),@v1))
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24789465
Jeff, you might be making a lot of work for yourself, did you try the CHAR mode ? Takes care of variable lengths as well...

Don't get me wrong though, I am sure pssandhu's solution will work, just seems that if you do not have to double handle, then dont...
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now