Question: Concatenate values from multiple columns of 3 tables and display in one column. Date field is datetime.now my problem is if date is repeat of one table then amt2,amt3 is also repeat on same date
table1:
date amt1
-----------------
1-1-2016 111
1-1-2016 222 repeat date
3-4-2016 111
table2:
date amt2
-----------------
1-1-2016 101
2-2-2016 333
2-3-2016 444
3-3-2016 456
1-4-2016 101
3-4-2016 111
table3:
date amt3
-----------------
2-2-2016 001
2-3-2016 002
3-3-2016 003
1-4-2016 555
2-4-2016 666
3-4-2016 777
query
SELECT
COALESCE(t1.date, t2.date, t3.date)
, t1.amount
, t2.cashamount
, t3.grandtotal
FROM tb_amount t1
FULL JOIN tb_cashamt t2 ON t1.date =t2.date
FULL JOIN tb_grandtotal t3 ON t2.date = t3.date
output:
date amt1 amt2 amt3
----------------------------------
1-1-2016 111 101 NULL data repeat on amt2 and amt3
1-1-2016 222 101 null
2-3-2016 NULL 444 002
3-3-2016 NULL 456 003
------------------------------------
TOTAL --- --- -----
Desired output:
date amt1 amt2 amt3
----------------------------------
1-1-2016 333 101 NULL
2-3-2016 NULL 444 002
3-3-2016 NULL 456 003
------------------------------------
TOTAL --- --- -----
What I have tried:
Concatenate values from multiple columns of 3 tables and display in one column. Date field is smalldatetime.now my problem is if date is repeat of one table then amt2,amt3 is also repeat on same date.i explain desired output in the same date total amt1,amt2,amt3 display according to date.