No Data Available ReportName() Which dates:MONTHLYMonthly: the start of last month to todaySetup, smaller practices: all 24 months in one runSetup, larger practices, part 1 of 3: months 24 to 17 backSetup, larger practices, part 2 of 3: months 16 to 9 backSetup, larger practices, part 3 of 3: the last 8 months to todayCustom dates (then Next): to split a run that is still too largeCustom: choose it, then click Next to pick the dates. Anything else: click OK. NextOK ReportName() The first and last day:2026-09-01 to 2026-09-30 BackOK /content/package[@name='eCWEBO']/model[@name='model'] SELECT u.RowType AS RowType, u.C01 AS C01, u.C02 AS C02, u.C03 AS C03, u.C04 AS C04, u.C05 AS C05, u.C06 AS C06, u.C07 AS C07, u.C08 AS C08, u.C09 AS C09, u.C10 AS C10, u.C11 AS C11, u.C12 AS C12, u.C13 AS C13, u.C14 AS C14, u.C15 AS C15, u.C16 AS C16, u.C17 AS C17, u.C18 AS C18, u.C19 AS C19, u.C20 AS C20, u.C21 AS C21, u.C22 AS C22, u.C23 AS C23, u.C24 AS C24, u.C25 AS C25, u.C26 AS C26, u.C27 AS C27, u.C28 AS C28, u.C29 AS C29, u.C30 AS C30, u.C31 AS C31, u.C32 AS C32, u.C33 AS C33 FROM ( SELECT 'CTL' AS RowType, CAST('stamp' AS varchar(200)) AS C01, CAST(CONVERT(varchar(16), GETDATE(), 120) AS varchar(200)) AS C02, CAST(CONVERT(varchar(10), CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END, 120) AS varchar(200)) AS C03, CAST(CONVERT(varchar(10), CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END, 120) AS varchar(200)) AS C04, CAST('money-8' AS varchar(200)) AS C05, CAST(NULL AS varchar(200)) AS C06, CAST(NULL AS varchar(200)) AS C07, CAST(NULL AS varchar(200)) AS C08, CAST(NULL AS varchar(200)) AS C09, CAST(NULL AS varchar(200)) AS C10, CAST(NULL AS varchar(200)) AS C11, CAST(NULL AS varchar(200)) AS C12, CAST(NULL AS varchar(200)) AS C13, CAST(NULL AS varchar(200)) AS C14, CAST(NULL AS varchar(200)) AS C15, CAST(NULL AS varchar(200)) AS C16, CAST(NULL AS varchar(200)) AS C17, CAST(NULL AS varchar(200)) AS C18, CAST(NULL AS varchar(200)) AS C19, CAST(NULL AS varchar(200)) AS C20, CAST(NULL AS varchar(200)) AS C21, CAST(NULL AS varchar(200)) AS C22, CAST(NULL AS varchar(200)) AS C23, CAST(NULL AS varchar(200)) AS C24, CAST(NULL AS varchar(200)) AS C25, CAST(NULL AS varchar(200)) AS C26, CAST(NULL AS varchar(200)) AS C27, CAST(NULL AS varchar(200)) AS C28, CAST(NULL AS varchar(200)) AS C29, CAST(NULL AS varchar(200)) AS C30, CAST(NULL AS varchar(200)) AS C31, CAST(NULL AS varchar(200)) AS C32, CAST(NULL AS varchar(200)) AS C33 FROM sys.tables t WHERE t.name = 'enc' UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('charges_claim_month' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), x.M, 120), CAST(CAST(x.Billed AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.Claims AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.Patients AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.Lines AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT DATEADD(month, DATEDIFF(month, 0, iv.InvoiceDt), 0) AS M, SUM(cp.BilledFee) AS Billed, COUNT(DISTINCT iv.Id) AS Claims, COUNT(DISTINCT iv.PatientId) AS Patients, COUNT(*) AS Lines FROM edi_inv_cpt cp JOIN edi_invoice iv ON iv.Id = cp.InvoiceId AND iv.DeleteFlag = 0 AND iv.VoidFlag = 0 WHERE cp.deleteFlag = 0 AND iv.InvoiceDt >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND iv.InvoiceDt < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) GROUP BY DATEADD(month, DATEDIFF(month, 0, iv.InvoiceDt), 0) ) x UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('charges_service_month' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), x.M, 120), CAST(CAST(x.Billed AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.Claims AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.Patients AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.Lines AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT DATEADD(month, DATEDIFF(month, 0, cp.SDOS), 0) AS M, SUM(cp.BilledFee) AS Billed, COUNT(DISTINCT iv.Id) AS Claims, COUNT(DISTINCT iv.PatientId) AS Patients, COUNT(*) AS Lines FROM edi_inv_cpt cp JOIN edi_invoice iv ON iv.Id = cp.InvoiceId AND iv.DeleteFlag = 0 AND iv.VoidFlag = 0 WHERE cp.deleteFlag = 0 AND cp.SDOS >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND cp.SDOS < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) GROUP BY DATEADD(month, DATEDIFF(month, 0, cp.SDOS), 0) ) x UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('payments' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), x.M, 120), CAST(CAST(x.Paid AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.Adj AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.Wh AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.PT AS int) AS varchar(24)), CAST(CAST(x.N AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT DATEADD(month, DATEDIFF(month, 0, ip.date), 0) AS M, ip.PayorType AS PT, SUM(pd.paid) AS Paid, SUM(pd.adjustment) AS Adj, SUM(pd.withheld) AS Wh, COUNT(*) AS N FROM edi_paymentdetail pd JOIN edi_inspayments ip ON ip.paymentId = pd.paymentId LEFT JOIN edi_invoice iv ON iv.Id = pd.invoiceId WHERE ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) AND ip.deleteFlag = 0 AND ISNULL(CAST(ip.VoidFlag AS int), 0) = 0 AND ISNULL(pd.invoiceId, 0) > 0 AND iv.Id IS NOT NULL AND iv.DeleteFlag = 0 AND iv.VoidFlag = 0 GROUP BY DATEADD(month, DATEDIFF(month, 0, ip.date), 0), ip.PayorType ) x UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('payments_no_claim' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), x.M, 120), CAST(CAST(x.Paid AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.Adj AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.PT AS int) AS varchar(24)), CAST(CAST(x.N AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT DATEADD(month, DATEDIFF(month, 0, ip.date), 0) AS M, ip.PayorType AS PT, SUM(pd.paid) AS Paid, SUM(pd.adjustment) AS Adj, COUNT(*) AS N FROM edi_paymentdetail pd JOIN edi_inspayments ip ON ip.paymentId = pd.paymentId WHERE ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) AND ip.deleteFlag = 0 AND ISNULL(CAST(ip.VoidFlag AS int), 0) = 0 AND ISNULL(pd.invoiceId, 0) = 0 GROUP BY DATEADD(month, DATEDIFF(month, 0, ip.date), 0), ip.PayorType ) x UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('unposted' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), x.M, 120), CAST(CAST(x.Amt AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.Unp AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.N AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT DATEADD(month, DATEDIFF(month, 0, ip.date), 0) AS M, SUM(ip.amount) AS Amt, SUM(ISNULL(ip.UnpostedAmount, 0)) AS Unp, COUNT(*) AS N FROM edi_inspayments ip WHERE ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) AND ip.deleteFlag = 0 GROUP BY DATEADD(month, DATEDIFF(month, 0, ip.date), 0) ) x UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('writeoffs' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), x.M, 120), CAST(CAST(x.WoAll AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.WoLive AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.N AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT DATEADD(month, DATEDIFF(month, 0, adj.date), 0) AS M, SUM(adj.amount) AS WoAll, SUM(CASE WHEN iv.DeleteFlag = 0 AND iv.VoidFlag = 0 THEN adj.amount ELSE 0 END) AS WoLive, COUNT(*) AS N FROM edi_inv_adjustments adj LEFT JOIN edi_invoice iv ON iv.Id = adj.InvId WHERE adj.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND adj.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) GROUP BY DATEADD(month, DATEDIFF(month, 0, adj.date), 0) ) x UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('refunds' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), x.M, 120), CAST(CAST(x.Amt AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.Unp AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.N AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT DATEADD(month, DATEDIFF(month, 0, rf.date), 0) AS M, SUM(rf.amount) AS Amt, SUM(ISNULL(rf.unpostedrefunds, 0)) AS Unp, COUNT(*) AS N FROM refunds rf WHERE rf.deleteFlag = 0 AND rf.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND rf.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) GROUP BY DATEADD(month, DATEDIFF(month, 0, rf.date), 0) ) x UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('ar_today' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), CAST(GETDATE() AS date), 120), CAST(CAST(x.Credits AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.Bal AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.PtBal AS decimal(16, 2)) AS varchar(24)), CAST(CAST(x.N AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT SUM(iv.Balance) AS Bal, SUM(ISNULL(iv.PtBalance, 0)) AS PtBal, SUM(CASE WHEN iv.Balance < 0 THEN iv.Balance ELSE 0 END) AS Credits, COUNT(*) AS N FROM edi_invoice iv WHERE iv.DeleteFlag = 0 AND iv.VoidFlag = 0 AND iv.Balance <> 0 ) x UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('rows_CLM' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), CAST((CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AS date), 120), CAST(CAST(x.N AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT COUNT(*) AS N FROM ( SELECT iv0.Id AS Id FROM edi_invoice iv0 WHERE iv0.DeleteFlag = 0 AND (iv0.InvoiceDt >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND iv0.InvoiceDt < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) OR iv0.ServiceDt >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND iv0.ServiceDt < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) OR iv0.Balance <> 0) UNION SELECT pd0.invoiceId FROM edi_paymentdetail pd0 JOIN edi_inspayments ip0 ON ip0.paymentId = pd0.paymentId WHERE pd0.invoiceId > 0 AND ip0.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip0.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) UNION SELECT adj0.InvId FROM edi_inv_adjustments adj0 WHERE adj0.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND adj0.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) ) cs JOIN edi_invoice iv ON iv.Id = cs.Id AND iv.DeleteFlag = 0 ) x UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('rows_CHG' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), CAST((CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AS date), 120), CAST(CAST(x.N AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT COUNT(*) AS N FROM edi_inv_cpt cp JOIN edi_invoice iv ON iv.Id = cp.InvoiceId AND iv.DeleteFlag = 0 WHERE cp.deleteFlag = 0 AND (cp.SDOS >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND cp.SDOS < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) OR iv.InvoiceDt >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND iv.InvoiceDt < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END)) ) x UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('rows_PAY' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), CAST((CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AS date), 120), CAST(CAST(x.N AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT COUNT(*) AS N FROM edi_inv_eob eb JOIN edi_paymentdetail pd ON pd.PmtDetailId = eb.PaymentDetailId JOIN edi_inspayments ip ON ip.paymentId = pd.paymentId WHERE ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) ) x UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('rows_WOF' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), CAST((CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AS date), 120), CAST(CAST(x.N AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT COUNT(*) AS N FROM edi_inv_adjustments adj WHERE adj.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND adj.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) ) x UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('rows_WOL' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), CAST((CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AS date), 120), CAST(CAST(x.N AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT COUNT(*) AS N FROM adjustmentposting ap JOIN edi_inv_adjustments adj ON adj.Id = ap.AdjustmentId WHERE ap.DeleteFlag = 0 AND adj.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND adj.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) ) x UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('rows_REF' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), CAST((CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AS date), 120), CAST(CAST(x.N AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT COUNT(*) AS N FROM refunds rf WHERE rf.deleteFlag = 0 AND rf.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND rf.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) ) x UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('rows_DEN' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), CAST((CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AS date), 120), CAST(CAST(x.N AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT COUNT(*) AS N FROM edi_inveob_cas c JOIN edi_inv_eob eb ON eb.Id = c.InvEobId JOIN edi_paymentdetail pd ON pd.PmtDetailId = eb.PaymentDetailId JOIN edi_inspayments ip ON ip.paymentId = pd.paymentId WHERE c.deleteFlag = 0 AND ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) ) x UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('rows_RMK' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), CAST((CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AS date), 120), CAST(CAST(x.N AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT (SELECT COUNT(*) FROM edi_claimorlinelevel_eraremarkcodes r JOIN edi_inv_eob eb ON eb.Id = r.DetailId JOIN edi_paymentdetail pd ON pd.PmtDetailId = eb.PaymentDetailId JOIN edi_inspayments ip ON ip.paymentId = pd.paymentId WHERE r.deleteFlag = 0 AND ISNULL(CAST(r.ClaimLevel AS int), 0) = 0 AND ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END)) + (SELECT COUNT(*) FROM edi_claimorlinelevel_eraremarkcodes r JOIN edi_paymentdetail pd ON pd.PmtDetailId = r.DetailId JOIN edi_inspayments ip ON ip.paymentId = pd.paymentId WHERE r.deleteFlag = 0 AND ISNULL(CAST(r.ClaimLevel AS int), 0) = 1 AND ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END)) AS N ) x UNION ALL SELECT 'CLM', CAST(CAST(iv.Id AS int) AS varchar(24)), CAST(CAST(iv.PatientId AS int) AS varchar(24)), CAST(CAST(iv.EncounterId AS int) AS varchar(24)), CONVERT(varchar(10), iv.ServiceDt, 120), CONVERT(varchar(10), iv.InvoiceDt, 120), CAST(CAST(iv.InvoiceAmount AS decimal(16, 2)) AS varchar(24)), CAST(CAST(iv.Balance AS decimal(16, 2)) AS varchar(24)), CAST(CAST(iv.PtBalance AS decimal(16, 2)) AS varchar(24)), CAST(CAST(py.Ins1 AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(i1.insuranceclass AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(py.Ins2 AS int) AS varchar(24)), CAST(CAST(py.Ins3 AS int) AS varchar(24)), CAST(CAST(iv.CreatedBy AS int) AS varchar(24)), CAST(CAST(iv.RenProviderId AS int) AS varchar(24)), CAST(CAST(e.ResourceId AS int) AS varchar(24)), CAST(CAST(e.doctorID AS int) AS varchar(24)), CAST(CAST(iv.InvFacilityId AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(iv.InvPOS AS varchar(10)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(e.visittypeid AS int) AS varchar(24)), CAST(CAST(iv.VoidFlag AS int) AS varchar(24)), CAST(CAST(iv.BilledToIdType AS int) AS varchar(24)), CAST(CAST(iv.BilledToId AS int) AS varchar(24)), CONVERT(varchar(10), CASE WHEN iv.FirstSubmittedDate > '1902-01-01' THEN iv.FirstSubmittedDate END, 120), CONVERT(varchar(10), lg.FirstSub, 120), CAST(CAST(lg.Subs AS int) AS varchar(24)), CONVERT(varchar(10), lg.FirstCha, 120), CAST(CAST(lg.Chr AS int) AS varchar(24)), CAST(CAST(lg.Inr AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(ll.ToStatus AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), ll.date, 120), REPLACE(REPLACE(REPLACE(CAST(pt.ControlNo AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(cdx.RefProvId AS int) AS varchar(24)), CAST(CAST(CASE WHEN e.refPrId > 0 THEN e.refPrId END AS int) AS varchar(24)) FROM ( SELECT iv0.Id AS Id FROM edi_invoice iv0 WHERE iv0.DeleteFlag = 0 AND (iv0.InvoiceDt >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND iv0.InvoiceDt < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) OR iv0.ServiceDt >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND iv0.ServiceDt < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) OR iv0.Balance <> 0) UNION SELECT pd0.invoiceId FROM edi_paymentdetail pd0 JOIN edi_inspayments ip0 ON ip0.paymentId = pd0.paymentId WHERE pd0.invoiceId > 0 AND ip0.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip0.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) UNION SELECT adj0.InvId FROM edi_inv_adjustments adj0 WHERE adj0.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND adj0.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) ) cs JOIN edi_invoice iv ON iv.Id = cs.Id AND iv.DeleteFlag = 0 LEFT JOIN patients pt ON pt.pid = iv.PatientId LEFT JOIN enc e ON e.encounterID = iv.EncounterId LEFT JOIN ( SELECT cd.InvId, MAX(cd.RefProvId) AS RefProvId FROM edi_inv_claimdata cd WHERE cd.RefProvId > 0 GROUP BY cd.InvId ) cdx ON cdx.InvId = iv.Id LEFT JOIN ( SELECT ii.InvoiceId AS InvId, MIN(CASE WHEN ii.SeqNo = 1 THEN ii.InsId END) AS Ins1, MIN(CASE WHEN ii.SeqNo = 2 THEN ii.InsId END) AS Ins2, MIN(CASE WHEN ii.SeqNo = 3 THEN ii.InsId END) AS Ins3 FROM edi_inv_insurance ii WHERE ii.deleteFlag = 0 AND ii.SeqNo IN (1, 2, 3) AND ii.InsId <> 0 GROUP BY ii.InvoiceId ) py ON py.InvId = iv.Id LEFT JOIN insurance i1 ON i1.insId = py.Ins1 LEFT JOIN insurance i2 ON i2.insId = py.Ins2 LEFT JOIN insurance i3 ON i3.insId = py.Ins3 LEFT JOIN ( SELECT l.InvId AS InvId, MIN(CASE WHEN l.ToStatus = 'CMS' THEN l.date END) AS FirstSub, SUM(CASE WHEN l.ToStatus = 'CMS' THEN 1 ELSE 0 END) AS Subs, MIN(CASE WHEN l.ToStatus = 'CHA' THEN l.date END) AS FirstCha, SUM(CASE WHEN l.ToStatus = 'CHR' THEN 1 ELSE 0 END) AS Chr, SUM(CASE WHEN l.ToStatus = 'INR' THEN 1 ELSE 0 END) AS Inr, SUM(CASE WHEN l.ToStatus = 'EPD' THEN 1 ELSE 0 END) AS Epd, MAX(l.Id) AS LastId FROM edi_inv_claimstatus_log l GROUP BY l.InvId ) lg ON lg.InvId = iv.Id LEFT JOIN edi_inv_claimstatus_log ll ON ll.Id = lg.LastId UNION ALL SELECT 'CHG', CAST(CAST(cp.InvoiceId AS int) AS varchar(24)), CAST(CAST(cp.Id AS int) AS varchar(24)), CAST(CAST(iv.PatientId AS int) AS varchar(24)), CAST(CAST(iv.EncounterId AS int) AS varchar(24)), CONVERT(varchar(10), cp.SDOS, 120), CONVERT(varchar(10), cp.EDOS, 120), CONVERT(varchar(10), iv.InvoiceDt, 120), REPLACE(REPLACE(REPLACE(CAST(cp.Code AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(cp.Mod1 AS varchar(10)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(cp.Mod2 AS varchar(10)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(cp.Mod3 AS varchar(10)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(cp.Mod4 AS varchar(10)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(x1.Code AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(x2.Code AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(x3.Code AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(x4.Code AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(cp.Units AS decimal(14, 3)) AS varchar(24)), CAST(CAST(cp.BilledFee AS decimal(16, 2)) AS varchar(24)), CAST(CAST(cp.BillToIns AS decimal(16, 2)) AS varchar(24)), CAST(CAST(cp.AllowedFee AS decimal(16, 2)) AS varchar(24)), CAST(CAST(cp.cptBalance AS decimal(16, 2)) AS varchar(24)), CAST(CAST(cp.RendPrUserId AS int) AS varchar(24)), CAST(CAST(cp.OrdPrUserId AS int) AS varchar(24)), CAST(CAST(cp.RefPrUserId AS int) AS varchar(24)), CAST(CAST(cp.FacilityId AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(cp.POS AS varchar(10)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(iv.VoidFlag AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(cp.NDCCode AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(cp.NDCUnits AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(cp.ItemId AS int) AS varchar(24)), NULL, NULL, NULL FROM edi_inv_cpt cp JOIN edi_invoice iv ON iv.Id = cp.InvoiceId AND iv.DeleteFlag = 0 LEFT JOIN (SELECT d.InvoiceId AS InvId, CAST(d.icdOrder AS int) AS Ord, MIN(d.Code) AS Code FROM edi_inv_diagnosis d GROUP BY d.InvoiceId, CAST(d.icdOrder AS int)) x1 ON x1.InvId = cp.InvoiceId AND x1.Ord = TRY_CONVERT(int, cp.ICD1) LEFT JOIN (SELECT d.InvoiceId AS InvId, CAST(d.icdOrder AS int) AS Ord, MIN(d.Code) AS Code FROM edi_inv_diagnosis d GROUP BY d.InvoiceId, CAST(d.icdOrder AS int)) x2 ON x2.InvId = cp.InvoiceId AND x2.Ord = TRY_CONVERT(int, cp.ICD2) LEFT JOIN (SELECT d.InvoiceId AS InvId, CAST(d.icdOrder AS int) AS Ord, MIN(d.Code) AS Code FROM edi_inv_diagnosis d GROUP BY d.InvoiceId, CAST(d.icdOrder AS int)) x3 ON x3.InvId = cp.InvoiceId AND x3.Ord = TRY_CONVERT(int, cp.ICD3) LEFT JOIN (SELECT d.InvoiceId AS InvId, CAST(d.icdOrder AS int) AS Ord, MIN(d.Code) AS Code FROM edi_inv_diagnosis d GROUP BY d.InvoiceId, CAST(d.icdOrder AS int)) x4 ON x4.InvId = cp.InvoiceId AND x4.Ord = TRY_CONVERT(int, cp.ICD4) WHERE cp.deleteFlag = 0 AND (cp.SDOS >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND cp.SDOS < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) OR iv.InvoiceDt >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND iv.InvoiceDt < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END)) UNION ALL SELECT 'PAY', CAST(CAST(eb.Id AS int) AS varchar(24)), CAST(CAST(pd.invoiceId AS int) AS varchar(24)), CAST(CAST(eb.InvCptId AS int) AS varchar(24)), CAST(CAST(iv.PatientId AS int) AS varchar(24)), CAST(CAST(pd.paymentId AS int) AS varchar(24)), CAST(CAST(pd.PmtDetailId AS int) AS varchar(24)), CONVERT(varchar(10), ip.date, 120), CONVERT(varchar(10), cp.SDOS, 120), CONVERT(varchar(10), ip.PostedDt, 120), CONVERT(varchar(10), CASE WHEN ip.depositDate > '1902-01-01' THEN ip.depositDate END, 120), REPLACE(REPLACE(REPLACE(CAST(cp.Code AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(cp.Mod1 AS varchar(10)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(cp.Mod2 AS varchar(10)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(eb.Paid AS decimal(16, 2)) AS varchar(24)), CAST(CAST(eb.Adjustment AS decimal(16, 2)) AS varchar(24)), CAST(CAST(eb.WithHeld AS decimal(16, 2)) AS varchar(24)), CAST(CAST(eb.Allowed AS decimal(16, 2)) AS varchar(24)), CAST(CAST(eb.deduct AS decimal(16, 2)) AS varchar(24)), CAST(CAST(eb.coins AS decimal(16, 2)) AS varchar(24)), CAST(CAST(eb.MemResp AS decimal(16, 2)) AS varchar(24)), CAST(CAST(ip.PayorType AS int) AS varchar(24)), CAST(CAST(ip.payorId AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(CASE WHEN ip.PayorType = 2 THEN NULL ELSE pi.insuranceclass END AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(pd.ERAClaimStatusCLP02 AS varchar(10)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(CASE WHEN ip.eraDetailId > 0 THEN 1 ELSE 0 END AS int) AS varchar(24)), CAST(CAST(ip.PostedBy AS int) AS varchar(24)), CAST(CAST(iv.VoidFlag AS int) AS varchar(24)), CAST(CAST(iv.DeleteFlag AS int) AS varchar(24)), CAST(CAST(cp.deleteFlag AS int) AS varchar(24)), CAST(CAST(ip.deleteFlag AS int) AS varchar(24)), CAST(CAST(ip.VoidFlag AS int) AS varchar(24)), CAST(CAST(pd.seqno AS int) AS varchar(24)), NULL FROM edi_inv_eob eb JOIN edi_paymentdetail pd ON pd.PmtDetailId = eb.PaymentDetailId JOIN edi_inspayments ip ON ip.paymentId = pd.paymentId LEFT JOIN edi_inv_cpt cp ON cp.Id = eb.InvCptId LEFT JOIN edi_invoice iv ON iv.Id = pd.invoiceId LEFT JOIN insurance pi ON ip.payorId <> 0 AND pi.insId = ip.payorId WHERE ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) UNION ALL SELECT 'PDR', CAST(CAST(pd.invoiceId AS int) AS varchar(24)), CAST(CAST(iv.PatientId AS int) AS varchar(24)), CAST(CAST(pd.paymentId AS int) AS varchar(24)), CAST(CAST(pd.PmtDetailId AS int) AS varchar(24)), CONVERT(varchar(10), ip.date, 120), CONVERT(varchar(10), ip.PostedDt, 120), CAST(CAST(ISNULL(pd.paid, 0) - ISNULL(eo.P, 0) AS decimal(16, 2)) AS varchar(24)), CAST(CAST(ISNULL(pd.adjustment, 0) - ISNULL(eo.A, 0) AS decimal(16, 2)) AS varchar(24)), CAST(CAST(ISNULL(pd.withheld, 0) - ISNULL(eo.W, 0) AS decimal(16, 2)) AS varchar(24)), CAST(CAST(ip.PayorType AS int) AS varchar(24)), CAST(CAST(ip.payorId AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(CASE WHEN ip.PayorType = 2 THEN NULL ELSE pi.insuranceclass END AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(pd.ERAClaimStatusCLP02 AS varchar(10)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(CASE WHEN ip.eraDetailId > 0 THEN 1 ELSE 0 END AS int) AS varchar(24)), CAST(CAST(ip.PostedBy AS int) AS varchar(24)), CAST(CAST(iv.VoidFlag AS int) AS varchar(24)), CAST(CAST(iv.DeleteFlag AS int) AS varchar(24)), CAST(CAST(ip.deleteFlag AS int) AS varchar(24)), CAST(CAST(ip.VoidFlag AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM edi_paymentdetail pd JOIN edi_inspayments ip ON ip.paymentId = pd.paymentId LEFT JOIN edi_invoice iv ON iv.Id = pd.invoiceId LEFT JOIN insurance pi ON ip.payorId <> 0 AND pi.insId = ip.payorId LEFT JOIN ( SELECT eb.PaymentDetailId AS PdId, SUM(eb.Paid) AS P, SUM(eb.Adjustment) AS A, SUM(eb.WithHeld) AS W, COUNT(*) AS N FROM edi_inv_eob eb JOIN edi_paymentdetail pd2 ON pd2.PmtDetailId = eb.PaymentDetailId JOIN edi_inspayments ip2 ON ip2.paymentId = pd2.paymentId WHERE ip2.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip2.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) GROUP BY eb.PaymentDetailId ) eo ON eo.PdId = pd.PmtDetailId WHERE ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) AND (ISNULL(pd.paid, 0) <> ISNULL(eo.P, 0) OR ISNULL(pd.adjustment, 0) <> ISNULL(eo.A, 0) OR ISNULL(pd.withheld, 0) <> ISNULL(eo.W, 0)) UNION ALL SELECT 'WOF', CAST(CAST(adj.InvId AS int) AS varchar(24)), CAST(CAST(iv.PatientId AS int) AS varchar(24)), CAST(CAST(adj.Id AS int) AS varchar(24)), CONVERT(varchar(10), adj.date, 120), REPLACE(REPLACE(REPLACE(CAST(adj.code AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(adj.amount AS decimal(16, 2)) AS varchar(24)), CAST(CAST(adj.insuranceId AS int) AS varchar(24)), CAST(CAST(adj.UserId AS int) AS varchar(24)), CAST(CAST(iv.VoidFlag AS int) AS varchar(24)), CAST(CAST(iv.DeleteFlag AS int) AS varchar(24)), CAST(CAST(adj.insuranceSeq AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM edi_inv_adjustments adj LEFT JOIN edi_invoice iv ON iv.Id = adj.InvId WHERE adj.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND adj.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) UNION ALL SELECT 'WOL', CAST(CAST(ap.Id AS int) AS varchar(24)), CAST(CAST(ap.InvId AS int) AS varchar(24)), CAST(CAST(ap.LineItemId AS int) AS varchar(24)), CAST(CAST(ap.AdjustmentId AS int) AS varchar(24)), CONVERT(varchar(10), adj.date, 120), REPLACE(REPLACE(REPLACE(CAST(adj.code AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(ap.amount AS decimal(16, 2)) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM adjustmentposting ap JOIN edi_inv_adjustments adj ON adj.Id = ap.AdjustmentId WHERE ap.DeleteFlag = 0 AND adj.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND adj.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) UNION ALL SELECT 'REF', CAST(CAST(rf.RefundId AS int) AS varchar(24)), CAST(CAST(rf.VoidRefundRefId AS int) AS varchar(24)), CONVERT(varchar(10), rf.date, 120), CONVERT(varchar(10), rf.PostedDt, 120), REPLACE(REPLACE(REPLACE(CAST(rf.refundType AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(rf.type AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(rf.amount AS decimal(16, 2)) AS varchar(24)), CAST(CAST(rf.unpostedrefunds AS decimal(16, 2)) AS varchar(24)), CAST(CAST(rf.ReceiverType AS int) AS varchar(24)), CAST(CAST(rf.ReceiverId AS int) AS varchar(24)), CAST(CAST(rf.PostedBy AS int) AS varchar(24)), CAST(CAST(rf.facilityId AS int) AS varchar(24)), CAST(CAST(CASE WHEN rpx.Claims = 1 THEN rpx.ClaimId END AS int) AS varchar(24)), CAST(CAST(CASE WHEN rpx.Lines = 1 THEN rpx.LineId END AS int) AS varchar(24)), CAST(CAST(rpx.Posts AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM refunds rf LEFT JOIN ( SELECT TRY_CONVERT(int, rp.RefundID) AS RefundId, MIN(rp.InvId) AS ClaimId, COUNT(DISTINCT rp.InvId) AS Claims, MIN(rp.LineItemId) AS LineId, COUNT(DISTINCT rp.LineItemId) AS Lines, COUNT(*) AS Posts FROM refundposting rp WHERE rp.DeleteFlag = 0 GROUP BY TRY_CONVERT(int, rp.RefundID) ) rpx ON rpx.RefundId = rf.RefundId WHERE rf.deleteFlag = 0 AND rf.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND rf.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) UNION ALL SELECT 'DEN', CAST(CAST(c.InvEobCASId AS int) AS varchar(24)), CAST(CAST(eb.Id AS int) AS varchar(24)), CONVERT(varchar(10), ip.date, 120), REPLACE(REPLACE(REPLACE(CAST(c.ReasonCode AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(c.GroupCode AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(c.amount AS decimal(16, 2)) AS varchar(24)), CAST(CAST(ip.deleteFlag AS int) AS varchar(24)), CAST(CAST(c.PostedAs AS int) AS varchar(24)), CAST(CAST(c.IsERAPostedCASCode AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM edi_inveob_cas c JOIN edi_inv_eob eb ON eb.Id = c.InvEobId JOIN edi_paymentdetail pd ON pd.PmtDetailId = eb.PaymentDetailId JOIN edi_inspayments ip ON ip.paymentId = pd.paymentId WHERE c.deleteFlag = 0 AND ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) UNION ALL SELECT 'RMK', CAST(CAST(r.Id AS int) AS varchar(24)), CAST(CAST(eb.Id AS int) AS varchar(24)), CONVERT(varchar(10), ip.date, 120), REPLACE(REPLACE(REPLACE(CAST(r.RemarkCode AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(ip.deleteFlag AS int) AS varchar(24)), CAST(CAST(0 AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM edi_claimorlinelevel_eraremarkcodes r JOIN edi_inv_eob eb ON eb.Id = r.DetailId JOIN edi_paymentdetail pd ON pd.PmtDetailId = eb.PaymentDetailId JOIN edi_inspayments ip ON ip.paymentId = pd.paymentId WHERE r.deleteFlag = 0 AND ISNULL(CAST(r.ClaimLevel AS int), 0) = 0 AND ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) UNION ALL SELECT 'RMK', CAST(CAST(r.Id AS int) AS varchar(24)), CAST(CAST(pd.PmtDetailId AS int) AS varchar(24)), CONVERT(varchar(10), ip.date, 120), REPLACE(REPLACE(REPLACE(CAST(r.RemarkCode AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(ip.deleteFlag AS int) AS varchar(24)), CAST(CAST(1 AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM edi_claimorlinelevel_eraremarkcodes r JOIN edi_paymentdetail pd ON pd.PmtDetailId = r.DetailId JOIN edi_inspayments ip ON ip.paymentId = pd.paymentId WHERE r.deleteFlag = 0 AND ISNULL(CAST(r.ClaimLevel AS int), 0) = 1 AND ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) UNION ALL SELECT 'DIC', REPLACE(REPLACE(REPLACE(CAST('payer' AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(i.insId AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(i.InsurancePrimaryType AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(i.insId AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(i.insuranceclass AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(i.Inactive AS int) AS varchar(24)), CAST(CAST(i.deleteFlag AS int) AS varchar(24)), CAST(CAST(i.PrimaryTimelyFiling AS decimal(16, 2)) AS varchar(24)), CAST(CAST(i.SecondaryTimelyFiling AS decimal(16, 2)) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(i.insuranceName AS varchar(200)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM insurance i UNION ALL SELECT 'DIC', REPLACE(REPLACE(REPLACE(CAST('claim_status' AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(s.Id AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(s.code AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(s.ShortDesc AS varchar(200)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(s.deleteFlag AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM claimstatuscodes s UNION ALL SELECT 'DIC', REPLACE(REPLACE(REPLACE(CAST('processed_as' AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(s.Id AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(s.Code AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(s.ShortDesc AS varchar(200)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(s.deleteflag AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM era_claimstatuscode s UNION ALL SELECT 'DIC', REPLACE(REPLACE(REPLACE(CAST('adjustment_code' AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(a.Id AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(a.Code AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(a.Name AS varchar(200)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(a.Inactive AS int) AS varchar(24)), CAST(CAST(a.insuranceAdjustment AS int) AS varchar(24)), CAST(CAST(a.system_define AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM edi_adj_codes a UNION ALL SELECT 'DIC', REPLACE(REPLACE(REPLACE(CAST('facility' AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(f.Id AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(f.code AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(f.Id AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(f.POS AS varchar(10)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(f.Name AS varchar(200)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(f.DeleteFlag AS int) AS varchar(24)), CAST(CAST(f.isInactive AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM edi_facilities f UNION ALL SELECT 'DIC', REPLACE(REPLACE(REPLACE(CAST('hcpcs_item' AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(it.itemID AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(it.itemName AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(it.itemID AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(SUBSTRING(it.itemDesc, 1, 200) AS varchar(200)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM items it WHERE it.itemID IN ( SELECT cp.ItemId FROM edi_inv_cpt cp WHERE cp.deleteFlag = 0 AND cp.Code LIKE '[A-Z]%' AND cp.SDOS >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND cp.SDOS < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) ) UNION ALL SELECT 'DIC', REPLACE(REPLACE(REPLACE(CAST('user' AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(u.uid AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(dr.NPI AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(CASE WHEN dr.doctorID IS NOT NULL OR u.UserType IN (1, 2, 9) THEN CONCAT(u.ulname, ', ', u.ufname) END AS varchar(200)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(u.UserType AS int) AS varchar(24)), CAST(CAST(u.IsResource AS int) AS varchar(24)), CAST(CAST(u.status AS int) AS varchar(24)), CAST(CAST(u.delFlag AS decimal(16, 2)) AS varchar(24)), CAST(CAST(dr.inactive AS decimal(16, 2)) AS varchar(24)), CAST(CAST(u.uid AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(dr.ProviderType AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(dr.speciality AS varchar(60)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(u.uminitial AS varchar(10)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM users u LEFT JOIN doctors dr ON dr.doctorID = u.uid WHERE NOT EXISTS (SELECT 1 FROM patients p WHERE p.pid = u.uid) UNION ALL SELECT 'DIC', REPLACE(REPLACE(REPLACE(CAST('visit_type' AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(v.CodeId AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(v.Name AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(v.CodeId AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(v.Description AS varchar(200)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(v.ReqClaim AS int) AS varchar(24)), CAST(CAST(v.Active AS int) AS varchar(24)), CAST(CAST(v.telemedVisit AS int) AS varchar(24)), CAST(CAST(v.visitType AS decimal(16, 2)) AS varchar(24)), CAST(CAST(v.webVisit AS decimal(16, 2)) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM visitcodes v UNION ALL SELECT 'DIC', REPLACE(REPLACE(REPLACE(CAST('visit_status' AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(s.codeId AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(s.code AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(s.status AS varchar(200)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(s.NonBillable AS int) AS varchar(24)), CAST(CAST(s.active AS int) AS varchar(24)), CAST(CAST(s.codeId AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM visitstscodes s UNION ALL SELECT 'DIC', REPLACE(REPLACE(REPLACE(CAST('work_set' AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(whs.SetId AS int) AS varchar(24)), CAST(CAST(whs.UserId AS int) AS varchar(24)), REPLACE(REPLACE(REPLACE(CAST(whs.description AS varchar(200)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(whs.displayIndex AS int) AS varchar(24)), CAST(CAST(whs.RecurrenceId AS int) AS varchar(24)), CAST(CAST(whs.deleteFlag AS int) AS varchar(24)), CAST(CAST(whs.templateid AS decimal(16, 2)) AS varchar(24)), CAST(CAST(whs.SetId AS int) AS varchar(24)), CONVERT(varchar(19), whs.StartDate, 120), CONVERT(varchar(19), whs.EndDate, 120), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM workinghourssets whs UNION ALL SELECT 'DIC', REPLACE(REPLACE(REPLACE(CAST('work_hours' AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(wh.Id AS int) AS varchar(24)), CAST(CAST(wh.facilityId AS int) AS varchar(24)), CAST(CAST(wh.Weekday AS int) AS varchar(24)), CAST(CAST(wh.frequency AS int) AS varchar(24)), CAST(CAST(wh.SetId AS int) AS varchar(24)), CONVERT(varchar(19), wh.StartTime, 120), CONVERT(varchar(19), wh.EndTime, 120), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM workhours wh UNION ALL SELECT 'DIC', REPLACE(REPLACE(REPLACE(CAST('weekday' AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(d.Id AS int) AS varchar(24)), CONVERT(varchar(10), d.eBODate, 120), REPLACE(REPLACE(REPLACE(CAST(d.eBOweekDay AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(d.eBOWeek AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ebocaldate d WHERE d.eBODate >= '2026-09-06' AND d.eBODate < '2026-09-13' UNION ALL SELECT 'DNP', CAST(CAST(c.Id AS int) AS varchar(24)), CAST(CAST(pd.invoiceId AS int) AS varchar(24)), CAST(CAST(eb.InvCptId AS int) AS varchar(24)), CAST(CAST(pd.paymentId AS int) AS varchar(24)), CAST(CAST(c.DetailId AS int) AS varchar(24)), CONVERT(varchar(10), ip.date, 120), REPLACE(REPLACE(REPLACE(CAST(c.ReasonCode AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(c.GroupCode AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(c.Amount AS decimal(16, 2)) AS varchar(24)), CAST(CAST(c.PostedAs AS int) AS varchar(24)), CAST(CAST(ip.deleteFlag AS int) AS varchar(24)), CAST(CAST(0 AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM edi_claimorlinelevel_cascodes c JOIN edi_inv_eob eb ON eb.Id = c.DetailId JOIN edi_paymentdetail pd ON pd.PmtDetailId = eb.PaymentDetailId JOIN edi_inspayments ip ON ip.paymentId = pd.paymentId WHERE c.deleteFlag = 0 AND ISNULL(CAST(c.ClaimLevelCasCode AS int), 0) = 0 AND ISNULL(CAST(c.PostedAs AS int), 0) = 9 AND ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) UNION ALL SELECT 'DNP', CAST(CAST(c.Id AS int) AS varchar(24)), CAST(CAST(pd.invoiceId AS int) AS varchar(24)), NULL, CAST(CAST(pd.paymentId AS int) AS varchar(24)), CAST(CAST(c.DetailId AS int) AS varchar(24)), CONVERT(varchar(10), ip.date, 120), REPLACE(REPLACE(REPLACE(CAST(c.ReasonCode AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(c.GroupCode AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(c.Amount AS decimal(16, 2)) AS varchar(24)), CAST(CAST(c.PostedAs AS int) AS varchar(24)), CAST(CAST(ip.deleteFlag AS int) AS varchar(24)), CAST(CAST(1 AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM edi_claimorlinelevel_cascodes c JOIN edi_paymentdetail pd ON pd.PmtDetailId = c.DetailId JOIN edi_inspayments ip ON ip.paymentId = pd.paymentId WHERE c.deleteFlag = 0 AND ISNULL(CAST(c.ClaimLevelCasCode AS int), 0) = 1 AND ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) UNION ALL SELECT 'DPC', CAST(CAST(pc.PmtDtlCASId AS int) AS varchar(24)), CAST(CAST(pd.invoiceId AS int) AS varchar(24)), CAST(CAST(pd.paymentId AS int) AS varchar(24)), CAST(CAST(pc.PaymentDetailId AS int) AS varchar(24)), CONVERT(varchar(10), ip.date, 120), REPLACE(REPLACE(REPLACE(CAST(pc.ReasonCode AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), REPLACE(REPLACE(REPLACE(CAST(pc.GroupCode AS varchar(20)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CAST(CAST(pc.amount AS decimal(16, 2)) AS varchar(24)), CAST(CAST(pc.PostedAs AS int) AS varchar(24)), CAST(CAST(ip.deleteFlag AS int) AS varchar(24)), CAST(CAST(pc.IsERAPostedCASCode AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM edi_paymentdetail_cas pc JOIN edi_paymentdetail pd ON pd.PmtDetailId = pc.PaymentDetailId JOIN edi_inspayments ip ON ip.paymentId = pd.paymentId WHERE pc.deleteFlag = 0 AND ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('rows_DNP' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), CAST((CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AS date), 120), CAST(CAST(x.N AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT (SELECT COUNT(*) FROM edi_claimorlinelevel_cascodes c JOIN edi_inv_eob eb ON eb.Id = c.DetailId JOIN edi_paymentdetail pd ON pd.PmtDetailId = eb.PaymentDetailId JOIN edi_inspayments ip ON ip.paymentId = pd.paymentId WHERE c.deleteFlag = 0 AND ISNULL(CAST(c.ClaimLevelCasCode AS int), 0) = 0 AND ISNULL(CAST(c.PostedAs AS int), 0) = 9 AND ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END)) + (SELECT COUNT(*) FROM edi_claimorlinelevel_cascodes c JOIN edi_paymentdetail pd ON pd.PmtDetailId = c.DetailId JOIN edi_inspayments ip ON ip.paymentId = pd.paymentId WHERE c.deleteFlag = 0 AND ISNULL(CAST(c.ClaimLevelCasCode AS int), 0) = 1 AND ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END)) AS N ) x UNION ALL SELECT 'CTL', REPLACE(REPLACE(REPLACE(CAST('rows_DPC' AS varchar(40)), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), CONVERT(varchar(10), CAST((CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AS date), 120), CAST(CAST(x.N AS int) AS varchar(24)), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT COUNT(*) AS N FROM edi_paymentdetail_cas pc JOIN edi_paymentdetail pd ON pd.PmtDetailId = pc.PaymentDetailId JOIN edi_inspayments ip ON ip.paymentId = pd.paymentId WHERE pc.deleteFlag = 0 AND ip.date >= (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 24, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP3' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN CAST(#sq(prompt('StartDate','date','''2026-09-01'''))# AS datetime) ELSE DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) END) AND ip.date < (CASE #sq(prompt('DateType','token','''MONTHLY'''))# WHEN 'SETUP1' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 16, 0) WHEN 'SETUP2' THEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0) WHEN 'CUSTOM' THEN DATEADD(day, 1, CAST(#sq(prompt('EndDate','date','''2026-09-30'''))# AS datetime)) ELSE DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) END) ) x ) u [SQL1].[RowType][SQL1].[C01][SQL1].[C02][SQL1].[C03][SQL1].[C04][SQL1].[C05][SQL1].[C06][SQL1].[C07][SQL1].[C08][SQL1].[C09][SQL1].[C10][SQL1].[C11][SQL1].[C12][SQL1].[C13][SQL1].[C14][SQL1].[C15][SQL1].[C16][SQL1].[C17][SQL1].[C18][SQL1].[C19][SQL1].[C20][SQL1].[C21][SQL1].[C22][SQL1].[C23][SQL1].[C24][SQL1].[C25][SQL1].[C26][SQL1].[C27][SQL1].[C28][SQL1].[C29][SQL1].[C30][SQL1].[C31][SQL1].[C32][SQL1].[C33] KPI Money ParamValue('DateType') = 'CUSTOM'