Date Comparison Formula

[kchaudhry]

I am using CR 8.5 with SQL 2000 backend. I am facing a problem while comparing dates. I have to show certain records if the year and month match. I am using the following formula:

if {Participant_Move_in_Trans.Term} = "L"
and
{Participant_Move_in_Trans.International_Indicator} = "D"
and
{Participant_Move_in_Trans.Project} < &quot;900&quot;
and
{Participant_Move_in_Trans.Trans_Category} = &quot;Local&quot;
and
{participant.position} = &quot;OAKWOOD AE&quot;
and
(left({Participant_Move_in_Trans.Orig_Move_in_Date}, 4))
=
(left({plan_manager.set_period_str},4))
and
(mid({Participant_Move_in_Trans.Orig_Move_in_Date},5,2))
=
(mid({plan_manager.set_period_str},5,2))
then
1
else
0

{Participant_Move_in_Trans.Orig_Move_in_Date} is a string with data like &quot;20031019&quot;
{Plan_manager.set_period_str} is a string with data like &quot;20031019&quot;

My problem is that the report is only returning records where the year, month and date are exaclty the same. I need records with the same year and month not the date. Can someone please help me correct the formula?

Thanks in advance.

Kchaudhry

 

[lbass]

I don't really see a problem with your formula, but you might simplify it:

...and
left({Participant_Move_in_Trans.Orig_Move_in_Date}, 6)
=
left({plan_manager.set_period_str},6)
then
1
else
0

Is the string always formatted so that it has eight digits?

-LB

 

[kchaudhry]

Lbass,

Thanks for replying. Yes, the strings is always formatted to have eight digits. I am really confused, any other ideas as to why this is happening?


Kchaudhry

 

[lbass]

Have you laid out your details to see what the range of dates are that meet all your criteria? You have so many criteria in the formula that maybe you really only have a few dates that match. You might try commenting the date matching out of the formula and see how many detail records meet your criteria.

Also, another thought is to check the links between your two tables. I wonder if they are linked on the date field--in fact that might make sense since one table is for plan data. That would cause you to only get matching dates in your details.

-LB

 

[kchaudhry]

No my details sections is not looking for these date ranges. I have tried commenting out the dates portions and I get lots of results back.

I do have the tables linked on these date fields with a left outer join. This way I see the records with zeros in there too. If I change the linking to equal join I only see one record. This is the record which matches the year, month and day.

Do you suggest that I try putting the date matching formula in the record selection formula?

Kchaudhry

 

[lbass]

I think the table link is the problem. Even with a left join, you will only return records that either have exactly matching dates or you will get records in Table 1 that don't have a matching date in Table 2. You will never get the same month/year but different days. Try placing each date field in the details section of the report to verify this.

The question then becomes how the plan manager table was intended to be used. Maybe you should explain more generally what you are trying to accomplish.

-LB

 

[kchaudhry]

Lbass,

I have tried to place both the values in the details section. All the dates are showing up for the {Participant_Move_in_trans.Orig_Move_in_Date} but the {Plan_Manager.Set_Period_Str}is coming up blank. When I go on the blank field and browse data it shows me 20031019.
The plan manager table only has one row which inlcudes the date field.

Would it be easier if I share my SQL over here? I appreciate all your help.

Kchaudhry

 

[kchaudhry]

Can anyone think of any reason why this is happening?

Any help is appreciated.

Kchaudhry

 

[lyanch]

The minute you put any criteria on an outer join, Crystal tries to make it a regular join. You specifically have to state that the nulls count.

However, besides that, your join won't work at all because you want subsets of the data to be joins? What version of Crystal are you using? I think you may be wanting to do this as a subselect rather than as a join??? could you include your SQL here so we can see what you are trying to do?

Lisa

 

[lbass]

I don't think I can be of much more help. I think you need to explore what's in the plan manager table and determine how the table was meant to be used. For example, if these are plan figures, you might expect dates to be based on the beginnings and ends of periods, and maybe there would be more than one date field to set a range. Maybe you are getting many blanks because of the link on dates with Table 1 having dates that don't happen to fall at the beginning/end of month periods.

I would test the contents of the table by reversing the link to see what shows up or by using it alone in a separate report and adding all the fields to the details section so you can see what's in it. Then it may become clear what your next step might be.

-LB

 

[kchaudhry]

Hi Lisa,

I am using CR 8.5. Here is what my SQL looks like

SELECT DISTINCT
participant.&quot;name_last&quot;, participant.&quot;name_first&quot;, participant.&quot;id&quot;, participant.&quot;position&quot;, participant.&quot;title&quot;, participant.&quot;division&quot;, participant.&quot;rollup_hierarchy_id_parent1&quot;,
Participant_Move_in_Trans.&quot;Project&quot;, Participant_Move_in_Trans.&quot;Orig_Move_in_Date&quot;, Participant_Move_in_Trans.&quot;Term&quot;, Participant_Move_in_Trans.&quot;International_Indicator&quot;, Participant_Move_in_Trans.&quot;Trans_Category&quot;,
participant_1.&quot;name_last&quot;, participant_1.&quot;name_first&quot;, participant_1.&quot;position&quot;,
plan_manager.&quot;set_period_des&quot;, plan_manager.&quot;set_period_str&quot;
FROM
{ oj ((&quot;TestOakwood&quot;.&quot;dbo&quot;.&quot;participant&quot; participant INNER JOIN &quot;TestOakwood&quot;.&quot;dbo&quot;.&quot;participant&quot; participant_1 ON
participant.&quot;rollup_hierarchy_id_parent1&quot; = participant_1.&quot;id&quot
INNER JOIN &quot;TestOakwood&quot;.&quot;dbo&quot;.&quot;Participant_Move_in_Trans&quot; Participant_Move_in_Trans ON
participant.&quot;id&quot; = Participant_Move_in_Trans.&quot;Participant_Id&quot
LEFT OUTER JOIN &quot;TestOakwood&quot;.&quot;dbo&quot;.&quot;plan_manager&quot; plan_manager ON
Participant_Move_in_Trans.&quot;Orig_Move_in_Date&quot; = plan_manager.&quot;set_period_str&quot;}
WHERE
participant_1.&quot;position&quot; = 'RSM' AND
(participant.&quot;position&quot; = 'OCH AE' OR
participant.&quot;position&quot; = 'OAKWOOD AE')
ORDER BY
participant.&quot;rollup_hierarchy_id_parent1&quot; ASC,
participant.&quot;id&quot; ASC

In this report I have two groups. First Group is based on Manager ID and second one on all the employee Id under this manager.
I am trying to get a summary of the employees.
Thanks for your help.

Kchaudhry

 

[synapsevampire]

LB is correct, your joins are by a literal string, not the month/year.

So you'll only get hits for those that join identically.

Try posting example data and expected output (always) as I fear that you're heading towards a cartesian product with this.

-k

 

[kchaudhry]

synapsevampire,

Here is the formula that I am using:
if {Participant_Move_in_Trans.Term} = &quot;L&quot;
and
{Participant_Move_in_Trans.International_Indicator} = &quot;D&quot;
and
{Participant_Move_in_Trans.Project} < &quot;900&quot;
and
{Participant_Move_in_Trans.Trans_Category} = &quot;Local&quot;
and
{participant.position} = &quot;OAKWOOD AE&quot;
and
(left({Participant_Move_in_Trans.Orig_Move_in_Date}, 4))
=
(left({plan_manager.set_period_str},4))
and
(mid({Participant_Move_in_Trans.Orig_Move_in_Date},5,2))
=
(mid({plan_manager.set_period_str},5,2))
then
1
else
0

{Participant_Move_in_Trans.Orig_Move_in_Date} is a string with data like &quot;20031019&quot;
{Plan_manager.set_period_str} is a string with data like &quot;20031019&quot;

The desired end result is a summary of this field. So in other words I want to get 1 where these conditions match and then I am going to get a sum of all these values. I have also posted my SQL in my previous post.

I am very grateful for everybody's help.

Kchaudhry