SQL for calc fields that sum (data is flattened) are dynamic -- based on current year

Options

Hi there: I need help creating these calculated fields (in Beast Model) where they change based on current year. I created this in EXCEL but am still learning SQL - any help would be much appreciated!

calculated fields

cy

Sum `rtfs_amount` for each DISTINCT `Base Request ID`where `fsa_spending_year` is current year

cy+1

Sum `rtfs_amount` for each DISTINCT `Base Request ID`where `fsa_spending_year` is current year+1

cy+2

Sum `rtfs_amount` for each DISTINCT `Base Request ID`where `fsa_spending_year` is current year + 2

cy+3

Sum `rtfs_amount` for each DISTINCT `Base Request ID`where `fsa_spending_year` is current year +3

Here is SAMPLE data I am working with:

request_transactions_organization_payee_id

Base Request ID

rtfs_amount

fsa_spending_year

700

1

$22,500.00

2023

700

1

$22,500.00

2024

700

1

$22,500.00

2025

800

2

$1,000.00

2023

800

2

$1,000.00

2024

800

2

$1,000.00

2025

800

2

$1,000.00

2026

800

2

$1,000.00

2027

900

3

$200.00

2023

900

3

$200.00

2024

800

4

$350.00

2024

700

5

$1,450.00

2024

700

5

$1,450.00

2025

700

5

$1,450.00

2026

700

5

$1,450.00

2027

Best Answers

  • ArborRose
    ArborRose Coach
    Answer ✓
    Options

    You don't need SQL. In beast mode, make a calculation such as…

    CY:

    CASE WHEN year(`fsa_spending_year`)=year(curdate()) THEN `rfs_amount` ELSE 0 END
    

    For previous year, subtract 365 days. Two years previous, 730 days. Etc.

    PY:

    CASE WHEN year(`fsa_spending_year`) = year(date_sub(curdate(), interval 365 day)) THEN rfs_amount ELSE 0 END
    

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • david_cunningham
    Answer ✓
    Options

    It's because you're comparing an integer YEAR(CURDATE()) to a string (fsa_spending_year).

    Try this instead

    CASE WHEN CAST(`fsa_spending_year` AS INT)=year(curdate()) THEN rtfs_amount ELSE 0 END
    

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Answers

  • ArborRose
    ArborRose Coach
    Answer ✓
    Options

    You don't need SQL. In beast mode, make a calculation such as…

    CY:

    CASE WHEN year(`fsa_spending_year`)=year(curdate()) THEN `rfs_amount` ELSE 0 END
    

    For previous year, subtract 365 days. Two years previous, 730 days. Etc.

    PY:

    CASE WHEN year(`fsa_spending_year`) = year(date_sub(curdate(), interval 365 day)) THEN rfs_amount ELSE 0 END
    

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • AngelaO415
    AngelaO415 Member
    Options

    Thanks @ArborRose - I see conceptually how it should work and I appreciate how you did that! How do I add in there that I want to SUM the rtfs_amount by fsa_spending_year for each Base Request ID. I realize I gave you too perfect of a data sample. There could be cases where the rtfs_amount exist 2-3 times in a given year so I want to add those since they represent diff sources of money within that year.

    request_transactions_organization_payee_id

    Base Request ID

    rtfs_amount

    fsa_spending_year

    700

    1

    $22,500.00

    2024

    700

    1

    $22,500.00

    2024

    700

    1

    $22,500.00

    2025

    800

    2

    $1,000.00

    2023

    800

    2

    $1,000.00

    2024

    800

    2

    $1,000.00

    2025

    800

    2

    $1,000.00

    2026

    800

    2

    $1,000.00

    2027

    900

    3

    $200.00

    2023

    900

    3

    $200.00

    2024

    800

    4

    $350.00

    2024

    700

    5

    $1,450.00

    2024

    700

    5

    $1,450.00

    2024

    700

    5

    $1,450.00

    2025

    700

    5

    $1,450.00

    2025

  • AngelaO415
    AngelaO415 Member
    Options

    I also meant to ask: fsa_spending_year is formatted as string (abc) - do I have to convert that to integer in order to have it match to current year? Sorry, I am such a newbie.

  • ArborRose
    Options

    Typically, you would do it on a date field. But I believe it will convert a string value using

    YEAR(`fsa_spending_year`)
    

    You can use aggregate functions like COUNT and SUM inside beast mode calculations. Just wrap the whole case statement in SUM(…).

    It's okay to be new. I haven't been on here as long as the ancient ones. There are a lot of people here to help, just ask.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • AngelaO415
    AngelaO415 Member
    Options

    For some reason, the Beast Mode didn't work. This is the what I used:

    CASE WHEN year(fsa_spending_year)=year(curdate()) THEN rtfs_amount ELSE 0 END

    This became my output. I would have expected 150000 to appear in the test(cy payments) column.

  • david_cunningham
    Answer ✓
    Options

    It's because you're comparing an integer YEAR(CURDATE()) to a string (fsa_spending_year).

    Try this instead

    CASE WHEN CAST(`fsa_spending_year` AS INT)=year(curdate()) THEN rtfs_amount ELSE 0 END
    

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**