Using HeidiSQL it works but takes 50 seconds to run (only takes 2.6 seconds on my PC using the same data). Using my C# app, I get an error "Fatal error encountered during command execution". Any idea what is different about those two situations that it would work in one but not the other? Does the execution time have any bearing on it?
Code: Select all
SELECT p.patnum AS reference_id,
p.guarantor AS guarantor_reference_id,
p.bal_0_30 AS balance_0_30,
p.bal_31_60 AS balance_31_60,
p.bal_61_90 AS balance_61_90,
p.balover90 AS balance_over_90,
p.baltotal AS account_balance,
pending_writeoff,
claims_estimated_amount,
patient_portion_due AS amount_due_from_patient,
Sum(procfee) AS lifetime_value
FROM patient AS p
LEFT JOIN (SELECT patient.patnum,
patient.guarantor,
patient.bal_0_30,
patient.bal_31_60,
patient.bal_61_90,
patient.balover90,
guarAging.baltotal,
guarAging.inswoest
AS
pending_writeoff,
guarAging.inspayest
AS
claims_estimated_amount,
guarAging.baltotal - guarAging.inspayest -
guarAging.inswoest
AS
patient_portion_due
FROM (SELECT tSums.patnum,
Round(CASE
WHEN tSums.totalcredits >=
tSums.chargesover90
THEN 0
ELSE tSums.chargesover90 -
tSums.totalcredits
end, 2) Balover90,
Round(CASE
WHEN tSums.totalcredits <=
tSums.chargesover90
THEN
tSums.charges_61_90
WHEN tSums.chargesover90 +
tSums.charges_61_90
<=
tSums.totalcredits
THEN 0
ELSE tSums.chargesover90 +
tSums.charges_61_90
-
tSums.totalcredits
end, 2) Bal_61_90,
Round(CASE
WHEN tSums.totalcredits <
tSums.chargesover90 +
tSums.charges_61_90 THEN
tSums.charges_31_60
WHEN tSums.chargesover90 +
tSums.charges_61_90
+ tSums.charges_31_60 <=
tSums.totalcredits THEN 0
ELSE tSums.chargesover90 +
tSums.charges_61_90
+ tSums.charges_31_60 -
tSums.totalcredits
end, 2) Bal_31_60,
Round(CASE
WHEN tSums.totalcredits <
tSums.chargesover90 +
tSums.charges_61_90
+ tSums.charges_31_60 THEN
tSums.charges_0_30
WHEN tSums.chargesover90 +
tSums.charges_61_90
+ tSums.charges_31_60 +
tSums.charges_0_30 <=
tSums.totalcredits
THEN 0
ELSE tSums.chargesover90 +
tSums.charges_61_90
+ tSums.charges_31_60 +
tSums.charges_0_30 -
tSums.totalcredits
end, 2) Bal_0_30,
Round(tSums.baltotal, 2) BalTotal,
Round(tSums.inswoest, 2) InsWoEst,
Round(tSums.inspayest, 2) InsPayEst,
Round(tSums.payplandue, 2) PayPlanDue
FROM (SELECT p.guarantor PatNum,
Sum(CASE
WHEN trans.tranamount > 0
AND trans.trandate <
'2018-01-11'
THEN
trans.tranamount
ELSE 0
end) ChargesOver90,
Sum(CASE
WHEN trans.tranamount > 0
AND trans.trandate <
'2018-01-12'
AND trans.trandate >=
'2018-02-10'
THEN
trans.tranamount
ELSE 0
end) Charges_61_90,
Sum(CASE
WHEN trans.tranamount > 0
AND trans.trandate <
'2018-02-11'
AND trans.trandate >=
'2018-03-12'
THEN
trans.tranamount
ELSE 0
end) Charges_31_60,
Sum(CASE
WHEN trans.tranamount > 0
AND trans.trandate <=
'2018-03-13'
AND trans.trandate >=
'2018-04-12'
THEN
trans.tranamount
ELSE 0
end) Charges_0_30,
-Sum(CASE
WHEN trans.tranamount < 0
AND trans.trandate <=
'2018-04-12'
THEN
trans.tranamount
ELSE 0
end) TotalCredits,
Sum(CASE
WHEN trans.tranamount != 0 THEN
trans.tranamount
ELSE 0
end) BalTotal,
Sum(trans.inswoest) InsWoEst,
Sum(trans.inspayest) InsPayEst,
Sum(trans.payplanamount) PayPlanDue
FROM (SELECT 'Proc'
TranType,
pl.patnum,
pl.procdate
TranDate,
pl.procfee * (
pl.unitqty + pl.baseunits )
TranAmount,
0
PayPlanAmount
,
0
InsWoEst,
0
InsPayEst
FROM procedurelog pl
WHERE pl.procstatus = 2
AND pl.procfee != 0
UNION ALL
SELECT 'Claimproc' TranType,
cp.patnum,
cp.datecp TranDate,
( CASE
WHEN cp. status != 0 THEN
( CASE
WHEN cp.payplannum = 0 THEN
-
cp.inspayamt
ELSE 0
end )
-
cp.writeoff
ELSE 0
end ) TranAmount,
( CASE
WHEN cp.payplannum != 0
AND cp. status IN ( 1,
4, 5 )
THEN -
cp.inspayamt
ELSE 0
end ) PayPlanAmount,
( CASE
WHEN cp. status = 0 THEN
cp.writeoff
ELSE 0
end ) InsWoEst,
( CASE
WHEN cp. status = 0 THEN
cp.inspayest
ELSE 0
end ) InsPayEst
FROM claimproc cp
WHERE cp. status IN ( 0, 1, 4, 5, 7 )
HAVING tranamount != 0
OR payplanamount != 0
OR inswoest != 0
OR inspayest != 0
UNION ALL
SELECT 'Adj' TranType,
a.patnum,
a.adjdate TranDate,
a.adjamt TranAmount,
0 PayPlanAmount,
0 InsWoEst,
0 InsPayEst
FROM adjustment a
WHERE a.adjamt != 0
UNION ALL
SELECT 'PatPay' TranType,
ps.patnum,
ps.datepay TranDate,
( CASE
WHEN ps.payplannum = 0 THEN
-
ps.splitamt
ELSE 0
end ) TranAmount,
( CASE
WHEN ps.payplannum != 0
THEN -
ps.splitamt
ELSE 0
end ) PayPlanAmount,
0 InsWoEst,
0 InsPayEst
FROM paysplit ps
WHERE ps.splitamt != 0
UNION ALL
SELECT 'PPCharge'
TranType,
ppc.guarantor
PatNum,
ppc.chargedate
TranDate,
0
TranAmount,
Coalesce (ppc.principal +
ppc.interest, 0)
PayPlanAmount
,
0
InsWoEst,
0
InsPayEst
FROM payplancharge ppc
WHERE ppc.chargedate <= '2018-04-22'
AND ppc.chargetype = 0
AND Coalesce (ppc.principal +
ppc.interest, 0)
!= 0
UNION ALL
SELECT 'PPComplete' TranType,
pp.patnum,
pp.payplandate TranDate,
-pp.completedamt TranAmount,
0 PayPlanAmount,
0 InsWoEst,
0 InsPayEst
FROM payplan pp
WHERE pp.completedamt != 0) trans
RIGHT JOIN patient p
ON p.patnum = trans.patnum
GROUP BY p.guarantor
ORDER BY NULL) tSums) guarAging
INNER JOIN patient
ON patient.patnum = guarAging.patnum
LEFT JOIN (SELECT patnum,
claimstatus,
claimfee,
inspayest,
writeoff
FROM claim) AS c
ON c.patnum = patient.patnum
GROUP BY patient.patnum) AS results
ON results.patnum = p.patnum
LEFT JOIN procedurelog AS plog
ON plog.patnum = p.patnum
AND plog.procstatus = 2
GROUP BY p.patnum
Code: Select all
try
{
using (var mySqlConnection = new MySqlConnection(connBuilder.ConnectionString))
{
mySqlConnection.Open();
using (var cmd = new MySqlCommand(strQuery, mySqlConnection))
{
using (var reader = cmd.ExecuteReader()) <= FAILS HERE
{