Need query RSS

• • Reply
  

  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

• • Reply
  

  sandeepmitta...

  Contributor

  6767 Points

  1057 Posts

  Re: Need query

  Dec 08, 2012 06:08 AM|LINK

  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

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
• • Reply
  

  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

  • Edited by Priya_here on Dec 08, 2012 06:22 AM
• • Reply
  

  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

  
  

• • Reply
  

  sandeepmitta...

  Contributor

  6767 Points

  1057 Posts

  Re: Need query

  Dec 08, 2012 06:39 AM|LINK

  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

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by sandeepmittal11 on Dec 08, 2012 06:42 AM
• • Reply
  

  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..?

  
  

  • Edited by Priya_here on Dec 08, 2012 06:55 AM
  • Edited by Priya_here on Dec 08, 2012 06:56 AM
• • Reply
  

  sandeepmitta...

  Contributor

  6767 Points

  1057 Posts

  Re: Need query

  Dec 08, 2012 06:54 AM|LINK

  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

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by sandeepmittal11 on Dec 08, 2012 06:54 AM
  • Marked as answer by Priya_here on Dec 08, 2012 08:10 AM
• • Reply
  

  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..?

  • Edited by Priya_here on Dec 08, 2012 06:59 AM
• • Reply
  

  sandeepmitta...

  Contributor

  6767 Points

  1057 Posts

  Re: Need query

  Dec 08, 2012 07:10 AM|LINK

  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

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Marked as answer by Priya_here on Dec 08, 2012 08:10 AM