Endpoint (varchar(15)) - SQL Server Forums

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

Endpoint (varchar(15))

Author

Topic

confuzed04
Starting Member

39 Posts

Posted - 2007-06-06 : 11:26:29

I am trying to get my query to pull all the occurances where someone called 411 assistance. It pulls anything that has 411 in it right now, and I just need it to find where the number is just either '411' or '1411' and thats it. Here is what I have so far:

SELECT sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM VoiceCallDetailRecord
WHERE CallDate >= '02/19/2007' and calldate < '03/19/2007'
and (Left(Endpoint,3) = '411'
or Left(Endpoint,4) = ('1411'))
UNION
SELECT sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM ZeroChargeVCDRecord
WHERE CallDate >= '02/19/2007' and calldate < '03/19/2007'
and (Left(Endpoint,3) = '411'
or Left(Endpoint,4) = ('1411'))

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-06 : 11:48:18

select x.*, case when left(x.endpoint,1) = '1' then 1 else 0 end as Is1411
from
(your sql here) x

- Jeff
http://weblogs.sqlteam.com/JeffS

confuzed04
Starting Member

39 Posts

Posted - 2007-06-06 : 12:10:22

I don't really understand what you mean... Can you set it up in my query so I can see how it should be written?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-06 : 13:36:36

I gave you a sql statement, and indicated where you should put the sql statement that you posted for us. Just run it an look at the results. Maybe this will be more clear:

select x.*, case when left(x.endpoint,1) = '1' then 'it starts with 1' else 'it does not start with 1' end as Result
from
(your sql here) x

You didn't indicate what you mean by "find if it is 1411 or 411", so I demonstrated how to use a CASE expression to determine it. If you need something more specific, just us know specifically what you need.

- Jeff
http://weblogs.sqlteam.com/JeffS

confuzed04
Starting Member

39 Posts

Posted - 2007-06-06 : 17:23:03

I have a database that lists all the phone numbers called during a certain time period. I want to find out where the number '411' or '1411' (which is directory assistance) occurs. I don't want it to give me the numbers that have 411 in it, I want the entire number to just be 411 or 1411. I am also confused what the x.* stands for. I know almost nothing about writing querys, so I am very sorry for the annoyance. Thanks for your help

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-06 : 17:31:10

Jsut cut/paste your entire SELECT statement in JEff's original reply where it says (your sql here).

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

confuzed04
Starting Member

39 Posts

Posted - 2007-06-06 : 17:38:13

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

This is what I get when I do that.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-06 : 17:40:40

[code]
select x.*, case when left(x.endpoint,1) = '1' then 1 else 0 end as Is1411
from (
SELECT sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes, Endpoint
FROM VoiceCallDetailRecord
WHERE CallDate >= '02/19/2007' and calldate < '03/19/2007'
and (Left(Endpoint,3) = '411'
or Left(Endpoint,4) = ('1411'))
UNION
SELECT sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes, Endpoint
FROM ZeroChargeVCDRecord
WHERE CallDate >= '02/19/2007' and calldate < '03/19/2007'
and (Left(Endpoint,3) = '411'
or Left(Endpoint,4) = ('1411'))
) x
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

confuzed04
Starting Member

39 Posts

Posted - 2007-06-06 : 17:59:02

I'm sorry. I cut and pasted it exactly and this is what I got

Msg 8120, Level 16, State 1, Line 1
Column 'VoiceCallDetailRecord.Endpoint' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 1 of 'x'.
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'x'.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-06 : 18:13:18

Well, your query is summing up all the rows whether they ar eof type 411 or 1411 right? So you need a GROUP BY to separate them out.


SELECT sum(PaidBalCost) PaidBalCost, sum(BonusBalCost) BonusBalCost, sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes ,  case when left(endpoint,1) = '1' then 1 else 0 end as Is1411
FROM VoiceCallDetailRecord  
WHERE CallDate >= '02/19/2007' and calldate < '03/19/2007' 	
	and (Left(Endpoint,3) = '411' 	or Left(Endpoint,4) = ('1411')) 
Group by endpoint

UNION 

SELECT sum(PaidBalCost) PaidBalCost, sum(BonusBalCost) BonusBalCost, sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes,  case when left(endpoint,1) = '1' then 1 else 0 end as Is1411
FROM ZeroChargeVCDRecord 
WHERE CallDate >= '02/19/2007' and calldate < '03/19/2007' 
	and (Left(Endpoint,3) = '411' 	or Left(Endpoint,4) = ('1411'))
Group by Endpoint

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources