0

Could you please guide

I have a scenario where credit_Date,debit_date and loan_date can have different values of date or same. Output table have below columns

Date: should combine credit_date, debit_date and loan_date ( credit_date, debit_date and loan_date can be same (or) have different dates)

Credit_payment: Find the sum of credit amount for a given credit_date, entity, currency, owner

Debit_payment: Find the sum of debit amount for a given debit_date, entity, currency, owner

Loan_payment: Find the sum of loan amount for a given loan_date, entity, currency, owner,

entity: values from Table1

currency : values from Table 1

Owner: values from Table 1

Total : sum of ( credit_payment + debit_payement+ loan_payment)

Please find the screenshot as below. enter image description here

smp97
  • 63
  • 6

1 Answers1

1

You can use a similar query as in my previous answer, and change the format of table1 in order to facilitate grouping by date:

select coalesce(credit_date, debit_date, loan_date) as date, 
       coalesce(sum(credit_amount), 0) as credit_payment, 
       coalesce(sum(debit_amount), 0) as debit_payment,
       coalesce(sum(loan_amount), 0) as loan_payment,
       entity, currency, owner,
       coalesce(sum(credit_amount), 0) + coalesce(sum(debit_amount), 0) + coalesce(sum(loan_amount), 0) as Total
from (
    select credit_date, credit_amount, null as debit_date, null as debit_amount, null as loan_date, null as loan_amount, entity, currency, owner
    from table1
    union all
    select null as credit_date, null as credit_amount, debit_date, debit_amount, null as loan_date, null as loan_amount, entity, currency, owner
    from table1
    union all
    select null as credit_date, null as credit_amount, null as debit_date, null as debit_amount, loan_date, loan_amount, entity, currency, owner
    from table1
) t
group by coalesce(credit_date, debit_date, loan_date), entity, currency, owner
mck
  • 40,932
  • 13
  • 35
  • 50
  • Thank @mck for the response. When I am executing above query, it's populating the values of `credit_date` for the date column in the table2. we need to populate all the values of `credit_date`, `debit_date`, `loan_date` in the `date` column of `table2`. Could you please guide. – smp97 Mar 09 '21 at 04:40
  • the credit_date and debit_date and loan_date are of datatype string. – smp97 Mar 09 '21 at 05:00
  • I cannot reproduce your problem... I tested on the sample data you provided and it gave the expected output. Could you describe your problem in more details please? – mck Mar 09 '21 at 08:27
  • Thanks for the response @mck I have posted another query. Could you please guide. https://stackoverflow.com/questions/66548308/hive-hql-for-derived-columns – smp97 Mar 09 '21 at 14:00
  • Hi @smp97 would you mind posting sample data in text format? It's hard to read the excel screenshot. – mck Mar 09 '21 at 14:01
  • Sure... could you please tell me how to post tables in .txt format!? – smp97 Mar 09 '21 at 14:37
  • https://meta.stackexchange.com/questions/356997/new-feature-table-support – mck Mar 09 '21 at 14:38
  • Thanks... I've updated the table in txt format... but table-1 is having some issues updating correctly.. – smp97 Mar 09 '21 at 15:57
  • you need an empty new line before the table – mck Mar 09 '21 at 15:58
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/229710/discussion-between-smp97-and-mck). – smp97 Mar 10 '21 at 04:45