Help in writing this statement in SQL

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)

Help in writing this statement in SQL

Author

Topic

imtiaz
Starting Member

4 Posts

Posted - 2014-07-25 : 20:31:06

I have two CTE's that I am joining. CTE1 gets date for the Fiscal_Year while CTE2 gets data for the following FISCAL_YEAR. So, if an account does not exist in the FISCAL_YEAR ( say 2013) but exits in the following year (2014), then I want the FISCAL_YEAR of that account to be equal to the previous FISCAL_YEAR. Cos when I am combining the two CTE's I am stating where FISCAL_YEAR = FISCAL_YEAR -1.

What I am attempting to do is get July to June to be the same Fiscal Year. So, Jan through June of the next year will be part of the prior year.

How do I write this statement? Any suggestions is most welcome.

thanks for any help you can provide.

MT.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-25 : 20:31:54

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-07-25 : 22:21:52

please post sample data , existing query and required result

KH
[spoiler]Time is always against us[/spoiler]

imtiaz
Starting Member

4 Posts

Posted - 2014-07-26 : 01:27:44

My query is shown below. The problem I have is the way this query currently works is that when an account does not exist in the prior fiscal year, say 2013 and exists in the current fiscal year, say 2014 which becomes a part of the prior fiscal year by joining the two CTE's in the final SELECT where I join
USHW1.FISCAL_YEAR = USHW2.FISCAL_YEAR_USHW. This join causes any accounts that are zero in the prior fiscal year to be a part of fiscal year 2014 and therefore does not equal the prior year I.e. 2013. Therefore this record is excluded from the result. My question is: How can I code to tell it that if the account did not exist or was zero in the prior year and exists in the following year, it should still be shown as a part of 2013 as FY 2013 goes from July - June. I have included the entire query below so you can have a better idea of my problem in case my explanation is still not clear. I hope I have better explained my problem and hope someone can suggest a solution.

Thank you very much for your help.

COMPANY FISCAL_YEAR FISCAL_YEAR_USHW ACCT_UNIT ACCOUNT
2014 2014 11001001 600560

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources