SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Help in writing this statement in SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

imtiaz
Starting Member

USA
4 Posts

Posted - 07/25/2014 :  20:31:06  Show Profile  Reply with Quote
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

USA
36983 Posts

Posted - 07/25/2014 :  20:31:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 07/25/2014 :  22:21:52  Show Profile  Reply with Quote
please post sample data , existing query and required result


KH
Time is always against us

Go to Top of Page

imtiaz
Starting Member

USA
4 Posts

Posted - 07/26/2014 :  01:27:44  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000