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 3 years, 11 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 3 years, 12 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, 1 month ago by DaveBriCam.

Is this what is needed to encapsulate the above logic into a TVF? I'm wanting to use a like clause to pass in the variable: ' WHERE [Agent] like '%...

After a good night's sleep letting it 'incubate', I can now see where a Table Valued Function encapsulating the logic above is the way to make this versatile and reusable.

I thought that was what i was doing in my original code:

DECLARE @period INT = (SELECT period FROM [a2hr].[payroll].[fnGetPrevPeriodYear](GetDate())),
@yearINT = (SELECT year FROM [a2hr].[payroll].[fnGetPrevPeriodYear](GetDate())),
@PRStartDate Date,
@PREndDate Date

I'm quite...

If I wanted to do a SELECT on the resulting CTE query results on e.g. "WHERE date = '2021-07-26'  where would I place that?

OR if I wanted to add fields...

• This reply was modified 4 years, 1 month ago by DaveBriCam.