IF OBJECT_ID(N'tvf_PRODUCTandPCM') IS NOT NULL

DROP FUNCTION tvf_PRODUCTandPCM;

GO

CREATE FUNCTION [dbo].[tvf_PRODUCTandPCM]

(

@REF varchar(18)

,@EndDate date

)

RETURNS table

AS

RETURN

WITH SL_Reduced

...

Slowness is relative to your busyness, it this just you getting more and more impatient? Show us the code and your stats.

Try running the package in 32/64 bit in VS.

http://sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/running-32-bit-ssis-in-a-64-bit-environment.aspx

The person has a calendar where it can add calculated values, if you think CPU then date range is slower than comparing to a small int.

Is there a function f(@StartOfWeek,@EndOfWeek)...

This looks a bit heavy

AND Calendar.Datetime >= @StartOfWeek

AND...

The database is not smart, you will have to come up with something like predictive search, where the user will get a, hopefully short, list of spelling options based on...

No, there is no type that supports IN with a delimited string

One solution is to convert the dates to rows in a datetable

DECLARE @datetable TABLE(d date NOT NULL)

TODO: invent date...

What is the data type of the keys?

sp_helpindex tablename

Yes, the tables can be created under the same dbo.

It depends on what version you are using

MSSQL 2014+ has that feature:

https://msdn.microsoft.com/en-us/library/bb933994.aspx

Same thing, just shorter

SELECT RIGHT(CONVERT(varchar(30), DATEADD(second, DATEDIFF(second, ISNULL(QueueDate,StartDate), EndDate), 0), 120), 8)

Same thing, just shorter

SELECT RIGHT(CONVERT(varchar(30), DATEADD(second, DATEDIFF(second, ISNULL(QueueDate,StartDate), EndDate), 0), 120), 8)

Here's a solution without all the if's and butt's

-- 1) Get the code from sp_linkedservers

sp_helptext sp_linkedservers

-- 2) Copy the code into inline table valued udf

CREATE FUNCTION dbinv.ufn_Get_Linked_Servers ( )

RETURNS table

AS

RETURN

...

Here is a version of your nice script that uses CTE to avoid temp table. It's also re-formatted with SQL Prompt.

DECLARE @year int = 2015

DECLARE @month tinyint = 8

;WITH ...