Hi All,
I have a query in which I am trying to get the YTD Account Balances for a financial statement. There are accounts which does not have balance for all the months . What I am trying to get is the results for all the 12 months irrespective of having balance in a particular month or not. With the help of various forum posts I have reached until the below query. This is giving me YTD balance until the month, the account has balance.
The results from my existing query is like below.
year month NetAmount
2017 1 4315.77
2017 2 8213.89
2017 3 12529.66
2017 4 16567
2017 5 NULL
2017 6 NULL
2017 7 NULL
2017 8 NULL
2017 9 NULL
2017 10 NULL
2017 11 NULL
2017 12 NULL
I need the results as
year month NetAmount
2017 1 4315.77
2017 2 8213.89
2017 3 12529.66
2017 4 16567.00
2017 5 16567.00
2017 6 16567.00
2017 7 16567.00
2017 8 16567.00
2017 9 16567.00
2017 10 16567.00
2017 11 16567.00
2017 12 16567.00
QUERY
SELECT years.y, months.m, fk_pandL.NetAmount
FROM (
SELECT distinct year(begindate) as y from ESGOFiscalYear ) years
CROSS JOIN (
SELECT 1 AS m UNION ALL
SELECT 2 AS m UNION ALL
SELECT 3 AS m UNION ALL
SELECT 4 AS m UNION ALL
SELECT 5 AS m UNION ALL
SELECT 6 AS m UNION ALL
SELECT 7 AS m UNION ALL
SELECT 8 AS m UNION ALL
SELECT 9 AS m UNION ALL
SELECT 10 AS m UNION ALL
SELECT 11 AS m UNION ALL
SELECT 12 AS m ) months
left join
(SELECT DISTINCT year(fy.BeginDate) AS YEAR, fp.Description AS FiscalPeriod,
CASE WHEN SUBSTRING(fp.Description, 6, 3) = 'JAN' THEN 'January'
WHEN SUBSTRING(fp.Description, 6, 3)= 'FEB' THEN 'February'
WHEN SUBSTRING(fp.Description, 6, 3) = 'MAR' THEN 'March'
WHEN SUBSTRING(fp.Description, 6, 3) = 'APR' THEN 'April'
WHEN SUBSTRING(fp.Description, 6, 3) = 'MAY' THEN 'May'
WHEN SUBSTRING(fp.Description, 6, 3) = 'JUN' THEN 'June'
WHEN SUBSTRING(fp.Description, 6, 3) = 'JUL' THEN 'July'
WHEN SUBSTRING(fp.Description, 6, 3) = 'AUG' THEN 'August'
WHEN SUBSTRING(fp.Description, 6, 3) = 'SEP' THEN 'September'
WHEN SUBSTRING(fp.Description, 6, 3) = 'OCT' THEN 'October'
WHEN SUBSTRING(fp.Description, 6, 3) = 'NOV' THEN 'November'
WHEN SUBSTRING(fp.Description, 6, 3) = 'DEC' THEN 'December' END AS Period,
row_number() over (order by fp.enddate) as monthno,
gp.fCompanyCode AS CompanyCode,
bu.fTableField1Code AS BusinessUnitCategory,
gl.code AS AccountCode,
gl.name AS AccountDescription,
(sum(gp.debitvalue) OVER (Partition BY gl.code, gp.fcompanycode, bu.ftablefield1code,
Year(fp.enddate) ORDER BY fp.enddate ) -
sum(gp.CreditValue) OVER (Partition BY gl.code, gp.fcompanycode, bu.fTableField1Code,
Year(fp.enddate)
ORDER BY fp.enddate )) AS NetAmount
FROM ESGLAccountPeriodics gp LEFT JOIN ESGOFiscalPeriod fp ON fp.gid =
gp.fFiscalPeriodGID LEFT JOIN
ESGOFiscalYear fy
ON fy.gid = gp.fFiscalYearGID
LEFT JOIN
esglaccount gl ON gl.gid = gp.fAccountGID
LEFT JOIN
ESGOZBusinessUnit bu ON bu.Code = gp.fBusinessUnitCode
WHERE
gl.ChartOfAccounts = 0 AND gl.FlagField2 = 1
and gl.code='7001xxxx' and
year(fy.BeginDate) = '2017'
and gp.fCompanyCode ='10x'
and datepart(m,fp.enddate) <=12
GROUP BY gl.code,
fp.Description,
fp.EndDate,
fy.EndDate,
gp.DebitValue,
gp.CreditValue,
gp.fCompanyCode,
bu.fTableField1Code,
gl.name,
fy.BeginDate,
gp.gid,
gp.fAccountGID)
fk_pandL on
fk_pandL.year = years.y and fk_pandL.monthno =months.m
where years.y='2017'
Any Help is highly appreciated
Thanks In advance.