Query two tables - 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)

Query two tables

Author

Topic

nhaas
Yak Posting Veteran

90 Posts

Posted - 2009-02-19 : 16:52:08

Hi all I am trying to put two tables together with a query to place into 1 Store Procedure.
1. I need to select all the active numbers in a table.
2. then loop through another table with the active numbers and get the highest value in anothe table.

for the active numbers query I have Services db:
I can get what numbers I need by doing "Select [SERVE_NO] from Services where serv_type = 'cellphone' and LINE_STATUS = 'ACTIVE'"
works as expected, but I am not sure how to add in the next part where I need to get the newest number (minutes used).
2. So I plug in a number 5555555555 and get 2350 as the highest number (works great):
SELECT max(ACC_HEADER)
FROM dbo.UsageSummary
WHERE (SERVE_NO = '5555555555') AND (CHARGE_CAT = 'Airtime')

3. I take 2350 and the phone number adn Charge_cat again and get the Duration amount of 3780

SELECT Duration
FROM dbo.UsageSummary
WHERE ACC_HEADER = '2350' and Serve_no = '5555555555' and CHARGE_CAT = 'airtime'
which of course is in seconds - so i will convert that to minutes. but in the mean time how can I put this altogether in 1 SP?

thanks for any suggestions.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 17:12:58

SELECT    Duration
FROM      dbo.UsageSummary
WHERE     ACC_HEADER = (SELECT max(ACC_HEADER)
FROM      dbo.UsageSummary
WHERE    SERVE_NO in (Select distinct[SERVE_NO] from Services where serv_type = 'cellphone'and [SERVE_NO]='5555555555' and LINE_STATUS = 'ACTIVE')) AND CHARGE_CAT = 'Airtime'

nhaas
Yak Posting Veteran

90 Posts

Posted - 2009-02-19 : 17:44:32

sodeep, Thank you for the help - issue here I am getting 370 results (modified code below). its seems that it is getting the Highest ACC_HEADER and giving me the duration. My issue is that I have (my fault I forgot to mention it) each phone number MAY have different AC_HEADER numbers.

SELECT SERVE_NO, Duration, CHARGE_CAT
FROM dbo.UsageSummary
WHERE ACC_HEADER = (SELECT max(ACC_HEADER)
FROM dbo.UsageSummary
WHERE SERVE_NO in (Select distinct[SERVE_NO] from Services where serv_type = 'cellphone' and LINE_STATUS = 'ACTIVE')) and CHARGE_CAT='AIRTIME' ORDER BY serve_NO

This portion (Select distinct[SERVE_NO] from Services where serv_type = 'cellphone' and LINE_STATUS = 'ACTIVE') does return all numbers that I need information as.

nhaas
Yak Posting Veteran

90 Posts

Posted - 2009-02-19 : 18:27:33

I was looking at this more and should I be doing something like this??? the first two steps are done but it seems like it is taking a long time......

declare PN_cursor cursor

/* Selects Phone Numbers (Serve_No) That are active in services and inserts the PN_CURSOR*/
for
Select distinct[SERVE_NO]
from Services
where serv_type = 'cellphone' and LINE_STATUS = 'ACTIVE'

open PN_cursor

declare @PH_No varchar(50)
set @PH_No = ''

/* loop through dbo.UsageSummary and find highest account number for the number*/

fetch next from PN_cursor into @PH_No
while (@@Fetch_status <> -1)
Begin
if (@@Fetch_status <> -2)
Begin
(SELECT max(ACC_HEADER)
FROM dbo.UsageSummary
WHERE SERVE_NO = @PH_NO )

End
fetch next from PN_cursor into @PH_No
End
close PN_cursor
deallocate PN_cursor

/* NOW GO BACK AND USE THE PN_CUSOR (NUMBER) AND HIGHEST ACC_HEADER AND LOOK THROUGH DBO.USEAGESUMMAR AGAIN AND FIND DURATION AND DIVIDE IT BY 60/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 11:54:38

you dont need a cursor for this. what you need is just this


SELECT s.[SERVE_NO],t.Duration/60.0
FROM  Services s
INNER JOIN
(SELECT us.Serve_no,us.Duration
 FROM dbo.UsageSummary us
 INNER JOIN (SELECT Serve_no,MAX(ACC_HEADER) AS recent
             FROM dbo.UsageSummary
             WHERE CHARGE_CAT = 'airtime'
             GROUP BY Serve_no) us1
 ON us1.Serve_no=us.Serve_no
 AND us1.recent=us.ACC_HEADER
 WHERE us.CHARGE_CAT = 'airtime')t
ON t.Serve_no=s.[SERVE_NO]
where s.serv_type = 'cellphone' 
and s.LINE_STATUS = 'ACTIVE'

nhaas
Yak Posting Veteran

90 Posts

Posted - 2009-02-20 : 17:14:32

Works great - thanks for the help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-21 : 02:38:18

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources