Select distinct ids whilst performing inner select

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

SQL Server 2000 Forums

Transact-SQL (2000)

Select distinct ids whilst performing inner select

Author

Topic

badman3k
Starting Member

15 Posts

Posted - 2009-05-15 : 14:01:49

I'm trying to write an insert query that takes a whole lot of data stored in a temporary table, processes it, and then puts the results back into the final table.

The purpose of this is that every month the company phone bill needs processing, we've managed to obtain a CSV file of it, but it's 5500+ entries each month. Rather than storing all that huge amount of data, I want to process it and just store the data I need.

Now to the SQL query. This is what I have at present, which partially works, except that the inner selects return the same results regardless of the mobile_id.

truncate table mobile_usage
create table #tmp_mobile_usage (date varchar(20), time varchar(20), called varchar(30), mobile_id varchar(20), country varchar (3), key_word varchar(20), description varchar(100), usage varchar(15), cost varchar(20))
BULK
insert #tmp_mobile_usage FROM 'C:\wamp\www\csv\latest.csv' WITH ( FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n')
set @rowcount = @@rowcount
set @usage_err = @@error

insert into mobile_usage (mobile_id, date, incl_calls, incl_texts, incl_data, add_calls, add_texts, roam_calls, roam_texts, roam_data, add_calls_cost, add_texts_cost, roam_calls_cost, roam_texts_cost, roam_data_cost)

select mobile_id, '01-Apr-2009',
(select count(called) from #tmp_mobile_usage where (key_word = 'X-Net' or key_word = 'Voice' or key_word = 'ICC' or key_word = 'Land' or key_word = 'Mobile') and country = 'GBR'),
(select count(called) from #tmp_mobile_usage where (key_word = 'sms or roam') and country = 'GBR' and cost = '0.000'),
(select count(called) from #tmp_mobile_usage where (key_word = 'Blackberry' or key_word = 'GPRS' or key_word = 'WAP') and country = 'GBR'),
(select count(called) from #tmp_mobile_usage where (key_word = 'NGN' or key_word = 'PRC') and country = 'GBR'),
(select count(called) from #tmp_mobile_usage where (key_word = 'MMS' or key_word = 'sms or roam') and country = 'GBR' and cost <> '0.000'),
(select count(called) from #tmp_mobile_usage where (key_word = 'X-Net' or key_word = 'Voice' or key_word = 'ICC' or key_word = 'Land' or key_word = 'Mobile') and country <> 'GBR'),
(select count(called) from #tmp_mobile_usage where (key_word = 'sms or roam') and country <> 'GBR'),
(select count(called) from #tmp_mobile_usage where (key_word = 'Blackberry' or key_word = 'GPRS' or key_word = 'WAP') and country <> 'GBR'),
0, 0, 0, 0, 0
from #tmp_mobile_usage group by mobile_id

drop table #tmp_mobile_usage

As you can see the telephone company like to include different key words for the same thing. The selects I believe are roughly right, in the sense that for a single unique id they'd return the correct values that I want.

This is one of those queries where if I look at it too hard I get a headache. I'd appreciate any help you guys can offer.

Many thanks in advance,
Rich

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-15 : 14:11:59

seems like insert...select should be this


insert into mobile_usage (mobile_id, date, incl_calls, incl_texts, incl_data, add_calls, add_texts, roam_calls, roam_texts, roam_data, add_calls_cost, add_texts_cost, roam_calls_cost, roam_texts_cost, roam_data_cost)

select mobile_id, '01-Apr-2009',
count(case when (key_word = 'X-Net' or key_word = 'Voice' or key_word = 'ICC' or key_word = 'Land' or key_word = 'Mobile') and country = 'GBR' then called else null end),
count(case when (key_word = 'sms or roam') and country = 'GBR' and cost = '0.000' then called else null end),
count(case when (key_word = 'Blackberry' or key_word = 'GPRS' or key_word = 'WAP') and country = 'GBR' then called else null end),
count(case when (key_word = 'NGN' or key_word = 'PRC') and country = 'GBR' then called else null end),
count(case when (key_word = 'MMS' or key_word = 'sms or roam') and country = 'GBR' and cost <> '0.000' then called else null end),
count(case when (key_word = 'X-Net' or key_word = 'Voice' or key_word = 'ICC' or key_word = 'Land' or key_word = 'Mobile') and country <> 'GBR' then called else null end),
count(case when (key_word = 'sms or roam') and country <> 'GBR') then called else null end),
count(case when (key_word = 'Blackberry' or key_word = 'GPRS' or key_word = 'WAP') and country <> 'GBR' then called else null end),
0, 0, 0, 0, 0
from #tmp_mobile_usage group by mobile_id

badman3k
Starting Member

15 Posts

Posted - 2009-05-16 : 10:14:15

That indeed solved my problem, I didn't think to use the CASE - guess I was looking at it for too long.

Thanks visakh16 for all your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-16 : 14:38:51

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources