Help needed on recursive sql

Database
Enthusiast

Help needed on recursive sql

Hi , 

i have an table its having four fields cost,retail,units,weekdayyr. sample data is given below.

cost,retail,units,weekdayofyr

120,125,100,1

115,132,142,1

114,859,236,2

114,158,258,2

147,258,639,3

i need a sequel to that generate summation of the fields by groupng on week also total item cost would be the sum of three fields (tot_cost,tot_retail,tot_units) . here i need value of 1st week total item cost as in the field prv_wk_item_cost on 2nd record, look at the required output.

prv_wk_item_cost,tot_cost,tot_retail,tot_units,weekdayofyr

0,235,257,242,1

734,228,1017,494,2

1729,114,158,258,2

prv_wk_item_cost = previous week(tot_cost + tot_retail + tot_units).

Appreciable your help

2 REPLIES
Senior Apprentice

Re: Help needed on recursive sql

I can't match your sample data and required output based on your narrative.

But it seems you don't need recursion, a simple OLAP function might be enough:

SELECT
SUM(tot_cost + tot_retail + tot_units)
OVER (ORDER BY weekdayofyr
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
SUM(cost) AS tot_cost,
SUM(retail) AS tot_retail,
SUM(units) AS tot_units,
weekdayofyr
FROM tab
GROUP BY weekdayofyr
ORDER BY weekdayofyr

Dieter

Enthusiast

Re: Help needed on recursive sql

Thanks .. working fine