Fiscal year concat not works in Sqlserver 2008

Question

Fiscal year concat not works in Sqlserver 2008

smer

SSCommitted

Points: 1684
More actions
May 16, 2016 at 2:07 am

#327078

Hi All,
how to modify the fiscal year sqlquery to works in 2008r2?
SELECT
CASE WHEN MONTH(service_date)>=7 THEN
concat(YEAR(service_date), '-',YEAR(service_date)+1)
ELSE concat(YEAR(service_date)-1,'-', YEAR(service_date)) END AS financial_year,
SUM(invoice_amount)
FROM mytable
GROUP BY financial_year

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 23000 More actions · Answer 1

smer (5/16/2016)
Hi All,
how to modify the fiscal year sqlquery to works in 2008r2?
SELECT
CASE WHEN MONTH(service_date)>=7 THEN
concat(YEAR(service_date), '-',YEAR(service_date)+1)
ELSE concat(YEAR(service_date)-1,'-', YEAR(service_date)) END AS financial_year,
SUM(invoice_amount)
FROM mytable
GROUP BY financial_year

The CONCAT function isn't available on 2008 R2. You'll need to use '+' to concatenate your string. You'll also have to convert the datatypes like so:

SELECT CAST(YEAR(service_date) AS CHAR(4)) + '-' + CAST(YEAR(DATEADD(YEAR,1,service_date))AS CHAR(4)

It's better to use the DATEADD function too. Adding or subtract INT values from a date doesn't work with the datatypes introduced in 2008. It's also easier to see what the query is intended to do.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

smer SSCommitted Points: 1684 More actions · Answer 2

Thank you !

I tried this as you advised below but how to count the no of days in fiscal year to date?

SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, service_date) - (12 + DATEPART(MONTH, service_date) - 7) % 12, 0) FinancialYearStart,
DATEADD(MONTH, DATEDIFF(MONTH, 0, service_date) - (12 + DATEPART(MONTH, service_date) - 7) % 12 + 12, -1)FinancialYearEnd,
SUM(invoice_amount)
FROM my table
WHERE ITEM_NAME='tt'
AND service_date<='3/23/2015'
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, service_date) - (12 + DATEPART(MONTH, service_date) - 7) % 12, 0),
DATEADD(MONTH, DATEDIFF(MONTH, 0, service_date) - (12 + DATEPART(MONTH, service_date) - 7) % 12 + 12, -1)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

At this point, you might want to consider building a "Calendar Table". There are a lot of good articles out there on the subject that you can Google.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Neil Burton SSC-Insane Points: 23000 More actions · Answer 4

What Jeff said.

It's quite a big jump from concatenating strings to calculating days in a fiscal year. A calendar table of some description will certainly make things easier.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537