TSQL GROUP BY in recursive CTE

Question

Is there a way to bypass GROUP BY inside a CTE loop, or is there a workaround?

I need to group the results of a CTE table and use it in another loop with the same CTE, but I get the following error:

GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of the recursive common table expression 'cte'.

Here's the request:

WITH cte AS ( SELECT id, dailyconsumption, stock/dailyconsumption as cutoff FROM items WHERE father IS NULL UNION ALL SELECT i.id, SUM(father.dailyconsumption*i.num), MAX(stock)/SUM(father.dailyconsumption*i.num) FROM cte father JOIN items i ON father.id=i.father group by i.id ) SELECT id, MIN(cutoff) FROM cte GROUP BY id 

SQL-Fiddle (with sample data)

---

EDIT ... this is a logical problem

I have a set of end-user elements (father = NULL) and other sub-items made by a number of other elements (field father and num field filled). I only got dailyconsumption for the end user items (I start my cte with "WHERE father IS NULL"), and the dailyconsumption subitems are calculated by SUM (father.dailyconsumption * item.num).

 WITH cte AS( SELECT id, dailyconsumption, stock/dailyconsumption as cutoff FROM items WHERE father IS NULL UNION ALL SELECT i.id, father.dailyconsumption*i.num 0 FROM cte father JOIN items i ON father.id=i.father ) SELECT id, SUM(dailyconsumption) FROM cte GROUP BY id 

http://sqlfiddle.com/#!3/f4f2a/95

With the help of this reliable request, I will have all the dailyconsumption filled in for all elements (end users and sub-items). Keep in mind that the relationship between father and son can be more than one level.

Now I need to calculate the cutoff (how many days I have enough). For end use, it is very easy and already calculated in the first CTE: stocks / dailyconsumption . For subitems, this is a little more complicated: subitem.stock / subitem.dailyconsumption + MIN (father.cutoff) where MIN (father.cutoff) is the minimum disconnection from all fathers of this subitem. This is because I need another group.

Maybe I need another CTE in order to fixate on the relationship between father and son?

Thank you for your attention and sorry for my English.

Answer 1

 ;WITH cte AS ( SELECT id, father, dailyconsumption, (stock / dailyconsumption) AS cutoff, 0 AS [Level] FROM items WHERE father IS NULL UNION ALL SELECT i.id, i.father, c.dailyconsumption * i.num, i.stock / (c.dailyconsumption * i.num), [Level] + 1 FROM cte c JOIN items i ON c.id = i.father ) SELECT c.id, c.dailyconsumption, c.cutoff AS subItemsCutoff, MIN(ct.cutoff) OVER(PARTITION BY ct.[Level]) AS fatherCutoff, (c.cutoff + ISNULL(MIN(ct.cutoff) OVER(PARTITION BY ct.[Level]), 0)) AS Cutoff FROM cte c LEFT JOIN cte ct ON c.father = ct.id 

SQLFiddle Demo

Answer 2

Instead, I recommend using a variable table. Declare the table, and then insert these records into it. You will need to find out how to scroll it by the second insertion into the command. I got this to get you started:

 DECLARE @staging TABLE ( id INT ,dailyconsumption FLOAT ,cutoff FLOAT ) INSERT INTO @staging SELECT id, dailyconsumption, stock/dailyconsumption as cutoff FROM items WHERE father IS NULL INSERT INTO @staging SELECT i.id, SUM(father.dailyconsumption*i.num), MAX(stock)/SUM(father.dailyconsumption*i.num) FROM @staging father JOIN items i ON father.id=i.father group by i.id SELECT id ,MIN(cutoff) FROM @staging GROUP BY id