Converting formula from Crystal to SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Converting formula from Crystal to SQL

Author

Topic

bonaboard
Starting Member

7 Posts

Posted - 2010-03-17 : 16:57:51

I need to transition my Crystal Reports formulas into my SQL select statement because we are diong away with the report. I am having difficulties making the transition into the SQL select statment.

--------------------------------------------------------------------------------

In Crystal I pull the following statement

SELECT "PatientRefund"."INVOICE", "PatientRefund"."TRANS_NBR", "PatientRefund"."CHECK_DATE", "PatientRefund"."TO_BASE_AMT", "PatientRefund"."SEQ_NBR" FROM "PROD90"."dbo"."PatientRefund" "PatientRefund"

and then I have 2 formulas in Crystal for the Invoice#.

//used to trim out unneeded numbers

Formula 1 MidInvoice: Mid({PatientRefund.INVOICE}, 1,8)

//used to add spaces before number - this is the formula that prints in the report

Formula 2 PrintInvoice: if length({@MidInvoice}) < 11 then space(11 - length({@MidInvoice}) )+ totext({@MidInvoice}) else {@MidInvoice}

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-17 : 17:07:15

It would be fine if you could show your needs by giving example data and wanted results.

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-18 : 03:45:26

They should be

Substring(PatientRefund.INVOICE,1,8)
right(replicate(' ',11)+Substring(PatientRefund.INVOICE,1,8),11)

Madhivanan

Failing to plan is Planning to fail

bonaboard
Starting Member

7 Posts

Posted - 2010-03-18 : 09:18:43

Thank you, that formula worked perfect!