| Author |
Message |
vpriyanka2001
Groupie
Joined: 28 May 2009
Online Status: Offline
Posts: 59
|
   Posted: 27 Aug 2009 at 10:02am |
|
All the Application Received dates.
|
 IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 27 Aug 2009 at 10:15am |
Sorry, I was asking for actually sample data not the concept of the data.
None of this is tracking properly for me.
You state you had an existing accurate report with that formula and it worked.
You repalced the source for this report with a command using the same fields so it should continue to work fine.
However you are stating that the value that is not working is a varchar. THis should have broke the original formula.
If it is really is just a string and you want to check if it is not an EXACT match to 1/1/1900 you can make it
{Command_1.date_app_recvd} <> "1/1/1900"
but that was why i wanted to see sample data to see what the real string is.
|
|
IP Logged |
|
vpriyanka2001
Groupie
Joined: 28 May 2009
Online Status: Offline
Posts: 59
|
Posted: 27 Aug 2009 at 10:48am |
|
Sorry to confuse you. Let me try and be more clear this time.
1. I have to replace the Old report database (eg Progress Database) with New Database (Dateinfo)
2. Old table had all the columns in Progress database, where as in New Report I had to join 2 Tables to get all the columns (Eg (table 1) Date info + Table 2 Policy.
3. Table 2 Policy has Field named as date_app_recvd and all other fields are in Date info table.
A) I see 1 Formula (Formula Name = Include) used in old table
IIF ({date_info.date_app_entered} <> #1/1/1900# and {date_info.date_app_recvd} <> #1/1/1900# and
{date_info.date_qt_mailed} = #1/1/1900# and {date_info.date_gen_letter} = #1/1/1900# and
{date_info.date_declined} = #1/1/1900# , "YES" , "NO")
B) In select Expert Record
1. Date_info_lifecycle_status is one of the {date_info.lifecycle_status} in ["QUOTE", "RENEWAL", "SUBMISSION"] and
{@Include} = "YES" and
{date_info.date_app_entered} >= Date (2008, 01, 01)
2. Date_info_date_app_entered is greater than or equal to 1/1/2008
{@Include} = "YES" and
{date_info.lifecycle_status} in ["QUOTE", "RENEWAL", "SUBMISSION"] and
{date_info.date_app_entered} >= Date (2008, 01, 01)
3.Formula Include = YES
{date_info.lifecycle_status} in ["QUOTE", "RENEWAL", "SUBMISSION"] and
{date_info.date_app_entered} >= Date (2008, 01, 01) and
{@Include} = "YES"
4. date_info date_app recvd = IS ANY VALUE
{date_info.lifecycle_status} in ["QUOTE", "RENEWAL", "SUBMISSION"] and
{date_info.date_app_entered} >= Date (2008, 01, 01) and
{@Include} = "YES"
Date what I see in OLD REEPORT for Date_app_recvd is
eg: 2/3/2008
7/21/2008
ANything after 1/1/2008
Edited by vpriyanka2001 - 27 Aug 2009 at 10:54am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 27 Aug 2009 at 11:05am |
OK , this makes a bit more sense. Sounds like the upgrade altered a field from an actual datetime to a "timestamp" stored as a string.
Is that correct?
Depending on how the string is stored (again seeing some actual sample data from this field would help) you have a few options.
In your command you can use a SQL to cast the value from a varchar to a datetime before it gets into the DB. This is the simpletest solution if you can get it to work for you.
Otherwise once in crystal you will have to write a formula to change it from a string to a date or datetime. THis again depends on how the field is written as a string.
Does this help?
|
|
IP Logged |
|
vpriyanka2001
Groupie
Joined: 28 May 2009
Online Status: Offline
Posts: 59
|
Posted: 27 Aug 2009 at 11:30am |
|
3/4/2008
7/28/2008
8/5/2008
8/25/2009
10/3/2009
This is how date_app_recvd data looks like
I have picked the 1st and last date and 3 in between.
IN New POLICY Table: date_app_recvd: (nvarchar(50), NULL)
-----------------------------
Command
select
d.ISN,
d.name_ins,
d.lifecycle_status,
d.date_app_entered,
p.date_app_recvd,
d.date_qt_mailed,
d.date_gen_letter,
d.date_declined,
d.uwinits,
d.bornum
from DateInfo D,
policy P
where
d.OBJECT_NUM = p.POLICY_NUM
--------
Now my Current report has :
1. Command
select
d.ISN,
d.name_ins,
d.lifecycle_status,
d.date_app_entered,
p.date_app_recvd,
d.date_qt_mailed,
d.date_gen_letter,
d.date_declined,
d.uwinits,
d.bornum
from DateInfo D,
policy P
where
d.OBJECT_NUM = p.POLICY_NUM
2. Include Formula
IIF ({command.date_app_entered} <> #1/1/1900# and cdate({command.date_app_recvd}) <> #1/1/1900# and
{command.date_qt_mailed} = #1/1/1900# and {command.date_gen_letter} = #1/1/1900# and
{command.date_declined} = #1/1/1900# , "YES" , "NO")
3. Select Expert Record
command.lifecycle status: is one of
{command.date_app_entered} >= Date (2008, 01, 01) and
{@Include} = "YES" and
{Command.lifecycle_status} in ["QUOTE", "RENEWAL", "SUBMISSION"]
b. command.date_app_entered : is equal to: 1/1/2008
{@Include} = "YES" and
{Command.lifecycle_status} in ["QUOTE", "RENEWAL", "SUBMISSION"] and
{command.date_app_entered} >= Date (2008, 01, 01)
c. Formula Include = YES
{Command.lifecycle_status} in ["QUOTE", "RENEWAL", "SUBMISSION"] and
{Command.date_app_entered} >= DateTime (2008, 01, 01, 00, 00, 00) and
{@Include} = "YES"
I am getting lot more Data thna the original report and my Date_app_recvd field has date coming as :2000-09-14 00:00:00
and when I do FORMAT FIELD I cannot See DATE TAB.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 27 Aug 2009 at 11:58am |
You only get date time on the format field if the field itself is a datetime type. It is now a string field based on your new table designs. Cast it using your Command from a Varchar to a datetime.
select
d.ISN,
d.name_ins,
d.lifecycle_status,
d.date_app_entered,
cast(p.date_app_recvd as datetime) as date_app_recvd,
d.date_qt_mailed,
d.date_gen_letter,
d.date_declined,
d.uwinits,
d.bornum
from DateInfo D,
policy P
where
d.OBJECT_NUM = p.POLICY_NUM
|
|
IP Logged |
|
vpriyanka2001
Groupie
Joined: 28 May 2009
Online Status: Offline
Posts: 59
|
Posted: 28 Aug 2009 at 7:54am |
Iam getting error: Arithmetic Overflow error, converting expression to data type column 
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Aug 2009 at 9:19am |
Not sure why that is happening.
If you go back to the previous post and use the answer of:
cdate({command.date_app_recvd}) <> #1/1/1900#
you said you want the format to be different.
Create a new formula field as "recvd_Date" (or whatever you want as)
cdate({command.date_app_recvd})
Use it on your report instead of the original field from the DB. It will allow you to change the format using the Time Tab in the Format Field.
Edited by DBlank - 02 Sep 2009 at 10:12am
|
|
IP Logged |
|
vpriyanka2001
Groupie
Joined: 28 May 2009
Online Status: Offline
Posts: 59
|
Posted: 02 Sep 2009 at 10:06am |
|
Thanks for all your help.
|
|
IP Logged |
|
|
|
