rows unbounded preceding

[TdMan]

Can anyone tell me what this statement is used for? I found this as a reply to one of the query in this forum.

[Barry-1604]

"Rows unbounded preceding" is used in an ordered analytical function to tell it to include all of the preceding rows in the partition in the calculation being performed. When you specify it, it will essentially give you a "cumulative" calculation (cumulative sum, cumulative average, etc.).

So, if I wanted to get a cumulative sum of an amount column, I could say:

select sum(amount) over(partition by ...
order by ...
rows unbounded preceding)

Hope that helps!

[TdMan]

Hi Barry,

I ran a query like

select sum(key)over(partition by key order by key rows unbounded preceding) from XXXXXX

========================AND======================
select sum(key)over(partition by key order by key) from XXXXXX

Both the queries gave me the same output. Is that correct? If yes what is the use of having 'rows unbounded preceding' in the first query. Also I want to know the exact meaning of (through example if possible) "include all of the preceding rows in the partition".

It might be a silly doubt, but am sorry am anxious to know abt it.

[Barry-1604]

It doesn't make any sense to partition on the same column that you're summing on. The partition field should be what you want the running sum to be for. For instance, if I have a bunch of orders for various departments, I might want to have a running sum of the order amounts by order date. I could write that as:

select department
,order_number
,order_amount
,sum(order_amount) over (partition by department
order by order_date
rows unbounded preceding) running_sum

If the table data was the following:

order_number order_date order_amount department
123 1/1/07 3000 A05
124 1/2/07 2000 B01
125 1/3/07 1500 A05
126 1/3/07 1000 B01

I would get the following answer:

department order_number order_amount running_sum
A05 123 3000 3000
A05 125 1500 4500
B01 124 2000 2000
B01 126 1000 3000

This would give me a cumulative (or running) sum of the order_amount by order_date. Whenever a new department is encountered, the sum is reset to zero.

Hope that helps.

[sam99]

Hi Experts,

I been trying to handle the below logic using unbounded preceeding for the past few days..Can you please have a look?

My table:

mbr  amt1 amt2 

123  50     70

123  50      90

123  50     100

I am expecting output in the format

mbr  amt1 amt2  amt3

123  50     70       20 [ ie 70-50]

123  50      90       20 [ ie (90-50) - 20]

123  50     100      10 [ ie (100-50) - 20(2nd amt3) -20(1st amt3)]

Can you please tell  me whether this can be achieved in TD?

Regards,

Sam99

[dnoeth]

Is the data sorted by amt2?

Rephrasing your desired result you need the current difference minus the previous difference, like this:

(amt2 - amt1)
- coalesce(sum (amt2 - amt1)
           over (partition by mbr 
                 order by amt2
                 rows between 1 preceding and 1 preceding
           ,0)

Btw, why do you need this result, what's the business question?

Dieter

[sam99]

Thank you Dieter

The data will be sorter based on the chkdt field. Sorry to miss that.

 Input:

mbr    chkdt      amt1 amt2 

123  11/01/11   50     70

123  11/05/11   50      90

123  11/10/11   50     100

Output:

mbr    chkdt      amt1 amt2  amt3

123  11/01/11   50     70     20 [ ie 70-50]

123  11/05/11   50      90    20 [ ie (90-50) - 20]

123  11/10/11   50     100   10 [ ie (100-50) - 20(2nd amt3) -20(1st amt3)]

amt3 is the incetive paid to a member each time.

First time paid 20

Second time 90-50=40. But since we already paid 20, 40-20 ie 20

Third time 100-50=50. We already paid 20 + 20 = 40. so 50-40=10

This can repeat until the correct amount is paid to the member.

Also Dieter couldn't we use MDIFF function also to handle this logic.

SELECT

MBR,chkdt,AMT1,AMT2 , AMT2-AMT1 AS AMT3,

CASE when mdiff(amt3,1,chkdt) is null then amt3 else mdiff(amt3,1,chkdt) end as inct_paid

FROM C3

Thanks again...

[G787010]

Hi All,

Could anyone provide the suggestion for the below query,

The requirement is, considering 2 columns with date and amount, if the month of the date is 1(January) then the amount should be the same, in case if it is 2,3,..,12 then the amount should be the difference with last month.

Sample:

Date             - Amount   - Diff

01/01/2016 - 100       - 100

01/02/2016 - 20         -  80

02/02/2016  - 500      -500

02/03/2016  -250       -250

Thanks in Advance,

Suwarna S

[sachin_sebastia]

Why is the result of the 3rd row 500 (and not 500-80 = 420)? Should there be no subtraction within a month? Also is the date format in example given DD/MM/YYYY?

[nbwest76]

I'm a bit stuck ... I have 3 tables (table a, b and c). table a contains the base population with two columns 'ID' and 'Start_Date'.  table b contains a 'Required_Amt' field that can be joined by the same 'ID'. Table c has the complete payment transactions for each 'ID' from table a.

I would like to take the cumulative sum from table c only for the amounts received after the 'start_date' from table a.  I would also like to reference the date and cumulative amount once the cumulative amount is >= the 'Requited_Amt' from table b.  Basically I would like the sum to stop calculating anything additional payments received after the required amount and received date have been met. In other words, if 5 payments were received since the start_date, and the req_amt was met on the 4th payment.  I'd like to see the date and amount for that 4th payment.

I'm getting close but not quite there yet.  I've tried playing around with the sum partition by and unbounded preceding options but to no avail.  Here is a sample starter code.

SELECT
a.ID,
a.START_DATE,
b.REQ_AMT,
c.REC_DT,
c.TOTAL_RECD
FROM a
LEFT JOIN 
(SELECT ID, REQ_AMT
FROM b) b ON a.ID = b.ID
LEFT JOIN
(SELECT ID, TOTAL_RECD, REC_DT
FROM c
WHERE TRAN_ID = '173' AND TOTAL_RECD>0) c ON a.ID = c.ID AND c.REC_DT >= a.START_DT