SQLTeam.com | Weblogs | Forums

How to use split tables in a CTE

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 + '')

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
)

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.

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.

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

Thanks Jeff.

This is what I am looking for.