quote:

---

Originally posted by sateesh.sqldba


ALTER Proc [dbo ].[ic_Proc_IBA]
(@yr int, @qtr int, @mn int, @wk int, @Zone varchar(50), @Area varchar(50), @LCT varchar(50),
@business varchar(50), @Modality varchar(50), @subMod varchar(50), @report_type varchar(50)) as
Begin
SET NOCOUNT ON



if @report_type = 'Management'

BEGIN
Create table #temp_1 (yr numeric, mn numeric, zone varchar(100), cnt numeric)
insert into #temp_1
SELECT Yr Yr2, Mn Mn2, Zone COL1,SUM(sid) from(
select Tm.Yr,Tm.Mn,RM.Zone,
count(*) sid from ic IBA Inner Join
ic_LCTMapping RM on IBA.LCT = RM.Center_Description
Inner Join
(Select distinct m.Mn_Id, m.mn, q.yr from C_Time_Wk w, C_Time_Mn m, C_Time_Qtr q


Where q.qtr_id = m.qtr_id and q.yr_id = m.yr_id and q.qtr_id = w.qtr_id and q.yr_id = w.yr_id and


w.mn_id = m.mn_id and w.yr_id = m.yr_id) Tm on IBA.Mn_Id = Tm.Mn_Id
where Tm.Mn_Id between @mn_id - 11 and @mn_id
and RM.Zone = isnull(@Zone, RM.Zone) and
RM.LCT = isnull(@LCT, RM.LCT) Group by Tm.Yr,Tm.Mn,RM.Zone )y
Group by Yr,Mn,Zone order by 3,1,2

Create table #temp_2 (yr numeric, mn numeric, zone varchar(100), cnt numeric)
insert into #temp_2
SELECT Yr Yr2, Mn Mn2, Zone COL1,SUM(sid) from(
select Tm.Yr,Tm.Mn,RM.Zone,
count(*) sid from ic_Defects IBA Inner Join
ic_LCTMapping RM on IBA.LCT = RM.Center_Description
Inner Join
(Select distinct m.Mn_Id, m.mn, q.yr from C_Time_Wk w, C_Time_Mn m, C_Time_Qtr q


Where q.qtr_id = m.qtr_id and q.yr_id = m.yr_id and q.qtr_id = w.qtr_id and q.yr_id = w.yr_id and


w.mn_id = m.mn_id and w.yr_id = m.yr_id) Tm on IBA.Mn_Id = Tm.Mn_Id
where Tm.Mn_Id between @mn_id - 11 and @mn_id
and RM.Zone = isnull(@Zone, RM.Zone) and
RM.LCT = isnull(@LCT, RM.LCT) Group by Tm.Yr,Tm.Mn,RM.Zone )y
Group by Yr,Mn,Zone order by 3,1,2

Select a.Yr, a.Mn, a.Zone, cast(((1-(b.Cnt/ a.Cnt))*100) as numeric) from #temp_1 a
left outer join #temp_2 b on a.yr = b.yr and a.mn = b.mn and a.zone = b.zone


END

SET NOCOUNT off


end




this query is working fine but we r using temp tables here with out that con't we make this

main thing we could need to group yr,mn,Zone there is only on change in two temp tables

con't we?


sateesh

---

quote:

---

Originally posted by Idera

Is there any relationship between the 2 tables?


Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

---

quote:

---

Originally posted by sateesh.sqldba

yes i have two columns zone,lct

quote:

---

Originally posted by Idera

Is there any relationship between the 2 tables?


Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

---

sateesh

---