Dynamic year help!

[chunbabachun8]

Hi,
I was wandering if anyone can help me out. I want get rid of hard coded date and year instead make this range of date dynamic and by changing the year only.
I assume my (Fiscal Year)
@FY = '2018'
CASE BETWEEN (TDATE,{^2017-07-01},{^2018-06-30}) -- I want to make only Year Dynamic like {TDATE,{^(@YR-1)+(-07-01),{^(@YR)+(-06-30)}
YR = '2018'
CASE BETWEEN (TDATE,{^2016-07-01},{^2017-06-30}) -- {TDATE,{^(@YR-2)+(-07-01),{^(@YR-1)+(-06-30)}
YR = '2017'
CASE BETWEEN (TDATE,{^2015-07-01},{^2016-06-30}) -- {TDATE,{^(@YR-3)+(-07-01),{^(@YR-2)+(-06-30)}
YR = '2016'

I'm new to FoxPro programming and I am helping my company to fix the hardcoded years to dynamic fiscal year ending and beginning.

I mostly work on sql programming.

Really appreciate for quick help!

chunbabachun8

 

[JRB-Bldr]

I can't tell if your CASE code above is supposed to be within a SQL Query command or just as 'regular' CASE/ENDCASE code.

Also, just give us the First and Last dates of your Fiscal Calendar (regardless of YEAR) so that we can better understand what you are trying to do.

BTW: to get a better understanding of VFP you might want to spend some time with the free, on-line VFP tutorial videos at: Link

Good Luck,
JRB-Bldr

 

[chunbabachun8]

Thanks JRB,
First date would be 2017-07-01 and 2018-06-30 would be the last day of fiscal year.
BTW, thanks for the link. I'd definitely spend some time with this link.

chunbabachun8

 

[JRB-Bldr]

With VFP, like with most other languages, there are many ways to approach an issue.

One way that comes to mind might be as follows:
EDIT: I keep thinking about how to change this. Here is the latest... (Sorry for the confusion)

* --- Enable 4 digit Year ---
SET CENTURY ON

* --- For any TDate, it is either <= Last Date or >= First Date ---
nTDateYr = YEAR(TDate)
DO CASE
   CASE MONTH(TDate) <= 6 AND DAY(TDate) <= 30
      * --- If Early Months of Year, Date is Already Within Fiscal Year ---
      nFiscYr = nTDateYr

   CASE MONTH(TDate) >= 7 AND DAY(TDate) >= 1
      * --- If Late Months of Year, Fiscal Year needs to be Pushed Out ---
      nFiscYr = nTDateYr + 1
ENDCASE

* --- Change Numeric Fiscal Year To String ---
cFiscYr = STR(nFiscYr,4)

NOTE - I have not tested the above code in ALL situations. And it 'feels' like the code could readily be simplified.

If you want the code integrated into a SQL Query statement, then it would be done somewhat differently.

Good Luck,
JRB-Bldr

 

[chunbabachun8]

Thanks GRB, I'll let you know if the code works for me.

chunbabachun8

 

[Olaf Doschke]

The question you have is T-SQL, so should rather be asked in forum183

Your code seems to have some flaws, T-SQL variables are prefixed by @, not only when reading them, also when setting them, you set YR='2018', '2017', '2016', where that also should be @YR, rather SET @YTR=something. then I don't know if a string is better here than a number.

Given the outset, you want to pass in a year number from VFP into a T-SQL script that could work by VFPs TextMerge into a script you put together with TEXT...ENDTEXT and that would be the VFP part of the problem, but you could also fully solve that within SQL Server in a stored procedure with a parameter. The call within VFP then would be exec sp_yourstoredprocname(2017), for example. and the rest of the problem is to be solved in T-SQL.

Bye, Olaf.

 

[JRB-Bldr]

First date would be 2017-07-01 and 2018-06-30

Since I wasn't sure which way your Fiscal Calendar went I assumed that the Fiscal Year 2017 went Forward from 7-1-17.
However if it goes back from 6-30-17 then you can readily reverse the logic.

Again, if you wanted the code to reside within a SQL Query statement, then you would need to modify it accordingly.

Example:

SELECT *,;
IIF(MONTH(TDate) <= 6 AND DAY(TDate) <= 30, YEAR(TDate), YEAR(TDate)+1) as FiscYear;
FROM DataTable;
INTO CURSOR AAA READWRITE

SELECT AAA

Keep in mind that if your VFP code is doing a SQL Query into an 'external', non-native database (such as M$ SQL Server) you will need to use THAT Database's SQL Syntax, not VFP's.


Good Luck,
JRB-Bldr