I didn't write this Query and for some reason I am having trouble modifying it. I need to change the time period to look at the entire YTD transactions. However, it's only pulled up the prior month. I tried to modify it but without success. Thoughts?
SELECT
T.JRNENTRY AS [J/E NUMBER]
, A.ACTNUMBR_1 AS LOC
, A.ACTNUMBR_2 AS MAIN
, A.ACTNUMBR_3 AS RGN
, REPLACE(A.ACTNUMBR_1+'-'+A.ACTNUMBR_2+'-'+A.ACTNUMBR_3, ' ','') AS [FULL ACCOUNT NUMBER]
, A.ACTDESCR AS ACCOUNT
, T.TRXDATE AS [TRANSACTION DATE]
, FP.PERIOD_NUMBER AS [FISCAL PERIOD]
, FP.FY AS [FISCAL YEAR]
, T.REFRENCE AS REFERENCE
, T.DEBITAMT
, T.CRDTAMNT
, (T.DEBITAMT- T.CRDTAMNT) AS [TRX AMOUNT]
, CASE WHEN T.ORMSTRNM ='' THEN T.REFRENCE ELSE T.ORMSTRNM END AS [ORIGINATING MASTER NAME]
FROM GL00100 AS A
INNER JOIN GL20000 AS T ON A.ACTINDX = T.ACTINDX
INNER JOIN (
SELECT
D.PERIODID AS PERIOD_NUMBER
, D.FORIGIN
, D.PERIODDT AS PERIOD_BEGIN_DATE
, D.PERDENDT AS PERIOD_END_DATE
, H.FSTFSCDY AS FY_BEGIN_DATE
, H.LSTFSCDY AS FY_END_DATE
, H.NUMOFPER AS NUMBER_PERIODS
, H.YEAR1 AS FY
, H.HISTORYR AS ARCHIVED
FROM
SY40100 AS D INNER JOIN SY40101 AS H ON H.YEAR1 = D.YEAR1
WHERE D.FORIGIN = 1 AND D.PERIODID <> 0
) AS FP ON T.TRXDATE BETWEEN FP.PERIOD_BEGIN_DATE AND FP.PERIOD_END_DATE
CROSS JOIN (
SELECT
CAST(MAX(D.PERIODID)-1 AS INT) AS PERIOD_NUMBER
FROM
SY40100 AS D INNER JOIN SY40101 AS H ON H.YEAR1 = D.YEAR1
WHERE D.FORIGIN = 1 AND D.PERIODID <> 0 AND GETDATE() BETWEEN D.PERIODDT AND D.PERDENDT
) AS MFP
WHERE (A.ACTNUMBR_1 >= '001') AND (A.ACTNUMBR_2 BETWEEN '6000' AND '8999')
--IF YOU NOTE/DELETE THE [FP.PERIOD_NUMBER = MFP.PERIOD_NUMBER] FILTER ALL FISCAL PERIODS WILL APPEAR
AND FP.PERIOD_NUMBER = MFP.PERIOD_NUMBER
ORDER BY LOC, [TRANSACTION DATE]