How to use split tables in a CTE

Transact-SQL
1

Hi

I have a store proc that contains a CTE.

;WITH Glt_Transactions
AS
(
select ga.Dash_Fiscal_Period_Nbr as Period_Nbr, ga.Account_ID, ga.Segment_2 as Facility_Code, ga.Segment_3 as Department_Code, ga.Segment_4 as Gl_Account_Code, glc.Account_Desc, glc.GL_Category_Class_Code,

Activity_Amt = isnull(SUM(CASE
WHEN glc.Balance_Type_Code = 'D' THEN ISNULL(ga.Debit_Amt,0) - ISNULL(ga.Credit_Amt,0)
WHEN glc.Balance_Type_Code = 'C' THEN ISNULL(ga.Credit_Amt,0) - ISNULL(ga.Debit_Amt,0)
END),0)

from GLTS_ACCOUNTS_2016 as ga
inner join GLTS_2016 as g on g.M2K_Glt_ID = ga.M2K_Glt_ID
inner join GLCOAS as glc on glc.Account_ID = ga.Account_ID
left outer join JTP_REPORT_TYPES as jrt on glc.GL_Category_Class_Code = jrt.GL_Category_Class_Code and jrt.report_type_code = '1'
left outer join GL_CATEGORY_CLASS_CODES as gcc on glc.GL_Category_Class_Code = gcc.GL_Category_Class_Code
left outer join GL_CATEGORY_DESC_CODES as gcd2 on gcd2.GL_Category_Level_Code = gcc.GL_Category_Level_2_Code
where ga.Segment_4 >= '40000' and ga.Transaction_Date >= @startDate and ga.Transaction_Date <= @endDate and g.Post_Ind is not null
AND jrt.GL_Category_Class_Code is not null and jrt.report_type_code = '1'
AND (gcd2.GL_Category_Level_Code IN (Select value from dbo.fn_convertCommaValueToTable(@glCategoryLevelCode, ',') ) OR @glCategoryLevelCode is null)
group by ga.Dash_Fiscal_Period_Nbr, ga.Account_ID, ga.Segment_2, ga.Segment_3, ga.Segment_4, glc.Account_Desc, glc.GL_Category_Class_Code
)

I also have split tables based on years.

Example :
GLTS_2016
GLTS_2015
GLTS_2014
etc

and

GLTS_ACCOUNTS_2016
GLTS_ACCOUNTS_2015
GLTS_ACCOUNTS_2014
etc

My question is how can I allow a user to select on different years with in the same store proc.

I did try creating a view within the store proc but the problem that I run into is I can;t make the view dynamic.

declare @endyear varchar(30)
set @endyear = 'GLTS_2014'

EXEC ('CREATE VIEW Test
as

SELECT *
FROM GLTS
UNION ALL
SELECT *
FROM ' + @endYear + '')

2

You could use an inline-table-valued-function as effectively a "dynamic view". For example:

SELECT * FROM dbo.GLTS_ALL(2015);

CREATE FUNCTION dbo.GLTS_ALL ( @year int )
RETURNS TABLE
AS
RETURN (
    SELECT *
    FROM GLTS_2014
    WHERE @year >= 2014
    UNION ALL
    SELECT *
    FROM GLTS_2015
    WHERE @year >= 2015
    UNION ALL
    SELECT *
    FROM GLTS_2016
    WHERE @year >= 2016
)
3

Thanks for responding.

If I use this example the problem that I will have is union on all the tables..

So if I select on 2016 and 2015 date range. I don't want the function to also select on 2014, 2013 tables.

This will make the process very slow.

4

The WHERE condition should prevent unneeded tables from being accessed. You can verify that by looking at the query plan. If necessary, add OPTION to RECOMPILE the query.

5

Please read the following on a thing called "Partitioned Views", which is just exactly what you're looking for.
https://technet.microsoft.com/en-us/library/ms190019(v=sql.105).aspx

6

Thanks Jeff.

This is what I am looking for.