Count Data After a date from another table

waleeed_mahmood — Mon, 04 Oct 2021 21:41:03 GMT

Hello all,

I hope you are doing well.

I have two tables as shown below. I want to sum the Amount of Table 2 only where the Transaction Date is greater than the approved date for that company. Please assume i have many columns in table 2 and I'm only showing a few here. I have added a column in Table 2 (To be Counted) that shows if the value should be counted based on expected results.

Please let me know if you have any questions and thank you for your help.

Table 1
Company	Approved Date
Company 1	2020-03-17
Company 2	2020-11-15

Table 2
Company	Transaction Date	Amount	To be Counted:
Company 1	2020-03-12	1	No
Company 1	2020-03-14	2	No
Company 1	2020-03-16	3	No
Company 1	2020-03-20	4	Yes
Company 1	2020-03-21	5	Yes
Company 1	2020-03-22	6	Yes
Company 1	2020-03-23	7	Yes
Company 1	2020-03-24	8	Yes

Company 2	2020-11-09	9	No
Company 2	2020-11-10	10	No
Company 2	2020-11-17	11	Yes
Company 2	2020-11-18	12	Yes
Company 2	2020-11-19	13	Yes
Company 2	2020-11-20	14	Yes
Company 2	2020-11-21	15	Yes

@sunny_talwar

Re: Count Data After a date from another table

Saravanan_Desingh — Tue, 05 Oct 2021 02:03:21 GMT

Try this,

SET DateFormat='YYYY-MM-DD'; tab2: LOAD * INLINE [ Company, Transaction Date, Amount Company 1, 2020-03-12, 1 Company 1, 2020-03-14, 2 Company 1, 2020-03-16, 3 Company 1, 2020-03-20, 4 Company 1, 2020-03-21, 5 Company 1, 2020-03-22, 6 Company 1, 2020-03-23, 7 Company 1, 2020-03-24, 8 Company 2, 2020-11-09, 9 Company 2, 2020-11-10, 10 Company 2, 2020-11-17, 11 Company 2, 2020-11-18, 12 Company 2, 2020-11-19, 13 Company 2, 2020-11-20, 14 Company 2, 2020-11-21, 15 ]; Left Join(tab2) tab1: LOAD * INLINE [ Company, Approved Date Company 1, 2020-03-17 Company 2, 2020-11-15 ]; Left Join(tab2) LOAD Company, Sum(Amount) As Sum_Amount Resident tab2 Where [Transaction Date]>[Approved Date] Group By Company;

topic Re: Count Data After a date from another table in App Development

Count Data After a date from another table

Re: Count Data After a date from another table