Viewing 15 posts - 46 through 60 (of 391 total)
Got it, putting debug clues in like PRINT @sql helps of course to see what the dynamic is creating:
declare
@ColumnNames nvarchar(max),
...
October 26, 2021 at 3:34 pm
I thought about using dynamic SQL like below but there appears to be a syntax error in my dynamic part:
declare
@ColumnNames nvarchar(max),
...
October 26, 2021 at 3:07 pm
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...
October 6, 2021 at 12:51 pm
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...
September 28, 2021 at 5:29 pm
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...
September 27, 2021 at 1:01 pm
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
September 23, 2021 at 5:20 pm
Yes this by itself workss:
SELECT
SUM(CAST([Convo] AS DECIMAL(10,2)))
FROM [a2hr].[dbo].[FinalPayroll] where period = 18
September 23, 2021 at 5:11 pm
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.
September 23, 2021 at 3:48 pm
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
...
August 18, 2021 at 5:53 pm
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
...
August 18, 2021 at 4:17 pm
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 '%...
August 18, 2021 at 2:49 pm
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.
August 18, 2021 at 12:36 pm
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...
August 17, 2021 at 9:29 pm
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...
August 17, 2021 at 8:16 pm
Viewing 15 posts - 46 through 60 (of 391 total)