I am attempting to write a CTE query that is probably the most advanced query for me. (Been working with SQL for 3 weeks). When I try to run this query, I get an error of
Msg 252, Level 16, State 1, Line 4
Recursive common table expression 'Sixer' does not contain a top-level UNION ALL operator.
So here is the skinny – I am including DDL (which is HUGE) and also stating that table fiver and table first look the same, but they are different as the fact that first could contain acctNum that do not exist in fiver and vice versa. Now here is DDL
Create Table First
(
Doctor varchar(100)
,AcctNum varchar(10)
,ChargedAmt decimal(10,2)
,DOS date
)
Insert Into First (Doctor, AcctNum, ChargedAmt, DOS) VALUES
('Doctor A', '912224661', '1329', '1/31/2016'),
('Doctor A', '912221628', '12203', '1/31/2015'),
('Doctor A', '912219195', '1096', '5/14/2014'),
('Doctor A', '912224987', '4195.42', '2/29/2016'),
('Doctor A', '912219200', '11442.03', '5/16/2014'),
('Doctor A', '912217592', '7223.77', '12/26/2013'),
('Doctor A', '912217652', '2570.24', '12/31/2013'),
('Doctor A', '912221613', '332.51', '2/28/2015'),
('Doctor A', '912218592', '13087', '3/31/2014'),
('Doctor B', '912212830', '260', '7/28/2012'),
('Doctor B', '912217148', '216', '10/25/2013'),
('Doctor B', '912213308', '225', '8/31/2012'),
('Doctor B', '912225658', '1120', '5/18/2016'),
('Doctor B', '912224030', '1044.1', '10/27/2015'),
('Doctor B', '912224821', '270', '2/15/2016'),
('Doctor B', '912215253', '1833', '4/12/2013'),
('Doctor B', '912215226', '1943', '4/26/2013'),
('Doctor B', '912224103', '697.05', '10/31/2015'),
('Doctor C', '912226150', '321.16', '7/29/2016'),
('Doctor C', '912226728', '409.87', '8/31/2016'),
('Doctor D', '912227487', '1579.39', '11/28/2016'),
('Doctor D', '912227536', '689.03', '11/28/2016'),
('Doctor E', '912225574', '252.45', '4/22/2016'),
('Doctor E', '912225190', '443.13', '3/25/2016'),
('Doctor F', '912228020', '5341', '1/31/2017'),
('Doctor F', '912216621', '2550', '8/30/2013'),
('Doctor F', '912224493', '5908.48', '12/31/2015'),
('Doctor F', '912210468', '5863.19', '1/31/2012'),
('Doctor F', '912227606', '4657', '12/16/2016'),
('Doctor F', '912212275', '12202.39', '5/31/2012'),
('Doctor F', '912213649', '3640', '10/31/2012'),
('Doctor F', '912226417', '2801', '8/12/2016'),
('Doctor F', '912226633', '452', '8/24/2016'),
('Doctor G', '912212694', '0', '7/18/2012'),
('Doctor G', '912214793', '11659', '2/28/2013'),
('Doctor G', '912224178', '4749', '11/17/2015'),
('Doctor G', '912219997', '16501', '8/20/2014'),
('Doctor G', '912222180', '15872', '3/31/2015'),
('Doctor G', '912223090', '18101', '7/14/2015'),
('Doctor G', '912212692', '8197', '7/18/2012'),
('Doctor G', '912222048', '0', '6/26/2015'),
('Doctor G', '912223539', '2429', '8/31/2015'),
('Doctor H', '912220020', '12262', '8/22/2014'),
('Doctor H', '912223503', '12377', '8/31/2015'),
('Doctor H', '912212365', '43987.59', '6/25/2012'),
('Doctor H', '912212363', '615', '6/29/2012'),
('Doctor H', '912214260', '2661', '12/31/2012'),
('Doctor H', '912210695', '199', '1/28/2012'),
('Doctor H', '912216444', '4425', '8/22/2013'),
('Doctor H', '912216446', '3475', '8/22/2013'),
('Doctor H', '912227667', '12522', '12/21/2016'),
('Doctor I', '912216502', '500', '8/28/2013'),
('Doctor I', '912222006', '4379', '2/28/2015'),
('Doctor I', '912212492', '3532', '6/30/2012'),
('Doctor I', '912212934', '4625', '8/10/2012'),
('Doctor I', '912213175', '5802', '9/28/2012'),
('Doctor I', '912210537', '2968.79', '1/31/2012'),
('Doctor I', '912225766', '4365', '5/27/2016'),
('Doctor I', '912225768', '4236', '5/27/2016'),
('Doctor I', '912210536', '4571', '1/27/2012')
Create Table Sec
(
Doctor varchar(100)
,PendingCharges decimal(10,2)
)
Insert Into SEC (doctor, pendingcharges) VALUES
('Doctor A', '100.12'),
('Doctor C', '200.1'),
('Doctor I', '300.12')
Create Table Tree
(
Doctor varchar(100)
,InsuranceReviewAmt decimal(10,2)
)
Insert Into Tree (doctor, insurancereviewamt) VALUES
('Doctor F', '8710.12 ')
Create Table Four
(
Doctor varchar(100)
,prevyrbills decimal(10,2)
,AcctNum varchar(10)
,DOS date
)
INSERT INTO four (doctor, prevyrbills, AcctNum, Dos) VALUES
('Doctor A', '100.12', '999111333', '1/1/2016'),
('Doctor A', '200.12', '888111999', '1/3/2016'),
('Doctor A', '500.44', '333888111', '4/1/2016'),
('Doctor C', '800.11', '111888333', '6/1/2016'),
('Doctor E', '700.11', '444333888', '8/1/2016'),
('Doctor E', '900.12', '222331992', '10/1/2016')
Create Table AllDocs
(
Doctor varchar(100)
)
Insert Into AllDocs (Doctor) VALUES
('Doctor A'),('Doctor B'),('Doctor C'),
('Doctor D'),('Doctor E'),('Doctor F'),
('Doctor G'),('Doctor H'),('Doctor I'),
('Doctor J'),('Doctor K'),('Doctor L'),
('Doctor M'),('Doctor N'),('Doctor O'),
('Doctor P'),('Doctor Q'),('Doctor R')
Create Table Fiver
(
Doctor varchar(100)
,AcctNum varchar(10)
,ChargedAmt decimal(10,2)
,DOS date
)
Insert Into Fiver (doctor, acctnum, chargedamt, DOS) VALUES
('Doctor A', '912224661', '1329', '1/31/2016'),
('Doctor A', '912221628', '12203', '1/31/2015'),
('Doctor A', '912219195', '1096', '5/14/2014'),
('Doctor A', '912224987', '4195.42', '2/29/2016'),
('Doctor A', '912219200', '11442.03', '5/16/2014'),
('Doctor A', '912217592', '7223.77', '12/26/2013'),
('Doctor A', '912217652', '2570.24', '12/31/2013'),
('Doctor A', '912221613', '332.51', '2/28/2015'),
('Doctor A', '912218592', '13087', '3/31/2014'),
('Doctor B', '912212830', '260', '7/28/2012'),
('Doctor B', '912217148', '216', '10/25/2013'),
('Doctor B', '912213308', '225', '8/31/2012'),
('Doctor B', '912225658', '1120', '5/18/2016'),
('Doctor B', '912224030', '1044.1', '10/27/2015'),
('Doctor B', '912224821', '270', '2/15/2016'),
('Doctor B', '912215253', '1833', '4/12/2013'),
('Doctor B', '912215226', '1943', '4/26/2013'),
('Doctor B', '912224103', '697.05', '10/31/2015'),
('Doctor C', '912226150', '321.16', '7/29/2016'),
('Doctor C', '912226728', '409.87', '8/31/2016'),
('Doctor D', '912227487', '1579.39', '11/28/2016'),
('Doctor D', '912227536', '689.03', '11/28/2016'),
('Doctor E', '912225574', '252.45', '4/22/2016'),
('Doctor E', '912225190', '443.13', '3/25/2016'),
('Doctor F', '912228020', '5341', '1/31/2017'),
('Doctor F', '912216621', '2550', '8/30/2013'),
('Doctor F', '912224493', '5908.48', '12/31/2015'),
('Doctor F', '912210468', '5863.19', '1/31/2012'),
('Doctor F', '912227606', '4657', '12/16/2016'),
('Doctor F', '912212275', '12202.39', '5/31/2012'),
('Doctor F', '912213649', '3640', '10/31/2012'),
('Doctor F', '912226417', '2801', '8/12/2016'),
('Doctor F', '912226633', '452', '8/24/2016'),
('Doctor G', '912212694', '0', '7/18/2012'),
('Doctor G', '912214793', '11659', '2/28/2013'),
('Doctor G', '912224178', '4749', '11/17/2015'),
('Doctor G', '912219997', '16501', '8/20/2014'),
('Doctor G', '912222180', '15872', '3/31/2015'),
('Doctor G', '912223090', '18101', '7/14/2015'),
('Doctor G', '912212692', '8197', '7/18/2012'),
('Doctor G', '912222048', '0', '6/26/2015'),
('Doctor G', '912223539', '2429', '8/31/2015'),
('Doctor H', '912220020', '12262', '8/22/2014'),
('Doctor H', '912223503', '12377', '8/31/2015'),
('Doctor H', '912212365', '43987.59', '6/25/2012'),
('Doctor H', '912212363', '615', '6/29/2012'),
('Doctor H', '912214260', '2661', '12/31/2012'),
('Doctor H', '912210695', '199', '1/28/2012'),
('Doctor H', '912216444', '4425', '8/22/2013'),
('Doctor H', '912216446', '3475', '8/22/2013'),
('Doctor H', '912227667', '12522', '12/21/2016'),
('Doctor I', '912216502', '500', '8/28/2013'),
('Doctor I', '912222006', '4379', '2/28/2015'),
('Doctor I', '912212492', '3532', '6/30/2012'),
('Doctor I', '912212934', '4625', '8/10/2012'),
('Doctor I', '912213175', '5802', '9/28/2012'),
('Doctor I', '912210537', '2968.79', '1/31/2012'),
('Doctor I', '912225766', '4365', '5/27/2016'),
('Doctor I', '912225768', '4236', '5/27/2016'),
('Doctor I', '912210536', '4571', '1/27/2012')
Create Table Sixer
(
Doctor varchar(100)
,Practice varchar(100)
,AnnRev decimal(10,2)
)
INSERT INTO Sixer (doctor, Practice, AnnRev) VALUES
('Doctor A', 'Practice A', '150000'),
('Doctor B', 'Practice B', '200000'),
('Doctor C', 'Practice C', '150000'),
('Doctor D', 'Practice D', '150000'),
('Doctor E', 'Practice E', '200000'),
('Doctor F', 'Practice F', '150000'),
('Doctor G', 'Practice G', '75000'),
('Doctor H', 'Practice H', '90000'),
('Doctor I', 'Practice I', '80000')
Create Table Last
(
Doctor varchar(100)
,oIsAcctID varchar(100)
,DOS date
)
Insert Into Last (Doctor, oIsAcctID, dos) VALUES
('Doctor A', 'NS12420924', '10/10/2014'),
('Doctor A', 'NS12408229', '8/16/2013'),
('Doctor A', 'NS12423200', '12/29/2014'),
('Doctor A', 'NS12408173', '8/15/2013'),
('Doctor A', 'NS12429761', '10/6/2015'),
('Doctor A', 'NS12414659', '4/10/2014'),
('Doctor A', 'NS12424319', '2/9/2015'),
('Doctor A', 'NS12412810', '2/14/2014'),
('Doctor A', 'NS12409574', '10/7/2013'),
('Doctor B', 'NS12361225', '3/3/2010'),
('Doctor B', 'NS12404381', '4/1/2013'),
('Doctor B', 'NS12385336', '9/15/2011'),
('Doctor B', 'NS12404310', '3/28/2013'),
('Doctor B', 'NS12424614', '2/20/2015'),
('Doctor B', 'NS12378790', '5/2/2011'),
('Doctor B', 'NS12436716', '9/12/2016'),
('Doctor B', 'NS12394012', '4/30/2012'),
('Doctor B', 'NS12433703', '4/20/2016'),
('Doctor C', 'NS12436322', '8/22/2016'),
('Doctor C', 'NS12436322', '8/23/2016'),
('Doctor C', 'NS12435005', '6/21/2016'),
('Doctor D', 'NS12437886', '11/15/2016'),
('Doctor D', 'NS12437804', '11/10/2016'),
('Doctor E', 'NS12433080', '4/21/2016'),
('Doctor E', 'NS12432817', '3/7/2016'),
('Doctor E', 'NS12433080', '3/18/2016'),
('Doctor F', 'NS12370205', '10/4/2010'),
('Doctor F', 'NS12423205', '12/29/2014'),
('Doctor F', 'NS12370705', '10/14/2010'),
('Doctor F', 'NS12415295', '4/28/2014'),
('Doctor F', 'NS12405405', '5/7/2013'),
('Doctor F', 'NS12370754', '10/14/2010'),
('Doctor F', 'NS12419878', '9/10/2014'),
('Doctor F', 'NS12396650', '7/25/2012'),
('Doctor F', 'NS12397679', '8/21/2012'),
('Doctor G', 'NS12392440', '3/5/2012'),
('Doctor G', 'NS12418439', '7/30/2014'),
('Doctor G', 'NS12406864', '6/28/2013'),
('Doctor G', 'NS12431461', '12/21/2015'),
('Doctor G', 'NS12375850', '2/25/2011'),
('Doctor G', 'NS12414269', '3/31/2014'),
('Doctor G', 'NS12417709', '7/14/2014'),
('Doctor G', 'NS12403820', '3/14/2013'),
('Doctor G', 'NS12427115', '6/5/2015')
And this is the query I wrote to try to extract my needed data that throws the error above
DECLARE @Startdate date = '20170101',
@Enddate date = '20170131';
WITH Sixer As
(
Select
DISTINCT(Doctor) As Doctor
,Practice
,AnnRev
FROM Sixer
)
,First As
(
Select
Doctor
,ChargedAmt
FROM dbo.First
WHERE DOS >= @Startdate
AND DOS < DATEADD(DAY,1,@Enddate)
)
,Sec As
(
Select
Doctor
,PendingCharges
FROM Sec
),
Tree As
(
Select
Doctor
,InsuranceReviewAmt
FROM Tree
)
,Four As
(
Select
Doctor
,prevyrbills
FROM Four
WHERE CAST(DOS As DATE) BETWEEN CAST(DateAdd(yy, -1, @startdate) As Date)
AND CAST(DateAdd(yy, -1, @enddate) As Date)
)
,Fiver As
(
Select
Doctor
,AcctNum
,DOS
FROM Fiver
WHERE DOS >= @Startdate
AND DOS < DATEADD(DAY,1,@Enddate)
)
,Last As
(
Select
Doctor
,oIsAcctID
FROM Last
WHERE DOS >= @Startdate
AND DOS < DATEADD(DAY,1,@Enddate)
)
Select
DISTINCT(ad.[Doctor]) As [Doctor]
,[Practice Name] = sx.practice
,[Charged Amt] = ROUND(SUM(ISNULL(sx.ChargedAmt,0)),0)
,[Summation] = CAST(ROUND(SUM(ISNULL(sec.PendingCharges,0))+SUM(ISNULL(tr.InsuranceReviewAmt,0)),0) As INT)
,[ABC] = ROUND(SUM(ISNULL(fr.prevyrbills,0)),0)
,[DEF] = ISNULL(sx.AnnRev,0)
,[Count One] = COUNT(fv.AcctNum)
,[Count Two] = COUNT(ls.oIsAcctID)
FROM AllDocs ad
LEFT JOIN Sixer sx
ON ad.[Doctor] = sx.[Doctor]
LEFT JOIN First fst
ON fst.Doctor = ad.Doctor
LEFT JOIN sec sec
ON sec.Doctor = ad.Doctor
LEFT JOIN Tree tr
ON tr.Doctor = ad.Doctor
LEFT JOIN Four fr
ON ad.Doctor = fr.Doctor
LEFT JOIN Fiver fv
ON ad.Doctor = fv.Doctor
LEFT JOIN Last ls
ON ls.Doctor = ad.Doctor
GROUP BY ad.[Doctor],sx.practice, sx.AnnRev
ORDER BY ad.[Doctor] ASC
Best Answer
The error is due to this CTE:
You have a table named
Sixer
and a cte namedSixer
. So when the query parser reads theFROM Sixer
, it considers the cte (and not the table). And this is not allowed - unless it's a recursive CTE (and thus the error).Solution is simple: change the CTE name to something else:
Another solution would be to reference (prefix) the table with the schema name (
dbo.Sixer
). This way you could keep the same name for the CTE:Irrelevant to the error, I removed the parenthesis around
Doctor
.DISTINCT
is not a function. It applies to the wholeSELECT
list.