Calculating the first call - 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

Calculating the first call

Author

Topic

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-08-22 : 12:26:48

Hi,

I have the following script to calculate the calls. What it does is set's the rate for the first call to 2 and If I get another call within one hour it doesn't give that 2 dollar rate. It compares the end time to the previous call and decides if should pay or not. Everything works...

However, I just realized if it's the FIRST call ever, the person doesn't get the $2. It has nothing to compare the end time of the previous call and it thinks that it's new call.

Is there any way, I can set the first call ever to $2 and then do the calculation as it suppose to.

Here's the code:

SELECT     VendorID, FirstName, LastName, UCID, RemoteAgentID, CallDateConvert, [Customer NO], Company, ANI, Language, [Talk Time (Minutes)], [StartTime], [EndTime], 
                      PerMinuteRate, AgentCallID, RowNum = RANK() OVER (PARTITION BY RemoteAgentID
ORDER BY StartTime)
FROM         dbo.RemoteAgentCallRecords)
    SELECT     c.VendorID, c.FirstName, c.LastName, c.UCID, c.RemoteAgentID, c.CallDateConvert, c.[Customer NO], c.Company, c.ANI, c.Language, c.[Talk Time (Minutes)], 
                            c.[StartTime], c.[EndTime], c.PerMinuteRate, c.AgentCallID, c.RowNum, (CASE WHEN c.RowNum = 1 THEN 2 WHEN DATEDIFF(mi, COALESCE (p.[EndTime], 
                            GETDATE()), c.[StartTime]) > 60 THEN 2 ELSE 0 END) AS AnswerRate, (CASE WHEN DATEDIFF(mi, COALESCE (p.[EndTime], GETDATE()), c.[StartTime]) 
                            > 60 THEN (2 + (c.[Talk Time (Minutes)] * c.PerMinuteRate)) ELSE (c.[Talk Time (Minutes)] * c.PerMinuteRate) END) AS [Total Earned]
     FROM         CTE c LEFT JOIN
                            CTE p ON (p.RemoteAgentID = c.RemoteAgentID) AND (p.RowNum = c.RowNum - 1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 12:31:18

should you be using ROW_NUMBER rather than RANK? does StartTime have timepart also in addition to date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-08-22 : 12:34:18

Yes, one person may get multiple calls within one date. So yes the time is also key part in it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 12:36:26

quote:
Originally posted by burzum

Yes, one person may get multiple calls within one date. So yes the time is also key part in it.

sorry are you telling that StartTime has timepart? otherwise RANK will return same value for multiple records with same date value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-08-22 : 12:44:50

So what I did is I have another column which combines all the records. For example, one user has uniqe ID, which can have multiple calls. Another column I have that it counts the calls. For example, if the user has 10 calls, the count in that column would be 1-10 and then for the other user it would start from 1 again. It resets for each user.

I hope I'm making sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 14:31:51

quote:
Originally posted by burzum

So what I did is I have another column which combines all the records. For example, one user has uniqe ID, which can have multiple calls. Another column I have that it counts the calls. For example, if the user has 10 calls, the count in that column would be 1-10 and then for the other user it would start from 1 again. It resets for each user.

I hope I'm making sense.

thats what i asked

for that shouldnt you be using ROW_NUMBER()
unless column you used have distinct values,RANK can return same value for multiple records within same user based on column value (in this case starttime)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-08-22 : 15:11:08

But what should I do with the default case, to give that $2.00 if it's the first call.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 15:12:25

quote:
Originally posted by burzum

But what should I do with the default case, to give that $2.00 if it's the first call.

add a case condition like

CASE WHEN RowNum=1 then 2 ELSE..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-08-22 : 17:14:46

I already have my first case as that.

Can I make it CASE WHEN RowNum<1 then 2 ELSE..?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 18:00:30

quote:
Originally posted by burzum

I already have my first case as that.

Can I make it CASE WHEN RowNum<1 then 2 ELSE..?

how can it ever be <1 ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-08-22 : 18:04:32

If it's 1 then it means that it's first call for a day. That's why I'm kind of confused how to get the first call ever...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 22:26:05

first call ever based on what?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-08-23 : 10:57:04

To the system. What happens is that everytime somebody calls in, that call get saved in database. And the code above calculates the rates based on that first call. However, there's nothing to compare for the first call. And I can't find a way to program, if the call has nothing to compare, set the rate to 2, else do the code above.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 11:34:54

so that means first call for entire system? if thats case use logic like

min(callnumber) over () as firstcall

and use it in case when

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources