hello sir,
i have following query
declare @month int
declare @year int
set @month=8
set @year =2014
select * from (
select CONVERT(varchar,s1.edatetime,103) as [date], AVG(s1.alumina) as alumina,
AVG(s1.caustic) as caustic,
AVG(s1.ratio) as ratio,
AVG(s2.alumina) as fsfalumina,
AVG(s2.caustic) as fsfcaustic,
AVG(s2.ratio) as fsfratio,
AVG(l1.nt_6o) as nt_6o,
AVG(l1.nt_6u) as nt_6u,
AVG(l2.density) as nt_6_density,
AVG(l2.solid) as nt_6_solid,
AVG(d1.sodagpldms) as sodagpldms1,
AVG(d1.soliddms) as soliddms1,
AVG(d2.mudmgpl) as mudmgpldms2,
AVG(d2.sodagplpf) as sodagplpfdms2,
AVG(d2.solidpf) as solidpfdms2,
AVG(e1.gpl_soda) as gplsodae1,
avg(e1.ph) as phe1,
AVG(s3.nt_2) as nt_2s3,
AVG(p1.pglmud) as pglmudp1,
AVG(p2.ofmgpl) as ofmgplp2,
AVG(p3.density) as densityp3,
AVG(p3.solid) as solidp3,
AVG(h1.hrddensity) as hrddensityh1,
AVG(h1.hrdsolid) as hrdsolidh1,
AVG(h1.ufdensity) as ufdensityh1,
AVG(h1.ufsolid) as ufsolidh1,
AVG(p4.mud) as mudp4,
AVG(tu1.density) as ntu1density,
AVG(tu1.soda) as ntu1so da,
AVG(tu1.solid) as ntu1solid,
AVG(tu2.density) as ntu2density,
AVG(tu2.soda) as ntu2soda,
AVG(tu2.solid) as ntu2solid,
AVG(tu3.density) as ntu3density,
AVG(tu3.soda) as ntu3soda,
AVG(tu3.solid) as ntu3solid,
AVG(tu4.density) as ntu4density,
AVG(tu4.soda) as ntu4soda,
AVG(tu4.solid) as ntu4solid,
AVG(tu5.density) as ntu5density,
AVG(tu5.soda) as ntu5soda,
AVG(tu5.solid) as ntu5solid,
AVG(tu6.density) as ntu6density,
AVG(tu6.soda) as ntu6soda,
AVG(tu6.solid) as ntu6solid,
AVG(to1.soda) as nto1soda,
AVG(to2.soda) as nto2soda,
AVG(to3.soda) as nto3soda,
AVG(to4.soda) as nto4soda,
AVG(to5.soda) as nto5soda,
AVG(to6.soda) as nto6soda
from caustic_batch_tank s1
full join caustic_batch_tank_fsf s2
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,s2.edatetime,103)
full join last_wash_gpl_soda l1
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,l1.edatetime,103)
full join last_wash_nt_6 l2
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,l2.edatetime,103)
full join dms1 d1
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,d1.edatetime,103)
full join dms2 d2
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,d2.edatetime,103)
full join evaporator_cooling_tower e1
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,e1.edatetime,103)
full join suspended_mud s3
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,s3.edatetime,103)
full join pgl_tank_mgpl_mud p1
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,p1.edatetime,103)
full join pd_tank p2
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,p2.edatetime,103)
full join pd_tank2 p3
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,p3.edatetime,103)
full join hrd_feed h1
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,h1.edatetime,103)
full join pfmud p4
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,p4.edatetime,103)
full join thickeners_u tu1
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,tu1.edatetime,103)
full join thickeners_u tu2
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,tu2.edatetime,103)
full join thickeners_u tu3
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,tu3.edatetime,103)
full join thickeners_u tu4
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,tu4.edatetime,103)
full join thickeners_u tu5
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,tu5.edatetime,103)
full join thickeners_u tu6
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,tu6.edatetime,103)
full join thickeners_o to1
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,to1.edatetime,103)
full join thickeners_o to2
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,to2.edatetime,103)
full join thickeners_o to3
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,to3.edatetime,103)
full join thickeners_o to4
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,to4.edatetime,103)
full join thickeners_o to5
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,to5.edatetime,103)
full join thickeners_o to6
on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,to6.edatetime,103)
where s1.edatetime>='2014-08-01'
or s2.edatetime >= '2014-08-01'
or l1.edatetime>='2014-08-01'
or l2.edatetime>='2014-08-01'
or d1.edatetime>='2014-08-01'
or d2.edatetime>='2014-08-01'
or e1.edatetime>='2014-08-01'
or s3.edatetime>='2014-08-01'
or p1.edatetime>='2014-08-01'
or (p2.edatetime>='2014-08-01' and p2.valstatus='Not Mud')
or p3.edatetime>='2014-08-01'
or h1.edatetime>='2014-08-01'
or p4.edatetime>='2014-08-01'
or (tu1.edatetime>='2014-08-01' and tu1.sample='NT - 1')
or (tu2.edatetime>='2014-08-01' and tu2.sample='NT - 2')
or (tu3.edatetime>='2014-08-01' and tu3.sample='NT - 3')
or (tu4.edatetime>='2014-08-01' and tu4.sample='NT - 4')
or (tu5.edatetime>='2014-08-01' and tu5.sample='NT - 5')
or (tu6.edatetime>='2014-08-01' and tu6.sample='NT - 6')
or (to1.edatetime>='2014-08-01' and to1.sample='NT - 1')
or (to2.edatetime>='2014-08-01' and to2.sample='NT - 2')
or (to3.edatetime>='2014-08-01' and to3.sample='NT - 3')
or (to4.edatetime>='2014-08-01' and to4.sample='NT - 4')
or (to5.edatetime>='2014-08-01' and to5.sample='NT - 5')
or (to6.edatetime>='2014-08-01' and to6.sample='NT - 6')
group by CONVERT(varchar,s1.edatetime,103))
as query
order by query.date asc
it shows averages date wise within a month but it takes 8-9 hours to execute.
i want to simplify this query to execute in less time.
plz help me