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

Options
Member

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

• Coach
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
```

** Did this solve your problem? Accept it as a solution! **

• Coach
Options

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

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

David Cunningham

** Did this solve your problem? Accept it as a solution! ✔️**

• Coach
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
```

** Did this solve your problem? Accept it as a solution! **

• 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

• 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.

• Coach
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.

** Did this solve your problem? Accept it as a solution! **

• 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.

• Coach
Options

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

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