How get matching and unmatching

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

hi,
following I am getting only matching entry not getting unmatching entry. I want both entry. if Transaction_date match both table then display both table amount ( C.CHEST_AMOUNT, C.BANK_AMOUNT) and if Transaction date is not matching then both table then display either C.CHEST_AMOUNT or C.BANK_AMOUNT.. Because i want display client when collection and bank entry done by data entry. if data entry done collection entry and bank entry make next date then I will display date wise entry and if data entry make both entry same date then it will must be display same entry.. i want display Transaction date wise entry..

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	If you are using SQL server 2005 later... Try this... 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 DISTINCT TC.DIV_ID As 'ID', TC.TRANSACTION_DATE, sum(TC.cash + TC.cheque) OVER(PARTITION BY TC.ID, TC.TRANSACTION_DATE) 'Chest_Amount' , sum(TD.total_amount) OVER(PARTITION BY TD.ID, TD.TRANSACTION_DATE) 'Bank_Amount' From tbl_chest_collection TC JOIN tbl_bank_collection TD ON TC.ID = TD.ID ) C ON C.ID = td.ID NOTE: Provide sample input data and expected output so that its easy to get quick response 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

If you are using SQL server 2005 later...
Try this...

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 DISTINCT TC.DIV_ID As 'ID', TC.TRANSACTION_DATE, sum(TC.cash + TC.cheque) OVER(PARTITION BY TC.ID, TC.TRANSACTION_DATE) 'Chest_Amount' , sum(TD.total_amount) OVER(PARTITION BY TD.ID, TD.TRANSACTION_DATE) 'Bank_Amount' 
         From tbl_chest_collection TC  
         JOIN tbl_bank_collection TD ON  TC.ID = TD.ID 
         ) C
	ON  C.ID = td.ID

NOTE: Provide sample input data and expected output so that its easy to get quick response

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	Have you done with the above query? If NOT post some sample data and explain the output... I'm not getting your above explanation.. 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	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