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.
Author |
Topic |
pascal_jimi
Posting Yak Master
167 Posts |
Posted - 2013-06-17 : 08:45:46
|
declare @table3 table(phone2 int) -- insert into @table3 values(111111)insert into @table3 values(222222)insert into @table3 values(333333)insert into @table3 values(444444)insert into @table3 values(555555)-- declare @table2 table(phone1 int,debt money) insert into @table2 values(111111,1.23)insert into @table2 values(222222,9.7)declare @table1 table(phone int,old_debt money) insert into @table1 values(111111,6.3)insert into @table1 values(444444,8.9)declare @t242 table(ph int,qebzbr money) insert into @t242 values(111111,6.3)insert into @t242 values(333333,9.0)declare @t1 table(phone int,sadbr money) insert into @t1 values(111111,-1.10)-- declare @absher table(telefon varchar(10),odenis varchar(10)) insert into @absher values('00111111','000000089')insert into @absher values('00111111','000000056')----------declare @avans table(tlf varchar(10),Test varchar(10)) insert into @avans values('00111111','000000009')insert into @avans values('00111111','000000523')insert into @avans values('00111111','000000026')--------------------------------------------------------------select *from (select t3.phone2, ISNULL(tt1.sadbr,0.0)-ISNULL(t242.qebzbr,0.0) as f2,ISNULL(t2.debt,0.0)-isnull(t1.old_debt,0.0) as f1,ISNULL(p.mny,0) as abser_ode,ISNULL(d.av_sad,0) as avansid_odefrom @table3 as t3left join (select ph,SUM(qebzbr) as qebzbr FROM @t242 group by ph) as t242 ON t242.ph= t3.phone2left join (select phone,SUM(sadbr) as sadbr FROM @t1 group by phone) as tt1 ON tt1.phone= t3.phone2left join (select phone1,SUM(debt) as debt FROM @table2 group by phone1) as t2 ON t2.phone1 = t3.phone2left join (select phone,SUM(old_debt) as old_debt from @table1 group by phone) as t1 ON t1.phone = t3.phone2left join (select convert(int,telefon) as tf,sum(convert(money,odenis)/100.0) as mny from @absher group by convert(int,telefon)) p ON p.tf = t3.phone2left join (select convert(int,tlf) as tlf ,sum(convert(money,Test)/100.0) as av_sad from @avans group by convert(int,tlf))as d on d.tlf=t3.phone2) uuuresult query-----------phon2 f2 f1 absher_ode avans_ode111111 -7,40 -5,07 1.450000000 5.580000000222222 0,00 9,70 0.000000000 0.000000000333333 -9,00 0,00 0.000000000 0.000000000444444 0,00 -8,90 0.000000000 0.000000000555555 0,00 0,00 0.000000000 0.000000000how can I get when phone2>=1 and phone2<=2 then 'A'phone2>=3 and phone2<=5 then 'B'how can I get resultphon2 f2 f1 absher_ode avans_odeA -7,40 -5,07 1.450000000 5.580000000A 0,00 9,70 0.000000000 0.000000000B -9,00 0,00 0.000000000 0.000000000B 0,00 -8,90 0.000000000 0.000000000B 0,00 0,00 0.000000000 0.000000000kmkmmm |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-06-17 : 08:51:22
|
SELECT CASE WHEN Phone2 between 1 and 2 then 'A'WHEN phone2 between 3 and 5 then 'B'END as Phone2,F2,F1,Absher_Ode,Avans_OdeFROM (........)CheersMIK |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-17 : 08:55:52
|
- I think this oneselect *from (select CASE WHEN LEFT(t3.phone2, 1) BETWEEN 1 AND 2 THEN 'A' WHEN LEFT(t3.phone2, 1) BETWEEN 3 AND 5 THEN 'B' END Phone2, ISNULL(tt1.sadbr,0.0)-ISNULL(t242.qebzbr,0.0) as f2 --Chandu |
|
|
pascal_jimi
Posting Yak Master
167 Posts |
Posted - 2013-06-17 : 09:04:20
|
thank youkmkmmm |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-17 : 09:24:32
|
quote: Originally posted by pascal_jimi thank youkmkmmm
Which one worked out?My solution is dependent on the first digit in the phone2 whereas MIK's solution is for checking entire number in the Phone2 column--Chandu |
|
|
pascal_jimi
Posting Yak Master
167 Posts |
Posted - 2013-06-17 : 09:25:39
|
bandiwhy LEFT(t3.phone2, 1) ??kmkmmm |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-17 : 09:29:53
|
quote: Originally posted by pascal_jimi bandiwhy LEFT(t3.phone2, 1) ??Kmkmmm
You had Phone2 values as 111111, 222222 .... By looking at your required output, I checked for first digit in the Phone2 columnLEFT ( Phone2, 1) means first character(digit) from the column Phone2--Chandu |
|
|
pascal_jimi
Posting Yak Master
167 Posts |
Posted - 2013-06-17 : 09:37:55
|
ok thank you but i wrote select CASE WHEN t3.phone2 BETWEEN 111111 AND 222222 THEN 'A' WHEN phone2 BETWEEN 333333 AND 555555 THEN 'B' else 'nomre' END Phone2,I need a complete full phone2kmkmmm |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-17 : 09:59:30
|
quote: Originally posted by pascal_jimi ok thank you but i wrote select CASE WHEN t3.phone2 BETWEEN 111111 AND 222222 THEN 'A' WHEN phone2 BETWEEN 333333 AND 555555 THEN 'B' else 'nomre' END Phone2,I need a complete full phone2kmkmmm
ok... is that Phone2 values had length of 6 digits only?--Chandu |
|
|
|
|
|
|
|