SQLTeam.com | Weblogs | Forums

Fetch the data from both tables based on the aggregation match


#1

I have Table A with below data
SK ACCOUNT LOC TIMESTAMP DATE VALUE
100 ABC BAN 2016-03-15 04:13:00 2016-03-15 0.1
101 ABC BAN 2016-03-15 04:13:00 2016-03-15 0.2
102 CDE HYD 2016-03-15 04:13:00 2016-03-15 0.3
103 EFG BAN 2016-03-15 04:13:00 2016-03-15 0.4

table B with below data
SK ACCOUNT LOC TIMESTAMP DATE VALUE
200 ABC BAN 2016-03-15 04:13:00 2016-03-15 0.3
201 CDE HYD 2016-03-15 04:13:00 2016-03-15 0.3
202 EFG BAN 2016-03-15 04:13:00 2016-03-15 0.4

I need output like below
SK_B SK_A DATE TIMESTAMP
200 100 2016-03-15 2016-03-15 04:13:00
200 101 2016-03-15 2016-03-15 04:13:00
201 102 2016-03-15 2016-03-15 04:13:00
202 103 2016-03-15 2016-03-15 04:13:00

Where table is having aggregated value based on the ACCOUNT and LOCATION fields.

If there is no aggregated data output needs like below:
In this scenario table A like
SK ACCOUNT LOC TIMESTAMP DATE VALUE
100 ABC BAN 2016-03-15 04:13:00 2016-03-15 0.1
101 ZZZ BAN 2016-03-15 04:13:00 2016-03-15 0.2
102 CDE HYD 2016-03-15 04:13:00 2016-03-15 0.3
103 EFG BAN 2016-03-15 04:13:00 2016-03-15 0.4

Table B like
SK ACCOUNT LOC TIMESTAMP DATE VALUE
200 ABC BAN 2016-03-15 04:13:00 2016-03-15 0.1
201 ZZZ BAN 2016-03-15 04:13:00 2016-03-15 0.2
202 CDE HYD 2016-03-15 04:13:00 2016-03-15 0.3
203 EFG BAN 2016-03-15 04:13:00 2016-03-15 0.4
204 XXX HYD 2016-02-20 02:00:00 2016-02-20 0.4

Output:
SK_B SK_A DATE TIMESTAMP
200 100 2016-03-15 2016-03-15 04:13:00
201 101 2016-03-15 2016-03-15 04:13:00
202 102 2016-03-15 2016-03-15 04:13:00
203 103 2016-03-15 2016-03-15 04:13:00

Both requirements needs to be handled in one query itself.

Any help is appreciated.

Note: In table B there may be records exist with other date but the data is exist in table A should compare table B date then output should be as expected