Query help add 'AbbrDesc' abbreviated description

For users or potential users.
Post Reply
Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Query help add 'AbbrDesc' abbreviated description

Post by Tom Zaccaria » Sun Sep 25, 2016 4:15 am

Looking for some help. Need to add field 'AbbrDesc' to the following query. Can't seem to get it to click.

/*# 1022 Query code written/modified: 10/30/2014*/
SET @FromDate='2014-01-10', @ToDate='2014-01-10';
SELECT A.Provider,A.ProcCode,
COUNT(A.ProcCode) AS 'Quantity',
SUM(A.ProcFee) AS $ProcFee_,
SUM(A.WriteOffs) AS $WriteOffs_,
SUM(A.Adjustments) AS $Adjustments_,
SUM(A.NetProd) AS $NetProd_
FROM (
SELECT provider.Abbr AS 'Provider',
pc.ProcCode,
pl.ProcFee*(pl.UnitQty+pl.BaseUnits)-IFNULL(COALESCE(cp7.WriteOffs,0),0) AS 'ProcFee',
COALESCE(cp.WriteOffs,0) AS 'WriteOffs',
COALESCE(SUM(adj.AdjAmt),0) AS 'Adjustments',
pl.ProcFee*(pl.UnitQty+pl.BaseUnits)-IFNULL(COALESCE(cp7.WriteOffs,0),0)
-COALESCE(cp.WriteOffs,0)+COALESCE(SUM(adj.AdjAmt),0) AS 'NetProd',
provider.Abbr
FROM procedurelog pl
LEFT JOIN (
SELECT SUM(cp.WriteOff) AS WriteOffs,pl.ProcNum
FROM procedurelog pl
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcStatus=2
AND cp.Status IN (1,4)
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum
)cp ON pl.ProcNum=cp.ProcNum
LEFT JOIN (
SELECT SUM(cp.WriteOff) AS WriteOffs,pl.ProcNum
FROM procedurelog pl
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcStatus=2
AND cp.Status=7
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum
)cp7 ON pl.ProcNum=cp7.ProcNum
INNER JOIN patient ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
INNER JOIN provider ON provider.ProvNum=pl.ProvNum
LEFT JOIN adjustment adj ON adj.ProcNum=pl.ProcNum
WHERE pl.ProcStatus='2'
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum
) A
GROUP BY A.Abbr,A.ProcCode
ORDER BY A.Abbr,A.ProcCode

Thanks,
drtmz

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Query help add 'AbbrDesc' abbreviated description

Post by cmcgehee » Mon Sep 26, 2016 3:21 pm

This ought to do the trick.

/*# 1022 Query code written/modified: 09/26/2016*/
SET @FromDate='2016-09-01', @ToDate='2016-09-30';
SELECT A.Provider,A.ProcCode,A.AbbrDesc,
COUNT(A.ProcCode) AS 'Quantity',
SUM(A.ProcFee) AS $ProcFee_,
SUM(A.WriteOffs) AS $WriteOffs_,
SUM(A.Adjustments) AS $Adjustments_,
SUM(A.NetProd) AS $NetProd_
FROM (
SELECT provider.Abbr AS 'Provider',
pc.ProcCode,
pc.AbbrDesc,
pl.ProcFee*(pl.UnitQty+pl.BaseUnits)-IFNULL(COALESCE(cp7.WriteOffs,0),0) AS 'ProcFee',
COALESCE(cp.WriteOffs,0) AS 'WriteOffs',
COALESCE(SUM(adj.AdjAmt),0) AS 'Adjustments',
pl.ProcFee*(pl.UnitQty+pl.BaseUnits)-IFNULL(COALESCE(cp7.WriteOffs,0),0)
-COALESCE(cp.WriteOffs,0)+COALESCE(SUM(adj.AdjAmt),0) AS 'NetProd',
provider.Abbr
FROM procedurelog pl
LEFT JOIN (
SELECT SUM(cp.WriteOff) AS WriteOffs,pl.ProcNum
FROM procedurelog pl
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcStatus=2
AND cp.Status IN (1,4)
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum
)cp ON pl.ProcNum=cp.ProcNum
LEFT JOIN (
SELECT SUM(cp.WriteOff) AS WriteOffs,pl.ProcNum
FROM procedurelog pl
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcStatus=2
AND cp.Status=7
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum
)cp7 ON pl.ProcNum=cp7.ProcNum
INNER JOIN patient ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
INNER JOIN provider ON provider.ProvNum=pl.ProvNum
LEFT JOIN adjustment adj ON adj.ProcNum=pl.ProcNum
WHERE pl.ProcStatus='2'
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum
) A
GROUP BY A.Abbr,A.ProcCode
ORDER BY A.Abbr,A.ProcCode
Chris McGehee
Open Dental Software
http://www.opendental.com

Post Reply