SQL text cannot be represented in the grid pane and diagram pane - View Creation

• ddly_ann

  Mr or Mrs. 500

  Points: 534

  More actions

  April 18, 2018 at 2:23 am

  #337173

  Dear All,

  I have a query which brings the results of Financial Account Statement. I need to create this as a View.The Query is giving me perfect results!. But When I convert it into View,its showing the error,
  "SQL text cannot be represented in the grid pane and diagram pane.". I ignored it and created the view . Its showing the results correctly. This View has to integrated with a third party DB (linked server), say MySQL. If I ignore this warning and proceed , will that cause any issues in viewing the results in third party DB.

  Please see the below View that is created for this purpose. Can anyone suggest what part of the query is causing this issue?

  
SELECT MainQuery.Code, mainquery.Description,MainQuery.CompanyCode, MainQuery.BusinessUnitCategory, 
mainquery.CalendarYTDDebit, CalendarYTDCredit
FROM (
SELECT DISTINCT

   gl.code, fp.Description, gp.fCompanyCode AS CompanyCode, bu.fTableField1Code AS BusinessUnitCategory,

   gp.fbusinessunitcode, CalendarYTDDebit = sum(gp.debitvalue) OVER (Partition BY gl.code, gp.fcompanycode, 

   gp.fbusinessunitcode, Year(fp.enddate) ORDER BY fp.enddate), CalendarYTDCredit = sum(gp.CreditValue) OVER 
                             
(Partition BY gl.code, gp.fcompanycode, gp.fbusinessunitcode, Year(fp.enddate) ORDER BY fp.enddate

  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 year(fy.BeginDate) =

  year(getdate()) AND gl.FlagField2 = 1 GROUP BY gl.code, fp.Description, fp.EndDate,gp.DebitValue, gp.CreditValue,

  gp.fCompanyCode, bu.fTableField1Code, gp.fBusinessUnitCode
UNION

  SELECT DISTINCT gl.code, fp.Description, '' AS CompanyCode, '' AS BusinessUnitCategory, '' AS

   fbusinessunitcode, CalendarYTDDebit = sum(gp.debitvalue) OVER (Partition BY gl.code, Year(fp.enddate)

  ORDER BY fp.enddate), CalendarYTDCredit = sum(gp.CreditValue) OVER (Partition BY gl.code, Year(fp.enddate)

  ORDER BY fp.enddate) 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 WHERE gl.ChartOfAccounts = 0 AND year(fy.BeginDate) = 

  year(getdate()) AND gl.FlagField2 = 0 GROUP BY gl.code, fp.Description, fp.EndDate, gp.DebitValue, gp.CreditValue)   

  MainQuery

  Thanks in Advance to all!!

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  April 18, 2018 at 11:15 am

  #1987381

  daleyvinod - Wednesday, April 18, 2018 2:23 AM

  Dear All,

  I have a query which brings the results of Financial Account Statement. I need to create this as a View.The Query is giving me perfect results!. But When I convert it into View,its showing the error,
  "SQL text cannot be represented in the grid pane and diagram pane.". I ignored it and created the view . Its showing the results correctly. This View has to integrated with a third party DB (linked server), say MySQL. If I ignore this warning and proceed , will that cause any issues in viewing the results in third party DB.

  Please see the below View that is created for this purpose. Can anyone suggest what part of the query is causing this issue?

  SELECT MainQuery.Code, mainquery.Description, MainQuery.CompanyCode, MainQuery.BusinessUnitCategory, mainquery.CalendarYTDDebit, CalendarYTDCredit
  FROM (SELECT DISTINCT
  gl.code, fp.Description, gp.fCompanyCode AS CompanyCode, bu.fTableField1Code AS BusinessUnitCategory, gp.fbusinessunitcode, CalendarYTDDebit = sum(gp.debitvalue)
  OVER (Partition BY gl.code, gp.fcompanycode, gp.fbusinessunitcode, Year(fp.enddate)
  ORDER BY fp.enddate), CalendarYTDCredit = sum(gp.CreditValue) OVER (Partition BY gl.code, gp.fcompanycode, gp.fbusinessunitcode, Year(fp.enddate)
  ORDER BY fp.enddate)
  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 year(fy.BeginDate) = year(getdate()) AND gl.FlagField2 = 1
  GROUP BY gl.code, fp.Description, fp.EndDate, gp.DebitValue, gp.CreditValue, gp.fCompanyCode, bu.fTableField1Code, gp.fBusinessUnitCode
  UNION
  SELECT DISTINCT gl.code, fp.Description, '' AS CompanyCode, '' AS BusinessUnitCategory, '' AS fbusinessunitcode, CalendarYTDDebit = sum(gp.debitvalue) OVER (Partition BY gl.code, Year(fp.enddate)
  ORDER BY fp.enddate), CalendarYTDCredit = sum(gp.CreditValue) OVER (Partition BY gl.code, Year(fp.enddate)
  ORDER BY fp.enddate)
  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
  WHERE gl.ChartOfAccounts = 0 AND year(fy.BeginDate) = year(getdate()) AND gl.FlagField2 = 0
  GROUP BY gl.code, fp.Description, fp.EndDate, gp.DebitValue, gp.CreditValue) MainQuery

  Thanks in Advance to all!!

  What are you using to create the view? Have you tried creating it in SSMS if you are using something else?

  Sue

• ddly_ann

  Mr or Mrs. 500

  Points: 534

  More actions

  April 18, 2018 at 11:37 am

  #1987386

  Sue_H - Wednesday, April 18, 2018 11:15 AM

  daleyvinod - Wednesday, April 18, 2018 2:23 AM

  Dear All,

  I have a query which brings the results of Financial Account Statement. I need to create this as a View.The Query is giving me perfect results!. But When I convert it into View,its showing the error,
  "SQL text cannot be represented in the grid pane and diagram pane.". I ignored it and created the view . Its showing the results correctly. This View has to integrated with a third party DB (linked server), say MySQL. If I ignore this warning and proceed , will that cause any issues in viewing the results in third party DB.

  Please see the below View that is created for this purpose. Can anyone suggest what part of the query is causing this issue?

  SELECT MainQuery.Code, mainquery.Description, MainQuery.CompanyCode, MainQuery.BusinessUnitCategory, mainquery.CalendarYTDDebit, CalendarYTDCredit
  FROM (SELECT DISTINCT
  gl.code, fp.Description, gp.fCompanyCode AS CompanyCode, bu.fTableField1Code AS BusinessUnitCategory, gp.fbusinessunitcode, CalendarYTDDebit = sum(gp.debitvalue)
  OVER (Partition BY gl.code, gp.fcompanycode, gp.fbusinessunitcode, Year(fp.enddate)
  ORDER BY fp.enddate), CalendarYTDCredit = sum(gp.CreditValue) OVER (Partition BY gl.code, gp.fcompanycode, gp.fbusinessunitcode, Year(fp.enddate)
  ORDER BY fp.enddate)
  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 year(fy.BeginDate) = year(getdate()) AND gl.FlagField2 = 1
  GROUP BY gl.code, fp.Description, fp.EndDate, gp.DebitValue, gp.CreditValue, gp.fCompanyCode, bu.fTableField1Code, gp.fBusinessUnitCode
  UNION
  SELECT DISTINCT gl.code, fp.Description, '' AS CompanyCode, '' AS BusinessUnitCategory, '' AS fbusinessunitcode, CalendarYTDDebit = sum(gp.debitvalue) OVER (Partition BY gl.code, Year(fp.enddate)
  ORDER BY fp.enddate), CalendarYTDCredit = sum(gp.CreditValue) OVER (Partition BY gl.code, Year(fp.enddate)
  ORDER BY fp.enddate)
  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
  WHERE gl.ChartOfAccounts = 0 AND year(fy.BeginDate) = year(getdate()) AND gl.FlagField2 = 0
  GROUP BY gl.code, fp.Description, fp.EndDate, gp.DebitValue, gp.CreditValue) MainQuery

  Thanks in Advance to all!!

  What are you using to create the view? Have you tried creating it in SSMS if you are using something else?

  Sue

  I am getting the error, while creating this view in SSMS.

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  April 18, 2018 at 12:25 pm

  #1987401

  Don't use the GUI to create the view, instead write a create view statement.

• ddly_ann

  Mr or Mrs. 500

  Points: 534

  More actions

  April 18, 2018 at 8:54 pm

  #1987441

  Joe Torre - Wednesday, April 18, 2018 12:25 PM

  Don't use the GUI to create the view, instead write a create view statement.

  Hi,
  Thankyou for the reply. I could create the View and its showing desired results. My concern is , this view has to be integrated (linked server), with  a third party DB (MySQL). Will this error cause any kind of issue in getting results there, when they try to read this view? I dont have access to this Db to test it.

  Thanks In Advance

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 18, 2018 at 10:25 pm

  #1987444

  daleyvinod - Wednesday, April 18, 2018 8:54 PM

  Joe Torre - Wednesday, April 18, 2018 12:25 PM

  Don't use the GUI to create the view, instead write a create view statement.

  Hi,
  Thankyou for the reply. I could create the View and its showing desired results. My concern is , this view has to be integrated (linked server), with  a third party DB (MySQL). Will this error cause any kind of issue in getting results there, when they try to read this view? I dont have access to this Db to test it.

  Thanks In Advance

  Were you using the visual designer in SSMS to create the view?

• ddly_ann

  Mr or Mrs. 500

  Points: 534

  More actions

  April 18, 2018 at 11:47 pm

  #1987448

  Lynn Pettis - Wednesday, April 18, 2018 10:25 PM

  daleyvinod - Wednesday, April 18, 2018 8:54 PM

  Joe Torre - Wednesday, April 18, 2018 12:25 PM

  Don't use the GUI to create the view, instead write a create view statement.

  Hi,
  Thankyou for the reply. I could create the View and its showing desired results. My concern is , this view has to be integrated (linked server), with  a third party DB (MySQL). Will this error cause any kind of issue in getting results there, when they try to read this view? I dont have access to this Db to test it.

  Thanks In Advance

  Were you using the visual designer in SSMS to create the view?

  No. I first wrote the query in SSMS and tested it. Then I created the view with the same query , using create View statement in SSMS.

• sgmunson

  SSC Guru

  Points: 110651

  More actions

  April 19, 2018 at 6:36 am

  #1987495

  I took a quick look at your view query, and there are a couple things I'd change.   When you use a function on a column in a WHERE clause, performance can suffer.  As your date requirement is for the column with the date to be "this year", all it needs to be is >= 1/1/xxx, where xxxx is this year.   This can be constructed using the DATEFROMPARTS function, together with GETDATE().  As long as your dates are never future dates, you are good to go with my changes.   If they are ever future dated rows, then that part of the change won't work.  Here's the suggested coding  - you will need to substitute in your schema and view names in the CREATE VIEW statement, and you don't need an outer SELECT for any reason that I can come up with:CREATE VIEW YourSchemaName.YourViewName
AS
SELECT DISTINCT
    gl.code,
    fp.[Description],
    gp.fCompanyCode AS CompanyCode,
    bu.fTableField1Code AS BusinessUnitCategory,
    gp.fbusinessunitcode,
    CalendarYTDDebit = SUM(gp.debitvalue) OVER (PARTITION BY gl.code, gp.fcompanycode, gp.fbusinessunitcode, YEAR(fp.enddate) ORDER BY fp.enddate),
    CalendarYTDCredit = SUM(gp.CreditValue) OVER (PARTITION BY gl.code, gp.fcompanycode, gp.fbusinessunitcode, Year(fp.enddate) ORDER BY fp.enddate)
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 fy.BeginDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
    AND gl.FlagField2 = 1
GROUP BY
    gl.code,
    fp.[Description],
    fp.EndDate,
    gp.DebitValue,
    gp.CreditValue,
    gp.fCompanyCode,
    bu.fTableField1Code,
    gp.fBusinessUnitCode
UNION
SELECT DISTINCT
    gl.code,
    fp.[Description],
    '' AS CompanyCode,
    '' AS BusinessUnitCategory,
    '' AS fbusinessunitcode,
    CalendarYTDDebit = SUM(gp.debitvalue) OVER (PARTITION BY gl.code, Year(fp.enddate) ORDER BY fp.enddate),
    CalendarYTDCredit = SUM(gp.CreditValue) OVER (PARTITION BY gl.code, Year(fp.enddate) ORDER BY fp.enddate)
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
WHERE gl.ChartOfAccounts = 0
    AND fy.BeginDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
    AND gl.FlagField2 = 0
GROUP BY
    gl.code,
    fp.[Description],
    fp.EndDate,
    gp.DebitValue,
    gp.CreditValue;

  EDIT: This also makes the code a LOT more readable.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• ddly_ann

  Mr or Mrs. 500

  Points: 534

  More actions

  April 19, 2018 at 7:00 am

  #1987502

  sgmunson - Thursday, April 19, 2018 6:36 AM

  I took a quick look at your view query, and there are a couple things I'd change.   When you use a function on a column in a WHERE clause, performance can suffer.  As your date requirement is for the column with the date to be "this year", all it needs to be is >= 1/1/xxx, where xxxx is this year.   This can be constructed using the DATEFROMPARTS function, together with GETDATE().  As long as your dates are never future dates, you are good to go with my changes.   If they are ever future dated rows, then that part of the change won't work.  Here's the suggested coding  - you will need to substitute in your schema and view names in the CREATE VIEW statement, and you don't need an outer SELECT for any reason that I can come up with:CREATE VIEW YourSchemaName.YourViewName
AS
SELECT DISTINCT
    gl.code,
    fp.[Description],
    gp.fCompanyCode AS CompanyCode,
    bu.fTableField1Code AS BusinessUnitCategory,
    gp.fbusinessunitcode,
    CalendarYTDDebit = SUM(gp.debitvalue) OVER (PARTITION BY gl.code, gp.fcompanycode, gp.fbusinessunitcode, YEAR(fp.enddate) ORDER BY fp.enddate),
    CalendarYTDCredit = SUM(gp.CreditValue) OVER (PARTITION BY gl.code, gp.fcompanycode, gp.fbusinessunitcode, Year(fp.enddate) ORDER BY fp.enddate)
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 fy.BeginDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
    AND gl.FlagField2 = 1
GROUP BY
    gl.code,
    fp.[Description],
    fp.EndDate,
    gp.DebitValue,
    gp.CreditValue,
    gp.fCompanyCode,
    bu.fTableField1Code,
    gp.fBusinessUnitCode
UNION
SELECT DISTINCT
    gl.code,
    fp.[Description],
    '' AS CompanyCode,
    '' AS BusinessUnitCategory,
    '' AS fbusinessunitcode,
    CalendarYTDDebit = SUM(gp.debitvalue) OVER (PARTITION BY gl.code, Year(fp.enddate) ORDER BY fp.enddate),
    CalendarYTDCredit = SUM(gp.CreditValue) OVER (PARTITION BY gl.code, Year(fp.enddate) ORDER BY fp.enddate)
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
WHERE gl.ChartOfAccounts = 0
    AND fy.BeginDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
    AND gl.FlagField2 = 0
GROUP BY
    gl.code,
    fp.[Description],
    fp.EndDate,
    gp.DebitValue,
    gp.CreditValue;

  EDIT: This also makes the code a LOT more readable.

  Hi. Thankyou for your reply. I have created the view using CREATE VIEW statement. I will adapt the changes you have suggested.

• sgmunson

  SSC Guru

  Points: 110651

  More actions

  April 19, 2018 at 7:37 am

  #1987515

  daleyvinod - Thursday, April 19, 2018 7:00 AM

  sgmunson - Thursday, April 19, 2018 6:36 AM

  I took a quick look at your view query, and there are a couple things I'd change.   When you use a function on a column in a WHERE clause, performance can suffer.  As your date requirement is for the column with the date to be "this year", all it needs to be is >= 1/1/xxx, where xxxx is this year.   This can be constructed using the DATEFROMPARTS function, together with GETDATE().  As long as your dates are never future dates, you are good to go with my changes.   If they are ever future dated rows, then that part of the change won't work.  Here's the suggested coding  - you will need to substitute in your schema and view names in the CREATE VIEW statement, and you don't need an outer SELECT for any reason that I can come up with:CREATE VIEW YourSchemaName.YourViewName
AS
SELECT DISTINCT
    gl.code,
    fp.[Description],
    gp.fCompanyCode AS CompanyCode,
    bu.fTableField1Code AS BusinessUnitCategory,
    gp.fbusinessunitcode,
    CalendarYTDDebit = SUM(gp.debitvalue) OVER (PARTITION BY gl.code, gp.fcompanycode, gp.fbusinessunitcode, YEAR(fp.enddate) ORDER BY fp.enddate),
    CalendarYTDCredit = SUM(gp.CreditValue) OVER (PARTITION BY gl.code, gp.fcompanycode, gp.fbusinessunitcode, Year(fp.enddate) ORDER BY fp.enddate)
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 fy.BeginDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
    AND gl.FlagField2 = 1
GROUP BY
    gl.code,
    fp.[Description],
    fp.EndDate,
    gp.DebitValue,
    gp.CreditValue,
    gp.fCompanyCode,
    bu.fTableField1Code,
    gp.fBusinessUnitCode
UNION
SELECT DISTINCT
    gl.code,
    fp.[Description],
    '' AS CompanyCode,
    '' AS BusinessUnitCategory,
    '' AS fbusinessunitcode,
    CalendarYTDDebit = SUM(gp.debitvalue) OVER (PARTITION BY gl.code, Year(fp.enddate) ORDER BY fp.enddate),
    CalendarYTDCredit = SUM(gp.CreditValue) OVER (PARTITION BY gl.code, Year(fp.enddate) ORDER BY fp.enddate)
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
WHERE gl.ChartOfAccounts = 0
    AND fy.BeginDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
    AND gl.FlagField2 = 0
GROUP BY
    gl.code,
    fp.[Description],
    fp.EndDate,
    gp.DebitValue,
    gp.CreditValue;

  EDIT: This also makes the code a LOT more readable.

  Hi. Thankyou for your reply. I have created the view using CREATE VIEW statement. I will adapt the changes you have suggested.

  Cool beans...   To ensure that your date values don't exceed the current year, here's a modification that will handle that:CREATE VIEW YourSchemaName.YourViewName
AS
SELECT DISTINCT
    gl.code,
    fp.[Description],
    gp.fCompanyCode AS CompanyCode,
    bu.fTableField1Code AS BusinessUnitCategory,
    gp.fbusinessunitcode,
    CalendarYTDDebit = SUM(gp.debitvalue) OVER (PARTITION BY gl.code, gp.fcompanycode, gp.fbusinessunitcode, YEAR(fp.enddate) ORDER BY fp.enddate),
    CalendarYTDCredit = SUM(gp.CreditValue) OVER (PARTITION BY gl.code, gp.fcompanycode, gp.fbusinessunitcode, YEAR(fp.enddate) ORDER BY fp.enddate)
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 fy.BeginDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
    AND fy.BeginDate < DATEFROMPARTS(YEAR(GETDATE()) + 1, 1, 1)
    AND gl.FlagField2 = 1
GROUP BY
    gl.code,
    fp.[Description],
    fp.EndDate,
    gp.DebitValue,
    gp.CreditValue,
    gp.fCompanyCode,
    bu.fTableField1Code,
    gp.fBusinessUnitCode
UNION
SELECT DISTINCT
    gl.code,
    fp.[Description],
    '' AS CompanyCode,
    '' AS BusinessUnitCategory,
    '' AS fbusinessunitcode,
    CalendarYTDDebit = SUM(gp.debitvalue) OVER (PARTITION BY gl.code, YEAR(fp.enddate) ORDER BY fp.enddate),
    CalendarYTDCredit = SUM(gp.CreditValue) OVER (PARTITION BY gl.code, YEAR(fp.enddate) ORDER BY fp.enddate)
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
WHERE gl.ChartOfAccounts = 0
    AND fy.BeginDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
    AND fy.BeginDate < DATEFROMPARTS(YEAR(GETDATE()) + 1, 1, 1)
    AND gl.FlagField2 = 0
GROUP BY
    gl.code,
    fp.[Description],
    fp.EndDate,
    gp.DebitValue,
    gp.CreditValue;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)