Link to home
Create AccountLog in
Avatar of garethtnash
garethtnashFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Select Order value for Financial Year

Hello,

I've got a an overly complicated query, that queries a database to find the total sales value for an individual for the current year.

This references an external table (FinanceYear) but this isn't needed as the finance year begins in April each year.

What would be the best way of rewriting this?

Thanks

SELECT ISNULL(SUM(O.OrderSubTotal),0.00) SubTotal 
FROM dbo.Ordertb O 
WHERE OrderDate >= (select  case  when MONTH(GETDATE())>= F.Month AND DAY(GETDATE())>= F.Day  then convert(datetime, cast(year(getdate())-0 as varchar) +'-'+ cast(F.Month as varchar) +'-'+ cast(F.Day as varchar), 110) Else convert(datetime, cast(year(getdate())-1 as varchar) +'-'+ cast(F.Month as varchar) +'-'+ cast(F.Day as varchar), 110) End as FinanceDate From dbo.FinanceYear F) 
And O.MemberID = 179

Open in new window

Avatar of rajvja
rajvja
Flag of United Kingdom of Great Britain and Northern Ireland image

What is the structure of FinancialYear table?
SOLUTION
Avatar of deighton
deighton
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
try this:

declare @year int = YEAR(getdate())
declare @date1 datetime = convert(datetime, cast(@year as varchar) +'-04-01', 110) 
declare @date2 datetime = convert(datetime, cast(@year-1 as varchar) +'-04-01', 110) 

SELECT ISNULL(SUM(O.OrderSubTotal),0.00) SubTotal 
FROM dbo.Ordertb O 
WHERE OrderDate >= (
select  case  when GETDATE()>@date1
then @date1
Else @date2
End ) 
And O.MemberID = 179 

Open in new window

SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of garethtnash

ASKER

Thank you