Date Conversion Formula

[kchaudhry]

I am using Crystal Report 8.5 with SQL server 2000 database. I am trying to acheive the following. I need to write a formula where {table.Commission_Qualify_Date} (Month & Year only) equals {table.Period_Date} (Month & Year only). Both these fields are present in the same table. The problem is that {table.Commission_Qualify_Date} is a string type field with data like 20030701 and {table.Period_Date} is a DateTime field with data like 01/07/2003 00:00:00AM.

The challenge is that I only need to make sure that the month and year are the same. I am not sure how to do this with two different types of data.

Can anyone please guide me in the right direction?

Thanks,

Kchaudhry

Kchaudhry

 

[synapsevampire]

Getting the database to do it would provide optimal performance, but you can use a record selection formula as in:

MONTH({mytable.somedate1}) = VAL(LEFT({mytable.somestringdate},2))

-k

 

[kchaudhry]

As per you directions I have tried to add the actual values from the tables and I am getting an error that says a date is required.

Here is what my formula looks like

if {Archive_Participant_Move_in_Trans.Trans_Category} = "Local"
and
MONTH("20030704&quot
=
VAL(LEFT(CDateTime (2003, 11, 07, 17, 20, 56),2))
then
1
else
0

I know I am doing something wrong but not sure what it is?

All your help is appreciated.

Thanks,


Kchaudhry

 

[synapsevampire]

Your post stated that you were comparing 2 date fields in the database, not 2 strings.

Your formula looks nothing like mine, I'm using fields, you're using strings.

year({table.Period_Date}) = val(left({table.Commission_Qualify_Date},4))
and
month({table.Period_Date}) =
val(mid({table.Commission_Qualify_Date},5,2))

Or you might use:

year({table.Period_Date}) =
year(cdate(val("20030701" [1 to 4]), val("20030701" [5 to 6]), val("20030701" [7 to 8])))
and
month({table.Period_Date}) =
month(cdate(val("20030701" [1 to 4]), val("20030701" [5 to 6]), val("20030701" [7 to 8])))

Note that the second formula converts the string date to a real date, which might prove useful elsewhere.

This formula should go into the report->edit record selection formula->record

-k

 

[kchaudhry]

synapsevampire,

Thanks for all your help. The formula is working perfectly fine.


Kchaudhry

 

[kchaudhry]

Sorry to be a pain. My formula is currently looking like this now:

if
{Archive_Participant_Move_in_Trans.Trans_Category} = "Local"
And
year({Archive_Participant_Move_in_Trans.period_date}) = val(left({Archive_Participant_Move_in_Trans.Commission_Qualifying_Date},4))
And
month({Archive_Participant_Move_in_Trans.period_date}) = val(mid({Archive_Participant_Move_in_Trans.Commission_Qualifying_Date},5,2))
then
1
else
0

I am getting repeated records for some reason in the details section. I have the "Select Distinct Records" option selected.

Any ideas why this is happening and how can I solve this?

Thanks in advance,



Kchaudhry

 

[synapsevampire]

Theformula has nothing to do with similar rows.

Check carefully and you'll find that ther eis something unique about each row.

I would guess that the problem is the result of your table joins, but I've no real information to go on, much less what you intend to do.

Try posting example data (not what's in the report), and what you want the report to look like.

-k

 

[kchaudhry]

Ok here is my table linking information:

I have total 7 tables in one report.
{Participant} equal join {Participant_Move_in_Trans}
{Participant_Move_in_Trans} equal join {Archive_Participant_Move_in_Trans}
{Participant} equal join {Compensation}
{Participant} equal join {Measure}
{Participant} equal join {Formula}
{Plan_Manager) No link

The report is grouped on {Participant.Participant_id}

Ther SQL query looks like this
SELECT DISTINCT
participant."name_last", participant."name_first", participant."id", participant."position",
formula."formula", formula."compensation_total",
Participant_Move_in_Trans."Project", Participant_Move_in_Trans."Zone", Participant_Move_in_Trans."Apartment_Type", Participant_Move_in_Trans."Sub_Project_Code", Participant_Move_in_Trans."Monthly_Rate", Participant_Move_in_Trans."Building", Participant_Move_in_Trans."Apartment", Participant_Move_in_Trans."Orig_Move_in_Date", Participant_Move_in_Trans."Reservation_Date", Participant_Move_in_Trans."Commission_Qualifying_Date", Participant_Move_in_Trans."Length_of_Stay_Days", Participant_Move_in_Trans."Tenant_Last_Name", Participant_Move_in_Trans."Corp_Name", Participant_Move_in_Trans."Six_Mo_Lease_Indicator", Participant_Move_in_Trans."Term", Participant_Move_in_Trans."International_Indicator", Participant_Move_in_Trans."Trans_Type", Participant_Move_in_Trans."Lease_Target_Rate", Participant_Move_in_Trans."Trans_Category", Participant_Move_in_Trans."Tier_Elig_Count", Participant_Move_in_Trans."Tier_Level", Participant_Move_in_Trans."Prod_Com_Elig", Participant_Move_in_Trans."Prod_Com_Local", Participant_Move_in_Trans."Prod_Com_Outbound", Participant_Move_in_Trans."Prod_Com_Inbound", Participant_Move_in_Trans."Override_Price_Variation", Participant_Move_in_Trans."Override_Com_Individual", Participant_Move_in_Trans."Min_Individual_Amt", Participant_Move_in_Trans."Other_Com_Type", Participant_Move_in_Trans."Other_Com_Individual_Amt", Participant_Move_in_Trans."Intl_Com_Amt", Participant_Move_in_Trans."Six_Mo_Contract_Com_Amt", Participant_Move_in_Trans."Total_Com",
compensation."compensation_total",
Archive_Participant_Move_in_Trans."International_Indicator", Archive_Participant_Move_in_Trans."Trans_Category"
FROM
{ oj ((("Oakwood"."dbo"."participant" participant INNER JOIN "Oakwood"."dbo"."Participant_Move_in_Trans" Participant_Move_in_Trans ON
participant."id" = Participant_Move_in_Trans."Participant_Id&quot
INNER JOIN "Oakwood"."dbo"."compensation" compensation ON
participant."id" = compensation."id&quot
INNER JOIN "Oakwood"."dbo"."formula" formula ON
participant."id" = formula."id&quot
INNER JOIN "Oakwood"."dbo"."Archive_Participant_Move_in_Trans" Archive_Participant_Move_in_Trans ON
Participant_Move_in_Trans."Participant_Id" = Archive_Participant_Move_in_Trans."Participant_Id"}
WHERE
(participant."position" = 'OAKWOOD AC' OR
participant."position" = 'OAKWOOD AE' OR
participant."position" = 'OCH AC' OR
participant."position" = 'OCH AE')
ORDER BY
participant."id" ASC,
Participant_Move_in_Trans."Reservation_Date" ASC,
Participant_Move_in_Trans."Orig_Move_in_Date" ASC,
Participant_Move_in_Trans."Project" ASC,
Participant_Move_in_Trans."Sub_Project_Code" ASC,
Participant_Move_in_Trans."Building" ASC,
Participant_Move_in_Trans."Apartment" ASC

Hope this helps in explaining what is the type of output I am looking for. I have looked carefully and it seems like the records are identical.

I am not sure what I am doing wrong.


Kchaudhry

 

[synapsevampire]

First, note that Plan_Manager isn't in the SQL, so it's not being used, remove it.

I'm afraid I can't really help based on the information provided, again "Try posting example data (not what's in the report), and what you want the report to look like."

Also see the OJ between tables:

Participant_Move_in_Trans."Participant_Id" = Archive_Participant_Move_in_Trans."Participant_Id"

-k

 

[kchaudhry]

Here is what I want in the details section:

Proj SubProj Bldg Apt Zone AptType CorpName Tenant ResDate
---- ------- ---- --- ---- ------- -------- ------ -------
0542 0542 A 102 C SF NASA ZHAO 20030804

MovDate QualifyDate LOS Target Actual Override MoveinType
------- ----------- --- ------ ------ ------- -----------
20030809 20030831 6 0 2610 0 Local

Override$ Prod$ Min$ TotalO/P ST$ 6Mo$ Intl WholeSale
--------- ----- ---- -------- --- ---- ---- ---------
0 0 0 0 6 0 0 0

Unfurnished TotalCom Prod# TierLevel
----------- -------- ----- --------
0 6 5 1

These are the fields included in the details section and the data. This is all in one row but due to space limitation I had to show it like this. I am getting repeated records in the deatils section. Hope this explains better what is my problem.

Thanks for all your help.


Kchaudhry

 

[kchaudhry]

Can anyone think of anything I am doing wrong?

All your help is appreciated.

Kchaudhry