NEED URGENT HELP PLEASE
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7633
Printed Date: 06 May 2024 at 12:49pm
Topic: NEED URGENT HELP PLEASE
Posted By: vpriyanka2001
Subject: NEED URGENT HELP PLEASE
Date Posted: 09 Sep 2009 at 11:02am
OLD QUERY
In Old report, all the data was coming from one table Date_info
1 Formula used: Include:
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")
-----
1 Record selection formula
{@Include} = "YES" and
{date_info.lifecycle_status} in ["QUOTE", "RENEWAL", "SUBMISSION"] and
{date_info.date_app_entered} >= Date (2008, 01, 01)
-----------------------------------------------------------
NEW QUERY
In New Report,
I need to JOIN 3 Tables and use all the old report Criteria to get the data.
BUt My New Query is failing on second last line please help.
/*+ PARALLEL(dateinfo,4)*/
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
left outer join policy p on d.lifecycle_status = p.lifecycle_status and
d.OBJECT_NUM = p.POLICY_NUM and
d.object_ver = p.policy_ver
left outer join quote q on d.lifecycle_status =
q.lifecycle_status and
d.OBJECT_NUM = q.quote_NUM and
d.object_ver = q.quote_ver
where
d.lifecycle_status in ('submission','quote','renewals')and
d.date_app_entered > '2008/01/01' and
p.date_app_recvd <> '1/1/1900' and
d.date_qt_mailed = '1/1/1900' and
d.date_gen_letter = '1/1/1900' and
d.date_declined = datetime '1/1/1900' , "YES" , "NO")
order by d.date_app_entered
----------------------------------------
|
Replies:
Posted By: hilfy
Date Posted: 09 Sep 2009 at 12:35pm
|
Take out ', "YES" , "NO")' from that line. It was part of the IIF statement in your original formula and it's not needed here.
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: vpriyanka2001
Date Posted: 10 Sep 2009 at 9:07am
My Query Below Works, But in Crystal format editor my
Field p.Date_app_recvd is not showing date time, I need to modify it to Date only.
Any Idea how to do that or if I can do that on my query it will be great.
In database field Date_app_recvd data type is DATETIME
select
d.object_num,
d.object_ver,
q.quote_num,
q.quote_ver,
p.policy_num,
p.policy_ver,
d.ISN,
d.name_ins,
d.lifecycle_status,
d.date_app_entered,
q.date_app_recvd,
p.date_app_recvd,
d.date_qt_mailed,
d.date_gen_letter,
d.date_declined,
d.uwinits,
d.bornum
from dateinfo d
left outer join quote q
ON d.object_num=q.quote_num
and d.object_ver=q.quote_ver
left outer join policy p
on d.object_num=p.policy_num
and d.object_ver=p.policy_ver
where
(d.lifecycle_status ='renewal' or
d.lifecycle_status = 'quote' or
d.lifecycle_status = 'submission') and
(p.date_app_recvd <> '1/1/1900' or
q.date_app_recvd <> '1/1/1900') and
d.date_app_entered > '2008/01/01' and
d.date_qt_mailed = '1/1/1900' and
d.date_gen_letter = '1/1/1900' and
d.date_declined = '1/1/1900'
order by d.date_app_entered
|
Posted By: Jyothi Yepuri
Date Posted: 13 Sep 2009 at 8:56pm
try using Convert(varchar(10),p.date_app_recvd ,101)
HTH,
Jyothi
|
|