select a.RefNo b.BatchNo, coalesce(c.Pcs, b.Pcs, a.Pcs)
from Table1 a
left join Table2 b on a.BatchNo = b.BatchNo
left join Table3 c on a.BatchNo = c.BatchNo
declare @table1 table( RefNo varchar(10), BatchNo varchar(10), Pcs varchar(10))
insert into @table1
select '001','B1', '50' union all
select '001','B2', '10' union all
select '002','B3', '30' union all
select '002','B4', '20' union all
select '003','B5', '40'
declare @table2 table(BatchNo varchar(10), NewBatchNo varchar(10), Pcs varchar(10))
insert into @table2
select 'B1','B1','40' union all
select 'B2','B2','10' union all
select 'B3','B3','30' union all
select 'B4','B4','20' union all
select 'B5','B5','25' union all
select 'B5','B5/A','15'
declare @table3 table(BatchNo varchar(10), Pcs varchar(10))
insert into @table3
select 'B1', '30' union all
select 'B2', null union all
select 'B3', '20' union all
select 'B4', null union all
select 'B5', '25'
select a.RefNo, b.NewBatchNo
, case when b.BatchNo<>b.NewBatchNo then b.Pcs else coalesce(c.Pcs, b.Pcs, a.Pcs) end as Pcs
from @table1 a
inner join @table2 b on b.BatchNo = a.BatchNo
inner join @table3 c on c.BatchNo = b.BatchNo
declare @table1 table( RefNo varchar(10), BatchNo varchar(10), Pcs varchar(10))
insert into @table1
select '001','B1', '50' union all
select '001','B2', '10' union all
select '002','B3', '30' union all
select '002','B4', '20' union all
select '003','B5', '40'
declare @table2 table(BatchNo varchar(10), NewBatchNo varchar(10), Pcs varchar(10))
insert into @table2
select 'B1','B1','40' union all
select 'B2','B2','10' union all
select 'B3','B3','30' union all
select 'B4','B4','20' union all
select 'B5','B5','25' union all
select 'B5','B5/A','15'
declare @table3 table(BatchNo varchar(10), Pcs varchar(10))
insert into @table3
select 'B1', '30' union all
select 'B2', null union all
select 'B3', '20' union all
select 'B4', null union all
select 'B5', '25'
select a.RefNo, SUM(cast(case when b.BatchNo<>b.NewBatchNo then b.Pcs else coalesce(c.Pcs, b.Pcs, a.Pcs) end as int)) as Pcs
from @table1 a
inner join @table2 b on b.BatchNo = a.BatchNo
inner join @table3 c on c.BatchNo = b.BatchNo
group by a.RefNo
declare @table1 table( RefNo varchar(10), BatchNo varchar(10), Pcs varchar(10))
insert into @table1
select '001','B1', '50' union all
select '001','B2', '10' union all
select '002','B3', '30' union all
select '002','B4', '20' union all
select '003','B5', '40'
declare @table2 table(BatchNo varchar(10), NewBatchNo varchar(10), Pcs varchar(10))
insert into @table2
select 'B1','B1','40' union all
select 'B2','B2','10' union all
select 'B3','B3','30' union all
select 'B4','B4','20' union all
select 'B5','B5','25' union all
select 'B5','B5/A','15'
declare @table3 table(BatchNo varchar(10), Pcs varchar(10))
insert into @table3
select 'B1', '30' union all
select 'B2', null union all
select 'B3', '20' union all
select 'B4', null union all
select 'B5', '25'
declare @table4 table(RefNo varchar(10), Pcs varchar(10))
insert into @table4
select '001', '05' union all
select '001', '15' union all
select '003', '40'
select t1.RefNo , t1.Pcs-isnull(t2.Pcs,0) as PCs
from (
select a.RefNo, SUM(cast(case when b.BatchNo<>b.NewBatchNo then b.Pcs else coalesce(c.Pcs, b.Pcs, a.Pcs) end as int)) as Pcs
from @table1 a
inner join @table2 b on b.BatchNo = a.BatchNo
inner join @table3 c on c.BatchNo = b.BatchNo
group by a.RefNo
) t1 left join (
select RefNo, SUM(CAST(Pcs as int)) as Pcs
from @table4
group by RefNo
) t2 on t1.RefNo = t2.RefNo
where t1.Pcs - isnull(t2.Pcs,0) <> 0
Priya_here
Member
709 Points
1854 Posts
Need query
Dec 08, 2012 05:56 AM|LINK
This is my table1
RefNo BatchNo Pcs
001 B1 50
001 B2 10
002 B3 30
002 B4 20
003 B5 40
This is my table2
BatchNo Pcs
B1 40
B2 10
B3 30
B4 05
B5 40
This is my table3
BatchNo Pcs
B1 30
B2 Null
B3 20
B4 Null
B5 45
Now I need the output like this
RefNo BatchNo Pcs
001 B1 30
001 B2 10
002 B3 20
002 B4 05
003 B5 45
sandeepmitta...
Contributor
6767 Points
1057 Posts
Re: Need query
Dec 08, 2012 06:08 AM|LINK
Sandeep Mittal | My Blog - IT Developer Zone
Priya_here
Member
709 Points
1854 Posts
Re: Need query
Dec 08, 2012 06:14 AM|LINK
check this
This is my table1
RefNo BatchNo Pcs
001 B1 50
001 B2 10
002 B3 30
002 B4 20
003 B5 40
This is my table2
BatchNo Pcs
B1 40
B2 10
B3 30
B4 05
B5 25
B5/A 15
<div></div>This is my table3
BatchNo Pcs
B1 30
B2 Null
B3 20
B4 Null
B5 25
B5/A 15
Now I need the output like this
RefNo BatchNo Pcs
001 B1 30
001 B2 10
002 B3 20
002 B4 05
003 B5 25
003 B5/A 15
Priya_here
Member
709 Points
1854 Posts
Re: Need query
Dec 08, 2012 06:32 AM|LINK
This is my complete requirment:-
This is my table1
RefNo BatchNo Pcs
001 B1 50
001 B2 10
002 B3 30
002 B4 20
003 B5 40
This is my table2
BatchNo NewBatchNo Pcs
B1 B1 40
B2 B2 10
B3 B3 30
B4 B4 20
B5 B5 25
B5 B5/A 15
This is my table3
BatchNo Pcs
B1 30
B2 Null
B3 20
B4 Null
B5 25
Now I need the output like this
RefNo BatchNo Pcs
001 B1 30
001 B2 10
002 B3 20
002 B4 20
003 B5 25
003 B5/A 15
sandeepmitta...
Contributor
6767 Points
1057 Posts
Re: Need query
Dec 08, 2012 06:39 AM|LINK
Sandeep Mittal | My Blog - IT Developer Zone
Priya_here
Member
709 Points
1854 Posts
Re: Need query
Dec 08, 2012 06:53 AM|LINK
My final requirment:-
This is my complete requirment:-
This is my table1
RefNo BatchNo Pcs
001 B1 50
001 B2 10
002 B3 30
002 B4 20
003 B5 40
This is my table2
BatchNo NewBatchNo Pcs
B1 B1 40
B2 B2 10
B3 B3 30
B4 B4 20
B5 B5 25
B5 B5/A 15
This is my table3
BatchNo Pcs
B1 30
B2 Null
B3 20
B4 Null
B5 25
This is my table 4 (goes subtract)
RefNo Pcs
001 15
003 40
Now I need the output like this
RefNo Pcs
001 10
001 15
002 40
-- here 003 wont come coz its zero now...?
how to get this..?
sandeepmitta...
Contributor
6767 Points
1057 Posts
Re: Need query
Dec 08, 2012 06:54 AM|LINK
Sandeep Mittal | My Blog - IT Developer Zone
Priya_here
Member
709 Points
1854 Posts
Re: Need query
Dec 08, 2012 06:57 AM|LINK
My final requirment:-
This is my complete requirment:-
This is my table1
RefNo BatchNo Pcs
001 B1 50
001 B2 10
002 B3 30
002 B4 20
003 B5 40
This is my table2
BatchNo NewBatchNo Pcs
B1 B1 40
B2 B2 10
B3 B3 30
B4 B4 20
B5 B5 25
B5 B5/A 15
This is my table3
BatchNo Pcs
B1 30
B2 Null
B3 20
B4 Null
B5 25
This is my table 4 (goes subtract)
RefNo Pcs
001 05
001 15
003 40
Now I need the output like this
RefNo Pcs
001 20
002 40
-- here 003 wont come coz its zero now...?
how to get this..?
sandeepmitta...
Contributor
6767 Points
1057 Posts
Re: Need query
Dec 08, 2012 07:10 AM|LINK
Sandeep Mittal | My Blog - IT Developer Zone