How Get ID, division from table tbl_bank_collection also

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

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

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

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

0	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

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

0	"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

Latest Posts