Hello All,
I am trying to help my IT group with getting a SSRS report to work properly. We are trying to get the functionality in the smartlist where the transaction date is between Beginning of Period and End Period to work in a SSRS report.
Using this code, stole from SQL queries for fiscal years and periods in Dynamics GP by VICTORIA YUDIN. I tried to see if this could be starting point, but our IT group hasn't had any luck.
SELECT D.PERIODID Period_Number, D.PERNAME Period_Name, D.PERIODDT Starting_Date, D.PERDENDT Ending_Date, D.YEAR1 Fiscal_Year FROM SY40100 D INNER JOIN SY40101 H ON H.YEAR1 = D.YEAR1 WHERE D.FORIGIN = 1 AND D.PERIODID <> 0 and GETDATE() between H.FSTFSCDY and H.LSTFSCDY ORDER BY D.PERIODID
This is the result.
| Period_Number |
Starting_Date |
Ending_Date |
Fiscal_Year |
| Period 1 |
2020-04-01 |
2020-05-03 |
2021 |
| Period 2 |
2020-05-04 |
2020-05-31 |
2021 |
| Period 3 |
2020-06-01 |
2020-06-28 |
2021 |
| Period 4 |
2020-06-29 |
2020-08-02 |
2021 |
| Period 5 |
2020-08-03 |
2020-08-30 |
2021 |
| Period 6 |
2020-08-31 |
2020-09-27 |
2021 |
| Period 7 |
2020-09-28 |
2020-11-01 |
2021 |
| Period 8 |
2020-11-02 |
2020-11-29 |
2021 |
| Period 9 |
2020-11-30 |
2020-12-27 |
2021 |
| Period 10 |
2020-12-28 |
2021-01-31 |
2021 |
| Period 11 |
2021-02-01 |
2021-28-28 |
2021 |
| Period 12 |
2021-03-01 |
2021-03-31 |
2021 |
What I want to do is to get the start and ending date to be pulled into the parameter of the SSRS based on the server date. For example today is 2020-07-07. The Starting date would be 2020-06-29 and the ending date would be 2020-08-02.
I am a complete novice on SQL and my Google Fu hasn't found anything that has pointed me in the right direction.
Any help would be much appreciated and Thanks in advance.
