SAP B1 Query Fiscal Year

[former_member584417]

Hi,

I have a Budget Query and I just found out my customer has a Fiscal Year from 01/10/2018 to 30/09/2019. My query is comparing the year.

Any idea how I can compare the Fiscal year if it includes 2018 and 2019 ?

,(SELECT obgt.CredLTotal - obgt.DebLTotal FROM OBGT WHERE YEAR(OBGT.FinancYear) = YEAR(getdate()) -1 AND T0.AcctCode = OBGT.AcctCode) AS 'Budget2019'

Here is the entire Query:

/*

/* SELECT FROM JDT1 D1 */

DECLARE @FROM AS DATE

/* WHERE */

SET @FROM = /* D1.RefDate */ '[%1]'

/* SELECT FROM JDT1 D2 */

DECLARE @TO AS DATE

/* WHERE */

SET @TO = /* D2.Refdate */ '[%2]' */

SELECT T1.[Description compte de GL],T1.[Liste des compte de GL],T1.Actual2019 as 'Année en cours (2019)',YEAR(getdate()) -1 as 'budget year',

t1.Budget2019 as 'Budget en cours (2019)',

(t1.actual2019 - t1.budget2019) / t1.budget2019 *-100 as '% 2019',

t1.[Actual2018] as 'Année précédente (2018)' ,

(t1.actual2018 - t1.budget2019) / t1.budget2019 *100 as '% 2018',

t1.[Actual2017] as 'Année précédente (2017)',

(t1.actual2017 - t1.budget2019) / t1.budget2019 *100 as '% 2017'

FROM(

SELECT T0.AcctName as 'Description compte de GL', T0.FormatCode as 'Liste des compte de GL'

,(SELECT obgt.CredLTotal - obgt.DebLTotal FROM OBGT WHERE YEAR(OBGT.FinancYear) = YEAR(getdate()) -1 AND T0.AcctCode = OBGT.AcctCode) AS 'Budget2019'

,(SELECT SUM(jdt1.credit)-SUM(jdt1.debit) FROM JDT1 WHERE YEAR(JDT1.RefDate) = YEAR(GETDATE()) -1 AND JDT1.Account = T0.AcctCode GROUP BY JDT1.Account) as'Actual2019'

,(SELECT SUM(jdt1.credit)-SUM(jdt1.debit) FROM JDT1 WHERE YEAR(JDT1.RefDate) = YEAR(GETDATE()) -2 AND JDT1.Account = T0.AcctCode GROUP BY JDT1.Account) as 'Actual2018'

,(SELECT SUM(jdt1.credit)-SUM(jdt1.debit) FROM JDT1 WHERE YEAR(JDT1.RefDate)= YEAR(GETDATE()) -3 AND JDT1.Account = T0.AcctCode GROUP BY JDT1.Account) as 'Actual2017'

FROM OACT T0

) T1