【Question】

I have two tables:

Table 1 – Transaction:

TransID |User| Date

1   | Andrew |2015-06-1613:13:00

2   | Andrew |2015-06-1613:15:00

3   | Andrew |2015-06-1613:17:00

4   | Andrew |2015-06-1614:15:00

5   | Andrew |2015-06-1614:18:00

6   | Andrew |2015-06-1614:25:00

7   | Andrew |2015-06-1614:35:00

8   | Andrew |2015-06-1614:55:00

 

Table2- DISCOUNT:

DiscountID |User| Date | DISCOUNT

1 | Andrew |2015-06-1613:00:00|30

2 | Andrew |2015-06-1614:00:00|25

2 | Andrew |2015-06-1614:30:00|20

I need to apply ‘Discount’ according to the time frame in ‘Table 1’.

Table outcome:

User| Date | Discount

Andrew |2015-06-1613:13:00|30

Andrew |2015-06-1613:15:00|30

Andrew |2015-06-1613:17:00|30

Andrew |2015-06-1614:15:00|25

Andrew |2015-06-1614:18:00|25

Andrew |2015-06-1614:25:00|25

Andrew |2015-06-1614:35:00|20

Andrew |2015-06-1614:55:00|20

Could anyone help me with this problem?

 

【Answer】

It’s difficult to code a cross-row calculation with SQL JOIN. An easier way is import the two tables and handle the computation in SPL (Structured Process Language). Below is the SPL script:

	A
1	$select * fromTransaction
2	$select * from Discountorder   by Dat
3	=A2.(Date)
4	=A1.new(User,Date,A2(A3.pseg(Date)).Discount:d)

A1: Retrieve data from Transaction;

A2: Retrieve data ordered by Dat from Discount;

A3: Get Date values from Discount;

A4: Find the corresponding discount according to the Date value in Transaction to generate a new table sequence.

The final result: