kevinrossen

Net Production by Op

Sep 16th, 2020
823
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Net Production by Operatory. Query provided by Divergent Dental Resources, LLC. Email kevin@divergentdental.com for info.
  2. SET
  3.     @FromDate = CURDATE() - INTERVAL 90 DAY,
  4.     @ToDate = CURDATE()
  5. ;  
  6. SELECT
  7.     Op,
  8.     SUM(CASE
  9.         WHEN TransType LIKE 'Prod' THEN TransAmt
  10.         WHEN TransType LIKE 'WO' THEN TransAmt
  11.         ELSE 0 END) AS NetProd
  12. FROM
  13.     (
  14.     -- Net Production
  15.     SELECT
  16.         'Prod' AS TransType,
  17.         DATE(pl.ProcDate) AS TransDate,
  18.         o.Abbrev AS Op,
  19.         pl.ProcFee AS TransAmt
  20.     FROM procedurelog pl
  21.     LEFT JOIN appointment a ON pl.AptNum = a.AptNum
  22.     LEFT JOIN operatory o ON a.Op = o.OperatoryNum
  23.     WHERE
  24.         pl.ProcStatus = 2
  25.         AND pl.ProcDate BETWEEN @FromDate AND @ToDate
  26.  
  27.     UNION ALL  
  28.  
  29.     -- WriteOffs
  30.     SELECT
  31.         'WO' AS TransType,
  32.         DATE(pl.ProcDate) AS TransDate,
  33.         o.Abbrev AS 'Op',
  34.         -cp.WriteOff AS '$WriteOff'
  35.     FROM claimproc cp
  36.     LEFT JOIN procedurelog pl ON cp.ProcNum=pl.ProcNum
  37.     LEFT JOIN appointment a ON pl.AptNum=a.AptNum
  38.     LEFT JOIN operatory o ON a.Op=o.OperatoryNum
  39.     WHERE
  40.         cp.ProcDate BETWEEN @FromDate AND @ToDate
  41.         AND cp.Status IN (0,1,4)
  42.     ) tmp1
  43. GROUP BY Op;
RAW Paste Data