[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Query Advice

Posted on 2009-07-06
15
Medium Priority
?
265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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
 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

650 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