August 17, 2021 at 5:15 pm
I'm having trouble getting this to fit into a usable CTE (the below is returning the expected data but I need to use it as a CTE for flexibility and OOP)... any clues?:
DECLARE @period INT = (SELECT period FROM [a2hr].[payroll].[fnGetPrevPeriodYear](GetDate())),
@yearINT = (SELECT year FROM [a2hr].[payroll].[fnGetPrevPeriodYear](GetDate())),
@PRStartDate Date,
@PREndDate Date
SELECT top 1 @PRStartDate = [startDate]
,@PREndDate = [endDate]
FROM [a2hr].[payroll].[PayPeriods]
where year = @year and period = @period
SELECT [Date] AS [Date]
,cast([AGENT EMAIL] as varchar) AS [AgentInfo]
,[LOGIN TIMESTAMP] AS [Login Timestamp]
,[LOGOUT TIMESTAMP] AS [Logout Timestamp]
,dbo.fnGetDecimalTime([LOGIN TIME]) AS [Time]
FROM [a2wh].[dbo].[AgentHours]
where date >= @PRStartDate and date <= @PREndDate
UNION ALL
SELECT [DATE] AS [Date]
,'' AS [AgentInfo]
,'' AS [Login Timestamp]
,'' AS [Logout Timestamp]
,dbo.fnGetDecimalTime([Total Time]) AS [Time]
FROM [a2hr].[dbo].[stgOneConvoHrs]
where date >= @PRStartDate and date <= @PREndDate
August 17, 2021 at 7:20 pm
I'm guessing you meant a UDF (user-defined function) or more specifically, an ITVF (inline-table valued function) rather than a CTE, since a common table expression doesn't really seem to enhance flexibility or reusability (assume that's what you meant referring to OOP)?
To get an inline function, you'd probably need to either
One could probably encapsulate the variable logic into the WITH portion of a CTE and then incorporate that into your union all selects with cross apply... but not in an ITVF.
.
August 17, 2021 at 7:34 pm
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 the results.
August 17, 2021 at 7:38 pm
Here is what I think you are looking for - if I am understanding the code you provided.
WITH prDates
AS (
SELECT dt.PRStartDate
, dt.PREndDate
FROM a2hr.payroll.fnGetPrevPeriodYear(getdate()) py
CROSS APPLY (SELECT TOP 1
PRStartDate = pp.startDate
, PREndDate = pp.endDate
FROM a2hr.payroll.PayPeriods pp
Where pp.[Year] = py.[Year]
AND pp.Period = py.Period
) dt
)
SELECT [Date] AS [Date]
,cast([AGENT EMAIL] as varchar) AS [AgentInfo]
,[LOGIN TIMESTAMP] AS [Login Timestamp]
,[LOGOUT TIMESTAMP] AS [Logout Timestamp]
,dbo.fnGetDecimalTime([LOGIN TIME]) AS [Time]
FROM [a2wh].[dbo].[AgentHours] ah
INNER JOIN prDates pd ON ah.[Date] BETWEEN pd.PRStartDate AND pd.PREndDate
UNION ALL
SELECT [DATE] AS [Date]
,'' AS [AgentInfo]
,'' AS [Login Timestamp]
,'' AS [Logout Timestamp]
,dbo.fnGetDecimalTime([Total Time]) AS [Time]
FROM [a2hr].[dbo].[stgOneConvoHrs] och
INNER JOIN prDates pd1 ON och.[Date] BETWEEN pd1.PRStartDate AND pd1.PREndDate;
This should return a single row for prDates - where that row has the start/end date range. If the column from the table(s) is actually a datetime data type then you would not want to use BETWEEN.
I highly recommend that you get in the habit of defining aliases for your tables - and using the alias for all column references.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 17, 2021 at 7:54 pm
This is exactly the direction I was looking for... thanks!!
August 17, 2021 at 8:16 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 like DOMAIN and AGENT and query the CTE based upon values I choose?:
SELECT [Date] AS [Date],
[DOMAIN] AS [Domain],
[AGENT] AS [Agent],
cast([AGENT EMAIL] as varchar) AS [AgentInfo],
[LOGIN TIMESTAMP] AS [Login Timestamp],
[LOGOUT TIMESTAMP] AS
[Logout Timestamp],
dbo.fnGetDecimalTime([LOGIN TIME]) AS [Time]
FROM [a2wh].[dbo].[AgentHours] ah
INNER JOIN prDates pd ON ah.[Date] BETWEEN pd.PRStartDate AND pd.PREndDate
August 17, 2021 at 9:17 pm
Not sure I follow - what CTE are you referring to? The only CTE in the above statement is prDates - which just returns the date range. I would create a function from this with a parameter to define the current date.
Then you can use the function as a table in the from clause - passing in getdate() or some other date and filter the results.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 17, 2021 at 9:29 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 lost here as I'm seeing no way to take the result set of my original code or the new code and selecting based on a value. My ending data from my original code, and the new code, looks something like this and I just want to do selects on dates, domains, etc.:
Date, Domain, Agent, AgentInfo, Login Timestamp, Logout Timestamp, Time
2021-07-26, MTM, shamyah.herbinmtm, 13876@myco.com, 2021-07-26 13:12:22 2021-07-26 14:30:38 1.30
2021-07-26, MTM, kasheena.parkermtm, 14735@myco.com, 2021-07-26 16:15:41 2021-07-26 16:39:39 0.40
2021-07-26, MTM, kasheena.parkermtm, 14735@myco.com, 2021-07-26 16:43:27 2021-07-26 18:01:25 1.30
2021-07-27, 05, jervis.escala5, jescala@myco.com, 2021-07-27 05:59:28 2021-07-27 06:49:58 0.84
2021-07-27, 05, jervis.escala5, jescala@myco.com, 2021-07-27 06:49:58 2021-07-27 07:04:30 0.24
2021-07-27, 05, jervis.escala5, jescala@myco.com, 2021-07-27 07:12:06 2021-07-27 10:39:34 3.46
August 17, 2021 at 9:44 pm
Like I said - you could use a function. But you could also do this:
WITH prDates
AS (
SELECT dt.PRStartDate
, dt.PREndDate
FROM a2hr.payroll.fnGetPrevPeriodYear(getdate()) py
CROSS APPLY (SELECT TOP 1
PRStartDate = pp.startDate
, PREndDate = pp.endDate
FROM a2hr.payroll.PayPeriods pp
Where pp.[Year] = py.[Year]
AND pp.Period = py.Period
) dt
)
, combinedData
AS (
SELECT [Date] AS [Date]
,cast([AGENT EMAIL] as varchar) AS [AgentInfo]
,[LOGIN TIMESTAMP] AS [Login Timestamp]
,[LOGOUT TIMESTAMP] AS [Logout Timestamp]
,dbo.fnGetDecimalTime([LOGIN TIME]) AS [Time]
FROM [a2wh].[dbo].[AgentHours] ah
INNER JOIN prDates pd ON ah.[Date] BETWEEN pd.PRStartDate AND pd.PREndDate
UNION ALL
SELECT [DATE] AS [Date]
,'' AS [AgentInfo]
,'' AS [Login Timestamp]
,'' AS [Logout Timestamp]
,dbo.fnGetDecimalTime([Total Time]) AS [Time]
FROM [a2hr].[dbo].[stgOneConvoHrs] och
INNER JOIN prDates pd1 ON och.[Date] BETWEEN pd1.PRStartDate AND pd1.PREndDate
)
SELECT *
FROM combinedData
WHERE [Date] = '2021-07-26';
If you want more columns - then add those columns to each select in the combinedData CTE.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 18, 2021 at 2:07 am
Jeff is helping you get there, but you should consider his advice about turning it into a function. It'll be reusable, cleaner and if you wanted to change the logic in the future, it'll be in one place.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
August 18, 2021 at 12:36 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 2:49 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 '% + @keyword + %' ':
USE [a2hr]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetDailyHoursByAgent]
(
@keyword varchar(50)
)
RETURNS TABLE
AS
RETURN
(
WITH prDates_CTE
AS (
SELECT dt.PRStartDate
,dt.PREndDate
FROM a2hr.payroll.fnGetPrevPeriodYear(getdate()) py
CROSS APPLY (SELECT TOP 1
PRStartDate = pp.startDate
,PREndDate = pp.endDate
FROM a2hr.payroll.PayPeriods pp
WHERE pp.[Year] = py.[Year]
AND pp.Period = py.Period
) dt
)
,combinedData_CTE
AS (
SELECT [Date] AS [Date]
,[DOMAIN] AS [Domain]
,[AGENT] AS [Agent]
,cast([AGENT EMAIL] as varchar) AS [AgentInfo]
,[LOGIN TIMESTAMP] AS [Login Timestamp]
,[LOGOUT TIMESTAMP] AS [Logout Timestamp]
,dbo.fnGetDecimalTime([LOGIN TIME]) AS [Time]
FROM [a2wh].[dbo].[AgentHours] ah
INNER JOIN prDates_CTE pd ON ah.[Date] BETWEEN pd.PRStartDate AND pd.PREndDate
UNION ALL
SELECT [DATE] AS [Date]
,[DOMAIN] AS [Domain]
,[Agent Name] AS [Agent]
,'' AS [AgentInfo]
,'' AS [Login Timestamp]
,'' AS [Logout Timestamp]
,dbo.fnGetDecimalTime([Total Time]) AS [Time]
FROM [a2hr].[dbo].[stgOneConvoHrs] och
INNER JOIN prDates_CTE pd1 ON och.[Date] BETWEEN pd1.PRStartDate AND pd1.PREndDate
)
SELECT date, Agent, AgentInfo, domain, sum(time) as 'total for day'
FROM combinedData_CTE
WHERE [Agent] like '% + @keyword + %'
GROUP BY date, Domain, Agent, AgentInfo
)
August 18, 2021 at 3:33 pm
That is one way - but I probably wouldn't set the function up to return the totals. Instead I would do this:
USE [a2hr]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetDailyHoursByAgent]
(
@current_date date
)
RETURNS TABLE
AS
RETURN
(
WITH prDates_CTE
AS (
SELECT dt.PRStartDate
,dt.PREndDate
FROM a2hr.payroll.fnGetPrevPeriodYear(@current_date) py
CROSS APPLY (SELECT TOP 1
PRStartDate = pp.startDate
,PREndDate = pp.endDate
FROM a2hr.payroll.PayPeriods pp
WHERE pp.[Year] = py.[Year]
AND pp.Period = py.Period
) dt
)
SELECT [Date] AS [Date]
,[DOMAIN] AS [Domain]
,[AGENT] AS [Agent]
,cast([AGENT EMAIL] as varchar) AS [AgentInfo]
,[LOGIN TIMESTAMP] AS [Login Timestamp]
,[LOGOUT TIMESTAMP] AS [Logout Timestamp]
,dbo.fnGetDecimalTime([LOGIN TIME]) AS [Time]
FROM [a2wh].[dbo].[AgentHours] ah
INNER JOIN prDates_CTE pd ON ah.[Date] BETWEEN pd.PRStartDate AND pd.PREndDate
UNION ALL
SELECT [DATE] AS [Date]
,[DOMAIN] AS [Domain]
,[Agent Name] AS [Agent]
,'' AS [AgentInfo]
,'' AS [Login Timestamp]
,'' AS [Logout Timestamp]
,dbo.fnGetDecimalTime([Total Time]) AS [Time]
FROM [a2hr].[dbo].[stgOneConvoHrs] och
INNER JOIN prDates_CTE pd1 ON och.[Date] BETWEEN pd1.PRStartDate AND pd1.PREndDate;
)
Then - I can use that as:
DECLARE @keyword varchar(50) = 'some agent';
SELECT date, Agent, AgentInfo, domain, sum(time) as 'total for day'
FROM dbo.fn_GetDailyHoursByAgent(getdate())
WHERE [Agent] like '% + @keyword + %'
GROUP BY date, Domain, Agent, AgentInfo;
This way - I can get either detail or totals based on the current date passed to the function. If I want to see data from a prior period - I pass in a different 'current date'.
Another option would be to pass in the year and period to the function - and use those values to lookup the start/end dates.
Or - you could just pass in the start/end date ranges to the function.
It all depends on how you want to use that function in other queries and/or what other similar queries you have for that same set of data.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 18, 2021 at 4:17 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
(
WITH prDates_CTE
AS (
SELECT dt.PRStartDate
,dt.PREndDate
FROM a2hr.payroll.fnGetPrevPeriodYear(@current_date) py
CROSS APPLY (SELECT TOP 1
PRStartDate = pp.startDate
,PREndDate = pp.endDate
FROM a2hr.payroll.PayPeriods pp
WHERE pp.[Year] = py.[Year]
AND pp.Period = py.Period
) dt
)
SELECT [Date] AS [Date]
,[DOMAIN] AS [Domain]
,[AGENT] AS [Agent]
,cast([AGENT EMAIL] as varchar) AS [AgentInfo]
,[LOGIN TIMESTAMP] AS [Login Timestamp]
,[LOGOUT TIMESTAMP] AS [Logout Timestamp]
,dbo.fnGetDecimalTime([LOGIN TIME]) AS [Time]
FROM [a2wh].[dbo].[AgentHours] ah
INNER JOIN prDates_CTE pd ON ah.[Date] BETWEEN pd.PRStartDate AND pd.PREndDate
UNION ALL
SELECT [DATE] AS [Date]
,[DOMAIN] AS [Domain]
,[Agent Name] AS [Agent]
,'' AS [AgentInfo]
,'' AS [Login Timestamp]
,'' AS [Logout Timestamp]
,dbo.fnGetDecimalTime([Total Time]) AS [Time]
FROM [a2hr].[dbo].[stgOneConvoHrs] och
INNER JOIN prDates_CTE pd1 ON och.[Date] BETWEEN pd1.PRStartDate AND pd1.PREndDate
)
Then I run this and get no data:
DECLARE @keyword varchar(50) = 'fenner';
SELECT date, Agent, AgentInfo, domain, sum(time) as 'total for day'
FROM dbo.fn_GetDailyHoursByAgent_Alternate('2021-07-26')
WHERE [Agent] like '% + @keyword + %'
--@end_date date,
GROUP BY date, Domain, Agent, AgentInfo;
My challenge is with my SQL, before I placed it in a function worked fine, and the results are part of the business requirement - to be able to pass in only the AgentInfo, the dates are determined by the other functions supplied. Simply passing in ' WHERE [Agent] like '%fenner%'' gave me this needed data:
date Agent AgentInfo domain total for day
2021-07-26 Mikayla Fenner 12881 Train 12.00
2021-07-27 Mikayla Fenner 12881 Train 3.00
2021-07-28 Mikayla Fenner 12881 Train 7.50
2021-07-29 MikalaFenner 12881 Except 7.50
2021-07-30 MikalaFenner 12881 Except 7.50
2021-08-02 (b) Mikala Fenner -12881 EVQ 5.91
2021-08-03 mikala.fenner5 12881@myco.com 05 3.93
2021-08-03 (b) Mikala Fenner -12881 EVQ 2.62
2021-08-04 mikala.fenner5 12881@myco.com 05 7.69
2021-08-05 mikala.fenner5 12881@myco.com 05 7.67
2021-08-06 mikala.fenner5 12881@myco.com 05 7.41
2021-08-06 (b) Mikala Fenner -12881 EVQ 0.56
I want to place my SQL that worked into a function, view, stored procedure, or something but not sure it can be done.
WITH prDates_CTE
AS (
SELECT dt.PRStartDate
,dt.PREndDate
FROM a2hr.payroll.fnGetPrevPeriodYear(getdate()) py
CROSS APPLY (SELECT TOP 1
PRStartDate = pp.startDate
,PREndDate = pp.endDate
FROM a2hr.payroll.PayPeriods pp
WHERE pp.[Year] = py.[Year]
AND pp.Period = py.Period
) dt
)
,combinedData_CTE
AS (
SELECT [Date] AS [Date]
,[DOMAIN] AS [Domain]
,[AGENT] AS [Agent]
,cast([AGENT EMAIL] as varchar) AS [AgentInfo]
,[LOGIN TIMESTAMP] AS [Login Timestamp]
,[LOGOUT TIMESTAMP] AS [Logout Timestamp]
,dbo.fnGetDecimalTime([LOGIN TIME]) AS [Time]
FROM [a2wh].[dbo].[AgentHours] ah
INNER JOIN prDates_CTE pd ON ah.[Date] BETWEEN pd.PRStartDate AND pd.PREndDate
UNION ALL
SELECT [DATE] AS [Date]
,[DOMAIN] AS [Domain]
,[Agent Name] AS [Agent]
,'' AS [AgentInfo]
,'' AS [Login Timestamp]
,'' AS [Logout Timestamp]
,dbo.fnGetDecimalTime([Total Time]) AS [Time]
FROM [a2hr].[dbo].[stgOneConvoHrs] och
INNER JOIN prDates_CTE pd1 ON och.[Date] BETWEEN pd1.PRStartDate AND pd1.PREndDate
)
SELECT date, Agent, AgentInfo, domain, sum(time) as 'total for day'
FROM combinedData_CTE
WHERE [Agent] like '%fenner%'
GROUP BY date, Domain, Agent, AgentInfo
;
August 18, 2021 at 4:37 pm
If you run your alternate function without a where clause or group by - does it return any data? If it does return data - does it return data where the Agent you are searching for is included?
If it doesn't return any data - then you need to determine why using a different date from getdate() in that function doesn't return the expected start/end dates.
The reason I think you should not be returning the totals is because you will then create a different function - using the same code - to return the detail data and now you have separate functions where you only need the one function. And then you will create yet another function when you want to filter on the agent email - or domain - or dates.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply