Sql for financial year - help?

Question

0.00/5 (No votes)

See more:

I have the following SQL. It gives the results for calendar year. I need it to give results for Financial Year (July to June). I only have one "SQL Expression" field where I can paste the code into so must be a running code if that makes sense?

CAST(SUM(CASE WHEN datediff(yyyy,getdate(),[vBoGiftsReceived].[GiftDate]) = 0 THEN [vBoGiftsReceived].[GiftAmount] ELSE 0 END) AS DECIMAL(10, 2))

Thanks!!

What I have tried:

I think I need to do something with the getdate but no idea what!

Posted 22-Sep-19 20:30pm

rach_303

Updated 22-Sep-19 22:40pm

Add a Solution

Comments

Herman<T>.Instance 23-Sep-19 3:09am

SQL Injection. Always a NO GO!
What is your exact problem? Can't look in your mind

rach_303 23-Sep-19 3:36am

Thanks for your reply. Sorry I'm only new and now sure of protocols.

In our database we use queries but its all done through a wizard builder so no actual coding. However for this particular query the SQL has been entered into a custom "SQL Expression" field, which is what I have pasted above.

The query looks at the GiftDate in the vBoGiftsReceived table and using the current system date (getdate) sums the gifts that are dated within the current calendar year. This is repeated using -1 -2 etc to look at previous calendar years.

I would like to know if there is a way to change this SQL to sum the gifts received for Financial Year, based on the giftdate and the current date. Financial year would be Jul-Jun.

Thanks!

CHill60 23-Sep-19 4:37am

Ok, I give in. Where is the SQL Injection in the code?

Herman<T>.Instance 23-Sep-19 5:40am

'I only have one "SQL Expression" field where I can paste the code into' .....

rach_303 23-Sep-19 7:04am

Sorry I actually don't know what that means?!

CHill60 23-Sep-19 7:36am

sql injection is where user input can be used to maliciously change the database - see SQL Injection - OWASP[^]
I disagree with digimanus - I think they have misunderstood your meaning.

rach_303 23-Sep-19 7:42am

Oh right, yes definitely a misunderstanding! So I found this also. I'm guessing this is the whole code for that query. Does that make it any clearer? I have no idea how to use what you have suggested below.

SELECT CAST(SUM(CASE WHEN datediff(yyyy,getdate(),[vBoGiftsReceived].[GiftDate]) = 0 THEN [vBoGiftsReceived].[GiftAmount] ELSE 0 END) AS DECIMAL(10, 2)) AS [2018], CAST(SUM(CASE WHEN datediff(yyyy,getdate(),[vBoGiftsReceived].[GiftDate]) = -1 THEN [vBoGiftsReceived].[GiftAmount] ELSE 0 END) AS DECIMAL(10, 2)) AS [2017], CAST(SUM(CASE WHEN datediff(yyyy,getdate(),[vBoGiftsReceived].[GiftDate]) = -2 THEN [vBoGiftsReceived].[GiftAmount] ELSE 0 END) AS DECIMAL(10, 2)) AS [2016], CAST(SUM(CASE WHEN datediff(yyyy,getdate(),[vBoGiftsReceived].[GiftDate]) = -3 THEN [vBoGiftsReceived].[GiftAmount] ELSE 0 END) AS DECIMAL(10, 2)) AS [2015], CAST(SUM(CASE WHEN datediff(yyyy,getdate(),[vBoGiftsReceived].[GiftDate]) = -4 THEN [vBoGiftsReceived].[GiftAmount] ELSE 0 END) AS DECIMAL(10, 2)) AS [2014] FROM vBoGiftsReceived WHERE ((abs(datediff(yyyy,getdate(),[vBoGiftsReceived].[GiftDate])) <= 5) AND [vBoGiftsReceived].[PartyId] = '88590bf7-a0e4-4582-8b19-73c9a396a4ec')

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2019-09-22T22:40:00

There is no standard approach worldwide but this apparently returns the Australian fiscal year (caveat - untested by me)

SQL

select DATEADD(dd,0, DATEDIFF(dd,0, DATEADD( mm,
 -(((12 + DATEPART(m, getDate())) - 7)%12), getDate() ) 
 - datePart(d,DATEADD( mm, -(((12 + DATEPART(m, getDate())) - 7)%12),getDate() ))+1 ) )

credit: Mouli @ accounting - Calculate fiscal year in SQL Server[^]