math in View - 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

math in View

Author

Topic

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-19 : 11:58:18

Hi,

I have a view that has 5 columns.
[Talk Time]
[PerMinuteRate]
[Total Rate]
[AnswerRate]=3 is always same number
[Total Earned]

[Total Rate]=[TalkTime]*[PerMinuteRate]
[Total Earned]=[Total Rate]+[AnswerRate]

What I want is to have in a view a where statement that could check if the talk time is less than an hour it will not add AnswerRate to total, if it's greater than it would.

Any suggestions?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-19 : 12:12:38

SELECT [TotalEarned] = CASE WHEN [Talk Time] < 1
THEN [Total Rate]
ELSE [Total Rate] + [Answer Rate]
END

Jim

Everyday I learn something that somebody else already knew

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-19 : 13:49:13

Jim, thank you for your help.

So as I was doing this I realized I needed some changes.

This is my view.

SELECT     TOP (1000) TRI_PORTAL.dbo.Remote_Staff_Loggin.VendorID, dbo.vw_remoteTSdata.FirstName, dbo.vw_remoteTSdata.LastName, dbo.vw_remoteTSdata.AgentID, 
                      dbo.vw_remoteTSdata.CallDateConvert, dbo.vw_remoteTSdata.[Customer NO], dbo.vw_remoteTSdata.Company, dbo.vw_remoteTSdata.ANI, 
                      dbo.vw_remoteTSdata.Language, dbo.vw_remoteTSdata.[Talk Time (Minutes)], TRI_PORTAL.dbo.Remote_Staff_Loggin.PerMinuteRate, 
                      dbo.vw_remoteTSdata.[Talk Time (Minutes)] * TRI_PORTAL.dbo.Remote_Staff_Loggin.PerMinuteRate AS [Total Rate], 
                      TRI_PORTAL.dbo.Remote_Staff_Loggin.AnswerRate, 
                      TRI_PORTAL.dbo.Remote_Staff_Loggin.AnswerRate + dbo.vw_remoteTSdata.[Talk Time (Minutes)] * TRI_PORTAL.dbo.Remote_Staff_Loggin.PerMinuteRate AS [Total Earned],
                       dbo.vw_remoteTSdata.StartTime
FROM         dbo.vw_remoteTSdata INNER JOIN
                      TRI_PORTAL.dbo.Remote_Staff_Loggin ON dbo.vw_remoteTSdata.AgentID = TRI_PORTAL.dbo.Remote_Staff_Loggin.ReMoteAgentID

The way I get my [Total Earned] is this:

TRI_PORTAL.dbo.Remote_Staff_Loggin.AnswerRate + dbo.vw_remoteTSdata.[Talk Time (Minutes)] * TRI_PORTAL.dbo.Remote_Staff_Loggin.PerMinuteRate AS [Total Earned]

That column doesn't technically exist until I do that operation.

So what I want is this logic:

quote:
if time starting at Start Time is greater than 60 minutes (to check if it's been an hour after the call)

set [Answer Rate] to [Answer Rate]

else set [Answer Rate] =0 as [Rate set]

then depending on that get TOTAL EARNED

[Rate set] + dbo.vw_remoteTSdata.[Talk Time (Minutes)] * TRI_PORTAL.dbo.Remote_Staff_Loggin.PerMinuteRate AS [Total Earned]

This is what I have, but it doesn't work:

SELECT     TOP (1000) TRI_PORTAL.dbo.Remote_Staff_Loggin.VendorID, dbo.vw_remoteTSdata.FirstName, dbo.vw_remoteTSdata.LastName, dbo.vw_remoteTSdata.AgentID, 
                      dbo.vw_remoteTSdata.CallDateConvert, dbo.vw_remoteTSdata.[Customer NO], dbo.vw_remoteTSdata.Company, dbo.vw_remoteTSdata.ANI, 
                      dbo.vw_remoteTSdata.Language, dbo.vw_remoteTSdata.[Talk Time (Minutes)], TRI_PORTAL.dbo.Remote_Staff_Loggin.PerMinuteRate, 
                      dbo.vw_remoteTSdata.[Talk Time (Minutes)] * TRI_PORTAL.dbo.Remote_Staff_Loggin.PerMinuteRate AS [Total Rate], 
					  
					  
					  
					  CASE
						 WHEN
							 dbo.vw_remoteTSdata.StartTime AS [Start Time] > 1
							 
							 THEN 
								TRI_PORTAL.dbo.Remote_Staff_Loggin.AnswerRate = TRI_PORTAL.dbo.Remote_Staff_Loggin.AnswerRate
						
							ELSE
								TRI_PORTAL.dbo.Remote_Staff_Loggin.AnswerRate=0 as [Rate set]
					  
					  
                      [Rate set] + dbo.vw_remoteTSdata.[Talk Time (Minutes)] * TRI_PORTAL.dbo.Remote_Staff_Loggin.PerMinuteRate AS [Total Earned],
					  
					  
					  
					  
                       dbo.vw_remoteTSdata.StartTime
FROM         dbo.vw_remoteTSdata INNER JOIN
                      TRI_PORTAL.dbo.Remote_Staff_Loggin ON dbo.vw_remoteTSdata.AgentID = TRI_PORTAL.dbo.Remote_Staff_Loggin.ReMoteAgentID

Any help please?

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-19 : 14:35:18

This query that I wrote it should do it, BUT there's one part that I can't figure out.

SELECT     TOP (1000) TRI_PORTAL.dbo.Remote_Staff_Loggin.VendorID, dbo.vw_remoteTSdata.FirstName, dbo.vw_remoteTSdata.LastName, dbo.vw_remoteTSdata.AgentID, 
                      dbo.vw_remoteTSdata.CallDateConvert, dbo.vw_remoteTSdata.[Customer NO], dbo.vw_remoteTSdata.Company, dbo.vw_remoteTSdata.ANI, 
                      dbo.vw_remoteTSdata.Language, dbo.vw_remoteTSdata.[Talk Time (Minutes)], TRI_PORTAL.dbo.Remote_Staff_Loggin.PerMinuteRate, 
                      dbo.vw_remoteTSdata.[Talk Time (Minutes)] * TRI_PORTAL.dbo.Remote_Staff_Loggin.PerMinuteRate AS [Total Rate], 
			
                    CASE WHEN DATEDIFF(mi,(endtime of previous call - subquery to determine),dbo.vw_remoteTSdata.StartTime) > 60
					
					THEN TRI_PORTAL.dbo.Remote_Staff_Loggin.AnswerRate 
					
					ELSE 0 
					
					END AS AnswerRate, 

					 TRI_PORTAL.dbo.Remote_Staff_Loggin.AnswerRate + dbo.vw_remoteTSdata.[Talk Time (Minutes)] * TRI_PORTAL.dbo.Remote_Staff_Loggin.PerMinuteRate AS [Total Earned],
                       
					   
					   
                      dbo.vw_remoteTSdata.StartTime
FROM         dbo.vw_remoteTSdata INNER JOIN
                      TRI_PORTAL.dbo.Remote_Staff_Loggin ON dbo.vw_remoteTSdata.AgentID = TRI_PORTAL.dbo.Remote_Staff_Loggin.ReMoteAgentID

The question is how to get ENDTIME OF PREVIOUS CALL?

to have it in this query:

 CASE WHEN DATEDIFF(mi,(endtime of previous call - subquery to determine),dbo.vw_remoteTSdata.StartTime) > 60

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-19 : 16:33:53

Any help?

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-21 : 11:19:41

Problem solved.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources