May 28, 2018 at 7:47 am
Hi All,
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 . I need 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.
| y | m | 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
| y | m | 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 |
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,
'YTD' AS VIEWv,
gp.fCompanyCode AS CompanyCode,
bu.fTableField1Code AS BusinessUnitCategory,
gl.code AS AccountCode,
'' AS Intercompanycode, 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.
May 28, 2018 at 8:25 am
As you have not provided DDL, I cannot post the exact solution. But here is an idea for creating running totals for 'missing' months by left joining from a calendar table.
DROP TABLE IF EXISTS #Calendar;
CREATE TABLE #Calendar
(
Yr SMALLINT,
Mth SMALLINT
);
INSERT #Calendar
(
Yr,
Mth
)
VALUES
(2017, 1),
(2017, 2),
(2017, 3),
(2017, 4),
(2017, 5),
(2017, 6),
(2017, 7),
(2017, 8),
(2017, 9),
(2017, 10),
(2017, 11),
(2017, 12);
DROP TABLE IF EXISTS #SomeData;
CREATE TABLE #SomeData
(
Yr SMALLINT,
Mth TINYINT,
NetAmount DECIMAL(19, 6)
);
INSERT #SomeData
(
Yr,
Mth,
NetAmount
)
VALUES
(2017, 1, 4315.77),
(2017, 2, 8213.89),
(2017, 3, 12529.66),
(2017, 4, 16567.);
SELECT c.Yr,
c.Mth,
RunningSum = SUM(sd.NetAmount) OVER (ORDER BY c.Yr, c.Mth ROWS UNBOUNDED PRECEDING)
FROM #Calendar c
LEFT JOIN #SomeData sd
ON c.Yr = sd.Yr
AND c.Mth = sd.Mth
ORDER BY c.Yr,
c.Mth;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 28, 2018 at 12:52 pm
Thankyou so much and taking your time to help .
The query you have posted have given me an idea how to make the changes in the existing query.
May 29, 2018 at 10:25 am
ddly_ann - Monday, May 28, 2018 7:47 AMHi All,
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 . I need 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.
y m 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
y m 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
Any help is highly appreciated.Thanks.
Just wondering whether it's actually appropriate to carry the last value you see in the data into months for which there is no data. If there not being data for a given month for a given account, does that mean that the balance for that month might actually be zero? And from that, carrying over that balance might be incorrect. Another potential consideration is whether the balance might need to be cumulative, as what if the balance number represents an amount purchased on credit terms, and thus the balance will always be cumulative? And from appearances, the numbers you already posted look like they may already be cumulative, and thus if there's no data, and this is in the past, there might oughta be zero's, as surely somewhere in those numbers, any payments made would be reflected?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 29, 2018 at 12:24 pm
CREATE TABLE Report_Periods
(report_name CHAR(10) NOT NULL PRIMARY KEY,
report_start_date DATE NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
ordinal_period INTEGER NOT NULL UNIQUE
CHECK(ordinal_period > 0)
etc);
These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. Remember that when you design report name column.
We now have some more basic problems with your design. The NULL in SQL actually has some meaning. Did this monthly report itself exist, rather than the amount of the monthly report. . Think about it. If I'm in the middle the year, then I can't get reports from the end of the year. However, if I am at the end of the year, and I'm missing data (all those departments never turned in their net amount reports, but I know that the amounts actually exist – – the missing value, or unreported value problem), then I have a really different situation between obviously physically not existing and not reported.
If you are projecting future revenue, then we have a different model.
In short, we need more specs.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 29, 2018 at 3:37 pm
ddly_ann - Monday, May 28, 2018 7:47 AMA useful idiom is a report period calendar. It gives a name to a range of dates. The worst way would be to use temporal math; it tells the world you do not think in sets or understand declarative programming yet. Here is a skeleton:
CREATE TABLE Report_Periods
(report_name CHAR(10) NOT NULL PRIMARY KEY,
report_start_date DATE NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
ordinal_period INTEGER NOT NULL UNIQUE
CHECK(ordinal_period > 0)
etc);These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. Remember that when you design report name column.
We now have some more basic problems with your design. The NULL in SQL actually has some meaning. Did this monthly report itself exist, rather than the amount of the monthly report. . Think about it. If I'm in the middle the year, then I can't get reports from the end of the year. However, if I am at the end of the year, and I'm missing data (all those departments never turned in their net amount reports, but I know that the amounts actually exist – – the missing value, or unreported value problem), then I have a really different situation between obviously physically not existing and not reported.
If you are projecting future revenue, then we have a different model.
In short, we need more specs.
Mr. Celko, for being a SQL Standards purist you seem to have a love fest for MySQL. You do realize that this site is For Microsoft SQL Server and not Oracle MySQL, correct? Stop hyping MySQL and actually provide some T-SQL help here.
May 29, 2018 at 3:39 pm
sgmunson - Tuesday, May 29, 2018 10:25 AMddly_ann - Monday, May 28, 2018 7:47 AMHi All,
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 . I need 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.
y m 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
y m 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
Any help is highly appreciated.Thanks.
Just wondering whether it's actually appropriate to carry the last value you see in the data into months for which there is no data. If there not being data for a given month for a given account, does that mean that the balance for that month might actually be zero? And from that, carrying over that balance might be incorrect. Another potential consideration is whether the balance might need to be cumulative, as what if the balance number represents an amount purchased on credit terms, and thus the balance will always be cumulative? And from appearances, the numbers you already posted look like they may already be cumulative, and thus if there's no data, and this is in the past, there might oughta be zero's, as surely somewhere in those numbers, any payments made would be reflected?
BWAAA-HAAA!!! I'm right there with you! "We shall post no dime before its time". 😀
Heh... and with those year and month columns used for display, I can't wait to see what happens "on the wrap" to a new year, either. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 29, 2018 at 3:55 pm
jcelko212 32090 - Tuesday, May 29, 2018 12:24 PMddly_ann - Monday, May 28, 2018 7:47 AMA useful idiom is a report period calendar. It gives a name to a range of dates. The worst way would be to use temporal math; it tells the world you do not think in sets or understand declarative programming yet. Here is a skeleton:
CREATE TABLE Report_Periods
(report_name CHAR(10) NOT NULL PRIMARY KEY,
report_start_date DATE NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
ordinal_period INTEGER NOT NULL UNIQUE
CHECK(ordinal_period > 0)
etc);These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. Remember that when you design report name column.
We now have some more basic problems with your design. The NULL in SQL actually has some meaning. Did this monthly report itself exist, rather than the amount of the monthly report. . Think about it. If I'm in the middle the year, then I can't get reports from the end of the year. However, if I am at the end of the year, and I'm missing data (all those departments never turned in their net amount reports, but I know that the amounts actually exist – – the missing value, or unreported value problem), then I have a really different situation between obviously physically not existing and not reported.
If you are projecting future revenue, then we have a different model.
In short, we need more specs.
Mr. Celko, for being a SQL Standards purist you seem to have a love fest for MySQL. You do realize that this site is For Microsoft SQL Server and not Oracle MySQL, correct? Stop hyping MySQL and actually provide some T-SQL help here.
Heh... and besides, it violates the very standards that Joe busts everyone chops about. "00" is not a valid representation for either day or month in the ISO 8601 standard on the subject of such temporal notations. It would be better to use blanks but that would also perpetuate the mistake of storing temporal display data in character based columns instead of using the proper data type.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2018 at 1:04 am
Hi All,
Thankyou all for the valuable points. Finally I could come up with the desired result with changes in the query.
QUERY
-
SELECT yr.y as Year,buc.buc,glc.ac,glc.descr,co.code, '' as Intercompanycode, 'YTD' as Viewv,
case c.m when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'Decemeber'
end as Period,
(sum(isnull(dt.NetAmount,0)) OVER ( partition by buc.buc,glc.ac,co.code,yr.y ORDER BY c.m, yr.y ROWS UNBOUNDED PRECEDING )) RunningSum
FROM
( select distinct year(fy.enddate) as y from ESGOFiscalYear fy )yr cross join
(select distinct gl.code as ac,gl.name as descr from esglaccount gl where gl.ChartOfAccounts = 0 and gl.SummaryAccount =0 and gl.FlagField2=1) glc cross join
(select distinct bu.fTableField1Code buc from ESGOZBusinessUnit bu ) buc cross join
(select distinct c.code from esgocompany c ) co
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) c
LEFT JOIN
(SELECT DISTINCT year(fy.BeginDate) AS YEAR, fp.Description AS FiscalPeriod, 'YTD' AS VIEWv, gp.fCompanyCode AS CompanyCode,
bu.fTableField1Code AS BusinessUnitCategory, gl.code AS AccountCode, '' AS Intercompanycode, gl.name AS AccountDescription,datepart(m,fp.enddate) as monthno,
isnull(sum(gp.debitvalue) OVER ( partition by datepart(m,fp.enddate), gl.code, gp.fcompanycode, bu.ftablefield1code, Year(fp.enddate) ORDER BY datepart(m,fp.enddate) ) -
sum(gp.CreditValue) OVER ( partition by datepart(m,fp.enddate), gl.code, gp.fcompanycode, bu.ftablefield1code, Year(fp.enddate) ORDER BY datepart(m,fp.enddate)),0 ) 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
datepart(m,fp.enddate) <=12
and bu.fTableField1Code is not null
and year(fy.begindate)>='2017'
GROUP BY gl.code,
fp.Description,
fp.EndDate,
gp.DebitValue,
gp.CreditValue,
gp.fCompanyCode,
bu.fTableField1Code,
gl.name,
fy.BeginDate,
gp.gid,
gp.fAccountGID) dt
ON dt.monthno =c.m
AND dt.year =yr.y and dt.AccountCode=glc.ac and dt.BusinessUnitCategory = buc.buc and dt.CompanyCode = co.Code
where buc.buc is not null and yr.y>='2017'
group by buc.buc,co.code,yr.y,c.m,dt.NetAmount,glc.ac,glc.descr
order by 1,3,2,5,c.m
May 30, 2018 at 4:01 am
When looking up the month names, you could also use the CHOOSE function
Replace this large CASE statementcase c.m when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'Decemeber'
end as Period,
with ...CHOOSE(c.m, 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December') as [Period]
May 30, 2018 at 5:21 am
You could also add the decoded monthname to the CROSS JOIN:
CROSS JOIN
(
SELECT m = 1,
mth = 'January'
UNION ALL
SELECT m = 2,
mth = 'February'
UNION ALL
SELECT m = 3,
mth = 'March'
UNION ALL
SELECT m = 4,
mth = 'April'
UNION ALL
SELECT m = 5,
mth = 'May'
UNION ALL
SELECT m = 6,
mth = 'June'
UNION ALL
SELECT m = 7,
mth = 'July'
UNION ALL
SELECT m = 8,
mth = 'August'
UNION ALL
SELECT m = 9,
mth = 'September'
UNION ALL
SELECT m = 10,
mth = 'October'
UNION ALL
SELECT m = 11,
mth = 'November'
UNION ALL
SELECT m = 12,
mth = 'Decemeber'
) c
and then change the SELECT bit to have
Period = c.mth,
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 30, 2018 at 11:40 am
ddly_ann - Wednesday, May 30, 2018 1:04 AMHi All,
Thankyou all for the valuable points. Finally I could come up with the desired result with changes in the query.QUERY
-
SELECT yr.y as Year,buc.buc,glc.ac,glc.descr,co.code, '' as Intercompanycode, 'YTD' as Viewv,
case c.m when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'Decemeber'
end as Period,
(sum(isnull(dt.NetAmount,0)) OVER ( partition by buc.buc,glc.ac,co.code,yr.y ORDER BY c.m, yr.y ROWS UNBOUNDED PRECEDING )) RunningSum
FROM
( select distinct year(fy.enddate) as y from ESGOFiscalYear fy )yr cross join
(select distinct gl.code as ac,gl.name as descr from esglaccount gl where gl.ChartOfAccounts = 0 and gl.SummaryAccount =0 and gl.FlagField2=1) glc cross join
(select distinct bu.fTableField1Code buc from ESGOZBusinessUnit bu ) buc cross join
(select distinct c.code from esgocompany c ) co
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) c
LEFT JOIN(SELECT DISTINCT year(fy.BeginDate) AS YEAR, fp.Description AS FiscalPeriod, 'YTD' AS VIEWv, gp.fCompanyCode AS CompanyCode,
bu.fTableField1Code AS BusinessUnitCategory, gl.code AS AccountCode, '' AS Intercompanycode, gl.name AS AccountDescription,datepart(m,fp.enddate) as monthno,
isnull(sum(gp.debitvalue) OVER ( partition by datepart(m,fp.enddate), gl.code, gp.fcompanycode, bu.ftablefield1code, Year(fp.enddate) ORDER BY datepart(m,fp.enddate) ) -
sum(gp.CreditValue) OVER ( partition by datepart(m,fp.enddate), gl.code, gp.fcompanycode, bu.ftablefield1code, Year(fp.enddate) ORDER BY datepart(m,fp.enddate)),0 ) 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
datepart(m,fp.enddate) <=12
and bu.fTableField1Code is not null
and year(fy.begindate)>='2017'
GROUP BY gl.code,
fp.Description,
fp.EndDate,
gp.DebitValue,
gp.CreditValue,
gp.fCompanyCode,
bu.fTableField1Code,
gl.name,
fy.BeginDate,
gp.gid,
gp.fAccountGID) dt
ON dt.monthno =c.m
AND dt.year =yr.y and dt.AccountCode=glc.ac and dt.BusinessUnitCategory = buc.buc and dt.CompanyCode = co.Code
where buc.buc is not null and yr.y>='2017'
group by buc.buc,co.code,yr.y,c.m,dt.NetAmount,glc.ac,glc.descr
order by 1,3,2,5,c.m
Your query is rather hard to read, so I've formatted it quite a bit to try and make it more readable, and also applied a simplification, as you don't need to use a CASE statement to figure out the month name. Of course, it would be on heck of a lot better if you would stop using year and month separately. That's why date and datetime data types were invented.SELECT
yr.y as Year,
buc.buc,
glc.ac,
glc.descr,
co.code,
'' as Intercompanycode,
'YTD' as Viewv,
DATENAME(month, DATEFROMPARTS(yr.y, c.m, 1)) as Period,
SUM(ISNULL(dt.NetAmount, 0)) OVER (PARTITION BY buc.buc, glc.ac, co.code, yr.y ORDER BY c.m, yr.y ROWS UNBOUNDED PRECEDING) AS RunningSum
FROM (
select distinct year(fy.enddate) as y
from ESGOFiscalYear AS fy
) AS yr
CROSS JOIN (
select distinct
gl.code as ac,
gl.name as descr
from esglaccount AS gl
where gl.ChartOfAccounts = 0
and gl.SummaryAccount = 0
and gl.FlagField2 = 1
) AS glc
CROSS JOIN (
select distinct bu.fTableField1Code buc
from ESGOZBusinessUnit AS bu
) AS buc
CROSS JOIN (
select distinct c.code
from esgocompany AS c
) AS co
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
) AS c
LEFT JOIN (
SELECT DISTINCT
year(fy.BeginDate) AS YEAR,
fp.Description AS FiscalPeriod,
'YTD' AS VIEWv,
gp.fCompanyCode AS CompanyCode,
bu.fTableField1Code AS BusinessUnitCategory,
gl.code AS AccountCode,
'' AS Intercompanycode,
gl.name AS AccountDescription,
datepart(m,fp.enddate) as monthno,
isnull( sum(gp.debitvalue) OVER (PARTITION BY datepart(m, fp.enddate), gl.code, gp.fcompanycode, bu.ftablefield1code, Year(fp.enddate) ORDER BY datepart(m, fp.enddate)),
sum(gp.CreditValue) OVER (PARTITION BY datepart(m, fp.enddate), gl.code, gp.fcompanycode, bu.ftablefield1code, Year(fp.enddate) ORDER BY datepart(m,fp.enddate)), 0) AS NetAmount
FROM ESGLAccountPeriodics AS gp
LEFT OUTER JOIN ESGOFiscalPeriod AS fp
ON fp.gid = gp.fFiscalPeriodGID
LEFT OUTER JOIN ESGOFiscalYear AS fy
ON fy.gid = gp.fFiscalYearGID
LEFT OUTER JOIN esglaccount AS gl
ON gl.gid = gp.fAccountGID
LEFT OUTER JOIN ESGOZBusinessUnit AS bu
ON bu.Code = gp.fBusinessUnitCode
WHERE gl.ChartOfAccounts = 0
AND gl.FlagField2 = 1
AND datepart(m, fp.enddate) <= 12
AND bu.fTableField1Code IS NOT NULL
and year(fy.begindate) >= '2017'
GROUP BY gl.code,
fp.Description,
fp.EndDate,
gp.DebitValue,
gp.CreditValue,
gp.fCompanyCode,
bu.fTableField1Code,
gl.name,
fy.BeginDate,
gp.gid,
gp.fAccountGID
) AS dt
ON dt.monthno = c.m
AND dt.year = yr.y
AND dt.AccountCode = glc.ac
AND dt.BusinessUnitCategory = buc.buc
AND dt.CompanyCode = co.Code
WHERE buc.buc is not null
AND yr.y >= '2017'
GROUP BY buc.buc,
co.code,
yr.y,
c.m,
dt.NetAmount,
glc.ac,
glc.descr
ORDER BY 1, 3, 2, 5, c.m;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 31, 2018 at 11:03 am
Phil Parkin - Wednesday, May 30, 2018 5:21 AMYou could also add the decoded monthname to the CROSS JOIN:CROSS JOIN
(
SELECT m = 1,
mth = 'January'
UNION ALL
SELECT m = 2,
mth = 'February'
UNION ALL
SELECT m = 3,
mth = 'March'
UNION ALL
SELECT m = 4,
mth = 'April'
UNION ALL
SELECT m = 5,
mth = 'May'
UNION ALL
SELECT m = 6,
mth = 'June'
UNION ALL
SELECT m = 7,
mth = 'July'
UNION ALL
SELECT m = 8,
mth = 'August'
UNION ALL
SELECT m = 9,
mth = 'September'
UNION ALL
SELECT m = 10,
mth = 'October'
UNION ALL
SELECT m = 11,
mth = 'November'
UNION ALL
SELECT m = 12,
mth = 'Decemeber'
) cand then change the SELECT bit to have
Period = c.mth,
Why are you doing display formatting in the database layer? Isn't that a violation of the basic principle of any tiered architecture? Why are you using the old Sybase UNION ALL syntax to build a table? How can a month exist without being in a year?
Please post DDL and follow ANSI/ISO standards when asking for help.
May 31, 2018 at 11:17 am
ddly_ann - Monday, May 28, 2018 7:47 AM
Mr. Celko, for being a SQL Standards purist you seem to have a love fest for MySQL. You do realize that this site is For Microsoft SQL Server and not Oracle MySQL, correct? Stop hyping MySQL and actually provide some T-SQL help here.
I suppose in the interest of full disclosure, I did work for the MySQL people and produced some website material. It was all about basic data modeling, etc. Having said this, the reason I keep mentioning their convention for temporal intervals is (1) it is being considered for ANSI ISO standards, thanks largely to the popularity of MySQL (2) it is language independent (3) it sorts properly with the rest of the ISO 8601 display format. In short, they are a good choice at the current time for conforming to international standards.
You probably don't remember this, but it one point thanks to the French (anytime something goes wrong in a standard, look for the French to have done something) NATO had a standard that used Roman numerals for months. No, really!
Since Microsoft has not implemented the ANSI/ISO standard interval data types in its temporal model, we have to do a "repair job" instead. If you have a better suggestion. I'd love to hear and put it in my books.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 31, 2018 at 11:18 am
jcelko212 32090 - Thursday, May 31, 2018 11:03 AMPhil Parkin - Wednesday, May 30, 2018 5:21 AMYou could also add the decoded monthname to the CROSS JOIN:CROSS JOIN
(
SELECT m = 1,
mth = 'January'
UNION ALL
SELECT m = 2,
mth = 'February'
UNION ALL
SELECT m = 3,
mth = 'March'
UNION ALL
SELECT m = 4,
mth = 'April'
UNION ALL
SELECT m = 5,
mth = 'May'
UNION ALL
SELECT m = 6,
mth = 'June'
UNION ALL
SELECT m = 7,
mth = 'July'
UNION ALL
SELECT m = 8,
mth = 'August'
UNION ALL
SELECT m = 9,
mth = 'September'
UNION ALL
SELECT m = 10,
mth = 'October'
UNION ALL
SELECT m = 11,
mth = 'November'
UNION ALL
SELECT m = 12,
mth = 'Decemeber'
) cand then change the SELECT bit to have
Period = c.mth,Why are you doing display formatting in the database layer? Isn't that a violation of the basic principle of any tiered architecture? Why are you using the old Sybase UNION ALL syntax to build a table? How can a month exist without being in a year?
Absolutely! I know I call up my HR department every year to ask when Christmas is. After all, "December 25" is meaningless without a year!
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply