I use SQL in Microsoft Query to dump data from a server built on Progress into my Excel Tables. More recently, I've started to make these reports more robust by having the data change based on what day it is. My problem is that this increases the runtime massively. On one it takes a report that can run in under 10 seconds to over 2 minutes. In this example, I am pulling our sales records and looking at the day the order will ship and if it is in the future, I want to display 0 but if it is in the past I want it to display the units shipped. The field oeh_shp_date seems to be in the MM/DD/YYYY format so this is what I rigged together although it is not pretty. Any advice on how to speed this up would be much appreciated. Thanks!
CASE
WHEN ord_head.oeh_shp_date > RIGHT('0' + RTRIM(Cast(Month(CURDATE()) as CHAR(2))),2) + '/' + RIGHT('0' + RTRIM(Cast(DayOfMonth(CURDATE()) as CHAR(2))),2) + '/' + Cast(Year(CURDATE()) as CHAR(4)) then 0
ELSE oe_ast.oel_plan_units
END AS 'UnitsShipped'
Since your querying a postgresql database, standard postgresql functions such as to_date should work. Go ahead and try the following:
CASE
WHEN to_date(ord_head.oeh_shp_date, 'MM/DD/YYYY') > current_date THEN 0
ELSE oe_ast.oel_plan_units
END AS UnitsShipped
The only thing that is strange to me is that it appears your query isn't Postgresql query language. For example CURDATE isn't a function. CURDATE is a function in MySql, so maybe you've gotten the server type wrong? If so, then try this:
CASE
WHEN str_to_date(ord_head.oeh_shp_date, '%m/%d/%Y') > curdate() THEN 0
ELSE oe_ast.oel_plan_units
END AS 'UnitsShipped'