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

This is exactly the direction I was looking for... thanks!!

Really my only goal is to be able to be able to query the results of the code posted and select different fields and values for those fields to narrow...