Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Order dates in DESC order, starting with current Month/Year Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 10:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 12:49 PM
Points: 39, Visits: 278
I'm trying to get month and year output for an SSRS report using an Oracle data source for use as a multiple value parameter; the user can select dates such as February 2013, January 2013, October 2012. My current query does that, but it does not start at February 2013, it starts at December 2013. The MONTH_YEAR field is populated with month/year up to December 2050, which is why I'm trying to limit the parameter's starting date to choose from the current month and year.
(I was going to post this in Reporting Services, but since it's the Oracle code that I want altered this seemed a more appropriate location.)

SELECT DISTINCT MONTH_YEAR	
FROM EDL.V_CLNDR
WHERE SUBSTR(MONTH_YEAR,1,INSTR(MONTH_YEAR,' ')-1) <= TO_CHAR(SYSDATE,'Month')
AND SUBSTR(MONTH_YEAR, -(LENGTH(MONTH_YEAR) - INSTR(MONTH_YEAR,' ')), (LENGTH(MONTH_YEAR) - INSTR(MONTH_YEAR,' '))) <= TO_CHAR(SYSDATE,'YYYY')
ORDER BY
SUBSTR(MONTH_YEAR, -(LENGTH(MONTH_YEAR) - INSTR(MONTH_YEAR,' ')), (LENGTH(MONTH_YEAR) - INSTR(MONTH_YEAR,' '))) DESC,
TO_DATE(UPPER(SUBSTR(MONTH_YEAR,1,INSTR(MONTH_YEAR,' ')-1)),'MONTH','nls_date_language=american') DESC



"Nicholas"
Post #1433328
Posted Friday, March 22, 2013 11:27 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
5280_Lifestyle (3/20/2013)
I'm trying to get month and year output for an SSRS report using an Oracle data source for use as a multiple value parameter; the user can select dates such as February 2013, January 2013, October 2012. My current query does that, but it does not start at February 2013, it starts at December 2013. The MONTH_YEAR field is populated with month/year up to December 2050, which is why I'm trying to limit the parameter's starting date to choose from the current month and year.
(I was going to post this in Reporting Services, but since it's the Oracle code that I want altered this seemed a more appropriate location.)

SELECT DISTINCT MONTH_YEAR	
FROM EDL.V_CLNDR
WHERE SUBSTR(MONTH_YEAR,1,INSTR(MONTH_YEAR,' ')-1) <= TO_CHAR(SYSDATE,'Month')
AND SUBSTR(MONTH_YEAR, -(LENGTH(MONTH_YEAR) - INSTR(MONTH_YEAR,' ')), (LENGTH(MONTH_YEAR) - INSTR(MONTH_YEAR,' '))) <= TO_CHAR(SYSDATE,'YYYY')
ORDER BY
SUBSTR(MONTH_YEAR, -(LENGTH(MONTH_YEAR) - INSTR(MONTH_YEAR,' ')), (LENGTH(MONTH_YEAR) - INSTR(MONTH_YEAR,' '))) DESC,
TO_DATE(UPPER(SUBSTR(MONTH_YEAR,1,INSTR(MONTH_YEAR,' ')-1)),'MONTH','nls_date_language=american') DESC



This might be a bit over engineered, would you please mind to share the definition of MONTH_YEAR column as well as an example of how data looks like there?


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1434432
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse