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 2005 Forums
 Transact-SQL (2005)
 SQL LOOP

Author  Topic 

nhess80
Yak Posting Veteran

83 Posts
Posted - 2009-05-06 : 18:43:22
Im not sure if a loop is what I need or not and if it is I don't know how to do it but here is the problem.

For testing purposes I have 2 Tables. The CDR table contains 1 field called TermNumber. TermNumber contains a phone number ie: 2175551212.

The Second table called Rates contains 2 fields npanxx and rate. npanxx will contain something like this: 217555 and the rate column will contain 0.012

Table1 Name = CDR
Field = TermNumber

Table2 Name = Rates
Fields = npanxx
= rate

Table1 Data =
2175551212
2175552323

Table2 Data =
217555,0.013
2175552,0.012

I need to somehow compare these tables to find the correct rate.
2175551212 should get the rate of 0.013
2175552323 should get the rate fo 0.012

I am trying to figure out a way to strip a digit off the end of the TermNumber in table1 and continue to do so until it finds the best possible match for both records and assigns it a rate

Tapalotapus
Starting Member

22 Posts
Posted - 2009-05-06 : 19:37:13
Likely a cleaner way to do this??? But it does work.

create table CDR
(TermNumber bigint)

insert into CDR select 2175551212
insert into CDR select 2175552323

create table Rates
(npanxx bigint,
rate varchar(100))

insert into Rates select 217555,0.013
insert into Rates select 2175552,0.012
---------------------------------------------
WITH UnionAll ( [len], npanxx, RATE, TermNumber)

AS

(
SELECT len(npanxx) as 'len',
npanxx,
RATE,
'0' as TermNumber
FROM Rates
UNION ALL
SELECT '0',
'0',
'0',
TermNumber
From CDR
)
SELECT min(b.termnumber), max(a.RATE)
FROM UnionAll a
RIGHT JOIN UnionAll b
ON a.npanxx = left(b.termnumber,a.len)
group by a.npanxx
HAVING max(a.RATE) <> '0'
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2009-05-06 : 20:00:23
try this


Declare @CDR table(TermNumber bigint)
insert into @CDR select 2175551212
insert into @CDR select 2175552323

Declare @Rates Table (npanxx bigint,rate varchar(100))

insert into @Rates select 217555,0.013
insert into @Rates select 2175552,0.012

Select *
from
(
Select Row_Number() over (Partition by a.TermNumber order by len(b.npanxx) desc) as RowID, * from
@CDR a
Inner Join
@Rates b
on left(a.TermNumber,len(b.npanxx)) = b.npanxx
) aa
where aa.Rowid = 1


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts
Posted - 2009-05-07 : 06:53:52
Select distinct termnumber,min(rate)over (partition by termnumber) as rate from
@CDR a
Inner Join
@Rates b
on left(a.TermNumber,len(b.npanxx)) = b.npanxx

select termnumber, (select min(rate) from @rates where left(a.TermNumber,len(npanxx)) = npanxx) as rates
from @CDR a
Go to Top of Page

nhess80
Yak Posting Veteran

83 Posts
Posted - 2009-05-07 : 12:37:25
Thanks you....this worked great....im not sure what the first part was but the second select statement was all i needed so thank you

quote:
Originally posted by bklr

Select distinct termnumber,min(rate)over (partition by termnumber) as rate from
@CDR a
Inner Join
@Rates b
on left(a.TermNumber,len(b.npanxx)) = b.npanxx

select termnumber, (select min(rate) from @rates where left(a.TermNumber,len(npanxx)) = npanxx) as rates
from @CDR a
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2009-05-07 : 14:44:11
I would suggest using the query I showed you. BKLR's query's will not return the best match.

please run the code below to illustrate.


Declare @CDR table(TermNumber bigint)
insert into @CDR select 2175551212
insert into @CDR select 2175552323

Declare @Rates Table (npanxx bigint,rate varchar(100))

insert into @Rates select 217555,0.014
insert into @Rates select 2175552,0.012
insert into @Rates select 21755523,0.013

--MY QUERY
Select *
from
(
Select Row_Number() over (Partition by a.TermNumber order by len(b.npanxx) desc) as RowID, * from
@CDR a
Inner Join
@Rates b
on left(a.TermNumber,len(b.npanxx)) = b.npanxx
) aa
where aa.Rowid = 1


-- OTHER QUERY's
Select distinct termnumber,min(rate)over (partition by termnumber) as rate from
@CDR a
Inner Join
@Rates b
on left(a.TermNumber,len(b.npanxx)) = b.npanxx

select termnumber, (select min(rate) from @rates where left(a.TermNumber,len(npanxx)) = npanxx) as rates
from @CDR a


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

nhess80
Yak Posting Veteran

83 Posts
Posted - 2009-05-07 : 17:16:35
Well I do need best match unless the rate is lower.

Example: if i pass 2175551212 and in the database i have

217555 with a rate of 0.012
2175551 with a rate of 0.013

The best match would be 2175551 but it has a higher rate so I need it to grab 217555 since the rate is lower. I did not say this in my original post because i just didn't think of it.

However I am having a problem that you might be able to help me with. Currently right now I will be using more than one rate table as you can see in the query below which works great but the problem is I don't know how to create a SUM of everything together without throwing an error. Like if I wanted a total duration and cost.

The current output of the query is:

duration,cost
3.6000 - 0.018
1.2000 - 0.0348
2.6000 - 0.013

I will need to added these together.

select Duration,

(Select min(iif(b.Rate IS NULL AND Rates2.Rate IS NULL, NULL,
iif(Rates2.Rate <= b.Rate OR b.Rate IS NULL,Rates2.Rate * a.Duration,b.Rate * a.Duration)))

from rates b LEFT OUTER JOIN Rates2 ON b.npanxx = Rates2.npanxx

where left(a.TermNumber,len(b.npanxx)) = b.npanxx)) as Cost

from CDR a






quote:
Originally posted by Vinnie881

I would suggest using the query I showed you. BKLR's query's will not return the best match.

please run the code below to illustrate.


Declare @CDR table(TermNumber bigint)
insert into @CDR select 2175551212
insert into @CDR select 2175552323

Declare @Rates Table (npanxx bigint,rate varchar(100))

insert into @Rates select 217555,0.014
insert into @Rates select 2175552,0.012
insert into @Rates select 21755523,0.013

--MY QUERY
Select *
from
(
Select Row_Number() over (Partition by a.TermNumber order by len(b.npanxx) desc) as RowID, * from
@CDR a
Inner Join
@Rates b
on left(a.TermNumber,len(b.npanxx)) = b.npanxx
) aa
where aa.Rowid = 1


-- OTHER QUERY's
Select distinct termnumber,min(rate)over (partition by termnumber) as rate from
@CDR a
Inner Join
@Rates b
on left(a.TermNumber,len(b.npanxx)) = b.npanxx

select termnumber, (select min(rate) from @rates where left(a.TermNumber,len(npanxx)) = npanxx) as rates
from @CDR a


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2009-05-07 : 19:41:27
Please post a sampled of the desired row results and I'll take a look.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

nhess80
Yak Posting Veteran

83 Posts
Posted - 2009-05-07 : 20:26:18
Tables and Data

CDR
TermNumber Duration
2177282827 3.6
2177274526 1.2
2177288983 2.6
2177288983 2.6

Rates
npanxx rate
2177283 $0.01
2177288 $0.01
217728 $0.01
2 $0.04
21 $0.03

Rates2
npanxx rate
217728 $0.01
2 $0.03
21 $0.03

Based on the info above the below query will return this

3.6000 - 0.018
1.2000 - 0.0348
2.6000 - 0.013
2.6000 - 0.013

Im trying to figure out a way to return these added together like this

10 - 0.0788

If I can get this figured out than I shouldn't have any problem figuring out the other things I need to do.

Thanks for any help



select Duration,

(Select min(iif(b.Rate IS NULL AND Rates2.Rate IS NULL, NULL,
iif(Rates2.Rate <= b.Rate OR b.Rate IS NULL,Rates2.Rate * a.Duration,b.Rate * a.Duration)))

from rates b LEFT OUTER JOIN Rates2 ON b.npanxx = Rates2.npanxx

where left(a.TermNumber,len(b.npanxx)) = b.npanxx) as Cost

from CDR a




quote:
Originally posted by Vinnie881

Please post a sampled of the desired row results and I'll take a look.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2009-05-08 : 13:44:51
If I am understanding correctly you want to check both RATE tables for the lowest rate then multiply the duration by the rate then SUM the total. Your #'s shown do not match the data you told me was in the table, but here is how to do what I just described.


Declare @CDR table(TermNumber bigint,Duration decimal(5,2))
insert into @CDR select 2177282827,3.6
insert into @CDR select 2177274526,1.2
insert into @CDR select 2177288983,2.6
insert into @CDR select 2177288983,2.6

Declare @Rates Table (npanxx bigint,rate varchar(100))

insert into @Rates select 2177283,0.01
insert into @Rates select 2177288,0.01
insert into @Rates select 217728,0.01
insert into @Rates select 2,0.04
insert into @Rates select 21,0.03

Declare @Rates2 Table (npanxx bigint,rate varchar(100))

insert into @Rates2 select 217728,0.01
insert into @Rates2 select 2,0.03
insert into @Rates2 select 21,0.03

Declare @Tmp table (ID int,TermNumber varchar(10),Duration decimal(5,3),npanxx varchar(10),rate decimal(5,3))
--Declare @Final table (Duration decimal(5,3),rate decimal(5,3))

insert @Tmp(ID,Termnumber,duration,npanxx,rate)
Select ID,Termnumber,duration,npanxx,rate
from
(
	Select Row_Number() over (Partition by a.ID order by b.rate) as RowID, * 
	from
	(
	Select Row_Number() over (order by (select 0)) as ID,*
	From
	@CDR 
	) a
		Inner Join
	(Select * 
	 from
	 @Rates aaa
	 Union all 
	 Select *
	 From
     @Rates2 bbb
    ) b
	on left(a.TermNumber,len(b.npanxx)) = b.npanxx
) aa
where aa.Rowid = 1


Select * from @TMP

select Duration,a.rate, (a.Rate * a.Duration) As Total
from @Tmp a

select Sum(Duration),sum((a.Rate * a.Duration)) As Total
from @Tmp a


Again that checks BOTH rate tables for the LOWEST rate that is a match.

BELOW IS THE RESULTS FROM THE ABOVE QUERY


Duration  rate     Total
------    -----    --------
3.600     0.010    0.036000
1.200     0.030    0.036000
2.600     0.010    0.026000
2.600     0.010    0.026000

HERE IS THE SUM's
Duration   Total
-------    --------
10.000     0.124000


Let me know if you need clarification, or please explain more if this is not what you want.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

nhess80
Yak Posting Veteran

83 Posts
Posted - 2009-05-13 : 14:00:06
This worked just like I ask so thank you for that. I thought I would be able to get a little help and then figure the rest out myself but that is just not the case. So here goes:

The query below is what I have been using for the past year or so and it is actually dynamically generated on the fly based on what the user selects. So this is what the query below contains and this is how it works:

-------------------TABLE 1--------Holds few around 3 million records---------------
TABLE = Data
FIELD = CallDuration,TermNumber,OrigNumber,CDRS
DATA = 4.8,217728,201220,ANI

Basically these are phone calls. The CallDuration field is the length of the call. TermNumber is the dialed number. OrigNumber is the number of the person calling and CDRS is the a group to seperate the calls. Now as you can see the TermNumber and the OrigNumber are just 6 digits. This is because I never needed the last four digits to get the rate. But now I will have a need to get a rate based on 6 digits and sometimes 7 digits. So from now on my data is going to contain the entire numbers. Instead of 217728 like the above example data it will be 2177285555. The entire number.
--------------------------------------------------

--------------------TABLE 2--------------------
TABLE = LergData
FIELD = npanxx,state
DATA = 217728,IL
217345,IL
201220,NJ

This table contains about 160,000 rows and contains all
npanxx(first 6 digits of a phone number is called npanxx) in the US and what state it pertains to.
------------------------------------------------

-------------------Table 3----------------------
TABLE = VENDOR1
FIELD = VENDOR1_npanxx,VENDOR1_inter,VENDOR1_intra
DATA = 217728,0.01,0.02
217345,0.03,0.04
201220,0.01,0.03


Ok so these are our vendors rates that we charge based on the terminating number. So if 2177285555 is dialed we are going to match it up with that rate in the vendor. But as you can see there are 2 different rates inter and intra. The way I determine which rate to use is based on the OrgNumber and the TermNumber. For example if 2177285555 calls 2173455555 I am going to use the 217345 VENDOR1_intra rate. How I know to do this is because those 2 numbers are in the same state. I find this out by checking it against the LergData Table to find out. If the OrgNumber and the TermNumber are not in the same state I use the VENDOR1_inter rate.
-------------------------------------------------------------------

Now the query below just uses 2 vendors to compare against and get the best rate for the data in the CDR table but sometimes I use up to 10 vendors. This is why I created a script to dynamically generate the query based on what vendors are selected.

-------------------Table 4----------------------
TABLE = VENDOR2
FIELD = VENDOR2_npanxx,VENDOR2_inter,VENDOR2_intra
DATA = 2177288,0.02,0.04
217728,0.03,0.05
217345,0.01,0.03
201220,0.02,0.05

As you can see I will have some vendors that base their rates on 7 digits and 6 digits.

This is what has caused me to have to figure out a different way to do this. I wish there was a simple solution or a way that I could just add to the query below to make this possible.

I know this is really long but any help would be great. If you have any questions as to why I did something a certain way please ask. And if you know of a better way to do this I am open to whatever.

Thank you very much,

Nick

---------------------------------------------------
This is what an output could look like:

CallDuration AvgRate Cost Vendor

15414.200 NULL NULL NULL
405.300 0.0079 3.2018700 VENDOR1 Inter
0.100 0.0079 0.0007900 VENDOR1 Intra
2780.900 0.0075 20.8567500 VENDOR2 Inter
7418.900 0.0075 55.6417500 VENDOR2 Intra


Whatever doesn't have a match gets a NULL
------------------------------------------------

SELECT SUM(a.CallDuration) AS CallDurationSum,



AVG(CASE WHEN b.state = c.state THEN
CASE
WHEN (VENDOR1_intra IS NULL AND VENDOR2_intra IS NULL) THEN NULL
WHEN (VENDOR1_intra <= VENDOR2_intra OR VENDOR2_intra IS NULL) THEN VENDOR1_intra
ELSE VENDOR2_intra
END
ELSE
CASE
WHEN (VENDOR1_inter IS NULL AND VENDOR2_inter IS NULL) THEN NULL
WHEN (VENDOR1_inter <= VENDOR2_inter OR VENDOR2_inter IS NULL) THEN VENDOR1_inter
ELSE VENDOR2_inter
END
END) AS AvgRate,



SUM(CASE WHEN b.state = c.state THEN
CASE
WHEN (VENDOR1_intra IS NULL AND VENDOR2_intra IS NULL) THEN NULL
WHEN (VENDOR1_intra <= VENDOR2_intra OR VENDOR2_intra IS NULL) THEN VENDOR1_intra * a.CallDuration
ELSE VENDOR2_intra * a.CallDuration
END
ELSE
CASE
WHEN (VENDOR1_inter IS NULL AND VENDOR2_inter IS NULL) THEN NULL
WHEN (VENDOR1_inter <= VENDOR2_inter OR VENDOR2_inter IS NULL) THEN VENDOR1_inter * a.CallDuration
ELSE VENDOR2_inter * a.CallDuration
END
END) AS Cost,


CASE WHEN b.state = c.state THEN
CASE
WHEN (VENDOR1_intra IS NULL AND VENDOR2_intra IS NULL) THEN NULL
WHEN (VENDOR1_intra <= VENDOR2_intra OR VENDOR2_intra IS NULL) THEN 'VENDOR1 Intra'
ELSE 'VENDOR2 Intra'
END
ELSE
CASE
WHEN (VENDOR1_inter IS NULL AND VENDOR2_inter IS NULL) THEN NULL
WHEN (VENDOR1_inter <= VENDOR2_inter OR VENDOR2_inter IS NULL) THEN 'VENDOR1 Inter'
ELSE 'VENDOR2 Inter'
END
END AS Vendor


FROM Data AS a LEFT OUTER JOIN
LergData AS b ON b.npanxx = a.OrigNumber LEFT OUTER JOIN
LergData AS c ON c.npanxx = a.TermNumber LEFT OUTER JOIN
VENDOR1 ON a.TermNumber = VENDOR1.VENDOR1_npanxx LEFT OUTER JOIN
VENDOR2 ON a.TermNumber = VENDOR2.VENDOR2_npanxx


WHERE (a.CDRS = 'ANI')



GROUP BY CASE WHEN b.state = c.state THEN
CASE
WHEN (VENDOR1_intra IS NULL AND VENDOR2_intra IS NULL) THEN NULL
WHEN (VENDOR1_intra <= VENDOR2_intra OR VENDOR2_intra IS NULL) THEN 'VENDOR1 Intra'
ELSE 'VENDOR2 Intra'
END
ELSE
CASE
WHEN (VENDOR1_inter IS NULL AND VENDOR2_inter IS NULL) THEN NULL
WHEN (VENDOR1_inter <= VENDOR2_inter OR VENDOR2_inter IS NULL) THEN 'VENDOR1 Inter'
ELSE 'VENDOR2 Inter'
END
END

ORDER BY vendor

------------------------------------------------
quote:
Originally posted by Vinnie881

If I am understanding correctly you want to check both RATE tables for the lowest rate then multiply the duration by the rate then SUM the total. Your #'s shown do not match the data you told me was in the table, but here is how to do what I just described.


Declare @CDR table(TermNumber bigint,Duration decimal(5,2))
insert into @CDR select 2177282827,3.6
insert into @CDR select 2177274526,1.2
insert into @CDR select 2177288983,2.6
insert into @CDR select 2177288983,2.6

Declare @Rates Table (npanxx bigint,rate varchar(100))

insert into @Rates select 2177283,0.01
insert into @Rates select 2177288,0.01
insert into @Rates select 217728,0.01
insert into @Rates select 2,0.04
insert into @Rates select 21,0.03

Declare @Rates2 Table (npanxx bigint,rate varchar(100))

insert into @Rates2 select 217728,0.01
insert into @Rates2 select 2,0.03
insert into @Rates2 select 21,0.03

Declare @Tmp table (ID int,TermNumber varchar(10),Duration decimal(5,3),npanxx varchar(10),rate decimal(5,3))
--Declare @Final table (Duration decimal(5,3),rate decimal(5,3))

insert @Tmp(ID,Termnumber,duration,npanxx,rate)
Select ID,Termnumber,duration,npanxx,rate
from
(
	Select Row_Number() over (Partition by a.ID order by b.rate) as RowID, * 
	from
	(
	Select Row_Number() over (order by (select 0)) as ID,*
	From
	@CDR 
	) a
		Inner Join
	(Select * 
	 from
	 @Rates aaa
	 Union all 
	 Select *
	 From
     @Rates2 bbb
    ) b
	on left(a.TermNumber,len(b.npanxx)) = b.npanxx
) aa
where aa.Rowid = 1


Select * from @TMP

select Duration,a.rate, (a.Rate * a.Duration) As Total
from @Tmp a

select Sum(Duration),sum((a.Rate * a.Duration)) As Total
from @Tmp a


Again that checks BOTH rate tables for the LOWEST rate that is a match.

BELOW IS THE RESULTS FROM THE ABOVE QUERY


Duration  rate     Total
------    -----    --------
3.600     0.010    0.036000
1.200     0.030    0.036000
2.600     0.010    0.026000
2.600     0.010    0.026000

HERE IS THE SUM's
Duration   Total
-------    --------
10.000     0.124000


Let me know if you need clarification, or please explain more if this is not what you want.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2009-05-13 : 16:41:56
Ok... You've got a little bit of a mess here. Please do this so I don't need to spend a ton of time going through everything.

Just give me just sample data and then the Desired results for that data.

so here are the first 3 tables, please add any others that factor into your results


Declare @CDR table(TermNumber bigint,Duration decimal(5,2))
insert into @CDR select 2177282827,3.6
insert into @CDR select 2177274526,1.2
insert into @CDR select 2177288983,2.6
insert into @CDR select 2177288983,2.6

Declare @Rates Table (npanxx bigint,rate varchar(100))

insert into @Rates select 2177283,0.01
insert into @Rates select 2177288,0.01
insert into @Rates select 217728,0.01
insert into @Rates select 2,0.04
insert into @Rates select 21,0.03

Declare @Rates2 Table (npanxx bigint,rate varchar(100))

insert into @Rates2 select 217728,0.01
insert into @Rates2 select 2,0.03
insert into @Rates2 select 21,0.03


In the same format as above show me the other tables, and include some sample data as I have with the first 3. Then post the Desired results you want for the Data provided. It should be a lot easier to look at that way.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

nhess80
Yak Posting Veteran

83 Posts
Posted - 2009-05-13 : 19:17:30
Ok so I did what you said but I had to make changes on the tables to reflect what they actually are. I need to do what I said before by finding the best rate then best match but if you look you will notice that I have an OrigNumber and TermNumber in the CDR table. You will also notice in the Rate tables which are now called vendor1 and vendor2 that there are 2 seperate rates in each table. An inter rate and intra rate. I determine this by checking the OrigNumber and TermNumber against the LergData table to get the state of the OrigNumber and the state of the TermNumber. If its in the same state I will use the intra rate and if the states do not match i will use the inter rate. Keep in mind that the actual tables are large and that I generate the query dynamically based on user selection so I could be comparing the CDR tables against many vendors.

Declare @CDR table(CallDuration decimal(5,2),TermNumber bigint,OrigNumber bigint,CDRS varchar(50))
insert into @CDR select 3.6,2177282827,2177251212
insert into @CDR select 4.6,2177282827,2177251212
insert into @CDR select 1.2,2177274526,2012201212
insert into @CDR select 2.6,2177288983,2177252121
insert into @CDR select 2.6,2177288983,2012201212
insert into @CDR select 5.6,2175550203,2012201212

Declare @VENDOR1 Table (VENDOR1_npanxx bigint,VENDOR1rate_inter varchar(100),VENDOR1rate_intra varchar(100))

insert into @VENDOR1 select 217728,0.02,0.03
insert into @VENDOR1 select 2177288,0.01,0.015
insert into @VENDOR1 select 217727,0.012,0.014


Declare @VENDOR2 Table (VENDOR2_npanxx bigint,VENDOR2rate_inter varchar(100),VENDOR2rate_intra varchar(100))

insert into @VENDOR2 select 217728,0.01,0.02
insert into @VENDOR2 select 2177274,0.011,0.013


Declare @LergData Table (npanxx bigint,state varchar(3))

insert into @LergData select 217728,IL
insert into @LergData select 217727,IL
insert into @LergData select 217725,IL
insert into @LergData select 201220,NJ



------------This is how the output should be-----------------

CallDuration,AvgRate,Cost,Vendor

5.6,NULL, NULL, NULL
8.2,0.02, 0.164, Vendor2_Intra
1.2,0.011,0.0132,Vendor2_Inter
2.6,0.015,0.039, Vendor1_Intra
2.6,0.01, 0.026, Vendor1_Inter

--------------------------------------------------------------


--------------------------------------------------------------

If I wasn't Averaging and Summing the data together this would be the output of each record. Average and Sum everything and you should get the above.

CallDuration,Rate,Cost,Vendor

3.6,0.02, 0.072, Vendor2_Intra
4.6,0.02, 0.092, Vendor2_Intra
1.2,0.011,0.0132,Vendor2_Inter
2.6,0.015,0.039, Vendor1_Intra
2.6,0.01, 0.026, Vendor1_Inter
5.6,NULL, NULL, NULL

--------------------------------------------------------------




quote:
Originally posted by Vinnie881

Ok... You've got a little bit of a mess here. Please do this so I don't need to spend a ton of time going through everything.

Just give me just sample data and then the Desired results for that data.

so here are the first 3 tables, please add any others that factor into your results


Declare @CDR table(TermNumber bigint,Duration decimal(5,2))
insert into @CDR select 2177282827,3.6
insert into @CDR select 2177274526,1.2
insert into @CDR select 2177288983,2.6
insert into @CDR select 2177288983,2.6

Declare @Rates Table (npanxx bigint,rate varchar(100))

insert into @Rates select 2177283,0.01
insert into @Rates select 2177288,0.01
insert into @Rates select 217728,0.01
insert into @Rates select 2,0.04
insert into @Rates select 21,0.03

Declare @Rates2 Table (npanxx bigint,rate varchar(100))

insert into @Rates2 select 217728,0.01
insert into @Rates2 select 2,0.03
insert into @Rates2 select 21,0.03


In the same format as above show me the other tables, and include some sample data as I have with the first 3. Then post the Desired results you want for the Data provided. It should be a lot easier to look at that way.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2009-05-13 : 20:53:46
[code]
Declare @CDR table(CallDuration decimal(5,2),TermNumber bigint,OrigNumber bigint,CDRS varchar(50))
insert into @CDR select 3.6,2177282827,2177251212,'CDRS'
insert into @CDR select 4.6,2177282827,2177251212,'CDRS'
insert into @CDR select 1.2,2177274526,2012201212,'CDRS'
insert into @CDR select 2.6,2177288983,2177252121,'CDRS'
insert into @CDR select 2.6,2177288983,2012201212,'CDRS'
insert into @CDR select 5.6,2175550203,2012201212,'CDRS'

Declare @VENDOR1 Table (VENDOR1_npanxx bigint,VENDOR1rate_inter varchar(100),VENDOR1rate_intra varchar(100))

insert into @VENDOR1 select 217728,0.02,0.03
insert into @VENDOR1 select 2177288,0.01,0.015
insert into @VENDOR1 select 217727,0.012,0.014


Declare @VENDOR2 Table (VENDOR2_npanxx bigint,VENDOR2rate_inter varchar(100),VENDOR2rate_intra varchar(100))
insert into @VENDOR2 select 217728,0.01,0.02
insert into @VENDOR2 select 2177274,0.011,0.013


Declare @LergData Table (npanxx bigint,[state] varchar(3))

insert into @LergData select 217728,'IL'
insert into @LergData select 217727,'IL'
insert into @LergData select 217725,'IL'
insert into @LergData select 201220,'NJ'



Declare @Tmp table (CallDuration decimal(5,3),Rate decimal(5,3),Cost decimal(10,5),Vendor Varchar(50))

Insert Into @TMP
select CallDuration
,case when StateO = StateT then RateIntra else RateInter end as rate
,case when StateO =StateT then Convert(Decimal(10,5),RateIntra) else Convert(decimal(10,5),RateInter) end * convert(decimal(10,5),CallDuration) as Cost
,case when StateO =StateT then MyRate + '_Intra' else MyRate + '_Inter' end as Vendor
--Select *
from
(
Select Row_Number() over (Partition by a.ID order by c.rateInter) as RowID,a.ID, a.CallDuration,a.TermNumber,a.OrigNumber,b.State as StateO,d.State as StateT,c.RateInter,c.RateIntra,c.MyRate,c.Npanxx
from
(
Select Row_Number() over (order by (select 0)) as ID,*
From
@CDR
) a
Left join
@LergData b
on left(a.TermNumber,len(b.npanxx)) = b.npanxx
Left Join
@LergData d
on left(a.OrigNumber,len(d.npanxx)) = d.npanxx
Left Join
(Select 'Vendor1' as MyRate,aaa.vendor1Rate_Inter as rateInter,Vendor1Rate_Intra as RateIntra,Vendor1_Npanxx as Npanxx
from
@Vendor1 aaa
Union all
Select 'Vendor2',bbb.Vendor2Rate_Inter,Vendor2Rate_Intra,Vendor2_npanxx
From
@Vendor2 bbb
) c
on left(a.TermNumber,len(c.npanxx)) = c.npanxx
) aa
where aa.Rowid = 1


Select * from @Tmp

Select
Sum(CallDuration) AS CallDuration
,AVG(Rate) as AverageRate
,Sum(Cost) as Cost
,Vendor
from
@TMP a
group by Vendor
order by Vendor desc
[/code]

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

nhess80
Yak Posting Veteran

83 Posts
Posted - 2009-05-13 : 21:12:56
Since this query will be created is various ways over and over, do I need to do a drop table after the query is run so that is doesn't store this data? I guess I thought that when you do a create tmp table that the data is stored until you do a drop table. Thanks for all your help by the way. I think I need to buy a book on sql. Any good onces you recommend?


quote:
Originally posted by Vinnie881


Declare @CDR table(CallDuration decimal(5,2),TermNumber bigint,OrigNumber bigint,CDRS varchar(50))
insert into @CDR select 3.6,2177282827,2177251212,'CDRS'
insert into @CDR select 4.6,2177282827,2177251212,'CDRS'
insert into @CDR select 1.2,2177274526,2012201212,'CDRS'
insert into @CDR select 2.6,2177288983,2177252121,'CDRS'
insert into @CDR select 2.6,2177288983,2012201212,'CDRS'
insert into @CDR select 5.6,2175550203,2012201212,'CDRS'

Declare @VENDOR1 Table (VENDOR1_npanxx bigint,VENDOR1rate_inter varchar(100),VENDOR1rate_intra varchar(100))

insert into @VENDOR1 select 217728,0.02,0.03
insert into @VENDOR1 select 2177288,0.01,0.015
insert into @VENDOR1 select 217727,0.012,0.014


Declare @VENDOR2 Table (VENDOR2_npanxx bigint,VENDOR2rate_inter varchar(100),VENDOR2rate_intra varchar(100))
insert into @VENDOR2 select 217728,0.01,0.02
insert into @VENDOR2 select 2177274,0.011,0.013


Declare @LergData Table (npanxx bigint,[state] varchar(3))

insert into @LergData select 217728,'IL'
insert into @LergData select 217727,'IL'
insert into @LergData select 217725,'IL'
insert into @LergData select 201220,'NJ'



Declare @Tmp table (CallDuration decimal(5,3),Rate decimal(5,3),Cost decimal(10,5),Vendor Varchar(50))

Insert Into @TMP
select CallDuration
,case when StateO = StateT then RateIntra else RateInter end as rate
,case when StateO =StateT then Convert(Decimal(10,5),RateIntra) else Convert(decimal(10,5),RateInter) end * convert(decimal(10,5),CallDuration) as Cost
,case when StateO =StateT then MyRate + '_Intra' else MyRate + '_Inter' end as Vendor
--Select *
from
(
	Select Row_Number() over (Partition by a.ID order by c.rateInter) as RowID,a.ID, a.CallDuration,a.TermNumber,a.OrigNumber,b.State as StateO,d.State as StateT,c.RateInter,c.RateIntra,c.MyRate,c.Npanxx
	from
	(
	Select Row_Number() over (order by (select 0)) as ID,*
	From
	@CDR 
	) a
	Left join
	@LergData b
	on left(a.TermNumber,len(b.npanxx)) = b.npanxx
	Left Join
	@LergData d
	on left(a.OrigNumber,len(d.npanxx)) = d.npanxx
	Left Join
	(Select 'Vendor1' as MyRate,aaa.vendor1Rate_Inter as rateInter,Vendor1Rate_Intra as RateIntra,Vendor1_Npanxx as Npanxx
	 from
	 @Vendor1 aaa
	 Union all 
	 Select 'Vendor2',bbb.Vendor2Rate_Inter,Vendor2Rate_Intra,Vendor2_npanxx
	 From
     @Vendor2 bbb
    ) c
	on left(a.TermNumber,len(c.npanxx)) = c.npanxx
) aa
where aa.Rowid = 1


Select * from @Tmp

Select 
Sum(CallDuration) AS CallDuration
,AVG(Rate) as AverageRate
,Sum(Cost) as Cost
,Vendor
from
@TMP a
group by Vendor
order by Vendor desc


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2009-05-13 : 23:41:05
I just used the table variables for sample purpose, you do not need to use them, and this was just to illustrate how you can use the query. The answer to your question is No you do not need to drop table variables(any table created with a Declare statment). Since they are variables, it will be disolved automatically as soon as the code is complete.

As far as books on SQL, I do not know of any. Almost everything I learned is from the BOL, and trial and error. SQL Forums is a great area to pick up tips as well. Good luck.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

nhess80
Yak Posting Veteran

83 Posts
Posted - 2009-05-14 : 15:03:39
Thank you very much for all of your help. And just so I understand, when I use my existing tables and I insert my output data into @TMP table or whatever I call it, after the code completes the @TMP will desolve?


quote:
Originally posted by Vinnie881

I just used the table variables for sample purpose, you do not need to use them, and this was just to illustrate how you can use the query. The answer to your question is No you do not need to drop table variables(any table created with a Declare statment). Since they are variables, it will be disolved automatically as soon as the code is complete.

As far as books on SQL, I do not know of any. Almost everything I learned is from the BOL, and trial and error. SQL Forums is a great area to pick up tips as well. Good luck.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2009-05-14 : 19:13:28
yes that is correct.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site