How display amount of with transaction date if match or not

Posted by Cpatil1000 under Sql Server on 11/9/2013 | Points: 10 | Views : 1605 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,
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

Sql queries

Select TC.DIV_ID As 'ID', TC.TRANSACTION_DATE, sum(TC.cash + TC.cheque) 'Chest_Amount'
Into #CHEST_B
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
Into #BANK_B
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

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 11/10/2013 [Member] [MVP] Platinum | Points: 25

0	Hi, Your query can be rewritten as follows: Select C.ID, Upper(td.NAME) As 'Division', C.CHEST_AMOUNT, C.BANK_AMOUNT, CONVERT(NVARCHAR, C.TRANSACTION_DATE,105) 'Transaction_Date' From tbl_div td JOIN (Select TC.DIV_ID As 'ID', TC.TRANSACTION_DATE, sum(TC.cash + TC.cheque) 'Chest_Amount' , sum(TD.total_amount) 'Bank_Amount' From tbl_chest_collection TC JOIN tbl_bank_collection TD ON TC.ID = TD.ID And TC.TRANSACTION_DATE = TD.TRANSACTION_DATE Group By TC.DIV_ID, TC.TRANSACTION_DATE ) C ON C.ID = td.ID NOTE: Post us back with some sample data of Bank and Chest tables and expected output... I will provide you the solution Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Cpatil1000, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,
Your query can be rewritten as follows:

	Select C.ID, Upper(td.NAME) As 'Division', C.CHEST_AMOUNT, C.BANK_AMOUNT, CONVERT(NVARCHAR, C.TRANSACTION_DATE,105) 'Transaction_Date' 
	From tbl_div td
	JOIN (Select TC.DIV_ID As 'ID', TC.TRANSACTION_DATE, sum(TC.cash + TC.cheque) 'Chest_Amount' , sum(TD.total_amount) 'Bank_Amount' 
         From tbl_chest_collection TC  
         JOIN tbl_bank_collection TD ON  TC.ID = TD.ID And TC.TRANSACTION_DATE = TD.TRANSACTION_DATE 
         Group By TC.DIV_ID, TC.TRANSACTION_DATE  
         ) C
	ON  C.ID = td.ID

NOTE: Post us back with some sample data of Bank and Chest tables and expected output...
I will provide you the solution

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cpatil1000, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/11/2013 [Member] [MVP] Platinum | Points: 25

0	Provide sample data and expected output Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Cpatil1000, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 11/11/2013 [Member] [MVP] Platinum | Points: 25

0	Is this?? SELECT td.ID, Upper(td.NAME) AS Division, TC.Chest_Amount, TB.Bank_Amount , CONVERT(NVARCHAR, COALESCE(TC.TRANSACTION_DATE, TB.TRANSACTION_DATE) ,105) 'Transaction_Date' FROM tbl_div td JOIN (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 ) TC ON td.ID = TC.ID FULL JOIN ( 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 ) TB On TC.ID = TB.ID And TC.TRANSACTION_DATE = TB.TRANSACTION_DATE Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Cpatil1000, if this helps please login to Mark As Answer. \| Alert Moderator

Is this??

SELECT td.ID, Upper(td.NAME) AS Division, TC.Chest_Amount, TB.Bank_Amount
, CONVERT(NVARCHAR, COALESCE(TC.TRANSACTION_DATE, TB.TRANSACTION_DATE) ,105) 'Transaction_Date' 
FROM tbl_div td
JOIN (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 
	) TC ON td.ID = TC.ID
FULL JOIN (	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 ) TB
 On TC.ID = TB.ID And TC.TRANSACTION_DATE = TB.TRANSACTION_DATE

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cpatil1000, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/12/2013 [Member] [MVP] Platinum | Points: 25

0	Click on Mark as Answer Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Cpatil1000, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts