Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How to write a CTE to combine two Fiscal years to

Author  Topic 

imtiaz
Starting Member

4 Posts
Posted - 2014-07-23 : 20:17:19
I like to get some input in writing a CTE to combine periods 7 through 12 of one Fiscal year with 1 through 6 of the following year to be part of the same Fiscal year.

I would appreciate any suggestions you may have to do this.

Thanks,
MT.

stepson
Aged Yak Warrior

545 Posts
Posted - 2014-07-24 : 00:47:00
[code]
DECLARE @dStartDate AS DATE ='20130701'

;WITH aCTE
AS
(
SELECT @dStartDate AS Period , 1 AS LVL
UNION ALL
SELECT DATEADD(m,1,Period) , LVL + 1
FROM aCTE
WHERE LVL <12
)


SELECT * FROM aCTE
[/code]

output:
[code]
Period LVL
2013-07-01 1
2013-08-01 2
2013-09-01 3
2013-10-01 4
2013-11-01 5
2013-12-01 6
2014-01-01 7
2014-02-01 8
2014-03-01 9
2014-04-01 10
2014-05-01 11
2014-06-01 12
[/code]

Is this what you expected ?




sabinWeb MCP
Go to Top of Page

imtiaz
Starting Member

4 Posts
Posted - 2014-07-24 : 13:30:08
I have the below shown CTE. The problem I have is that the way this currently works is that when an account does not exists in the prior fiscal year and exists in the current fiscal year which is made a part of the prior fiscal year by the join in the final SELECT.
USHW1.FISCAL_YEAR shows 2014 and USHW2.FISCAL_YEAR is also 2014. Due to this the record is eliminated. This happens when the account has no activity in the prior year. When the account exists in Prior Year, then it FISCAL_YEAR and FISCAL_YEAR_USHW will be equal to 2013 in this case.

COMPANY FISCAL_YEAR FISCAL_YEAR_USHW ACCT_UNIT ACCOUNT
2014 2014 11001001 600560
FISCAL_YEAR FISCAL_YEAR_USHW ACCT_UNIT 2013 2013 12005001 510390

The question I have in regards to this CTE is how can I have the prior fiscal year equal the current fiscal year when an account has no activity in the prior fiscal year.

Here's the CTE

WITH USHW1 AS
(
SELECT GAM.[COMPANY]
,[FISCAL_YEAR]
,[FISCAL_YEAR] AS [FISCAL_YEAR_USHW]
,GAM.[ACCT_UNIT]
,GAM.[ACCOUNT]
,GAM.[SUB_ACCOUNT]
,GAM.[VAR_LEVELS]
,GDT.[CHART_NAME]

/* what logic should be used for beginning balances?
,[DB_BEG_BAL]
,[CR_BEG_BAL]
*/
,COALESCE([DB_AMOUNT_07],0) AS [DB_AMOUNT_USHW_01]
,COALESCE([DB_AMOUNT_08],0) AS [DB_AMOUNT_USHW_02]
,COALESCE([DB_AMOUNT_09],0) AS [DB_AMOUNT_USHW_03]
,COALESCE([DB_AMOUNT_10],0) AS [DB_AMOUNT_USHW_04]
,COALESCE([DB_AMOUNT_11],0) AS [DB_AMOUNT_USHW_05]
,COALESCE([DB_AMOUNT_12],0) AS [DB_AMOUNT_USHW_06]

,COALESCE([CR_AMOUNT_07],0) AS [CR_AMOUNT_USHW_01]
,COALESCE([CR_AMOUNT_08],0) AS [CR_AMOUNT_USHW_02]
,COALESCE([CR_AMOUNT_09],0) AS [CR_AMOUNT_USHW_03]
,COALESCE([CR_AMOUNT_10],0) AS [CR_AMOUNT_USHW_04]
,COALESCE([CR_AMOUNT_11],0) AS [CR_AMOUNT_USHW_05]
,COALESCE([CR_AMOUNT_12],0) AS [CR_AMOUNT_USHW_06]
FROM [dbo].[GLAMOUNTS]GAM (NOLOCK)
JOIN GLCHARTDTL GDT ON GDT.ACCOUNT = GAM.ACCOUNT
JOIN GLNAMES GLN ON GLN.ACCT_UNIT = GAM.ACCT_UNIT


WHERE GDT.CHART_NAME ='USHW-OPS'
AND GLN.POSTING_FLAG = 'P'
AND GLN.LEVEL_DETAIL_02 = 1
)
,USHW2 AS
(
SELECT GAM.[COMPANY]
,[FISCAL_YEAR]

/* For the old way of doing things the first 6 months of the year
are part of the previous fiscal year. */
,[FISCAL_YEAR] - 1 AS [FISCAL_YEAR_USHW]
,GAM.[ACCT_UNIT]
,GAM.[ACCOUNT]
,GAM.[SUB_ACCOUNT]
,GAM.[VAR_LEVELS]
,GDT.[CHART_NAME]

/* what logic should be used for beginning balances?
,[DB_BEG_BAL]
,[CR_BEG_BAL]
*/
,COALESCE([DB_AMOUNT_01],0) AS [DB_AMOUNT_USHW_07]
,COALESCE([DB_AMOUNT_02],0) AS [DB_AMOUNT_USHW_08]
,COALESCE([DB_AMOUNT_03],0) AS [DB_AMOUNT_USHW_09]
,COALESCE([DB_AMOUNT_04],0) AS [DB_AMOUNT_USHW_10]
,COALESCE([DB_AMOUNT_05],0) AS [DB_AMOUNT_USHW_11]
,COALESCE([DB_AMOUNT_06],0) AS [DB_AMOUNT_USHW_12]

,COALESCE([CR_AMOUNT_01],0) AS [CR_AMOUNT_USHW_07]
,COALESCE([CR_AMOUNT_02],0) AS [CR_AMOUNT_USHW_08]
,COALESCE([CR_AMOUNT_03],0) AS [CR_AMOUNT_USHW_09]
,COALESCE([CR_AMOUNT_04],0) AS [CR_AMOUNT_USHW_10]
,COALESCE([CR_AMOUNT_05],0) AS [CR_AMOUNT_USHW_11]
,COALESCE([CR_AMOUNT_06],0) AS [CR_AMOUNT_USHW_12]
FROM [dbo].[GLAMOUNTS] GAM (NOLOCK)

JOIN GLCHARTDTL GDT ON GDT.ACCOUNT = GAM.ACCOUNT
JOIN GLNAMES GLN ON GLN.ACCT_UNIT = GAM.ACCT_UNIT


WHERE GDT.CHART_NAME ='USHW-OPS'
AND GLN.POSTING_FLAG = 'P'
AND GLN.LEVEL_DETAIL_02 = 1

--WHERE GDT.CHART_NAME ='USHW-OPS'

--AND GAM.ACCT_UNIT ='11001001' AND GAM.ACCOUNT = 600560
--WHERE 1=1
)
--SELECT * FROM USHW2 WHERE ACCOUNT = 390000 AND FISCAL_YEAR = 2014
, USHW3 AS -- Calculate the retained earnings to be removed from Jan
(
SELECT --GDT.CHART_NAME, COUNT(*) --32471

GDT.CHART_NAME
,GAM.COMPANY
,GAM.FISCAL_YEAR
,GAM.ACCT_UNIT
,'390000' RETEARN -- Net income to be assigned to this account.
,SUM([DB_AMOUNT_01]+ [CR_AMOUNT_01]+[DB_AMOUNT_02]+ [CR_AMOUNT_02]+[DB_AMOUNT_03]+ [CR_AMOUNT_03]+[DB_AMOUNT_04]+ [CR_AMOUNT_04]+[DB_AMOUNT_05]+ [CR_AMOUNT_05]+[DB_AMOUNT_06]+ [CR_AMOUNT_06])TOTAL_NET_INC_PER01_06
,SUM([DB_AMOUNT_01]+ [CR_AMOUNT_01]) NET_INCOME_PERIOD_01
,SUM([DB_AMOUNT_02]+ [CR_AMOUNT_02])NET_INCOME_PERIOD_02, SUM([DB_AMOUNT_03]+ [CR_AMOUNT_03])NET_INCOME_PERIOD_03, SUM([DB_AMOUNT_04]+ [CR_AMOUNT_04])NET_INCOME_PERIOD_04, SUM([DB_AMOUNT_05]+ [CR_AMOUNT_05])NET_INCOME_PERIOD_05, SUM([DB_AMOUNT_06]+ [CR_AMOUNT_06])NET_INCOME_PERIOD_06

FROM dbo.GLAMOUNTS GAM (NOLOCK)
/* 1 to 1 join. */
--JOIN dbo.GLNAMES GLN (NOLOCK)
-- ON GLN.COMPANY = GAM.COMPANY

/* 1 to 1 join. */
JOIN dbo.GLCHARTDTL GDT (NOLOCK)
ON
GAM.ACCOUNT = GDT.ACCOUNT
AND GAM.SUB_ACCOUNT = GDT.SUB_ACCOUNT

/* 1 to 1 join. */
JOIN dbo.GLCHARTSUM GCS (NOLOCK)
ON GDT.CHART_NAME = GCS.CHART_NAME
AND GDT.CHART_SECTION = GCS.CHART_SECTION
AND GDT.SUMRY_ACCT_ID = GCS.SUMRY_ACCT_ID
WHERE 1 = 1
AND GCS.SUMMARY_ACCT IN ('GROSSREV','CONTRACTDISC','BADDEBT','SWB','RENT','OTHREXPEXBD','DEP&AMORT','INTEREST','GAIN/LOSS' )
AND GAM.FISCAL_YEAR = 2013
AND GDT.CHART_SECTION = 2
--AND GAM.ACCOUNT = 390000
--AND GDT.CHART_NAME ='USHW-OPS'
AND GDT.CHART_NAME ='USHW-OPS'
GROUP BY GDT.CHART_NAME
,GAM.COMPANY
,GAM.FISCAL_YEAR
,GAM.ACCT_UNIT
)
--GROUP BY GDT.CHART_NAME
,USHW4 AS -- removes retained earnings from USHW2
(
SELECT USHW2.[COMPANY]
,USHW2.[FISCAL_YEAR]

,USHW2.[FISCAL_YEAR_USHW]
,USHW2.[ACCT_UNIT]
,USHW2.[ACCOUNT]
,USHW2.[SUB_ACCOUNT]
,USHW2.[VAR_LEVELS]
,USHW2.[CHART_NAME]

,USHW2.[DB_AMOUNT_USHW_07]

,USHW2.[DB_AMOUNT_USHW_08]
,USHW2.[DB_AMOUNT_USHW_09]
,USHW2.[DB_AMOUNT_USHW_10]
,USHW2.[DB_AMOUNT_USHW_11]
,USHW2.[DB_AMOUNT_USHW_12]

,USHW2.[CR_AMOUNT_USHW_07]
,USHW2.[CR_AMOUNT_USHW_08]
,USHW2.[CR_AMOUNT_USHW_09]
,USHW2.[CR_AMOUNT_USHW_10]
,USHW2.[CR_AMOUNT_USHW_11]
,USHW2.[CR_AMOUNT_USHW_12]
,USHW3.TOTAL_NET_INC_PER01_06
,USHW2.[DB_AMOUNT_USHW_07]
+ USHW2.[CR_AMOUNT_USHW_07]
- COALESCE(USHW3.TOTAL_NET_INC_PER01_06, 0) AS Net_Period_7
FROM USHW2
LEFT JOIN USHW3 ON
--USHW2.COMPANY = USHW3.COMPANY AND
USHW2.FISCAL_YEAR_USHW = USHW3.FISCAL_YEAR AND
USHW2.ACCT_UNIT = USHW3.ACCT_UNIT AND
--USHW2.ACCOUNT = USHW3.ACCOUNT AND
USHW2.SUB_ACCOUNT = 0
)
--SELECT * FROM USHW4


SELECT USHW1.[COMPANY]
,USHW1.[FISCAL_YEAR]
,USHW1.[FISCAL_YEAR] AS [FISCAL_YEAR_USHW]
,USHW1.[ACCT_UNIT]
,GLN.DESCRIPTION AS GLN_DESCRIPTION
--,CASE WHEN USHW1.ACCOUNT = 390000 THEN 1 ELSE 0 END AS RE_COLUMN
,USHW1.[ACCOUNT]
,USHW1.[SUB_ACCOUNT]
,GDT.ACCOUNT_DESC AS GDT_ACCOUNT_DESC
,USHW1.[VAR_LEVELS]
,USHW1.[CHART_NAME]
,GCS.CHART_SECTION
,GCS.SEQ_NUMBER
,GCS.SUMMARY_ACCT
,GCS.ACCOUNT_DESC AS GCS_ACCOUNT_DESC
,GCS.TOTAL_DESC AS GCS_TOTAL_DESC
,GCS.SUMRY_ACCT_ID

,COALESCE(USHW1.[DB_AMOUNT_USHW_01]+ USHW1.[CR_AMOUNT_USHW_01],0) Net_Period_1
,COALESCE(USHW1.[DB_AMOUNT_USHW_02]+ USHW1.[CR_AMOUNT_USHW_02],0) Net_Period_2
,COALESCE(USHW1.[DB_AMOUNT_USHW_03]+ USHW1.[CR_AMOUNT_USHW_03],0) Net_Period_3
,COALESCE(USHW1.[DB_AMOUNT_USHW_04]+ USHW1.[CR_AMOUNT_USHW_04],0) Net_Period_4
,COALESCE(USHW1.[DB_AMOUNT_USHW_05]+ USHW1.[CR_AMOUNT_USHW_05],0) Net_Period_5
,COALESCE(USHW1.[DB_AMOUNT_USHW_06]+ USHW1.[CR_AMOUNT_USHW_06],0) Net_Period_6
,COALESCE(USHW2.[DB_AMOUNT_USHW_07]+ USHW2.[CR_AMOUNT_USHW_07],0) Net_Period_7
,COALESCE(USHW2.[DB_AMOUNT_USHW_08]+ USHW2.[CR_AMOUNT_USHW_08],0) Net_Period_8
,COALESCE(USHW2.[DB_AMOUNT_USHW_09]+ USHW2.[CR_AMOUNT_USHW_09],0) Net_Period_9
,COALESCE(USHW2.[DB_AMOUNT_USHW_10]+ USHW2.[CR_AMOUNT_USHW_10],0) Net_Period_10
,COALESCE(USHW2.[DB_AMOUNT_USHW_11]+ USHW2.[CR_AMOUNT_USHW_11],0) Net_Period_11
,COALESCE(USHW2.[DB_AMOUNT_USHW_12]+ USHW2.[CR_AMOUNT_USHW_12],0) Net_Period_12


FROM USHW1
JOIN dbo.GLNAMES GLN (NOLOCK)
ON GLN.COMPANY = USHW1.COMPANY
AND GLN.ACCT_UNIT = USHW1.ACCT_UNIT
JOIN dbo.GLCHARTDTL GDT (NOLOCK)
ON GDT.CHART_NAME = USHW1.CHART_NAME
AND GDT.ACCOUNT = USHW1.ACCOUNT
AND GDT.SUB_ACCOUNT = USHW1.SUB_ACCOUNT
JOIN dbo.GLCHARTSUM GCS (NOLOCK)
ON GCS.CHART_NAME = USHW1.CHART_NAME
AND GCS.CHART_SECTION = GDT.CHART_SECTION
AND GCS.SUMRY_ACCT_ID = GDT.SUMRY_ACCT_ID
Left JOIN USHW2
ON USHW1.COMPANY = USHW2.COMPANY
AND USHW1.FISCAL_YEAR = USHW2.FISCAL_YEAR_USHW
AND USHW1.ACCT_UNIT = USHW2.ACCT_UNIT
AND USHW1.ACCOUNT = USHW2.ACCOUNT
AND USHW1.SUB_ACCOUNT = USHW2.SUB_ACCOUNT
WHERE USHW1.COMPANY = 1 AND GDT.CHART_NAME = 'USHW-OPS'
AND USHW2.ACCT_UNIT = '12005001' AND USHW2.ACCOUNT = 510390
AND USHW2.COMPANY IS NOT NULL
AND USHW2.FISCAL_YEAR = 2014


Any help is much appreciated.

thanks,
MT.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site