I am trying to write this query. But problem is that my output is showing only matching transaction date. I want display those are matching only that date and those transaction is not matching that date also. Because client want which date is CHEST_AMOUNT,BANK_AMOUNT is been done by operator.
So if trasaction_Date is done by that same entry then display is CHEST_AMOUNT,BANK_AMOUNT amount. But if trasaction_Date is done by different date then it must be display i.e.
ID Division CHEST_AMOUNT BANK_AMOUNT Transaction_Date
46 THANE 1100884 1100884 1/11/2013
46 THANE 120000 2/11/2013
46 THANE 120000 3/11/2013
46 THANE 470056 470056 31-10-2013
Select TC.DIV_ID As 'ID', TC.TRANSACTION_DATE, sum(TC.cash + TC.cheque) 'Chest_Amount'
From tbl_chest_collection TC
Group By TC.DIV_ID, TC.TRANSACTION_DATE
Order By TC.TRANSACTION_DATE Desc
Select TD.DIV_ID As 'ID', sum(TD.total_amount) 'Bank_Amount', TD.TRANSACTION_DATE
From tbl_bank_collection TD
Group By TD.DIV_ID, TD.TRANSACTION_DATE
Order By TD.TRANSACTION_DATE Desc
Select B.ID, Upper(D.NAME) As 'Division', C.CHEST_AMOUNT, B.BANK_AMOUNT, CONVERT(NVARCHAR, C.TRANSACTION_DATE,105) 'Transaction_Date'
From #CHEST_B C
Inner Join #BANK_B B On C.ID = B.ID And C.TRANSACTION_DATE = B.TRANSACTION_DATE
Inner Join tbl_div D On (C.ID = D.ID Or B.ID = D.ID)
Drop Table #CHEST_B
Drop Table #BANK_B