Max effective date less than specific future date 1

[John1Chr]

I'm using crystal 8.5 and within the SQL Expression Editor I want to prevent pulling in future date if it is 7/1/2013 or greater. Other than that I want the max effective date.

My current formula is:


(SELECT MAX(EFFDT)
FROM TABLE TABLE2
WHERE TABLE2.CT_VALUE = TABLE.CT_VALUE and
TABLE2.CFATTRIB = TABLE.CFATTRIB)

 

[hilfy]

Try something like this (you don't specify your database so I'm going to assume Oracle - change the syntax as needed for your DB):
(
Select MIN(MYDATE)
from
(SELECT MAX(EFFDT)as MYDATE FROM TABLE TABLE2WHERE TABLE2.CT_VALUE = TABLE.CT_VALUE and TABLE2.CFATTRIB = TABLE.CFATTRIB
UNION ALL
Select '01-JUL-2013' as MYDATE from Dual)
)

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[John1Chr]

I'm getting this error - ODBC error MS dbase engine can't find the input table or query 'Dual". Make sure it exists and its name is spelled correctly.

Is it possible to put if the table CFATTRIB is PROG or ACT then select if less than 7/1/2013? CFATTRIB can have up to 10 different attributes to it and I only want to focus on the PROG and ACT attributes in that field.

 

[hilfy]

Dual" is an Oracle thing. You should be able to remove the "From" part of the bottom of the union and you'll probably need to convert the string for the date into an actual date - Oracle will do the conversion automatically when the date string is in the format 'dd-MON-yyyy'.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[John1Chr]

Hi - getting error query must contain at least one table or query after removing the from Dual. (Ended with MyDate)

Also received another error that followed - Syntax error before the first MIN.

 

[hilfy]

You'll need to go into a database query tool such as Toad or SQL Server Management Studio to develop your query. I have not used much SQL Server, so I can't help you with that.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[John1Chr]

I think I'm almost there. I'm using in a Select formula like this and it pushes me back to the beginning MAX it doesn't like my date. If I use < Date (2013,07,01) flat out in the crystal record selection formula editor it works fine without the formula. It will not work in the formula though. I can't get the date to work in SQL Expression Editor for the formula below -

(SELECT MAX(TABLE2.EFFDT)
FROM TABLE TABLE2
WHERE TABLE2.CT_VALUE = TABLE.CT_VALUE and
TABLE2.CFATTRIB = TABLE.CFATTRIB
AND ( (TABLE2.CFATTRIB not in ('PROG','ACT') or TABLE2.EFFDT < '01 Jul
2013')) )

 

[hilfy]

You'll probably need to convert the string version of the date to a date in your SQL in order to get this working.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[John1Chr]

You're talking about converting the MAX(TABLE2.EFFDT) beginning and the TABLE2.EFFDT < '01 Jul 2013')) ) to the Date (2013,07,01) format correct? Crystal doesn't have a TODATE function. Not sure exactly how I would do this in the SQL Expression Editor. Is there a formula that I can try?

 

[John1Chr]

I'm thinking that I need to convert to DATETIME. That is the format on the ODBC Oracle table.

 

[hilfy]

Yes, and because this is a SQL Expression, you can't use any Crystal functions - you have to do it in the correct syntax for your database.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[John1Chr]

Is there a way in the record selection formula to say if the table CFATTRIB is PROG or ACT then select if effective date is less than 7/1/2013?

CFATTRIB can have up to 10 different attributes to it and I only want to focus on the PROG and ACT attributes in that field. Currently, I have a SQL expression field that pulls in max effect date for all 10 attributes:

(SELECT MAX(EFFDT)
FROM TABLE TABLE2
WHERE TABLE2.CT_VALUE = TABLE.CT_VALUE and
TABLE2.CFATTRIB = TABLE.CFATTRIB)

Problem is that when I say in the Record Selection Formula Editor TABLE.EFFDT = {%maxeffdt} AND TABLE.EFFDT < Date (2013,07,01) it completely removes the attribute that has any effdt exceeding 7/1/2013. It will not grab the attribute that has a max eff date less than 7/1/2013 - rather completely removes the PROG or ACT from the report.

 

[John1Chr]

I'm really thrown by the date. I know that the table that it is reading from is in DATE/TIME format and it accepts if I put EFFDT < '7/1/2013' in the SQL Expression Editor query. but when I run the report it bombs data type mismatch. Somehow the SQL needs to be converted to Date (YYYY,MM,DD) when it is read in the record selection formula editor. I think.

 

[IanWaterman]

TRy

<Convert(DateTime, '07/01/2013' , 102)

102 = US Format MM/DD/YYYY
103 = UK DD/MM/YYYY

Ian

 

[John1Chr]

It didn't take. The actual format in that field is '7/1/2013 12:00:00AM'. So when I put the Max effect Date SQL Expression Editor formula in the Record selection formula editor it bombs. It bombs if I try to place in the report, also.

Might need to go back to the drawing board.

 

[John1Chr]

it does work if I put the crystal funciton EFFDT < {fn NOW()})) ) at the end. It filters out the dates before now.