Hi Scott.... revisiting your code above.... I'm getting a "Msg 8120, Level 16, State 1, Line 24

Column 'alias1.CALL_TIME_SECS' is invalid in the select list because it is not contained in...

How do I integrate your

CROSS APPLY dbo.itvfGetSeconds(Call_Time) ctsec

CROSS APPLY dbo.itvfGetSeconds(Talk_Time) ttsec

code with the needed code below?:

FROM a2wh.dbo.CallLogCommon com with (NOLOCK)
JOIN a2wh.dbo.Campaigns ud with (NOLOCK)
ON com.[campaign] = ud.[campaign]

The data going into the function is in the form like 17:03:26 .... Here is my stored procedure but it probably needs some work:

USE [a2wh]
GO

SET ANSI_NULLS ON
GO

SET...

Here is the function:

CREATE FUNCTION [dbo].[fnGetSeconds]
(
@TimeFormatted varchar(10)
)
RETURNS decimal(10, 2)
AS
BEGIN
  RETURN
  (SELECT (LEFT(@TimeFormatted,2)*3600) +
       ROUND(DATEDIFF(MS, 0, '00' + RIGHT(@TimeFormatted,LEN(@TimeFormatted)-2))/1000.0,0)
 ...

My apologies, and I need to back up here as I over-simplified my base challenge and its code. I have a query that will be used as the datasource for...

Excellent tool! If I wanted to not hardcode the dates how could I declare and pass these in as variables?

What about this?:

Update A Set A.[Fname] = B.[Fname]
  from [Reporting].[dbo].[D02_CallLog] A 
  inner join [Reporting].[dbo].[D02_CallLog] B
    on A.[CallID] = B.[CallID]
  where...

Would a Self Join be in order OR something like this ?:

UPDATE [Reporting].[dbo].[D02_CallLog] A
SET A.[Fname] = (SELECT B.[Fname] FROM [Reporting].[dbo].[D02_CallLog] B) 
WHERE A.[CallID] = B.[CallID] and A.[Fname]...

CREATE TABLE [Reporting].[dbo].[D2_CallLog](
        [ID] [Int] NOT NULL,
[Fname] [nvarchar](255) NULL,
[Lname] [nvarchar](255) NULL,
[CallID] [int] NULL,
 CONSTRAINT [PK_D02CallLog] PRIMARY KEY CLUSTERED 
(
[ID] ASC

)...

• This reply was modified 4 years, 6 months ago by DaveBriCam.
• This reply was modified 4 years, 6 months ago by DaveBriCam.

the only link is the CallID number

fnGetSeconds takes time in 00:00:00 format and turns it into seconds:

ALTER FUNCTION [dbo].[fnGetSeconds](@TimeFormatted varchar(10))RETURNS decimal(10, 2)ASBEGINRETURN(SELECT (LEFT(@TimeFormatted,2)*3600) +ROUND(DATEDIFF(MS, 0, '00' + RIGHT(@TimeFormatted,LEN(@TimeFormatted)-2))/1000.0,0)AS 'TimeSeconds')END

then it seems like this should work:

SELECT * FROM (
SELECT  agent, dbo.fnGetSeconds(CALL_TIME) 
FROM 
    [a2wh].[dbo].[CallLogCommon] 
) t
pivot (
    SUM(dbo.fnGetSeconds(CALL_TIME))
 ...

Solved... there were no NULLS but one value was ''

I changed the function to have a length of 10 rather than MAX but the error persists:

CREATE FUNCTION [dbo].[fnGetSeconds]
(
@TimeFormatted varchar(10)
)
RETURNS decimal(10, 2)
AS
BEGIN
  RETURN
  (SELECT (LEFT(@TimeFormatted,2)*3600)...

No nulls in the data but there are some that are 00:00:00