Ranking data with condition in script

[berryandcherry6]

Hi,

I have two tables, with datas as below

table 1:

campaignID	startdate	expiration date
1	03-13-2017	03-26-2017
2	03-05-2017	03-17-2017
3	03-02-2017	03-14-2017
4	02-28-2017	03-13-2017

Table 2:    

campaignID	value_type	advertisement	Sales
1	4	35	23
2	4	67	2
3	4	45	3
1	4	12	56
2	5	33	34
3	4	5	42
3	5	5	56
4	4	3	2
4	5	4	5

Now i need to add rank column and sum type column to table,through script by creating single table  like below, where i need to check for  condition

1.) Active campaign, where expiration date >= today, will be given top ranking

2.) latest Expired Campaign, where expiration date < today(), will be given least ranking.

campaignID	value_type	sum type	Sum	rank
1	4	advertisement	47	top 1
2	4	advertisement	100	top 2
3	4	advertisement	45	top 3
1	4	Sales	79	top 1
2	4	Sales	36	top 2
3	4	Sales	3	top 3

Any idea, suggestion, workaround are all appreciated. Please help me to find solution for this. let me know if any clarification is required

[arockiyaselvana]

Hi,

1. To create the "sum type" column, you can use cross table concept.

2. For Ranking concept, you can create the rank in Front End using Rank() function.