Combining all these queries together (from Report) into One

[nmk1]

I would like to combine all these queries together (from Report) into One

Code: Select all

SELECT procedurelog.ProcDate Date, CONCAT(CONCAT(CONCAT(CONCAT(patient.LName, ', '),patient.FName),' '),patient.MiddleI) namelf, procedurecode.Descript Description, provider.Abbr, procedurelog.ClinicNum Clinic, procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)-IFNULL(SUM(claimproc.WriteOff),0) Production, procedurelog.ProcNum, provider.ProvNum FROM patient INNER JOIN procedurelog ON patient.PatNum=procedurelog.PatNum AND procedurelog.ProcStatus='2' AND procedurelog.ProcDate >= '2021-07-28' AND procedurelog.ProcDate <= '2021-07-28'  LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum AND claimproc.Status='7' INNER JOIN provider ON procedurelog.ProvNum=provider.ProvNum INNER JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum GROUP BY procedurelog.ProcNum ORDER BY Date,namelf

Code: Select all

SELECT adjustment.AdjDate Date, CONCAT(CONCAT(CONCAT(CONCAT(patient.LName, ', '),patient.FName),' '),patient.MiddleI) namelf, definition.ItemName Description, provider.Abbr, adjustment.ClinicNum Clinic, adjustment.AdjAmt AdjAmt, adjustment.AdjNum, provider.ProvNum FROM adjustment INNER JOIN patient ON adjustment.PatNum=patient.PatNum INNER JOIN definition ON adjustment.AdjType=definition.DefNum INNER JOIN provider ON adjustment.ProvNum=provider.ProvNum WHERE adjustment.AdjDate >= '2021-07-28' AND adjustment.AdjDate <= '2021-07-28'  ORDER BY Date,namelf

Code: Select all

SELECT claimproc.ProcDate Date,CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI) namelf,CONCAT(CONCAT(procedurecode.AbbrDesc,' '),carrier.CarrierName) Description,provider.Abbr,claimproc.ClinicNum Clinic,-SUM(claimproc.WriteOff) WriteOff,claimproc.ClaimNum, claimproc.ClaimProcNum, provider.ProvNum FROM claimproc LEFT JOIN patient ON claimproc.PatNum = patient.PatNum LEFT JOIN provider ON provider.ProvNum = claimproc.ProvNum LEFT JOIN insplan ON insplan.PlanNum = claimproc.PlanNum LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum LEFT JOIN procedurelog ON procedurelog.ProcNum=claimproc.ProcNum LEFT JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum WHERE claimproc.Status IN (1,4,0) AND (claimproc.WriteOff > '.0001' OR claimproc.WriteOff < -.0001) AND claimproc.ProcDate >= '2021-07-28' AND claimproc.ProcDate <= '2021-07-28' GROUP BY claimproc.ClaimProcNum ORDER BY Date,namelf

Code: Select all

SELECT paysplit.DatePay Date, CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI) namelf, definition.ItemName Description, IFNULL(provider.Abbr,'Unearned') Abbr, paysplit.ClinicNum Clinic, SUM(IF(paysplit.UnearnedType = 0,paysplit.SplitAmt,0)) PayAmt, SUM(IF(paysplit.UnearnedType != 0,paysplit.SplitAmt,0)) UnearnedIncome, payment.PayNum, provider.ProvNum FROM paysplit LEFT JOIN payment ON payment.PayNum=paysplit.PayNum LEFT JOIN patient ON patient.PatNum=paysplit.PatNum LEFT JOIN provider ON provider.ProvNum=paysplit.ProvNum LEFT JOIN definition ON payment.PayType=definition.DefNum WHERE payment.PayDate >= '2021-07-28' AND payment.PayDate <= '2021-07-28' AND paysplit.UnearnedType NOT IN (401) GROUP BY paysplit.PatNum,paysplit.ProvNum,paysplit.ClinicNum,PayType,paysplit.DatePay ORDER BY Date,namelf

Code: Select all

SELECT claimpayment.CheckDate Date, CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI) namelf, carrier.CarrierName Description, provider.Abbr, claimproc.ClinicNum Clinic, SUM(claimproc.InsPayAmt) InsPayAmt, claimproc.ClaimNum, provider.ProvNum FROM claimproc INNER JOIN patient ON claimproc.PatNum=patient.PatNum INNER JOIN insplan ON claimproc.PlanNum=insplan.PlanNum INNER JOIN carrier ON insplan.CarrierNum=carrier.CarrierNum INNER JOIN provider ON claimproc.ProvNum=provider.ProvNum  INNER JOIN claimpayment ON claimproc.ClaimPaymentNum=claimpayment.ClaimPaymentNum WHERE (claimproc.Status=1 OR claimproc.Status=4) AND claimpayment.CheckDate >= '2021-07-28' AND claimpayment.CheckDate <= '2021-07-28'  GROUP BY claimproc.PatNum,claimproc.ProvNum,claimproc.PlanNum,claimproc.ClinicNum,claimpayment.CheckDate ORDER BY Date,namelf

This was generated by the daily production reports:


I would like to generate it in a query where all these can be combined into one so I can calculate "Total Production (Production + Adjustments - Writeoffs)" and "Total Income (Pt Income + Ins Income)"

How would I combine all these queries?

[jordansparks]

Put the word "union" between all the queries to chain them together. I'm assuming they all return the same column names. If that doesn't work, then also surround each of the queries with parentheses. At the end of it all, you then do an order by on all the unioned result list.

[nmk1]

Hello

Unioning them didnt work because the queries seem to be very different.

I found Query #793 from the QueryList and it seems to work except the Writeoff amount is different from what is generated in the report. For reference, this is Query 793: https://pastebin.com/J5YhjjcL

The production, adjustments, Pt Income, Ins Income, & Total Income are all correct. The Writeoff is wrong though and thus, the Total Production is wrong.

How i generate my reports in OpenDental is Reports -> Standard -> More Options in "Production and Income" section -> Select a Date Range for one Day -> Ensure "All" is checked in Providers -> Ensure "Using procedure date" is checked in "Show Insurance Writeoffs" is selected -> "Ok"

This is my options in the Report: https://i.imgur.com/WCzDlo7.png

How come my writeoffs are wrong when I sum them up in the SQL query?

[joes]

The queries can be unioned if they all have the same number of columns. I modified each query to have the same columns with a format similar to query #793. I also added variables for setting the date range at the top.

Code: Select all

#Set Date range here
SET @FromDate='2021-07-28', @ToDate='2021-07-28';

#Procedure fees
SELECT
  procedurelog.ProcDate DATE,
  CONCAT(
    CONCAT(
      CONCAT(
        CONCAT(patient.LName, ', '),
        patient.FName
      ),
      ' '
    ),
    patient.MiddleI
  ) namelf,
  procedurecode.Descript Description,
  provider.Abbr,
  procedurelog.ClinicNum Clinic,
  procedurelog.ProcFee * (
    procedurelog.UnitQty + procedurelog.BaseUnits
  ) - IFNULL(SUM(claimproc.WriteOff), 0) AS '$Production_',
  0 AS '$Adjust_',
  0 AS '$Writeoff_',
  procedurelog.ProcFee * (
    procedurelog.UnitQty + procedurelog.BaseUnits
  ) - IFNULL(SUM(claimproc.WriteOff), 0) AS '$Total Prod_',
  0 AS '$Pt Income_',
  0 AS '$Unearned Income_',
  0 AS '$Ins Income_',
  0 AS '$Total Income_'
FROM
  patient
  INNER JOIN procedurelog
    ON patient.PatNum = procedurelog.PatNum
    AND procedurelog.ProcStatus = '2'
    AND procedurelog.ProcDate >= @FromDate
    AND procedurelog.ProcDate <= @ToDate
  LEFT JOIN claimproc
    ON procedurelog.ProcNum = claimproc.ProcNum
    AND claimproc.Status = '7'
  INNER JOIN provider
    ON procedurelog.ProvNum = provider.ProvNum
  INNER JOIN procedurecode
    ON procedurelog.CodeNum = procedurecode.CodeNum
GROUP BY procedurelog.ProcNum
  
UNION

#adjustments
SELECT
  adjustment.AdjDate DATE,
  CONCAT(
    CONCAT(
      CONCAT(
        CONCAT(patient.LName, ', '),
        patient.FName
      ),
      ' '
    ),
    patient.MiddleI
  ) namelf,
  definition.ItemName Description,
  provider.Abbr,
  adjustment.ClinicNum Clinic,
  0 AS '$Production_',
  adjustment.AdjAmt AS '$Adjust_',
  0 AS '$Writeoff_',
  adjustment.AdjAmt AS '$Total Prod_',
  0 AS '$Pt Income_',
  0 AS '$Unearned Income_',
  0 AS '$Ins Income_',
  0 AS '$Total Income_'
FROM
  adjustment
  INNER JOIN patient
    ON adjustment.PatNum = patient.PatNum
  INNER JOIN definition
    ON adjustment.AdjType = definition.DefNum
  INNER JOIN provider
    ON adjustment.ProvNum = provider.ProvNum
WHERE adjustment.AdjDate >= @FromDate
  AND adjustment.AdjDate <= @ToDate
  
UNION

#Writeoffs
SELECT
  claimproc.ProcDate DATE,
  CONCAT(
    CONCAT(
      CONCAT(
        CONCAT(patient.LName, ', '),
        patient.FName
      ),
      ' '
    ),
    patient.MiddleI
  ) namelf,
  CONCAT(
    CONCAT(procedurecode.AbbrDesc, ' '),
    carrier.CarrierName
  ) Description,
  provider.Abbr,
  claimproc.ClinicNum Clinic,
  0 AS '$Production_',
  0 AS '$Adjust_',
  - SUM(claimproc.WriteOff) AS '$Writeoff_',
  - SUM(claimproc.WriteOff) AS '$Total Prod_',
  0 AS '$Pt Income_',
  0 AS '$Unearned Income_',
  0 AS '$Ins Income_',
  0 AS '$Total Income_'
FROM
  claimproc
  LEFT JOIN patient
    ON claimproc.PatNum = patient.PatNum
  LEFT JOIN provider
    ON provider.ProvNum = claimproc.ProvNum
  LEFT JOIN insplan
    ON insplan.PlanNum = claimproc.PlanNum
  LEFT JOIN carrier
    ON carrier.CarrierNum = insplan.CarrierNum
  LEFT JOIN procedurelog
    ON procedurelog.ProcNum = claimproc.ProcNum
  LEFT JOIN procedurecode
    ON procedurelog.CodeNum = procedurecode.CodeNum
WHERE claimproc.Status IN (1, 4, 0)
  AND (
    claimproc.WriteOff > '.0001'
    OR claimproc.WriteOff < - .0001
  )
  AND claimproc.ProcDate >= @FromDate
  AND claimproc.ProcDate <= @ToDate
GROUP BY claimproc.ClaimProcNum

UNION

#patient payments
SELECT
  paysplit.DatePay DATE,
  CONCAT(
    CONCAT(
      CONCAT(
        CONCAT(patient.LName, ', '),
        patient.FName
      ),
      ' '
    ),
    patient.MiddleI
  ) namelf,
  definition.ItemName Description,
  IFNULL(provider.Abbr, 'Unearned') Abbr,
  paysplit.ClinicNum Clinic,
  0 AS '$Production_',
  0 AS '$Adjust_',
  0 AS '$Writeoff_',
  0 AS '$Total Prod_',
  SUM(
    IF(
      paysplit.UnearnedType = 0,
      paysplit.SplitAmt,
      0
    )
  ) '$Pt Income_',
  SUM(
    IF(
      paysplit.UnearnedType != 0,
      paysplit.SplitAmt,
      0
    )
  ) AS '$Unearned Income_',
  0 AS '$Ins Income_',
  SUM(paysplit.SplitAmt) AS '$Total Income_'
FROM
  paysplit
  LEFT JOIN payment
    ON payment.PayNum = paysplit.PayNum
  LEFT JOIN patient
    ON patient.PatNum = paysplit.PatNum
  LEFT JOIN provider
    ON provider.ProvNum = paysplit.ProvNum
  LEFT JOIN definition
    ON payment.PayType = definition.DefNum
WHERE payment.PayDate >= @FromDate
  AND payment.PayDate <= @ToDate
  AND paysplit.UnearnedType NOT IN (401)
GROUP BY paysplit.PatNum,
  paysplit.ProvNum,
  paysplit.ClinicNum,
  PayType,
  paysplit.DatePay
  
UNION

#claim payments
SELECT
  claimpayment.CheckDate DATE,
  CONCAT(
    CONCAT(
      CONCAT(
        CONCAT(patient.LName, ', '),
        patient.FName
      ),
      ' '
    ),
    patient.MiddleI
  ) namelf,
  carrier.CarrierName Description,
  provider.Abbr,
  claimproc.ClinicNum Clinic,
  0 AS '$Production_',
  0 AS '$Adjust_',
  0 AS '$Writeoff_',
  0 AS '$Total Prod_',
  0 AS '$Pt Income_',
  0 AS '$Unearned Income_',
  SUM(claimproc.InsPayAmt) AS '$Ins Income_',
  SUM(claimproc.InsPayAmt) AS '$Total Income_'
FROM
  claimproc
  INNER JOIN patient
    ON claimproc.PatNum = patient.PatNum
  INNER JOIN insplan
    ON claimproc.PlanNum = insplan.PlanNum
  INNER JOIN carrier
    ON insplan.CarrierNum = carrier.CarrierNum
  INNER JOIN provider
    ON claimproc.ProvNum = provider.ProvNum
  INNER JOIN claimpayment
    ON claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum
WHERE (
    claimproc.Status = 1
    OR claimproc.Status = 4
  )
  AND claimpayment.CheckDate >= @FromDate
  AND claimpayment.CheckDate <= @ToDate
GROUP BY claimproc.PatNum,
  claimproc.ProvNum,
  claimproc.PlanNum,
  claimproc.ClinicNum,
  claimpayment.CheckDate
ORDER BY DATE,
  namelf;

[joes]

To answer your other question, the writeoffs were different for query #793 because that query is similar to a production and income report that uses the "Using insurance payment date" option.