While loop skeeping records

[Motlatjo]

Hi all

I have a table with two records, and i want to manipulate one of the columns.

here are the records
Name DateCreated TotalTime(ms)
Chris 2013-01-31 07:32:20.657 9600000
Chris 2013-01-31 10:14:16.817 20479000

The column i want to manipulate is the totalTime(millisecond). i want each record to have a maximum time of less than an hour.
An hour = 3600000ms
expected results

Name DateCreated TotalTime(ms)
Chris 2013-01-31 07:32:20.657 3600000
Chris 2013-01-31 08:32:20.657 3600000
Chris 2013-01-31 09:32:20.657 2400000
------------------------------------------------
Chris 2013-01-31 10:14:16.817 3600000
Chris 2013-01-31 11:14:16.817 3600000
Chris 2013-01-31 12:14:16.817 3600000
Chris 2013-01-31 13:14:16.817 3600000
Chris 2013-01-31 14:14:16.817 3600000
Chris 2013-01-31 15:14:16.817 2479000

This is my code:

Declare @RowCount BigInt,
@Agent Varchar(50),
@Time datetime,
@TotalTime bigint,
@Version varchar(5),
@RowSize bigint,
@iNextAgentID Varchar(10),
@iLoopControl int,
@iCurrentAgentID Varchar(10),
@TotalTimeML int,
@iInnerLoopCtrl int


Select @RowCount = @@ROWCOUNT From AgentTimeDetailHRS


Select @RowCount RowCounts

select @iLoopControl = 0

select @iNextAgentID = min(Agent)
from AgentTimeDetailHRS
--Order by Time


if isnull(@iNextAgentID,'') = ''
begin
select 'No data in found in table!'
return
end



While @iLoopControl <= @RowCount
Begin

Select @Agent = Agent,
@Time = Time,
@TimeStamp = TimeStamp,
@TotalTime = TotalTime


From AgentTimeDetailHRS a
Where @iNextAgentID = a.Agent

Select @iLoopControl Loops
Select @TotalTimeML = Coalesce(@TotalTime, 0)
Select @TotalTimeML TotalTime
Select @Time Nako
--Inner loop to Iterate through total time > and hour
Select @iInnerLoopCtrl = 0


While @TotalTimeML > 3600000
Begin

Select @TotalTimeML = @TotalTimeML - 3600000

Select @iInnerLoopCtrl InnerLoop
Insert Into AgentTimeDetailHRSTEST
Select @Agent Agent,
DateAdd(HOUR, @iInnerLoopCtrl, @Time) Time,
3600000 TotalTime


select @iInnerLoopCtrl = @iInnerLoopCtrl + 1

Select @iNextTT = MIN(TotalTime)
from AgentTimeDetailHRS


End
Insert Into AgentTimeDetailHRSTEST
Select @Agent Agent,
DateAdd(HOUR, @iInnerLoopCtrl, @Time) Time,
@TotalTimeML TotalTime


Select @iCurrentAgentID = @iNextAgentID
select @iNextAgentID = null

select @iNextAgentID = min(Agent)
from AgentTimeDetailHRS
where Agent > @iCurrentAgentID

Select @iLoopControl = @iLoopControl + 1
End


The code works fine, but it only manipulate the second record, i need to manipulated all the records

Your help will be highly appreciated

Thank you!!

 

[gmmastros]

What if I told you this could be accomplished with a fraction of the code and no loops. It would require a numbers table, but the code would execute a lot faster and would likely to be easier to maintain. Would you be interested in that?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Motlatjo]

Hi gmmastros

Yes i will be interested, i am open for any suggestion

Thanks

 

[gmmastros]

I am going to overlook the code you posted and explain the solution in terms of the sample data.

To execute this query, I am using a numbers table. In my database, I have a table named Numbers, with just 1 column named Num. This table has 10000 rows where the Num of the first row is 1 and increments by 1 for each row.

To create a numbers table in your database:

SET NOCOUNT ON

Create Table Numbers(Num Int Identity(1,1) Primary Key)

Declare @i Int
Set @i = 1

Begin Transaction

While @i <= 10000
  Begin
    Insert Into Numbers Default Values
    Set @i = @i + 1
  End

Commit Transaction

The code above should only take a second or 2 to run. This numbers table will require less than 1 megabyte of storage in your database.

Now for the code...

The first thing I noticed is that you have multiple output rows for each input row. The number of rows in the output corresponds to the number of hours represented by the TotalTime column (rounded up). We can get this number and use it to join to the numbers table.

The code I show below is placing your sample data in to a table variable. This allows me to test the code without having your actual tables built (in my database). You should use this table variable to play around with the query until you are satisfied that it returns the correct data and you understand it completely.

Declare @Temp Table(Name VarChar(20), DateCreated DateTime, TotalTime BigInt)

Insert Into @Temp Values('Chris', '2013-01-31 07:32:20.657',9600000)
Insert Into @Temp Values('Chris', '2013-01-31 10:14:16.817',20479000)

Select	*, [!](T.TotalTime / 3600000) + 1[/!]
From    @Temp T

Copy/Paste the code above in to a query window and then execute it. Notice that the last column returned will show a number corresponding to the number of rows that should be returned for each row in the input table.

Next, we can join this to the numbers table to actually product the rows, like this:

Declare @Temp Table(Name VarChar(20), DateCreated DateTime, TotalTime BigInt)

Insert Into @Temp Values('Chris', '2013-01-31 07:32:20.657',9600000)
Insert Into @Temp Values('Chris', '2013-01-31 10:14:16.817',20479000)

Select  *, (T.TotalTime / 3600000) + 1
From	@Temp T
[!]        Inner Join Numbers
          On (T.TotalTime / 3600000) + 1 >= Numbers.Num[/!]

When you run this code, you will see 9 rows returned, 3 of them corresponding to the first row in the input table, and 6 for the second row. Also notice the "Num" column. This is producing an integer. For each group, we have a sequence of numbers (1 through NumOfRows). Of course, we're not done, but I hope you can see that we're getting closer.

We still need to modify the DateCreated column. For the first row, we don't want to change the value, but for each additional row, we want to. If you look at the Num column above... the number of hours we want to add to the DateCreated column is (Num - 1). So....

Declare @Temp Table(Name VarChar(20), DateCreated DateTime, TotalTime BigInt)

Insert Into @Temp Values('Chris', '2013-01-31 07:32:20.657',9600000)
Insert Into @Temp Values('Chris', '2013-01-31 10:14:16.817',20479000)

Select  Name,
        [!]DateAdd(Hour, (Numbers.Num - 1), DateCreated)[/!] As DateCreated,
        TotalTime
From	@Temp T
        Inner Join Numbers
          On (T.TotalTime / 3600000) + 1 >= Numbers.Num

The last thing we need to do is to modify the TotalTime column. Basically, we want to show 3600000 except for the last row where we will show the remaining time. This part is kinda tricky. Basically, we can subtract the accumulated number of hours from the original total. If this division results in a number less than 1, then we can show the remainder of the division (this is called Modulus).

Declare @Temp Table(Name VarChar(20), DateCreated DateTime, TotalTime BigInt)

Insert Into @Temp Values('Chris', '2013-01-31 07:32:20.657',9600000)
Insert Into @Temp Values('Chris', '2013-01-31 10:14:16.817',20479000)

Select  Name,
        DateAdd(Hour, (Numbers.Num - 1), DateCreated) As DateCreated,
        [!]Case When (TotalTime - (Numbers.Num - 1) * 3600000) / 3600000 = 0 
             Then TotalTime % 3600000
             Else 3600000 End As TotalTime[/!]
From    @Temp T
        Inner Join Numbers
          On (T.TotalTime / 3600000) + 1 >= Numbers.Num

Copy/paste this code to a query window and run it. Please do not use this code in production until you completely understand it. I know that there are a couple of tricky concepts introduced here. If there is any part of this that you don't understand, please let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Motlatjo]

First i want to thank for your time.
I need to to explain this formular further if you can

Case When (TotalTime - (Numbers.Num - 1) * 3600000) / 3600000 = 0
Then TotalTime % 3600000
Else 3600000 End As TotalTime

Which is like

Case When (9600000 - (3 - 1) * 3600000) / 3600000 = 0
Then TotalTime % 3600000
Else 3600000 End As TotalTime

It is confusing me

Thank you

 

[gmmastros]

Sure....

Let's look at the first row of the input table, where TotalTime = 9600000

We have 3 rows in the output. The Num column would be 1, 2, and 3.

In your example above, when the num column = 3...

Case When (9600000 - (3 - 1) * 3600000) / 3600000 = 0 
Then TotalTime % 3600000
Else 3600000 End As TotalTime

Evaluating the numbers...

Case When (9600000 - [!]2[/!] * 3600000) / 3600000 = 0 
Then TotalTime % 3600000
Else 3600000 End As TotalTime

Case When (9600000 - [!]2[/!] * 3600000) / 3600000 = 0 
Then TotalTime % 3600000
Else 3600000 End As TotalTime

2 * 3600000 = 7200000

Case When (9600000 - [!]7200000[/!]) / 3600000 = 0 
Then TotalTime % 3600000
Else 3600000 End As TotalTime

9600000 - 7200000 = 2400000

Case When [!]2400000[/!] / 3600000 = 0 
Then TotalTime % 3600000
Else 3600000 End As TotalTime

Let's get rid of some of the zero's. In fact, if we get rid of 5 zeros, we have this...

Case When [!]24 / 36[/!] = 0 
Then TotalTime % 3600000
Else 3600000 End As TotalTime

24 divide by zero is 0.6666666. In SQL Server, you get a different value though. If the numerator is an int, and the denominator is an int, you get integer division. Ex:

Select 24.0/36
Select 24/36

Since everything here is an int, we get 0.

Case When [!]0[/!] = 0 
Then TotalTime % 3600000
Else 3600000 End As TotalTime

Since 0 does equal 0, the case statement will return the "then" part.

Case When 0 = 0 
Then [!]TotalTime % 3600000[/!]
Else 3600000 End As TotalTime

Remember that TotalTime is 9600000 in this example, so we have:

[!]TotalTime % 3600000[/!] As TotalTime

The % symbol is the modulus operator, which returns the remainder of the division.

5 / 3 = 1 and 2/3
5 % 3 = 2

Finally, we have....

[!]2400000[/!] As TotalTime

So.... we're using integer division and the modulus operator in this one case/when statement. Trick and confusing, I know. I hope this makes sense now.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Motlatjo]

Yes it does, the problem was i forgot to use BODMAS rule in the calculation

Thank you

 

[Motlatjo]

Hi

Please help optimising this query if needed




declare @reportFrom DATE,
@reportTo DATE,
@agentID varchar(10),
@hourFrom varchar(10),
@hourTo varchar(10)

set @reportFrom = '2012-03-14'
set @reportTo = '2012-03-14'
set @agentID = 'jkjdkdd'
set @hourFrom = '07:00'
set @hourTo = '16:30'

select 'PSC' TopLevel,
agentName agentName,
surname surname,
q1.agentID agentID,
sum(TotalCalls) TotalCalls,
sum(AnsweredCalls) AnsweredCalls,
sum(AbandonedCalls) AbandonedCalls,
SUM(MissedCalls) MissedCalls,
sum(totContactTime) totContactTime,
sum(aht) aht,
sum(totBreakTime) totBreakTime,
sum(totDevelopmentTime) totDevelopmentTime,
sum(totMeetingTime) totMeetingTime,
sum(processTime) processTime,
sum(totSystemTime) totSystemTime,
sum((totContactTime + totBreakTime + totDevelopmentTime + totMeetingTime + processTime + unionTaskTime + totSystemTime + caserelated + LineBusy)) totTime,
sum((totContactTime + totBreakTime + totDevelopmentTime + totMeetingTime + processTime + unionTaskTime + caserelated + LineBusy )) totLogInTime ,
count(TotalCalls) CountTotalCalls,
Sum(transferCount) transferCount
from (
select c.repDate [Date],
c.agentID,
inboundCall,
--coalesce(noCalls,0) TotalCalls, Addition
coalesce(inboundCall,0) + coalesce(tc.transferCount,0) TotalCalls,
coalesce(answeredCalls,0) + coalesce(tc.answerCount,0) AnsweredCalls,
coalesce(abandonedCalls,0) AbandonedCalls,
coalesce(missedCall,0) MissedCalls,
coalesce(noCases,0) TotalCases,
(available + talk + autoWrap + manlWrap) totContactTime,
case when answeredCalls > 0 then ((autoWrap + manlWrap) / answeredCalls) + (talk / answeredCalls) else 0 end aht,
talk talkTime,
case when answeredCalls > 0 then (talk / answeredCalls) else 0 end avgTalkTime,
case
when c.repDate >= '2010-07-01' then manlWrap
else (autoWrap + manlWrap)
end totWrapUpTime,
autoWrap AutoWrapUpTime,
manlWrap ManualWrapUpTime,
case
when c.repDate >= '2010-07-01' then
case when answeredCalls > 0 then (manlWrap / answeredCalls) else 0 end
else
case when answeredCalls > 0 then ((autoWrap + manlWrap) / answeredCalls) else 0 end
end avgWrapUpTime,
available AvailableTime,
(tea + unavailableTea + lunch + unavailableLunch + personal + unavailablePerson) totBreakTime,
(personal + unavailablePerson) PersonalTime,
(tea + unavailableTea) TeaTime,
(unavailableLunch + lunch) LunchTime,
(coaching + task + training) totDevelopmentTime,
(coaching + unavailableCoachi) CoachingTime,
(task + unavailableTaskT) TaskTime,
training trainingTime,
(generalMeeting + managerMeeting + unionMeeting) totMeetingTime,
(generalMeeting + unavailableGenera) generalMeetingTime,
(managerMeeting + unavailableManage) managerMeetingTime,
unionMeeting uniontMeetingTime,
(process + unavailableProces) processTime,
(unionTask + unavailableUnion) unionTaskTime,
(xSM + xTel + reconnect + initial + timeouts) totSystemTime,
xSM ExitingServiceManagerTime,
xTel ExitingTelephonyTime,
reconnect ReconnectingTime,
initial InitialTime,
timeouts TimeOutTime,
unavailableCaseR caserelated,
LineBusy,

coalesce(transferCount,0) transferCount
from (
select Agent agentID,
[Date] repDate,
case
when [date] >= '2009-10-01' then sum (case when [State] in ('End','Bridged') and /*ISNUMERIC([Queue]) = 0*/ Queue = 'ssmo_english' then 1 else 0 end)
when [date] < '2009-10-01' then sum (case when [State] = 'End' and /*ISNUMERIC([Queue]) = 0*/ Queue = 'ssmo_english' then 1 else 0 end)
end answeredCalls,
sum (case when [State] in ('Hangup','Hang Up') and Queue = 'ssmo_english' then 1 else 0 end) abandonedCalls,
--Addition
sum(case when /**isNumeric([Queue]) = 0 */ Queue = 'ssmo_english' then 1 else 0 end ) inboundCall,
sum(case when isNumeric([Queue]) = 0 then 1 else 0 end ) noCalls
from Call
where convert(varchar(32),CAST(CallStart AS datetime),108) between convert(varchar(32),CAST(@hourFrom AS datetime),108) and convert(varchar(32),CAST(@hourTo AS datetime),108)
and [Date] between (@reportFrom) and (@reportTo)
and [State] <> ''
group by Agent, [Date]
) c
left outer join (
select [Date] repDate,
Agent agentID,
sum (case when Reason in ('Available','Idle') then TotalTime else 0 end) available,
sum (case when Reason in ('TimeOut') then TotalTime else 0 end) timeOuts,
--sum (case when Reason = 'TimeOut' then 1 else 0 end) missedCall ,
sum (case when Reason in ('TimeOut', 'Timed Out') then 1 else 0 end) missedCall, --Addition
sum (case when Reason = 'Coaching' then TotalTime else 0 end) coaching,
sum (case when Reason = 'Training' then TotalTime else 0 end) training,
sum (case when Reason in ('Exiting ServiceManag','Exiting+ServiceManag') then TotalTime else 0 end) xSM,
sum (case when Reason in ('Exiting Telephony','Exiting+Telephony') then TotalTime else 0 end) xTel,
sum (case when Reason in ('In Call' ,'In+Call') then TotalTime else 0 end) talk,
sum (case when Reason = 'Initial' then TotalTime else 0 end) initial,
sum (case when Reason = 'Lunch' then TotalTime else 0 end) lunch,
sum (case when Reason = 'Process' then TotalTime else 0 end) process,
sum (case when Reason in ('Meeting','General Meeting') then TotalTime else 0 end) generalMeeting,
sum (case when Reason like '%Manager%' then TotalTime else 0 end) managerMeeting,
sum (case when Reason = 'Union Meeting' then TotalTime else 0 end) unionMeeting,
sum (case when Reason = 'Union Task' then TotalTime else 0 end) unionTask,
sum (case when Reason = 'Personal' then TotalTime else 0 end) personal,
sum (case when Reason = 'Reconnecting' then TotalTime else 0 end) reconnect,
sum (case when Reason in ('Task Time' ,'Task+Time') then TotalTime else 0 end) task,
sum (case when Reason = 'Tea' then TotalTime else 0 end) tea,
sum (case when Reason = 'Wrap Up' then TotalTime else 0 end) autoWrap,
sum (case when Reason in ('Wrap+Up','Wrap-Up') then TotalTime else 0 end) manlWrap,
sum (case when Reason in ('Unavailable+-+Coachi','Unavailable - Coachi') then TotalTime else 0 end) unavailableCoachi,
sum (case when Reason in ('Unavailable+-+Genera','Unavailable - Genera') then TotalTime else 0 end) unavailableGenera ,
sum (case when Reason in ('Unavailable+-+Lunch','Unavailable - Lunch') then TotalTime else 0 end) unavailableLunch ,
sum (case when Reason in ('Unavailable+-+Manage','Unavailable - Manage') then TotalTime else 0 end) unavailableManage ,
sum (case when Reason in ('Unavailable+-+Person','Unavailable - Person') then TotalTime else 0 end) unavailablePerson ,
sum (case when Reason in ('Unavailable+-+Tea','Unavailable - Tea') then TotalTime else 0 end) unavailableTea ,
sum (case when Reason in ('Unavailable+-+Union','Unavailable - Union') then TotalTime else 0 end) unavailableUnion ,
sum (case when Reason in ('Unavailable+-+Proces','Unavailable - Proces') then TotalTime else 0 end) unavailableProces ,
sum (case when Reason in ('Unavailable+-+Case+R','Unavailable - Case R', 'Case Related') then TotalTime else 0 end) unavailableCaseR ,
sum (case when Reason in ('Unavailable+-+Task+T','Unavailable - Task T') then TotalTime else 0 end) unavailableTaskT,
sum (case when Reason in ('Unavailable+-+Wrap-U','Unavailable - Wrap-U') then TotalTime else 0 end) unavailableWrapU,

sum (case when Reason in ('Line Busy') then TotalTime else 0 end) LineBusy

from AgentTimeDetail
where convert(varchar(32),CAST(time AS datetime),108) between convert(varchar(32),CAST(@hourFrom AS datetime),108) and convert(varchar(32),CAST(@hourTo AS datetime),108)
AND [Date] between (@reportFrom) and (@reportTo)

group by [Date], Agent
) at on c.agentID = at.agentID and c.repDate = at.repDate
left outer join (
select USRID agentID,
DATECREATED repDate,
count(*) noCases
from ATPCases
where DATECREATED between (@reportFrom) and (@reportTo)
group by USRID,
DATECREATED
) ac on c.agentID = ac.agentID and c.repDate = ac.repDate
left outer join (
Select SUM(transferCount) transferCount,
SUM(answerCount) answerCount,
cast(Date as Date) date,
AgentIdFrom
from TransferedCallsSSMO
where CAST (Date as Date) between (@reportFrom) and (@reportTo)
AND USRID in (
group by cast(Date as Date),AgentIdFrom
) tc on c.agentID = tc.AgentIdFrom and c.repDate = tc.date
) q1
Left Outer join (
select AgentID agentID,
Name agentName,
Surname surname,
Title title
from lkpAgentSSMO
) an on q1.agentID = an.agentID

where q1.agentID in (@agentID)or q1.agentID is NULL
group by agentName,surname, q1.agentID

 

[kjv1611]

Hi, if this is a separate issue from your original post, you need to post a new thread. Otherwise, you need to get more into what is wrong with your current script... what are you trying to improve... You've got to share more info than "please help me make this faster".



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57