BETWEEN - 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

SQL Server 2000 Forums

SQL Server Development (2000)

BETWEEN

Author

Topic

MarioK
Starting Member

20 Posts

Posted - 2006-05-12 : 13:11:20

Hi everyone,
I have 3 different types of phone numbers. you can use each different phone to test the query as well.Could you all please give me some advice about my query. Thanks

phone1 phone2 phone3
+8005215009 1(800)521-5009 8005215009
+8005215008 1(800)521-5008 8005215008
+8585215009 1(858)521-5009 8005215009
+8004215009 1(800)421-5009 8004215009

SELECT phone1 = CONVERT(varchar(12),phone1,112)
FROM calldetail
WHERE phone1 BETWEEN '8005215009' AND '8005216009'

i want the result like this below:

phone1: but not:8004215009
8005215009
or
last 4 digits: 5009

Thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-12 : 14:35:09

Seems to be ur Data are not good enough (I did some changes )

Create table #t (phone1 varchar(25), phone2 varchar(25), phone3 varchar(25))

Insert into #t values ('+8005215009', '1(800)521-5009', '8005215009')
Insert into #t values ('+8005215008', '1(800)521-5008', '8005215008')
Insert into #t values ('+8585215009', '1(858)521-5009', '8585215009')
Insert into #t values ('+8004215009', '1(800)421-5009', '8004215009')

SELECT *
FROM #t
WHERE CONVERT(Numeric,phone3) BETWEEN 8005215009 AND 8005216009

Drop table #t

Srinika

MarioK
Starting Member

20 Posts

Posted - 2006-05-12 : 16:15:05

thanks Srinika,
it works good. but only show the first row for each phone number.

here is my new query:

SELECT InitiatedDate, remotenumbercallid,callDirection COUNT(*) AS t_calldirection
FROM Calldetail
WHERE InitiatedDate is not NULL AND InitiatedDate BETWEEN '5/8/2006'AND '5/10/2006'and RemoteNumbercallid = '5009' and calldirection like 'inbound%'
ORDER BY InitiatedDate desc,remotenumbercallid, calldirection, t_calldirection desc

table: calldetail
colume:initiateddate
5-8-2006
5-9-2006
5-10-2006
5-4-2003
5-1-2004
...
....an many more date, more than 120000 rows
colum: remotenumbercallid
8005215009
8005215009
8005215008
8005215009
8215215009
3105215009
..........and many more phone numbers, more than 120000 rows

colum: calldirection:
inbound
outbound
....
....

Ex: i want the result look like this:
initiateddate remotenumbercallid calldirection t_direction
5-8-2006 5009 inbound 2
5-9-2006 5009 inbound 6
5-10-2006 5009 inbound 100

and please careful the remotenumbercallid select have to
8005215009 in order to match with others colum

not 8004215009
or not 3105215009

I'd like to thank Srinika and all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources