June 5, 2014 at 12:25 am
Hi! Friend
I wrote this query
select phone ,sum(borclar) as total ,sum([M?bl?g< 1 ay]) [M?bl?g< 1 ay],
sum([M?bl?g< 2 ay]) [M?bl?g< 2 ay],
sum([M?bl?g< 3 ay]) [M?bl?g< 3 ay],sum([M?bl?g< 4 ay]) [M?bl?g< 4 ay],sum([M?bl?g< 5 ay]) [M?bl?g< 5 ay]
,sum([M?bl?g> 5 ay]) [M?blg> 5 ay] from (
select
phone,sum(borclar) as borclar,sum([qacqin])as [qacqin],sum([umumi]) [umumi],
sum([M?bl?g< 1 ay]) [M?bl?g< 1 ay],sum([M?bl?g< 2 ay]) [M?bl?g< 2 ay],sum([M?bl?g< 3 ay])[M?bl?g< 3 ay]
,sum([M?bl?g< 4 ay])[M?bl?g< 4 ay],sum([M?bl?g< 5 ay])[M?bl?g< 5 ay],sum([M?bl?g> 5 ay])[M?bl?g> 5 ay]
from(select phone,borclar,[qacqin],[umumi], [1 ay ],[2 ay ],[3 ay ],[4 ay ],[5 ay ],
[M?bl?g< 1 ay]=case when -(borclar) <=[1 ay ] then borclar
else 0
end,
[M?bl?g< 2 ay]=case when -(borclar) >[1 ay ] and -(borclar) <=[2 ay ] then borclar
else 0
end,
[M?bl?g< 3 ay]=case when -(borclar) >[2 ay ] and -(borclar) <=[3 ay ] then borclar
else 0
end,
[M?bl?g< 4 ay]=case when -(borclar) >[3 ay ] and
-(borclar) <=[4 ay ] then borclar
else 0
end,
[M?bl?g< 5 ay]=case when
-(borclar) >[4 ay ] and -(borclar) <=[5 ay ]then borclar
else 0
end,
[M?bl?g> 5 ay]=case when -(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] from(
select phone,Abune+cdma_borc as borclar,cdma_ayliq+ay_abune as ayliqlar,
[qacqin],[umumi] 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]
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([umumi]) as [umumi]
from(select phone,cdma_ayliq,ay_abune,Abune,cdma_borc,[qacqin] ,[umumi]
from(
select phone,sum (ay_abune) as ay_abune,sum(cdma_ayliq) as cdma_ayliq,
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,
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]
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
the result this query
so
phone total M?bl?g< 1 ay M?bl?g< 2 ay M?bl?g< 3 ay M?bl?g< 4 ay M?bl?g< 5 ay M?blg> 5 ay
--------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1421048 -3.200000000 -3.200000000 0.000000000 0.000000000 0.000000000 0.000000000 0.000000000
3400000 0.000000000 0.000000000 0.000000000 0.000000000 0.000000000 0.000000000 0.000000000
3400001 0.000000000 0.000000000 0.000000000 0.000000000 0.000000000 0.000000000 0.000000000
3400002 0.000000000 0.000000000 0.000000000 0.000000000 0.000000000 0.000000000 0.000000000
3400003 -3.200000000 -3.200000000 0.000000000 0.000000000 0.000000000 0.000000000 0.000000000
how i make create procedure
to select the column as desired
exec dbo.test @t1='1'
phone M?bl?g< 1 ay
--------------------------------------- ---------------------------------------
1421048 -3.200000000
3400000 0.000000000
3400001 0.000000000
3400002 0.000000000
3400003 -3.200000000
3400004 0.000000000
exec dbo.test @t1='1,2'
phone M?bl?g< 1 ay M?bl?g< 2 ay
--------------------------------------- --------------------------------------- ---------------------------------------
1421048 -3.200000000 0.000000000
3400000 0.000000000 0.000000000
3400001 0.000000000 0.000000000
3400002 0.000000000 0.000000000
3400003 -3.200000000 0.000000000
3400004 0.000000000 0.000000000
3400005 -3.200000000 0.000000000
3400006 -3.200000000 0.000000000
3400007 0.000000000 0.000000000
3400008 -2.000000000 0.000000000
June 5, 2014 at 12:41 am
Please, can you put a little effort in formatting your code?
This is really awful to read.
Anyway, you probably will need some dynamic SQL to select the columns needed from the result set based on the input parameters.
Execute Dynamic SQL commands in SQL Server[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2014 at 6:13 am
Koen Verbeeck (6/5/2014)
Please, can you put a little effort in formatting your code?This is really awful to read.
+1 !!
And format your code is so easy! You only need to copy/past it into www.poorsql.com (or use any other tool).
June 5, 2014 at 10:53 am
I ran this through a code formatter...the columns "names" are so painful that no amount of formatting is going to make this a lot better. Add to that the sheer number of subselects (the word 'SELECT' appears in this 21 times by my count) and my head is spinning.
SELECT phone
,sum(borclar) AS total
,sum([M?bl?g< 1 ay]) [M?bl?g< 1 ay]
,sum([M?bl?g< 2 ay]) [M?bl?g< 2 ay]
,sum([M?bl?g< 3 ay]) [M?bl?g< 3 ay]
,sum([M?bl?g< 4 ay]) [M?bl?g< 4 ay]
,sum([M?bl?g< 5 ay]) [M?bl?g< 5 ay]
,sum([M?bl?g> 5 ay]) [M?blg> 5 ay]
FROM (
SELECT phone
,sum(borclar) AS borclar
,sum([qacqin]) AS [qacqin]
,sum([umumi]) [umumi]
,sum([M?bl?g< 1 ay]) [M?bl?g< 1 ay]
,sum([M?bl?g< 2 ay]) [M?bl?g< 2 ay]
,sum([M?bl?g< 3 ay]) [M?bl?g< 3 ay]
,sum([M?bl?g< 4 ay]) [M?bl?g< 4 ay]
,sum([M?bl?g< 5 ay]) [M?bl?g< 5 ay]
,sum([M?bl?g> 5 ay]) [M?bl?g> 5 ay]
FROM (
SELECT phone
,borclar
,[qacqin]
,[umumi]
,[1 ay ]
,[2 ay ]
,[3 ay ]
,[4 ay ]
,[5 ay ]
,[M?bl?g< 1 ay] = CASE
WHEN - (borclar) <= [1 ay ]
THEN borclar
ELSE 0
END
,[M?bl?g< 2 ay] = CASE
WHEN - (borclar) > [1 ay ]
AND - (borclar) <= [2 ay ]
THEN borclar
ELSE 0
END
,[M?bl?g< 3 ay] = CASE
WHEN - (borclar) > [2 ay ]
AND - (borclar) <= [3 ay ]
THEN borclar
ELSE 0
END
,[M?bl?g< 4 ay] = CASE
WHEN - (borclar) > [3 ay ]
AND - (borclar) <= [4 ay ]
THEN borclar
ELSE 0
END
,[M?bl?g< 5 ay] = CASE
WHEN - (borclar) > [4 ay ]
AND - (borclar) <= [5 ay ]
THEN borclar
ELSE 0
END
,[M?bl?g> 5 ay] = CASE
WHEN - (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]
FROM (
SELECT phone
,Abune + cdma_borc AS borclar
,cdma_ayliq + ay_abune AS ayliqlar
,[qacqin]
,[umumi]
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]
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([umumi]) AS [umumi]
FROM (
SELECT phone
,cdma_ayliq
,ay_abune
,Abune
,cdma_borc
,[qacqin]
,[umumi]
FROM (
SELECT phone
,sum(ay_abune) AS ay_abune
,sum(cdma_ayliq) AS cdma_ayliq
,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
,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]
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
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 5, 2014 at 11:05 am
Sean Lange (6/5/2014)
I ran this through a code formatter...
Are you sure? 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2014 at 11:07 am
Koen Verbeeck (6/5/2014)
Sean Lange (6/5/2014)
I ran this through a code formatter...Are you sure? 😀
haha. it seems I forgot the closing [ code] tag. Will fix that now. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply