How Get ID, division from table tbl_bank_collection also

Posted by Cpatil1000 under Sql Server on 11/12/2013 | Points: 10 | Views : 951 | Status : [Member] | Replies : 6
Hi,
First all of thanks for help.
This sql query is runnig well but problem is that if there is no TC.Chest_Amount amount then it must be display 0 else TC.Chest_Amount and if there is no TB.Bank_Amount then display 0 else TB.Bank_Amount. But here is not display id and division in this sql query. So I am getting confuse to display division name because how know client the bank is which division/id. Because it is display id with null and division with null..
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

The following is table data
1) tbl_bank_collection
div_id transaction_date total_amount
67 2012-10-01 81400.00
70 2012-10-01 10598.00
70 2012-10-01 10598.00
70 2012-10-01 336360.00
70 2012-10-03 1000.00
67 2012-10-03 40241.00
70 2012-10-04 114167.00
70 2012-10-05 9900.00
70 2012-10-05 40966.00
67 2012-10-04 200.00
67 2012-10-09 450.00
67 2012-10-09 150.00
70 2012-10-10 26000.00
70 2012-10-11 132896.00
70 2012-10-12 100.00
40 2012-10-15 1000000.00


2) tbl_chest_collection
div_id transaction_date cash cheque
67 2012-07-31 0.00 67140.00
67 2012-08-01 40.00 0.00
42 2012-08-01 100.00 0.00
42 2012-08-01 100.00 0.00
42 2012-08-01 100.00 0.00
42 2012-08-01 100.00 0.00
54 2012-08-01 2000.00 0.00
54 2012-08-01 0.00 9410.00
40 2012-08-01 0.00 250000.00
63 2012-08-01 990.00 0.00
63 2012-08-01 2160.00 33373.00
63 2012-08-01 0.00 90139.00
68 2012-08-01 200.00 0.00
68 2012-08-01 0.00 128386.00
70 2012-08-02 0.00 500000.00
68 2012-08-02 1000.00 0.00
68 2012-08-02 0.00 56808.00
68 2012-08-03 0.00 46216.00
54 2012-08-03 0.00 11764.00
54 2012-08-03 0.00 3500000.00
54 2012-08-03 0.00 500000.00
63 2012-08-03 0.00 22372.00


3) tbl_div
id name
21 newpanvel
37 ambarnath
38 satara
40 sangali
41 nashik
42 aurangabad
43 gondia
44 amravati
45 yavatmal
46 thane
47 virar
48 mangaon
49 ratnagiri
50 kankavali
51 pune




Responses

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

Up
0
Down
For getting 0 in the case of NULL of BANK_Amount and CHEST_AMount
SELECT td.ID, Upper(td.NAME) AS Division
, COALESCE(TC.Chest_Amount, 0) Chest_Amount
, COALESCE(TB.Bank_Amount, 0) Bank_Amount
, CONVERT(NVARCHAR, COALESCE(TC.TRANSACTION_DATE, TB.TRANSACTION_DATE) ,105) 'Transaction_Date'
FROM

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

Up
0
Down
Can you post expected output for the above sample data?

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

Up
0
Down
Here is the query you want...

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


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

Up
0
Down
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

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

Up
0
Down
Sample script based on your sample data:



CREATE TABLE tbl_bank_collection (div_id int, transaction_date DATE, total_amount INT)
insert tbl_bank_collection
SELECT 67 , '2012-10-01', 81400.00 union all
SELECT 70 , '2012-10-01', 10598.00 union all
SELECT 70 , '2012-10-01', 10598.00 union all
SELECT 70 , '2012-10-01', 336360.00 union all
SELECT 70 , '2012-10-03', 1000.00 union all
SELECT 67 , '2012-10-03', 40241.00 union all
SELECT 70 , '2012-10-04', 114167.00 union all
SELECT 70 , '2012-10-05', 9900.00 union all
SELECT 70 , '2012-10-05', 40966.00 union all
SELECT 67 , '2012-10-04', 200.00 union all
SELECT 67 , '2012-10-09', 450.00 union all
SELECT 67 , '2012-10-09', 150.00 union all
SELECT 70 , '2012-10-10', 26000.00 union all
SELECT 70 , '2012-10-11', 132896.00 union all
SELECT 70 , '2012-10-12', 100.00 union all
SELECT 40 , '2012-10-15', 1000000.00


CREATE TABLE tbl_chest_collection (div_id int, transaction_date date, cash int, cheque int)
insert tbl_chest_collection
SELECT 67 , '2012-07-31', 0.00 ,67140.00 union all
SELECT 67 , '2012-08-01', 40.00 ,0.00 union all
SELECT 42 , '2012-08-01', 100.00 ,0.00 union all
SELECT 42 , '2012-08-01', 100.00 ,0.00 union all
SELECT 42 , '2012-08-01', 100.00 ,0.00 union all
SELECT 42 , '2012-08-01', 100.00 ,0.00 union all
SELECT 54 , '2012-08-01', 2000.00, 0.00 union all
SELECT 54 , '2012-08-01', 0.00 ,9410.00 union all
SELECT 40 , '2012-08-01', 0.00 ,250000.00 union all
SELECT 63 , '2012-08-01', 990.00, 0.00 union all
SELECT 63 , '2012-08-01', 2160.00, 33373.00 union all
SELECT 63 , '2012-08-01', 0.00 ,90139.00 union all
SELECT 68 , '2012-08-01', 200.00, 0.00 union all
SELECT 68 , '2012-08-01', 0.00, 128386.00 union all
SELECT 70 , '2012-08-02', 0.00, 500000.00 union all
SELECT 68 , '2012-08-02', 1000.00, 0.00 union all
SELECT 68 , '2012-08-02', 0.00, 56808.00 union all
SELECT 68 , '2012-08-03', 0.00, 46216.00 union all
SELECT 54 , '2012-08-03', 0.00, 11764.00 union all
SELECT 54 , '2012-08-03', 0.00, 3500000.00 union all
SELECT 54 , '2012-08-03', 0.00, 500000.00 union all
SELECT 63, '2012-08-03', 0.00 ,22372.00

CREATE TABLE tbl_div (id int, name varchar(40))
insert tbl_div SELECT 67 , '67th division' union all
SELECT 21 , 'newpanvel' union all
SELECT 37 , 'ambarnath' union all
SELECT 38 , 'satara' union all
SELECT 40 , 'sangali' union all
SELECT 42 , 'aurangabad' union all
SELECT 49 , 'ratnagiri' union all
SELECT 45 , 'yavatmal' union all
SELECT 46 , 'thane' union all
SELECT 47 , 'virar' union all
SELECT 51, 'pune' union all
SELECT 54 , 'amravati' union all
SELECT 63 , 'gondia' union all
SELECT 68 , 'mangaon' union all
SELECT 70 , 'nashik' union all
SELECT 80 , 'kankavali'


SELECT COALESCE(TC.ID, TB.ID) ID
, Upper(td.NAME) AS Division
, COALESCE(TC.Chest_Amount, 0) Chest_Amount , COALESCE(TB.Bank_Amount, 0) BANK_Amount
, CONVERT(NVARCHAR, COALESCE(TC.TRANSACTION_DATE, TB.TRANSACTION_DATE) ,105) 'Transaction_Date'
FROM (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
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
LEFT JOIN tbl_div td ON td.ID = TC.ID OR td.ID = TB.ID
ORDER BY ID

--OUTPUT
ID Division Chest_Amount BANK_Amount Transaction_Date
40 SANGALI 250000 0 01-08-2012
40 SANGALI 0 1000000 15-10-2012
42 AURANGABAD 400 0 01-08-2012
54 AMRAVATI 11410 0 01-08-2012
54 AMRAVATI 4011764 0 03-08-2012
63 GONDIA 22372 0 03-08-2012
63 GONDIA 126662 0 01-08-2012
67 67TH DIVISION 40 0 01-08-2012
67 67TH DIVISION 0 40241 03-10-2012
67 67TH DIVISION 0 600 09-10-2012
67 67TH DIVISION 0 81400 01-10-2012
67 67TH DIVISION 0 200 04-10-2012
67 67TH DIVISION 67140 0 31-07-2012
68 MANGAON 46216 0 03-08-2012
68 MANGAON 128586 0 01-08-2012
68 MANGAON 57808 0 02-08-2012
70 NASHIK 500000 0 02-08-2012
70 NASHIK 0 357556 01-10-2012
70 NASHIK 0 114167 04-10-2012
70 NASHIK 0 26000 10-10-2012
70 NASHIK 0 132896 11-10-2012
70 NASHIK 0 50866 05-10-2012
70 NASHIK 0 100 12-10-2012
70 NASHIK 0 1000 03-10-2012


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/30/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
"Mark it as Answer " it will help others who face this kind of problem

Simply click on the "Mark as Answer " link

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

Login to post response