I have attached input dataset sample that has values for the bar chart and I have also attached the wanted barchart sample. In the bar chart , I need find out from 2007 to 2010, how many times(frequency) GY1, GY2 and GY3 are ranked in each year (shown as numbers under bars on the graph attached). And within each year’s bars I’d also like to know what the proportions are respectively that GY1, GY2 and GY3 are ranked as 1 st , 2 nd and 3 rd (shown as percentages within each bars).     ... View more

I need a query help for the following requirement. I have a dataset with 4 variables that looks like the following: id date charge_mode code_no a1 2-Oct-15 5 127 a1 5-Oct-15 5 104 a1 5-Oct-15 5 510 a1 5-Oct-15 5 511 a1 1-Nov-15 5 128 a1 5-Nov-15 5 330 a2 2-Oct-15 6 430 a2 5-Oct-15 6 230 a2 5-Oct-15 6 128 a2 5-Oct-15 6 127 a2 1-Nov-15 6 520 a2 5-Nov-15 6 210 This is a transaction data for each custid, I need to find if any of transactions that has the code_no(127,128,104) which contributes to flag1 and flag2 variables that I need to create for each "month" of a customer id. The monthly cycle(/*most important*/) depends on the variable charge_mode, like in the above example the chargemode for id a1 is 5 and a2 is 6, meaning : 1. For A1,5th of oct to 4th of nov is the period to be considered for October month and 5th of Nov to 4th of Dec to be considered for November and so on. 2. For A2, the period would be from 6th to 5th and so on. The logic for flags: If in a month, all the 3 code_no(127,128,104) exist then flag_2=1 and flag_1=1; if in a month, only 104 exist, then flag_2=1,flag_1=1 If in a month, only (127 and 128) exist, and if 128 is the latest found record for the month, then flag_1=0,flag_2=0 If in a month, only (127 and 128) exist and if 127 is the latest found for the month, then flag_1=1, flag_2=0 1. If only 127 is there , then flag_1=1, flag_2=0 2. if only 128, exists, then flag_1=0. flag_2=0 Want should be in the following format: id flag1 flag2 month a1 0 1 oct a1 1 0 nov a1 0 0 dec followed a2 and so on....….. ... View more

I need a query help for the following requirement. I have a dataset with 4 variables that looks like the following: id date charge_mode code_no a1 2-Oct-15 5 127 a1 5-Oct-15 5 104 a1 5-Oct-15 5 510 a1 5-Oct-15 5 511 a1 1-Nov-15 5 128 a1 5-Nov-15 5 330 a2 2-Oct-15 6 430 a2 5-Oct-15 6 230 a2 5-Oct-15 6 128 a2 5-Oct-15 6 127 a2 1-Nov-15 6 520 a2 5-Nov-15 6 210 This is a transaction data for each custid, I need to find if any of transactions that has the code_no(127,128,104) which contributes to flag1 and flag2 variables that I need to create for each "month" of a customer id. The monthly cycle(/*most important*/) depends on the variable charge_mode, like in the above example the chargemode for id a1 is 5 and a2 is 6, meaning : 1. For A1,5th of oct to 4th of nov is the period to be considered for October month and 5th of Nov to 4th of Dec to be considered for November and so on. 2. For A2, the period would be from 6th to 5th and so on. The logic for flags: If in a month, all the 3 code_no(127,128,104) exist then flag_2=1 and flag_1=1; if in a month, only 104 exist, then flag_2=1,flag_1=1 If in a month, only (127 and 128) exist, and if 128 is the latest found record for the month, then flag_1=0,flag_2=0 If in a month, only (127 and 128) exist and if 127 is the latest found for the month, then flag_1=1, flag_2=0 1. If only 127 is there , then flag_1=1, flag_2=0 2. if only 128, exists, then flag_1=0. flag_2=0 Want should be in the following format: id flag1 flag2 month a1 0 1 oct a1 1 0 nov a1 0 0 dec followed a2 and so on....….. ... View more

How to join the 2 very large datasets with duplicate id keys in both the datasets   For example, I have the following datasets   table1 :   acct_no    a                  b                  c                  d a1                4                  7                  2                  8 a3                5                  32                8                  12 a5                42                12                54                65 a1                5                  2                  17                23   acct_no is the key with duplicates because it is a transaction dataset.   table 2: acct_no    card_no a4                54 a3                31 a4                43 a1                24 a8                12 a7                23 a8                45   acct_no in table 2 also has duplicates because the logic is that a single customer can have more than one card, one a primary card and the other being secondary card   Can anybody help me with the logic for the join?   The datasets contain 400 million records and are pretty wide and long, however i need to fetch only the card_no from table2, so table 1 would be my left table. Sorting will not help either   ... View more

How to join the 2 very large datasets with duplicate id keys in both the datasets   For example, I have the following datasets   table1 :   acct_no    a                  b                  c                  d a1                4                  7                  2                  8 a3                5                  32                8                  12 a5                42                12                54                65 a1                5                  2                  17                23   acct_no is the key with duplicates because it is a transaction dataset.   table 2: acct_no    card_no a4                54 a3                31 a4                43 a1                24 a8                12 a7                23 a8                45   acct_no in table 2 also has duplicates because the logic is that a single customer can have more than one card, one a primary card and the other being secondary card   Can anybody help me with the logic for the join?   The datasets contain 400 million records and are pretty wide and long, however i need to fetch only the card_no from table2, so table 1 would be my left table. Sorting will not help either   ... View more