This less than eloquent way works until I get a name like (t) Robert Hester -15421 :

Select Ltrim(SubString([agent name],1,Isnull(Nullif(CHARINDEX(' ',[agent name]),0),1000))) As FirstName,
Ltrim(SUBSTRING([agent name],CharIndex(' ',[agent name]),
Case When...

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

The last name is always the 2nd spot and the first is always the first sport and the employee number is always the number after the dash.

I have a different set of data on which I want to use PARSENAME:

Jamie Pourhussin (WTA) -692

Brenda Johnson -11337

Delia Rosenstiel -11256

Rodderick Douglas LD051121 -13224

Tynesha Townsend -14014

Mikala Fenner -12881

I can get...

That works and teaches me the PARSENAME function better which I'm still trying to understand, but this is most helpful in getting me started to understanding it!

That works even better... thanks!

Got it, putting debug clues in like PRINT @sql helps of course to see what the dynamic is creating:

declare
    @ColumnNames nvarchar(max),
   ...

I thought about using dynamic SQL like  below but there appears to be a syntax error in my dynamic part:

declare
    @ColumnNames nvarchar(max),
  ...

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

This works perfectly! Thanks! I was able to read from the actual file by just using this and was able to add an insert into to get it into a...

Can this be done with a populated table with 24MM rows and 130 fields? I keep getting errors that the tempdb is full (?) I tried copying the table, made...

Here is my complete code for the Merge:

USE [F905]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CLComToA2WH]

AS
BEGIN

SET NOCOUNT ON

UPDATE F905.dbo.stgCallLogCommon SET DOMAIN = '05';

MERGE a2wh.dbo.CallLogCommon AS TARGET

USING F905.dbo.stgCallLogCommon AS...

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

OK my bad... got it... it was the extra parenthesis:

SELECT

SUM(CAST([Convo] AS DECIMAL(10,2)))

,SUM(CAST([F9D2] AS DECIMAL(10,2)))

,SUM(CAST([F9D5] AS DECIMAL(10,2)))

,SUM(CAST([F9DMTM] AS DECIMAL(10,2)))

,SUM(CAST([Exception] AS DECIMAL(10,2)))

,SUM(CAST([Training] AS DECIMAL(10,2)))

,SUM(CAST([TOTAL Time] AS DECIMAL(10,2)))

FROM [a2hr].[dbo].[FinalPayroll] where period = 18

Yes this by itself workss:

SELECT

SUM(CAST([Convo] AS DECIMAL(10,2)))

FROM [a2hr].[dbo].[FinalPayroll] where period = 18

Errors of 'Invalid Column Name':  SUM(CAST([Convo] AS DECIMAL(10,2)))

Errors of 'Invalid Column Name':  SUM(CAST([F902] AS DECIMAL(10,2))) SUM is not recognized as a valid function

etc.

I ended up rolling all the code below into a stored procedure and it works this way:

USE [A2WH]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_GetDailyHoursByAgent]
@keyword varchar(50)
AS
BEGIN
WITH prDates_CTE 
...

I put this in as my function definition (changing the name so as to save the original):

USE [a2hr]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_GetDailyHoursByAgent_Alternate]
(
@current_date date
) 
RETURNS TABLE
AS
RETURN
...

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