All Forums Database
TdMan 91 posts Joined 01/07
10 Jul 2007
rows unbounded preceding

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 176 posts Joined 07/05
10 Jul 2007

"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 91 posts Joined 01/07
11 Jul 2007

Hi Barry,I ran a query likeselect 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 XXXXXXBoth 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 176 posts Joined 07/05
11 Jul 2007

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_sumIf the table data was the following:order_number order_date order_amount department123 1/1/07 3000 A05124 1/2/07 2000 B01125 1/3/07 1500 A05126 1/3/07 1000 B01I would get the following answer:department order_number order_amount running_sumA05 123 3000 3000A05 125 1500 4500B01 124 2000 2000B01 126 1000 3000This 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 6 posts Joined 11/11
17 Nov 2011

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 4628 posts Joined 11/04
19 Nov 2011

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

Dieter

sam99 6 posts Joined 11/11
21 Nov 2011

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 2 posts Joined 07/16
23 Aug 2016

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

23 Aug 2016

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?

You must sign in to leave a comment.