August 11, 2014 at 3:33 pm
Hi,
I have to create a view to query a GL table and consolidate amounts based on periods. The table has fields for account, fiscal_year, accounting_period, and posted_base_amt. There can be multiple entries for the same account, fiscal_year and accounting_period, as there can be multiple postings in GL. My view has to consolidate the amounts for each accounting_period and calculate the cumulative posted_base_amt. For example, in the GL table, there can be entries with different values for accounting_period (0-12), (same fiscal_year) but in my view, period 1 consists of all entries with accounting_period IN (0,1) in the GL table. Similarly, period 2 in view corresponds to accounting_period IN (0,1,2) in the GL table, and period 12 in the view corresponds to rows with accounting_period IN (0,1,2,3,4,5,6,7,8,9,10,11,12).
Data in the GL table starts from 2012-accounting_period 0, and goes up to 2014-accounting_period 7 for now. Next month, there will be data for year 2014, accounting_period 8, and my view has to take that into consideration. For years less than 2014, there are 12 periods, whereas for 2014, there are only 7 periods for now, which will increase every month.
How do I dynamically handle this in my view? Here's the code snippet to give an idea of how the view is being constructed:
---------------
select
FISCAL_YEAR AS [year]
, 1 AS [period]
, ACCOUNT AS account_code
, SUM(POSTED_BASE_AMT) AS ytd_lcl_amount
FROM LEDGER_DELTA
WHERE 1=1
AND FISCAL_YEAR BETWEEN '2012' AND (select year(getdate()))
AND ACCOUNTING_PERIOD IN (0, 1)
GROUP BY
FISCAL_YEAR
, ACCOUNT
UNION ALL
-- Accounting Period = 2
select
FISCAL_YEAR AS [year]
, 2 AS [period]
, ACCOUNT AS account_code
, SUM(POSTED_BASE_AMT) AS ytd_lcl_amount
FROM LEDGER_DELTA
WHERE 1=1
AND FISCAL_YEAR BETWEEN '2012' AND (select year(getdate()))
AND ACCOUNTING_PERIOD IN (0, 1, 2)
GROUP BY
FISCAL_YEAR
, ACCOUNT
UNION ALL
...
--Accounting period = 3
...
UNION ALL
...
--Accounting period = 4
...
UNION ALL
...
...
--Accounting period = 12
-----------
For 2012 and 2013, I require 12 periods, but for 2014, I only require up to period 7 this month, and up to period 8 next month, and so on. Is there a way to use Dynamic SQL in the view? I know it'll be easy to first put this data in a table and then select data up to the period I want in the view, but I'm looking to handle this in the view itself, without using a table.
I'll appreciate any help regarding this.
-Amit
August 11, 2014 at 4:06 pm
This seems horrible because of the triangular join that it includes, but it might get you what you need without dynamic sql. Why are you duplicating the rows? Would an inline table valued function be a better option?
WITH cteTally(N) AS(
SELECT N FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),)e(N)
)
SELECT
LD.FISCAL_YEAR AS [year]
,t.N AS [period]
,LD.ACCOUNT AS account_code
,SUM(LD.POSTED_BASE_AMT) AS ytd_lcl_amount
FROM LEDGER_DELTA LD
JOIN cteTally t ON LD.ACCOUNTING_PERIOD <= t.N
WHERE LD.FISCAL_YEAR > '2012' --Is this a number or a string?
AND LD.ACCOUNTING_PERIOD >= 0 --Is this even necessary?
AND (LD.FISCAL_YEAR * 100) + LD.ACCOUNTING_PERIOD <= CONVERT( char(6), GETDATE(), 112)
GROUP BY
LD.FISCAL_YEAR
,LD.ACCOUNT
,t.N
August 11, 2014 at 4:11 pm
What are you trying to accomplish here?
It looks like you return the running total balance per month an fiscal year.
Why would you worry about this FY having less than 12 month? It won't return any data anyway...
All you might want to do is to exclude results of the current month or accounting peroid. But I'd expect those values not being availablein LEDGER_DELTA if they shouldn't be part of the report...
August 12, 2014 at 8:00 am
LutzM (8/11/2014)
What are you trying to accomplish here?It looks like you return the running total balance per month an fiscal year.
Why would you worry about this FY having less than 12 month? It won't return any data anyway...
All you might want to do is to exclude results of the current month or accounting peroid. But I'd expect those values not being availablein LEDGER_DELTA if they shouldn't be part of the report...
Hi Lutz,
It will return data, as for period 10, year 2014, it'll put all the amounts for period 7, for period 10 too (also, for periods 8, 9, 11 and 12), because of this condition for period 10:
"AND ACCOUNTING_PERIOD IN (0,1,2,3,4,5,6,7,8,9,10)"
as any data for accounting_period = 0,1,2,3,4,5,6,7 - which is what's available now - will also be added for period 10.
I may have found a way to use CTE to build my initial result using UNION ALL, and then select only (period <= max(accounting_period)) for year 2014 from the result.
Thanks,
-Amit
August 12, 2014 at 8:03 am
Luis Cazares (8/11/2014)
WHERE LD.FISCAL_YEAR > '2012' --Is this a number or a string?AND LD.ACCOUNTING_PERIOD >= 0 --Is this even necessary?
[/code]
Hi Luis,
FISCAL_YEAR is an integer, so I'll change that in the script to without single quotes. Though, I've tested using both '2012' and 2012, and it seems to work the same.
I'll test your code and see if it works. 🙂
Thanks,
-Amit
August 21, 2014 at 9:24 am
Luis C.,
Tested your code, and it is exactly the solution I was looking for! Thanks for your help.
-Amit
Luis Cazares (8/11/2014)
This seems horrible because of the triangular join that it includes, but it might get you what you need without dynamic sql. Why are you duplicating the rows? Would an inline table valued function be a better option?
WITH cteTally(N) AS(
SELECT N FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),)e(N)
)
SELECT
LD.FISCAL_YEAR AS [year]
,t.N AS [period]
,LD.ACCOUNT AS account_code
,SUM(LD.POSTED_BASE_AMT) AS ytd_lcl_amount
FROM LEDGER_DELTA LD
JOIN cteTally t ON LD.ACCOUNTING_PERIOD <= t.N
WHERE LD.FISCAL_YEAR > '2012' --Is this a number or a string?
AND LD.ACCOUNTING_PERIOD >= 0 --Is this even necessary?
AND (LD.FISCAL_YEAR * 100) + LD.ACCOUNTING_PERIOD <= CONVERT( char(6), GETDATE(), 112)
GROUP BY
LD.FISCAL_YEAR
,LD.ACCOUNT
,t.N
August 21, 2014 at 9:26 am
One tiny correction in the code (in case anyone else uses it) - should be:
SELECT N FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12))e(N)
There was an extra comma after (12).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply