Tengo esta consulta que funiona bien:
Código PHP:
SELECT Claims.ClaimID, Claims.PatientID AS Patient, Claims.ProviderID AS Provider, Claims.InsurerID AS Insurer, Claims.DateOfService AS DOS, Claims.DateOfReceiptByMDabroad AS DOR, Repricing.ConvertedHospitalCharges AS Hosp_Charges, Repricing.HospitalChargesDiscountAmount AS Hosp_Dct_to_Insurer, Repricing.ConvertedProfessionalCharges AS Prof_Charges, Repricing.ProfessionalChargesDiscountAmount AS Prof_Dct_to_Ins, (Repricing.ConvertedTotalCharges-(Prof_Dct_to_Ins+Hosp_Dct_to_Insurer)) AS Due_By_Insurer, Claims.HospitalChargesDiscount AS Hosp_Pct_to_MD, Claims.ProfessionalChargesDiscount AS Prof_Pct_to_MD, SUM(IIf(ISNULL(ClaimsFollowUp.AmountReceived),0,ClaimsFollowUp.AmountReceived)) AS IP, Due_By_Insurer-IP AS Balance, (((Hosp_Charges-Hosp_Dct_to_Insurer)*100)/Due_By_Insurer) AS perc_hospital, (((Prof_Charges-Prof_Dct_to_Ins)*100)/Due_By_Insurer) AS perc_professional, ((Balance*perc_hospital)/100) AS Hosp_Balance, ((Balance*perc_professional)/100) AS Prof_Balance, (Hosp_Balance*Hosp_Pct_to_MD) AS Hosp_Fees_Outs, (Prof_Balance*Prof_Pct_to_MD) AS Prof_Fees_Outs, (Hosp_Fees_Outs+Prof_Fees_Outs) AS Total_Fees_Outstanding, Claims.ClaimStatus, Claims.Responsible, Claims.ClaimType
FROM (Claims INNER JOIN Repricing ON Claims.ClaimID=Repricing.RepricingID) LEFT JOIN ClaimsFollowUp ON Repricing.RepricingID=ClaimsFollowUp.RepricingID
WHERE (Claims.ClaimStatus)<>"9. Case Closed" AND DateOfReceiptByMDabroad BETWEEN date() AND date()-30 AND ClaimType="Billing"
GROUP BY Claims.ClaimStatus, Claims.ClaimID, Repricing.ConvertedTotalCharges, Repricing.ConvertedProfessionalCharges, Repricing.ConvertedHospitalCharges, Claims.HospitalChargesDiscount, Claims.ProfessionalChargesDiscount, Repricing.ProfessionalChargesDiscountAmount, Repricing.HospitalChargesDiscountAmount, Claims.DateOfService, Claims.DateOfReceiptByMDabroad, Claims.Responsible, Claims.InsurerID, Claims.ProviderID, Claims.PatientID, Claims.ClaimType;
Y cuando le coloco un Simple Count(*) No me lo hace, es decir sale 1 en esa columna:
Código PHP:
SELECT COUNT(*) AS Total, Claims.ClaimID, Claims.PatientID AS Patient, Claims.ProviderID AS Provider, Claims.InsurerID AS Insurer, Claims.DateOfService AS DOS, Claims.DateOfReceiptByMDabroad AS DOR, Repricing.ConvertedHospitalCharges AS Hosp_Charges, Repricing.HospitalChargesDiscountAmount AS Hosp_Dct_to_Insurer, Repricing.ConvertedProfessionalCharges AS Prof_Charges, Repricing.ProfessionalChargesDiscountAmount AS Prof_Dct_to_Ins, (Repricing.ConvertedTotalCharges-(Prof_Dct_to_Ins+Hosp_Dct_to_Insurer)) AS Due_By_Insurer, Claims.HospitalChargesDiscount AS Hosp_Pct_to_MD, Claims.ProfessionalChargesDiscount AS Prof_Pct_to_MD, SUM(IIf(ISNULL(ClaimsFollowUp.AmountReceived),0,ClaimsFollowUp.AmountReceived)) AS IP, Due_By_Insurer-IP AS Balance, (((Hosp_Charges-Hosp_Dct_to_Insurer)*100)/Due_By_Insurer) AS perc_hospital, (((Prof_Charges-Prof_Dct_to_Ins)*100)/Due_By_Insurer) AS perc_professional, ((Balance*perc_hospital)/100) AS Hosp_Balance, ((Balance*perc_professional)/100) AS Prof_Balance, (Hosp_Balance*Hosp_Pct_to_MD) AS Hosp_Fees_Outs, (Prof_Balance*Prof_Pct_to_MD) AS Prof_Fees_Outs, (Hosp_Fees_Outs+Prof_Fees_Outs) AS Total_Fees_Outstanding, Claims.ClaimStatus, Claims.Responsible, Claims.ClaimType
FROM (Claims INNER JOIN Repricing ON Claims.ClaimID=Repricing.RepricingID) LEFT JOIN ClaimsFollowUp ON Repricing.RepricingID=ClaimsFollowUp.RepricingID
WHERE (Claims.ClaimStatus)<>"9. Case Closed" AND DateOfReceiptByMDabroad BETWEEN date() AND date()-30 AND ClaimType="Billing"
GROUP BY Claims.ClaimStatus, Claims.ClaimID, Repricing.ConvertedTotalCharges, Repricing.ConvertedProfessionalCharges, Repricing.ConvertedHospitalCharges, Claims.HospitalChargesDiscount, Claims.ProfessionalChargesDiscount, Repricing.ProfessionalChargesDiscountAmount, Repricing.HospitalChargesDiscountAmount, Claims.DateOfService, Claims.DateOfReceiptByMDabroad, Claims.Responsible, Claims.InsurerID, Claims.ProviderID, Claims.PatientID, Claims.ClaimType;
Por qué? Como lo corrijo? Se puede o no ?