Solved

Query Report Builder - Fiscal Year CASE statement

Posted on 2011-03-25
2
1,672 Views
Last Modified: 2012-05-11
Please assist with this query. I am trying to create two additional fields in my dataset. One is for State Fiscal Year (State_FY) and one for Federal Fiscal Year (Fed_FY).  I'm not getting the results I expected.  How can I modify the query to assign the correct state fy and fed fy?
State FY runs 9/1 to 8/31 and Fed FY runs 10/1 - 9/30
 Screen Shot existing Syntax and results
0
Comment
Question by:gberkeley
2 Comments
 
LVL 3

Accepted Solution

by:
bhoenig earned 250 total points
ID: 35221046
In your case statements, you are using the MONTH and YEAR functions.  Those functions expect a date parameter and you are passing in a integer.  Since the SERVICE_MONTH column already is an integer, you don't need to get the MONTH of it.  Currently you are doing this MONTH(11) and 11 as a date is actually "1900-01-12 00:00:00.000".  My example code will show this better.

/* Run this to show why you aren't getting the results that you want. */
select month(11)
select year(2005)

/* You need to use the date column */
select month('11/1/2005 12:00:00 AM')
select year('11/1/2005 12:00:00 AM')

/* The real date value of 11 and 2005.  The number of days from 1/1/1900 */
select cast(11 as datetime)
select cast(2005 as datetime)

Open in new window

0
 

Author Closing Comment

by:gberkeley
ID: 35223986
Thanks for the assist!!!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question