Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 the running of the problem query

Author  Topic 

pascal_jimi
Posting Yak Master

167 Posts
Posted - 2014-02-26 : 01:23:21
hi guys
when performing this beautiful inquiry
performed 10-15 minutes and then gives me Results
You can reduce the time and
increase the speed?




select phone,
sum([say umumi]) [say umumi] ,sum([M#601;bl#601;g Umumi]) [M#601;bl#601;g Umumi],
sum([say odenis]) [say odenis],sum([abune odenis]) [abune odenis], sum([say qaliq]) [say qaliq],
sum(case when [mebleg galig]<0 then [mebleg galig] else 0 end) [mebleg galig]  ,sum([Say Qacqin]) [Say Qacqin],
sum([M#601;bl#601;g Qa#231;q#305;n]) [M#601;bl#601;g Qa#231;q#305;n],sum([ say < 1 ay]) [ say < 1 ay] ,sum([M#601;bl#601;g< 1 ay]) [M#601;bl#601;g< 1 ay] ,
sum([ say < 2 ay]) [ say < 2 ay],sum([M#601;bl#601;g< 2 ay]) [M#601;bl#601;g< 2 ay],sum([ say < 3 ay]) [ say < 3 ay]
,sum([M#601;bl#601;g< 3 ay]) [M#601;bl#601;g< 3 ay],sum([ say < 4 ay]) [ say < 4 ay],sum([M#601;bl#601;g< 4 ay]) [M#601;bl#601;g< 4 ay]
 ,sum([ say < 5 ay]) [ say < 5 ay],sum([M#601;bl#601;g< 5 ay]) [M#601;bl#601;g< 5 ay],
sum([ say > 5 ay]) [ say > 5 ay],sum([M#601;blg> 5 ay]) [M#601;blg> 5 ay]
from(select  phone=case when (phone >=3410000  and  phone <=3412287 ) or (phone >=3420000  and  phone <=3429999)
 then N'XIRDALAN'
 when phone >=3490000  and  phone <=3495951 then N'QUSCULUQ'
 when phone >=3400000  and  phone <=3400767 then N'FATMAI'
 when phone >=3402000  and  phone<=3403023 then N'GORADIL BAGLARI'
 when phone >=3404000  and  phone <=3405023 then N'M#399;H#399;MM#399;D#399;LI'
 when phone>=3430000   and  phone <=3434095 then N'MEHDIABAD'
 when phone >=3406000  and  phone <=3407023 then N'GORADIL K#399;NDI'
 when phone >=3408000  and  phone <=3409023 then N'DIGAH'
 when phone >=3440000  and  phone <=3442047 then N'SARAY'
 when phone >=3444000  and  phone <=3445535 then N'CEYRANBATAN'
 when phone >=3450000  and  phone <=3450511 then N'ASAGI GUZD#399;K'
 when phone >=3454000  and  phone <=3454511 then N'YUXARI  GUZD#399;K'
 when phone >=3456000  and  phone <=3456383 then N'CAYLI'
 when phone >=3457000  and  phone <=3458023 then N'QOBU'
 when phone>=3459000   and  phone <=3459511 then N'PIR#399;KUSKUL'
 when phone >=3472000  and  phone <=3472959 then N'AT YALI'
 when phone >=3460000  and  phone <=3465055 then N'SULU-T#399;P#399;'
 when phone >=3476000  and  phone <=3479327 then N'MASAZIR'
 when phone >=3480000  and  phone <=3481471 then N'NOVXANI BAGLARI'
 when phone >=3483000  and  phone <=3483959 then N'NOVXANI '
 when phone >=3474000  and  phone <=3475023 then N'CIC#399;K'
 when phone >=3499000  and  phone <=3499639 then N'N#399;VAI'
 when phone >=3416000  and  phone <=3419007 then N'QURTULUS'
 when phone >=3468000  and  phone <=3469023 then N'HOKM#399;LI'
 when phone >=3470000  and  phone <=3471023 then N'ATCILIQ'
 when phone >=3452000  and  phone <=3453023 then N'YENI QOBU'
 when phone >=3456400  and  phone <=3456911 then N'QOBUSTAN'
 when phone >=3467000  and  phone<=3467639 then N'XOCAH#399;S#399;N'
 when phone>=4080000   and  phone<=4099999 then N'CDMA'
 when phone >=5129000  and  phone <=5129099 then N'FHN'
 when phone >=3499950  and  phone <=3499999 then N'QAFQAZ UNV'
 when phone >=3414000  and  phone <=3415023 then N'Z#399;NGILAN QAC/S#399;H'
 else N'Nam#601;lum' END,[say umumi],[M#601;bl#601;g Umumi],[say odenis],[abune odenis],[say qaliq],[mebleg galig],[Say Qacqin],
[M#601;bl#601;g Qa#231;q#305;n],[ say < 1 ay],[M#601;bl#601;g< 1 ay],[ say < 2 ay],[M#601;bl#601;g< 2 ay],[ say < 3 ay],[M#601;bl#601;g< 3 ay],
[ say < 4 ay],[M#601;bl#601;g< 4 ay],[ say < 5 ay],[M#601;bl#601;g< 5 ay],[ say > 5 ay],[M#601;blg> 5 ay]
 from(select phone,[say umumi]=case when ([mebleg umumi ]-[1 ay ])<0 then 1 else 0 end,
([mebleg umumi ]-[1 ay ]) as [M#601;bl#601;g Umumi],[say odenis],[abune odenis],[say odenis] as [say qaliq],
([mebleg umumi ]-[1 ay ]+[abune odenis]) as [mebleg galig],[Say Qacqin],[Say Qacqin]*(-2.5) [M#601;bl#601;g Qa#231;q#305;n], [ say < 1 ay],[M#601;bl#601;g< 1 ay],[ say < 2 ay],
[M#601;bl#601;g< 2 ay],[ say < 3 ay],[M#601;bl#601;g< 3 ay],[ say < 4 ay],[M#601;bl#601;g< 4 ay],[ say < 5 ay],[M#601;bl#601;g< 5 ay],
 [ say > 5 ay],[M#601;blg> 5 ay]
from(select phone,
 sum(case when kborc<0 then kborc else 0 end)as [mebleg umumi ],
 sum(case when [abune odenis]<>0 then 1 else 0 end)as [say odenis],
 sum([abune odenis])  as [abune odenis],
sum([Say Qacqin]) as [Say Qacqin],
sum(case when [M#601;bl#601;g< 1 ay]<0 then 1 else 0 end ) as [ say < 1 ay],
sum(case when [M#601;bl#601;g< 1 ay]<0 then [M#601;bl#601;g< 1 ay] else 0 end ) as [M#601;bl#601;g< 1 ay],
sum(case when [M#601;bl#601;g< 2 ay]<0 then 1 else 0 end ) as [ say < 2 ay],
sum(case when [M#601;bl#601;g< 2 ay]<0 then [M#601;bl#601;g< 2 ay] else 0 end ) as [M#601;bl#601;g< 2 ay],
sum(case when [M#601;bl#601;g< 3 ay]<0 then 1 else 0 end ) as [ say < 3 ay],
sum(case when [M#601;bl#601;g< 3 ay]<0 then [M#601;bl#601;g< 3 ay] else 0 end ) as [M#601;bl#601;g< 3 ay],
sum(case when [M#601;bl#601;g< 4 ay]<0 then 1 else 0 end ) as [ say < 4 ay],
sum(case when [M#601;bl#601;g< 4 ay]<0 then [M#601;bl#601;g< 4 ay] else 0 end ) as [M#601;bl#601;g< 4 ay],
sum(case when [M#601;bl#601;g< 5 ay]<0 then 1 else 0 end ) as [ say < 5 ay],
sum(case when [M#601;bl#601;g< 5 ay]<0 then [M#601;bl#601;g< 5 ay] else 0 end ) as [M#601;bl#601;g< 5 ay],
sum(case when [M#601;blg> 5 ay]<0 then 1 else 0 end ) as [ say > 5 ay],
sum(case when [M#601;blg> 5 ay]<0 then [M#601;blg> 5 ay] else 0 end ) as [M#601;blg> 5 ay],
sum([1 ay ]) as [1 ay ]
from(
select phone,[Umumi Say], [abune odenis],Borclar,kborc,[Say Qacqin],[M#601;bl#601;g< 1 ay],[M#601;bl#601;g< 2 ay],
[M#601;bl#601;g< 3 ay],[M#601;bl#601;g< 4 ay],[M#601;bl#601;g< 5 ay],[M#601;blg> 5 ay],[1 ay ]
from(
select phone ,sum([umumi]) [Umumi Say],sum(borclar) as Borclar ,
sum([qacqin]) as [Say Qacqin] ,sum([M#601;bl#601;g< 1 ay]) [M#601;bl#601;g< 1 ay],
sum([M#601;bl#601;g< 2 ay]) [M#601;bl#601;g< 2 ay],
sum([M#601;bl#601;g< 3 ay]) [M#601;bl#601;g< 3 ay],sum([M#601;bl#601;g< 4 ay]) [M#601;bl#601;g< 4 ay],sum([M#601;bl#601;g< 5 ay]) [M#601;bl#601;g< 5 ay]
,sum([M#601;bl#601;g> 5 ay])  [M#601;blg> 5 ay],sum([abune odenis]) [abune odenis],sum(kborc) kborc ,sum([1 ay ]) as [1 ay ] from (
select 
phone,sum(borclar) as borclar,sum([qacqin])as [qacqin],sum([umumi]) [umumi],sum(kborc) kborc,sum([1 ay ]) [1 ay ],
sum([M#601;bl#601;g< 1 ay]) [M#601;bl#601;g< 1 ay],sum([M#601;bl#601;g< 2 ay]) [M#601;bl#601;g< 2 ay],sum([M#601;bl#601;g< 3 ay])[M#601;bl#601;g< 3 ay]
,sum([M#601;bl#601;g< 4 ay])[M#601;bl#601;g< 4 ay],sum([M#601;bl#601;g< 5 ay])[M#601;bl#601;g< 5 ay],sum([M#601;bl#601;g> 5 ay])[M#601;bl#601;g> 5 ay],sum([abune odenis]) [abune odenis]
from(select phone,borclar,[qacqin],[umumi],[abune odenis],kborc, [1 ay ],[2 ay ],[3 ay ],[4 ay ],[5 ay ],
[M#601;bl#601;g< 1 ay]=case 	when 	-(borclar) <=[1 ay ] then 	borclar
			                   else 	0
				end,
[M#601;bl#601;g< 2 ay]=case 	when 	-(borclar) >[1 ay ] and  -(borclar) <=[2 ay ] then 	borclar
			                   else 	0
				end,
[M#601;bl#601;g< 3 ay]=case 	when 	-(borclar) >[1 ay ] and  -(borclar) >[2 ay ] and -(borclar) <=[3 ay ] then 	borclar
			                   else 	0
				end,

[M#601;bl#601;g< 4 ay]=case 	when 	-(borclar) >[1 ay ] and  -(borclar) >[2 ay ] and -(borclar) >[3 ay ]  and
 -(borclar) <=[4 ay ] then 	borclar
			                   else 	0
				end,
[M#601;bl#601;g< 5 ay]=case 	when 	-(borclar) >[1 ay ] and  -(borclar) >[2 ay ] and -(borclar) >[3 ay ]  and
 -(borclar) >[4 ay ]  and -(borclar) <=[5 ay ]then 	borclar
			                   else 	0
				end,
[M#601;bl#601;g> 5 ay]=case 	when 	-(borclar) >[1 ay ] and  -(borclar) >[2 ay ] and -(borclar) >[3 ay ]  and
 -(borclar) >[4 ay ]  and -(borclar) >[5 ay ]then 	borclar
			                   else 	0
				end
 from(select phone,borclar,ayliqlar as [1 ay ],
 ayliqlar*2 as [2 ay ],
 ayliqlar*3 as [3 ay ],
 ayliqlar*4 as [4 ay ],
 ayliqlar*5 as [5 ay ],[qacqin],[umumi],[abune odenis],kborc from(
select phone,Abune+cdma_borc as borclar,cdma_ayliq+ay_abune as ayliqlar,
[qacqin],[umumi],[abune odenis],kborc from (
select
phone,Abune,cdma_borc,
cdma_ayliq=case when phone<3999999 then 0 else cdma_ayliq end ,
ay_abune=case when phone>3999999 then 0 else ay_abune end,
[qacqin],[umumi],[abune odenis],kborc
 from(
select phone ,
sum(Abune) as Abune,
sum(cdma_borc) as cdma_borc,sum(cdma_ayliq) as cdma_ayliq ,sum(ay_abune) as ay_abune,sum([qacqin]) as [qacqin] 
,sum([abune odenis]) [abune odenis]
,sum([umumi]) as  [umumi],sum(kborc) as kborc
from(select phone,cdma_ayliq,ay_abune,Abune,cdma_borc,[qacqin]  ,[umumi] ,[abune odenis],kborc
from(
select phone,sum(kborc) as kborc ,sum (ay_abune) as ay_abune,sum(cdma_ayliq) as cdma_ayliq,sum([abune odenis]) as [abune odenis],
sum(CASE WHEN abune<0  then abune else 0 end) as  Abune,
sum(CASE WHEN cdma_borc<0  then cdma_borc else 0 end) as cdma_borc,sum([qacqin]) as [qacqin] ,sum([umumi]) as [umumi]
from(
select phone, ay_abune, cdma_ayliq,kborc,
abune=case when phone>3999999 then 0 else abune end,
cdma_borc=case when phone<3999999 then 0 else cdma_borc end,shesab,[qacqin],[umumi],[abune odenis]
from(
select phone,shesab,(ayliq+servis) as ay_abune,cdma as cdma_ayliq,
 (kborc-(ayliq+servis)+[abune odenis]+abune_kr_dax) as abune,
(kborc-cdma+[abune odenis]+abune_kr_dax) as cdma_borc,kborc,cdma,[abune odenis],abune_kr_dax, [qacqin],[umumi]
 from (--
select phone,shesab,servis,[abune odenis],kborc,ayliq,abune_kr_cix,abune_kr_dax,cdma,
 [qacqin]=case  	when 	shesab <>0 then 	1
			                   else 	0 end ,
[umumi] =case  	when 	shesab =0 then 	1
			                   else 	0 end   from(
---
select ph.phone,ph.shesab ,isnull(meb,0) as servis,isnull(gt.abune,0)as  [abune odenis],isnull(t2.debt,0) as kborc,
isnull(ft.Qiymet,0) as ayliq,isnull(yu.abune_kor_cix,0) as abune_kr_cix,
isnull(ikj.abune_kor_dax,0) as abune_kr_dax,isnull(f.Qiymet,0) as cdma 
from
(select*from phone where shesab=0 or shesab=54989 ) as ph 
left join 
(select p.phone,p.cdmaalamet,nb.Qiymet
from  phone as p inner join natiq.budc as nb on p.cdmaalamet=nb.Name where shesab=0)as f on f.phone=ph.phone
left join 
(select p.phone,p.budce,nb.Qiymet
from  phone as p inner join natiq.budc as nb on p.budce=nb.Name where p.shesab=0
)as ft on ft.phone=ph.phone
left join 
(select nomre, sum(qiyme) as meb 
from(
select sw.phone as nomre,sw.name as nam ,qy.Qiymet as qiyme
from servis_new as sw inner  join servis_baza as qy on sw.name=qy.Name
)y group by nomre
)o 
on o.nomre=ph.phone
left join 
(select  convert(int,telefon) as te,
sum(convert(money,odenis)/100.0) as abune 
 from absher where  convert(int,kod)=92 and 
  convert(int,sifre) in (0,66)
group by convert(int,telefon)) as gt on ph.phone=gt.te
left join (select convert(int,phone1)as phone1,
sum(convert(money,debt)) as debt FROM borc92 group by convert(int,phone1)) as t2 ON t2.phone1 =ph.phone
left join
(select convert(int,telefon ) as kij,sum(convert(money,odenis)/100.0) as abune_kor_cix from absher
where convert(int,sifre)=93 and convert(int,kod)=92  group by convert(int,telefon ))yu on yu.kij=ph.phone
left join
(select convert(int,telefon ) as hu,sum(convert(money,odenis)/100.0) as abune_kor_dax from absher
where convert(int,sifre)=5093 and convert(int,kod)=92  group by convert(int,telefon ))ikj on ikj.hu=ph.phone
-----
)ko  
)lop
)llllll
)kol  group  by phone 
)op 
)ll group  by phone 
)yy
)jjj
)ppppp
)oo
)mn group by phone
)llb group by phone
)p
)jjk group by phone
)k
)xy
)xyz group by phone


http://sql-az.tr.gg/

bitsmed
Aged Yak Warrior

545 Posts
Posted - 2014-02-26 : 09:31:48
Try this:
select case when (phone>=3410000 and phone<=3412287) or (phone>=3420000 and phone<=3429999) then N'XIRDALAN'
            when phone>=3490000 and phone<=3495951 then N'QUSCULUQ'
            when phone>=3400000 and phone<=3400767 then N'FATMAI'
            when phone>=3402000 and phone<=3403023 then N'GORADIL BAGLARI'
            when phone>=3404000 and phone<=3405023 then N'M?H?MM?D?LI'
            when phone>=3430000 and phone<=3434095 then N'MEHDIABAD'
            when phone>=3406000 and phone<=3407023 then N'GORADIL K?NDI'
            when phone>=3408000 and phone<=3409023 then N'DIGAH'
            when phone>=3440000 and phone<=3442047 then N'SARAY'
            when phone>=3444000 and phone<=3445535 then N'CEYRANBATAN'
            when phone>=3450000 and phone<=3450511 then N'ASAGI GUZD?K'
            when phone>=3454000 and phone<=3454511 then N'YUXARI  GUZD?K'
            when phone>=3456000 and phone<=3456383 then N'CAYLI'
            when phone>=3457000 and phone<=3458023 then N'QOBU'
            when phone>=3459000 and phone<=3459511 then N'PIR?KUSKUL'
            when phone>=3472000 and phone<=3472959 then N'AT YALI'
            when phone>=3460000 and phone<=3465055 then N'SULU-T?P?'
            when phone>=3476000 and phone<=3479327 then N'MASAZIR'
            when phone>=3480000 and phone<=3481471 then N'NOVXANI BAGLARI'
            when phone>=3483000 and phone<=3483959 then N'NOVXANI '
            when phone>=3474000 and phone<=3475023 then N'CIC?K'
            when phone>=3499000 and phone<=3499639 then N'N?VAI'
            when phone>=3416000 and phone<=3419007 then N'QURTULUS'
            when phone>=3468000 and phone<=3469023 then N'HOKM?LI'
            when phone>=3470000 and phone<=3471023 then N'ATCILIQ'
            when phone>=3452000 and phone<=3453023 then N'YENI QOBU'
            when phone>=3456400 and phone<=3456911 then N'QOBUSTAN'
            when phone>=3467000 and phone<=3467639 then N'XOCAH?S?N'
            when phone>=4080000 and phone<=4099999 then N'CDMA'
            when phone>=5129000 and phone<=5129099 then N'FHN'
            when phone>=3499950 and phone<=3499999 then N'QAFQAZ UNV'
            when phone>=3414000 and phone<=3415023 then N'Z?NGILAN QAC/S?H'
            else N'Nam?lum'
       end as phone
      ,sum(case when case when kborc<0 then kborc else 0 end-case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end<0 then 1 else 0 end) as say_umumi
      ,sum(case when kborc<0 then kborc else 0 end-case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end) as mebleg_umumi
      ,sum(case when abune_odenis=0 then 0 else 1 end) as say_odenis
      ,sum(abune_odenis) as abune_odenis
      ,sum(case when abune_odenis=0 then 0 else 1 end) as say_qalig
      ,sum(case when kborc<0 then kborc else 0 end-case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end+abune_odenis) as mebleg_qalig
      ,sum(qacqin) as say_qacqin
      ,sum(qacqin)*-2.5 as mebleg_qacqin
      ,sum(case when (abune+cdma_borc)*-1<=case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end
                then case when abune+cdma_borc<0 then 1 else 0 end
                else 0
           end
          ) as say_1_ay
      ,sum(case when (abune+cdma_borc)*-1<=case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end
                then abune+cdma_borc
                else 0
           end
          ) as mebleg_1_ay
      ,sum(case when (abune+cdma_borc)*-1>case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end
                 and (abune+cdma_borc)*-1<=case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end*2
                then case when abune+cdma_borc<0 then 1 else 0 end
                else 0
           end
          ) as say_2_ay
      ,sum(case when (abune+cdma_borc)*-1>case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end
                 and (abune+cdma_borc)*-1<=case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end*2
                then abune+cdma_borc
                else 0
           end
          ) as mebleg_2_ay
      ,sum(case when (abune+cdma_borc)*-1>case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end*2
                 and (abune+cdma_borc)*-1<=case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end*3
                then case when abune+cdma_borc<0 then 1 else 0 end
                else 0
           end
          ) as say_3_ay
      ,sum(case when (abune+cdma_borc)*-1>case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end*2
                 and (abune+cdma_borc)*-1<=case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end*3
                then abune+cdma_borc
                else 0
           end
          ) as mebleg_3_ay
      ,sum(case when (abune+cdma_borc)*-1>case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end*3
                 and (abune+cdma_borc)*-1<=case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end*4
                then case when abune+cdma_borc<0 then 1 else 0 end
                else 0
           end
          ) as say_4_ay
      ,sum(case when (abune+cdma_borc)*-1>case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end*3
                 and (abune+cdma_borc)*-1<=case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end*4
                then abune+cdma_borc
                else 0
           end
          ) as mebleg_4_ay
      ,sum(case when (abune+cdma_borc)*-1>case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end*4
                 and (abune+cdma_borc)*-1<=case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end*5
                then case when abune+cdma_borc<0 then 1 else 0 end
                else 0
           end
          ) as say_5_ay
      ,sum(case when (abune+cdma_borc)*-1>case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end*4
                 and (abune+cdma_borc)*-1<=case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end*5
                then abune+cdma_borc
                else 0
           end
          ) as mebleg_5_ay
      ,sum(case when (abune+cdma_borc)*-1>case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end*5
                then case when abune+cdma_borc<0 then 1 else 0 end
                else 0
           end
          ) as say_5plus_ay
      ,sum(case when (abune+cdma_borc)*-1>case when phone>3999999 then cdma_aylig else 0 end+case when phone<=3999999 then ay_abune else 0 end*5
                then abune+cdma_borc
                else 0
           end
          ) as mebleg_5plus_ay
  from (select p.phone
              ,sum(case when phone>3999999 or isnull(b.debt,0)-case when nb.Name=p.budce then nb.Qiymet else 0 end+isnull(o.meb,0)+isnull(a.abune,0)+isnull(a.abune_kor_dax,0)>=0 then 0 else isnull(b.debt,0)-case when nb.Name=p.budce then nb.Qiymet else 0 end+isnull(o.meb,0)+isnull(a.abune,0)+isnull(a.abune_kor_dax,0) end) as abune
              ,sum(case when phone<=3999999 or isnull(b.debt,0)-case when nb_Name=p.cdmaalamet then nb.Qiymet else 0 end+isnull(a.abune,0)+isnull(a.abune_kor_dax,0)>=0 then 0 else isnull(b.debt,0)-case when nb_Name=p.cdmaalamet then nb.Qiymet else 0 end+isnull(a.abune,0)+isnull(a.abune_kor_dax,0) end) as cdma_borc
              ,sum(case when nb_Name=p.cdmaalamet then nb.Qiymet else 0 end) as cdma_ayliq
              ,sum(case when nb.Name=p.budce then nb.Qiymet else 0 end+isnull(o.meb,0)) as ay_abune
              ,sum(case when p.shesab=0 then 0 else 1 end) as qacqin
              ,sum(isnull(a.abune,0)) as abune_odenis
              ,sum(isnull(b.debt,0)) as kborc
          from phone as p
               left outer join natiq.budc as nb
                            on nb.Name in (p.cdmaalamet,p.budce)
                           and p.shesab=0
               left outer join (select sq.phone
                                      ,sum(qy.Qiymet) as meb
                                  from servis_new as sw
                                       inner join servis_baza as qy
                                               on qy.Name=sq.name
                                 group by sw.phone
                               ) as o
                            on o.phone=p.phone
               left outer join (select telefon
                                      ,sum(case when kode='92' and sifre in ('0','66') then odenis else 0 end)/100.0 as abune
                                      ,sum(case when kode='92' and sifre='5093' then odenis else 0 end)/100.0 as abune_kor_dax
                                  from absher
                                 where kode='92'
                                   and sifre in ('0','66','5093')
                                 group by telefon
                               ) as a
                             on convert(int,a.telefon)=p.phone
               left outer join (select phone1
                                      ,sum(dept) as dept
                                  from borc92
                                 group by phone1
                               ) as b
                             on convert(int,b.phone1)=p.phone
         where p.shesab in (0,54989)
         group by p.phone
       ) as p
 group by case when (phone>=3410000 and phone<=3412287) or (phone>=3420000 and phone<=3429999) then N'XIRDALAN'
               when phone>=3490000 and phone<=3495951 then N'QUSCULUQ'
               when phone>=3400000 and phone<=3400767 then N'FATMAI'
               when phone>=3402000 and phone<=3403023 then N'GORADIL BAGLARI'
               when phone>=3404000 and phone<=3405023 then N'M?H?MM?D?LI'
               when phone>=3430000 and phone<=3434095 then N'MEHDIABAD'
               when phone>=3406000 and phone<=3407023 then N'GORADIL K?NDI'
               when phone>=3408000 and phone<=3409023 then N'DIGAH'
               when phone>=3440000 and phone<=3442047 then N'SARAY'
               when phone>=3444000 and phone<=3445535 then N'CEYRANBATAN'
               when phone>=3450000 and phone<=3450511 then N'ASAGI GUZD?K'
               when phone>=3454000 and phone<=3454511 then N'YUXARI  GUZD?K'
               when phone>=3456000 and phone<=3456383 then N'CAYLI'
               when phone>=3457000 and phone<=3458023 then N'QOBU'
               when phone>=3459000 and phone<=3459511 then N'PIR?KUSKUL'
               when phone>=3472000 and phone<=3472959 then N'AT YALI'
               when phone>=3460000 and phone<=3465055 then N'SULU-T?P?'
               when phone>=3476000 and phone<=3479327 then N'MASAZIR'
               when phone>=3480000 and phone<=3481471 then N'NOVXANI BAGLARI'
               when phone>=3483000 and phone<=3483959 then N'NOVXANI '
               when phone>=3474000 and phone<=3475023 then N'CIC?K'
               when phone>=3499000 and phone<=3499639 then N'N?VAI'
               when phone>=3416000 and phone<=3419007 then N'QURTULUS'
               when phone>=3468000 and phone<=3469023 then N'HOKM?LI'
               when phone>=3470000 and phone<=3471023 then N'ATCILIQ'
               when phone>=3452000 and phone<=3453023 then N'YENI QOBU'
               when phone>=3456400 and phone<=3456911 then N'QOBUSTAN'
               when phone>=3467000 and phone<=3467639 then N'XOCAH?S?N'
               when phone>=4080000 and phone<=4099999 then N'CDMA'
               when phone>=5129000 and phone<=5129099 then N'FHN'
               when phone>=3499950 and phone<=3499999 then N'QAFQAZ UNV'
               when phone>=3414000 and phone<=3415023 then N'Z?NGILAN QAC/S?H'
               else N'Nam?lum'
          end
ps.: There probably is syntax errors as I don't have access to a database server at the moment (hooray for notepad).
Go to Top of Page

pascal_jimi
Posting Yak Master

167 Posts
Posted - 2014-02-28 : 01:57:37
thanks bistmed
Thank your beautiful sql code

but i have a error

The column prefix 'sq' does not match with a table name or alias name used in the query





http://sql-az.tr.gg/
Go to Top of Page

pascal_jimi
Posting Yak Master

167 Posts
Posted - 2014-02-28 : 02:05:27
1st
I changed

sifre='5093' and kod='092'


and then the weight of the script
used
over and over again


sum(case when ....then ...else ...end )


This is the complete script
and running
13 - 14 seconds



select phone,sum([say umumi]) [say umumi],sum([borc]) [mebleg umumi],sum([say odenis]) [say odenis],sum([abune odenis]) [mebleg odenis],
sum([say umumi])-sum([say odenis]) as [say qaliq],sum([say qacqin]) [say qacqin],sum([mebleg qacqin]) [mebleg qacqin],
sum([ say < 1 ay]) [ say < 1 ay],sum([M#601;bl#601;g< 1 ay]) [M#601;bl#601;g< 1 ay],sum([ say < 2 ay]) [ say < 2 ay],sum([M#601;bl#601;g< 2 ay]) [M#601;bl#601;g< 2 ay],
sum([ say < 3 ay]) [ say < 3 ay],sum([M#601;bl#601;g< 3 ay]) [M#601;bl#601;g< 3 ay],sum([ say < 4 ay]) [ say < 4 ay],sum([M#601;bl#601;g< 4 ay]) [M#601;bl#601;g< 4 ay],
sum([ say < 5 ay]) [ say < 5 ay],sum([M#601;bl#601;g< 5 ay]) [M#601;bl#601;g< 5 ay],sum([ say > 5 ay]) [ say > 5 ay],sum([M#601;bl#601;g> 5 ay])[M#601;bl#601;g> 5 ay]
from(
select phone=case when (phone >=3410000 and phone <=3412287 ) or (phone >=3420000 and phone <=3429999)
then N'XIRDALAN'
when phone >=3490000 and phone <=3495951 then N'QUSCULUQ'
when phone >=3400000 and phone <=3400767 then N'FATMAI'
when phone >=3402000 and phone<=3403023 then N'GORADIL BAGLARI'
when phone >=3404000 and phone <=3405023 then N'M#399;H#399;MM#399;D#399;LI'
when phone>=3430000 and phone <=3434095 then N'MEHDIABAD'
when phone >=3406000 and phone <=3407023 then N'GORADIL K#399;NDI'
when phone >=3408000 and phone <=3409023 then N'DIGAH'
when phone >=3440000 and phone <=3442047 then N'SARAY'
when phone >=3444000 and phone <=3445535 then N'CEYRANBATAN'
when phone >=3450000 and phone <=3450511 then N'ASAGI GUZD#399;K'
when phone >=3454000 and phone <=3454511 then N'YUXARI GUZD#399;K'
when phone >=3456000 and phone <=3456383 then N'CAYLI'
when phone >=3457000 and phone <=3458023 then N'QOBU'
when phone>=3459000 and phone <=3459511 then N'PIR#399;KUSKUL'
when phone >=3472000 and phone <=3472959 then N'AT YALI'
when phone >=3460000 and phone <=3465055 then N'SULU-T#399;P#399;'
when phone >=3476000 and phone <=3479327 then N'MASAZIR'
when phone >=3480000 and phone <=3481471 then N'NOVXANI BAGLARI'
when phone >=3483000 and phone <=3483959 then N'NOVXANI '
when phone >=3474000 and phone <=3475023 then N'CIC#399;K'
when phone >=3499000 and phone <=3499639 then N'N#399;VAI'
when phone >=3416000 and phone <=3419007 then N'QURTULUS'
when phone >=3468000 and phone <=3469023 then N'HOKM#399;LI'
when phone >=3470000 and phone <=3471023 then N'ATCILIQ'
when phone >=3452000 and phone <=3453023 then N'YENI QOBU'
when phone >=3456400 and phone <=3456911 then N'QOBUSTAN'
when phone >=3467000 and phone<=3467639 then N'XOCAH#399;S#399;N'
when phone>=4080000 and phone<=4099999 then N'CDMA'
when phone >=5129000 and phone <=5129099 then N'FHN'
when phone >=3499950 and phone <=3499999 then N'QAFQAZ UNV'
when phone >=3414000 and phone <=3415023 then N'Z#399;NGILAN QAC/S#399;H'
else N'Nam#601;lum' END,
sum(case when [borc]<0 then 1 else 0 end) as [say umumi],
sum([borc]) as [borc],
sum([qacqin]) as [say qacqin],
sum([mebleg qacqin]) as [mebleg qacqin],
sum(case when [abune odenis]<>0 then 1 else 0 end)as [say odenis],
sum([abune odenis]) as [abune odenis],
sum(case when [M#601;bl#601;g< 1 ay]<0 then 1 else 0 end ) as [ say < 1 ay],
sum([M#601;bl#601;g< 1 ay]) as [M#601;bl#601;g< 1 ay],
sum(case when [M#601;bl#601;g< 2 ay]<0 then 1 else 0 end ) as [ say < 2 ay],
sum([M#601;bl#601;g< 2 ay]) as [M#601;bl#601;g< 2 ay],
sum(case when [M#601;bl#601;g< 3 ay]<0 then 1 else 0 end ) as [ say < 3 ay],
sum([M#601;bl#601;g< 3 ay]) as [M#601;bl#601;g< 3 ay],
sum(case when [M#601;bl#601;g< 4 ay]<0 then 1 else 0 end ) as [ say < 4 ay],
sum([M#601;bl#601;g< 4 ay] ) as [M#601;bl#601;g< 4 ay],
sum(case when [M#601;bl#601;g< 5 ay]<0 then 1 else 0 end ) as [ say < 5 ay],
sum([M#601;bl#601;g< 5 ay]) as [M#601;bl#601;g< 5 ay],
sum(case when [M#601;bl#601;g> 5 ay]<0 then 1 else 0 end ) as [ say > 5 ay],
sum([M#601;bl#601;g> 5 ay]) as[M#601;bl#601;g> 5 ay]

from(
select phone,[umumi borc],[borc],[qacqin],[qacqin]*(-2.5) as [mebleg qacqin],[abune odenis],
[M#601;bl#601;g< 1 ay]=case when -[umumi borc] <=[1 ay ] then [umumi borc]
else 0
end,
[M#601;bl#601;g< 2 ay]=case when -[umumi borc] >[1 ay ] and -[umumi borc] <=[2 ay ] then [umumi borc]
else 0
end,
[M#601;bl#601;g< 3 ay]=case when -[umumi borc] >[1 ay ] and -[umumi borc] >[2 ay ] and -[umumi borc] <=[3 ay ] then [umumi borc]
else 0
end,

[M#601;bl#601;g< 4 ay]=case when -[umumi borc] >[1 ay ] and -[umumi borc] >[2 ay ] and -[umumi borc] >[3 ay ] and
-[umumi borc] <=[4 ay ] then [umumi borc]
else 0
end,
[M#601;bl#601;g< 5 ay]=case when -[umumi borc] >[1 ay ] and -[umumi borc] >[2 ay ] and -[umumi borc] >[3 ay ] and
-[umumi borc] >[4 ay ] and -[umumi borc] <=[5 ay ]then [umumi borc]
else 0
end,
[M#601;bl#601;g> 5 ay]=case when -[umumi borc] >[1 ay ] and -[umumi borc] >[2 ay ] and -[umumi borc] >[3 ay ] and
-[umumi borc] >[4 ay ] and -[umumi borc] >[5 ay ]then [umumi borc]
else 0
end from(
select phone,ayliqlar as [1 ay ],
ayliqlar*2 as [2 ay ],
ayliqlar*3 as [3 ay ],
ayliqlar*4 as [4 ay ],
ayliqlar*5 as [5 ay ],[qacqin],[abune odenis],kborc,[borc]=
case when (kborc-ayliqlar)<0 then (kborc-ayliqlar) else 0 end, [umumi borc]=
case when (kborc-ayliqlar+[abune odenis])<0 then (kborc-ayliqlar+[abune odenis]) else 0 end from(
select phone,cdma_ayliq+ay_abune as ayliqlar,
[qacqin],[abune odenis],kborc from (
---33
select phone,sum(kborc) as kborc ,sum (case when phone>3999999 then 0 else ay_abune end) as ay_abune,
sum(case when phone<3999999 then 0 else cdma_ayliq end ) as cdma_ayliq,sum([abune odenis]) as [abune odenis],
sum([qacqin]) as [qacqin]
from(
select phone,shesab, ay_abune, cdma_ayliq,kborc,
[qacqin],[abune odenis]
from(
--22
select phone,shesab,(ayliq+servis) as ay_abune,cdma as cdma_ayliq,kborc,[abune odenis],
[qacqin]=case when shesab <>0 then 1
else 0 end
from(

select ph.phone,ph.shesab ,isnull(meb,0) as servis,isnull(gt.abune,0)as [abune odenis],isnull(t2.debt,0) as kborc,
isnull(ft.Qiymet,0) as ayliq,isnull(yu.abune_kor_cix,0) as abune_kr_cix,
isnull(ikj.abune_kor_dax,0) as abune_kr_dax,isnull(f.Qiymet,0) as cdma
from
(select*from phone where shesab=0 or shesab=54989 ) as ph
left join
(select p.phone,p.cdmaalamet,nb.Qiymet
from phone as p inner join natiq.budc as nb on p.cdmaalamet=nb.Name where shesab=0)as f on f.phone=ph.phone
left join
(select p.phone,p.budce,nb.Qiymet
from phone as p inner join natiq.budc as nb on p.budce=nb.Name where p.shesab=0
)as ft on ft.phone=ph.phone
left join
(select nomre, sum(qiyme) as meb
from(
select sw.phone as nomre,sw.name as nam ,qy.Qiymet as qiyme
from servis_new as sw inner join servis_baza as qy on sw.name=qy.Name
)y group by nomre
)o
on o.nomre=ph.phone
left join
(select convert(int,telefon) as te,
sum(convert(money,odenis)/100.0) as abune
from absher where kod='092' and
convert(int,sifre) in (0,66)
group by convert(int,telefon)) as gt on ph.phone=gt.te
left join (select convert(int,phone1)as phone1,
sum(convert(money,debt)) as debt FROM borc92 group by convert(int,phone1)) as t2 ON t2.phone1 =ph.phone
left join
(select convert(int,telefon ) as kij,sum(convert(money,odenis)/100.0) as abune_kor_cix from absher
where sifre='0093' and kod='092' group by convert(int,telefon ))yu on yu.kij=ph.phone
left join
(select convert(int,telefon ) as hu,sum(convert(money,odenis)/100.0) as abune_kor_dax from absher
where sifre='5093' and kod='092' group by convert(int,telefon ))ikj on ikj.hu=ph.phone
)ko

--22
)llllll
)kol group by phone
--333
)jjj
)ppppp
)k
)plo group by phone
)kil group by phone


http://sql-az.tr.gg/
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts
Posted - 2014-02-28 : 13:49:09
Replace the two instances where ".sq" occur with ".sw".
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site