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.
| Author |
Topic |
|
pakumen
Starting Member
2 Posts |
 Posted - 2007-12-10 : 10:58:14
|
| select acr_application.appNo as 'applicationNo', acr_application.facilityName as 'nameOf DrugTesting', acr_application.adr as 'address', acr_reference_no.refNo as 'referenceNo', ref_charge.chargeDesc as 'charges/fee', acr_reference_no.amount as 'amount', ref_settings.earlyFileDiscnt as 'pdiscount', ((ref_settings.earlyFileDiscnt / 100) * acr_reference_no.amount) as 'discount', case when acr_reference_no.earlyFileDiscntDt is not null then acr_reference_no.earlyFileDiscntDt else case when '2007-09-29' <= ref_facility.validToDt then date_sub(ref_facility.validToDt, interval (ref_settings.earlyFileDiscntValid + 1) day) end end as 'discountValidity', ref_settings.lateFileDiscnt as 'psurcharge', ((ref_settings.lateFileDiscnt / 100) * acr_reference_no.amount) as 'surcharge', case when acr_reference_no.lateFileSurchargeDt is not null then acr_reference_no.lateFileSurchargeDt else case when '2007-09-29' <= ref_facility.validToDt then ref_facility.validToDt, select a.validTodt from acr_application a where a.appNo = (select max(b.appNo) from acr_application b where b.appNo < acr_application.appNo) end end end as 'surchargeValidity', '0012 2222 20' as 'accountNumber', concat('DOH-',acr_reference_no.refNo, '','-85619') as 'accountName', concat(if(acr_application.ownerTitle is null or acr_application.ownerTitle='','', concat(acr_application.ownerTitle, '')), acr_application.ownerFname, ' ', if(acr_application.ownerMname is null or acr_application.ownerMname='','', concat(substr(acr_application.ownerMname,1,1),'.', ' ')), acr_application.ownerLname, '', '', if(acr_application.ownerSuffix is null or acr_application.ownerSuffix='','', concat(' ', acr_application.ownerSuffix))) as 'nameOfRepresentative' case when acr_application.appType = 'INA' then acr_reference_no.amount else case when acr_application.appType = 'RNW' then (if(acr_reference_no.earlyFileDiscnt is not null and acr_reference_no.earlyFileDiscnt <='2007-09-29', acr_reference_no.amount + (acr_reference_no.amount * (ref_settings.earlyFileDiscnt / 100)))) else case when acr_reference_no.earlyFileDiscntDt is not null and acr_reference.earlyFileDiscntDt > '2007-09-29' and '2007-09-29' <= ref_facility.validToFt then acr_reference_no.amount, acr_reference_no.amount + (acr_reference_no.amount * (ref_settings.earlyFileDiscnt / 100)) end end end as 'totalCashDeposit', case when acr_application.appType = 'INA' then date_add(acr_applicationappDt, interval ref_settings.validityAfterAppDayy) else case when acr_application.appType = 'RNW' then acr_reference_no.earlyFileDiscntDt else case when acr_reference_no.earlyFileDiscntDt is not null and acr_reference_no.earlyFileDiscntDt > '2007-09-29' and '2007-09-29' <= ref_facility.validToDt then ref_facility.validToDt, date_add(ref_facility.validToDt, interval ref_settings.validityAfterAppDay) end end end as 'bankValidity'from acr_application left join ref_facility on acr_application.appNo = ref_facility.appNo left join ref_settings on ref_settings.service = acr_application.service and ref_settings.ownType = acr_application.ownType and ref_settings.facilityType = acr_application.facilityType and ref_settings.institutChar = acr_application.institutChar left join (acr_reference_no left join ref_charge on acr_reference_no.chargeId = ref_charge.chargeId and acr_reference_no.paid <> 'Y') on acr_application.appNo = acr_reference_no.appNo |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-10 : 11:00:26
|
It will help us if you tell us what the problem is.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-10 : 11:02:28
|
1) The query do not look like it is a MICROSOFT SQL SERVER query (T-SQL).2) With CASE keyword, first matching check gets the attention. All other checks are ignored.3) Do you want the query translated into T-SQL?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
pakumen
Starting Member
2 Posts |
Posted - 2007-12-10 : 19:40:57
|
| sorry i forgot to tell, but my problem is about the case statement can u help me plssss!!!! and sorry again co'z this is not a T-SQL but if you want to translate it in T-SQL its ok for me....... thanks again |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-12-10 : 19:56:26
|
this forum is for MS SQL Server.you'll have better luck if you post your question on a forum appropriate to your DBMS.plsssss!!!!
elsasoft.org |
|
|
|
|
|
|
|
|
can u help me debugging this i appreciate ty