Use a Local Variable in a select script that is set by the select script

Other SQL Server Topics
1

Hi There,
Please help me to find a way where I don't have to repitively use:
Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'')
in the following script.

Select xu.U_Name + ' ' + xu.U_Surname As 'Collector'
, Count(*) As 'Calls'
, SUM(Case When Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'') <= '00:01:00' Then 1 Else 0 End) As 'Less 1 Minute'
, SUM(Case When Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'') > '00:01:00' And Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'') <= '00:05:00' Then 1 Else 0 End) As '>1 <=5 Minutes'
, SUM(Case When Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'') > '00:05:00' And Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'') <= '00:10:00' Then 1 Else 0 End) As '>5 <=10 Minutes'
, SUM(Case When Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'') > '00:10:00' And Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'') <= '00:20:00' Then 1 Else 0 End) As '>10 <=20 Minutes'
, SUM(Case When Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'') > '00:20:00' And Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'') <= '00:30:00' Then 1 Else 0 End) As '>20 <=30 Minutes'
, SUM(Case When Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'') > '00:30:00' And Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'') <= '00:40:00' Then 1 Else 0 End) As '>30 <=40 Minutes'
, SUM(Case When Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'') > '00:40:00' And Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'') <= '00:50:00' Then 1 Else 0 End) As '>40 <=50 Minutes'
, SUM(Case When Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'') > '00:50:00' And Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'') <= '01:00:00' Then 1 Else 0 End) As '>50 <=60 Minutes'
, SUM(Case When Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'') > '01:00:00' Then 1 Else 0 End) As 'Over 1 Hour'
From History xh with (nolock)
Inner Join Users xu with (Nolock) on xu.ID = xh.H_UserID
Where xh.H_HA_ID = 6 -- 6 = Call
and cast(xh.H_DateTime as date) >= CAST(GETDATE() AS date)
Group By xh.H_UserID
, xu.u_title
, xu.U_Name
, xu.U_Surname
Order By Calls Desc

2

Not sure if there are any typos, but something like this might work

select Collector, Calls, 
		Case when TimeDiff <= '00:01:00' Then 1 Else 0 End As 'Less 1 Minute',
		Case when TimeDiff <= '00:05:00' Then 1 Else 0 End As '>1 <=5 Minutes',
		Case when TimeDiff <= '00:10:00' Then 1 Else 0 End As '>5 <=10 Minutes',
		Case when TimeDiff <= '00:20:00' Then 1 Else 0 End As  '>10 <=20 Minutes',
		Case when TimeDiff <= '00:30:00' Then 1 Else 0 End As '>20 <=30 Minutes',
		Case when TimeDiff <= '00:40:00' Then 1 Else 0 End As '>30 <=40 Minutes',
		Case when TimeDiff <= '00:50:00' Then 1 Else 0 End As '>40 <=50 Minutes',
		Case when TimeDiff <= '01:00:00' Then 1 Else 0 End As '>50 <=60 Minutes',
		Case when TimeDiff >  '01:00:00' Then 1 Else 0 End As  'Over 1 Hour'
from (
Select xu.U_Name + ' ' + xu.U_Surname As 'Collector'
, Count(*) As 'Calls'
, SUM(Isnull(cast(xh.H_Call_Ended - xh.H_Call_Start As time),'')) as TimeDiff
From History xh with (nolock)
Inner Join Users xu with (Nolock) on xu.ID = xh.H_UserID
Where xh.H_HA_ID = 6 -- 6 = Call
and cast(xh.H_DateTime as date) >= CAST(GETDATE() AS date)
Group By xh.H_UserID
, xu.u_title
, xu.U_Name
, xu.U_Surname) x
order by calls desc
3

You can use CROSS APPLY to calculate the time difference, then use that column in the calculations:

Select Collector          = xu.U_Name + ' ' + xu.U_Surname
     , Calls              = Count(*)
     , [Less 1 Minute]    = SUM(Case When ex.time_diff <= 01.0 Then 1 Else 0 End)
     , [>1 <=5 Minutes]   = SUM(Case When ex.time_diff >  01.0 And ex.time_diff <= 05.0 Then 1 Else 0 End)
     , [>5 <=10 Minutes]  = SUM(Case When ex.time_diff >  05.0 And ex.time_diff <= 10.0 Then 1 Else 0 End)
     , [>10 <=20 Minutes] = SUM(Case When ex.time_diff >  10.0 And ex.time_diff <= 20.0 Then 1 Else 0 End)
     , [>20 <=30 Minutes] = SUM(Case When ex.time_diff >  20.0 And ex.time_diff <= 30.0 Then 1 Else 0 End)
     , [>30 <=40 Minutes] = SUM(Case When ex.time_diff >  30.0 And ex.time_diff <= 40.0 Then 1 Else 0 End)
     , [>40 <=50 Minutes] = SUM(Case When ex.time_diff >  40.0 And ex.time_diff <= 50.0 Then 1 Else 0 End)
     , [>50 <=60 Minutes] = SUM(Case When ex.time_diff >  50.0 And ex.time_diff <= 60.0 Then 1 Else 0 End)
     , [Over 1 Hour]      = SUM(Case When ex.time_diff >  60.0 Then 1 Else 0 End)
  From History          xh with (nolock)
 Inner Join Users       xu with (Nolock) on xu.ID = xh.H_UserID
 Cross Apply (Values (datediff(second, xh.H_Call_Start, xh.H_Call_Ended) / 60.0)) As ex(time_diff)
 Where xh.H_HA_ID = 6 -- 6 = Call
   and cast(xh.H_DateTime as date) >= CAST(GETDATE() AS date)
 Group By xh.H_UserID, xu.u_title, xu.U_Name, xu.U_Surname
 Order By Calls desc;

I modified this to use the standard DATEDIFF function by seconds - then divided that by 60.0 to get the fractional minutes.

@mike01 - your solution won't actually work, since it would count anything less than 1 minute in all categories except the last category. You need the range check for each column...

4

[mike01]: I am not very good at scripting but I can't get this solution to work.

5

[jeffw8713]: This one works but I am not getting the right results. The Count for [Less 1 Minute] is incorrect and I don't know why.

Thanks for the help guys.

6

[jeffw8713]: I have your script working correctly. All I needed was an Isnull for the datediff before / 60.

I have to learn about the Cross Apply and the Values commands as I have never used them before.

Thanks again, I appreciate your help.

7

I assumed you would always have a start and end date. If either of those can be null - then yes, you would need something to handle the null values.

8

Hi Jeffrey, Your script is working perfectly. Thanks for all your help.

9

You are welcome - thank you for the feedback.