LOOP with CTE?

Question

I have the following code that works well but it only returns one well at a time. I am trying to return a resultset with all my wells with the most recent jib date and net / gross investment for each well. I understand that I can not create a loop around a CTE. Any thoughts?

-- C.Propery_Name = well name

;with grp as (
SELECT    
    datediff(month, @MinDate, C.Service_Date) / 2 as Group1,
    dateadd(month, datediff(month, '19000101',MIN(C.Service_Date)),'19000101') as PeriodStart,
    dateadd(day,-1,dateadd(month, 1+datediff(month, '19000101',MAX(C.Service_Date)),'19000101')) as PeriodEnd,
    -SUM(CASE WHEN C.main_account IN (810, 820, 830) THEN C.gross_88ths_value ELSE 0.00 END) +
    -SUM(CASE WHEN C.main_account IN (905, 920, 925, 305, 310, 315, 320, 321, 322, 323, 324, 325, 326) THEN C.gross_88ths_value ELSE 0.00 END) AS gross_revenue,
    -SUM(CASE WHEN C.main_account IN (810, 820, 830) THEN C.net_value ELSE 0.00 END) +
    -SUM(CASE WHEN C.main_account IN (905, 920, 925, 305, 310, 315, 320, 321, 322, 323, 324, 325, 326) THEN C.net_value ELSE 0.00 END) AS net_revenue
FROM Cost AS C, Wells W,CompanyWells CW, Company CC
WHERE (C.Service_Date BETWEEN @MinDate AND @MaxDate) AND C.Well_Code = W.MatrixWellNumber AND W.WellId = @in_well_id
AND CW.CompanyId = CC.CompanyId
AND CW.WellId = W.WellId
AND (CW.CompanyId = 1000)
AND C.Company_Code = 200
AND C.Accrual_Date Is Not Null
GROUP BY datediff(month, @MinDate, C.Service_Date) / 2
)

select  CAST(g1.PeriodEnd AS DATE) AS jib_date
, sum(g2.gross_revenue) AS cum_gross_investment
, sum(g2.net_revenue) AS cum_net_investment
from grp g1
join grp g2 on  g2.Group1 <= g1.Group1
Group By g1.Group1, g1.PeriodEnd
ORDER BY g1.PeriodEnd

Current ResultSet:

2011-04-30 -64120127.1000 -2717919.5800
2011-06-30 -98814734.9800 -3911837.3500
2011-08-31 -163436451.2200 -6605873.8800
2011-10-31 -176451059.3100 -8514962.4900
2011-12-31 -148159067.7600 -8625677.3400
2012-02-29 -167955317.9900 -9830939.9900
2012-04-30 -129374818.1200 -8816195.6300
2012-05-31 -129377010.6200 -8818320.3000

Answer 1

That looks like SQL rather than C# so this may not be the right forum. May be something from here is the place to be - possibly Transact SQL?

---

Regards David R
---------------------------------------------------------------
The great thing about Object Oriented code is that it can make small, simple problems look like large, complex ones.
Object-oriented programming offers a sustainable way to write spaghetti code. - Paul Graham.
Every program eventually becomes rococo, and then rubble. - Alan Perlis
The only valid measurement of code quality: WTFs/minute.

Answer 2

Take out the

AND W.WellId = @in_well_id

add WellId to the select and group by in the cte

add WellId to the join, group by and select in the outer query.

I suspect it's not as simple as that but it seems to be what you are requesting.

If you just want a single row per well - add a seq = row_number() over (partition by well_id order by PeriodEnd desc)

as a derived table and select where seq = 1