Joining 2 tables by year and month values only !

[antonx]

Hello,

Is there a formula or method to join 2 tables each with a date (YMD) field by using only the year and month (ignoring the day) of each table? For example:

Table A Table B
Trans_date Plan_Date Color
2003-10-21 2003-09-01 Blue
2003-10-22 2003-10-01 Red
2003-10-23 2003-11-01 Green
2003-11-15 2003-12-01 Orange
2003-11-16
2003-11-17

This is how I want to result to look like:
Trans_date Color
2003-10-21 Red
2003-10-22 Red
2003-10-23 Red
2003-11-15 Green
2003-11-16 Green
2003-11-17 Green

Thank you in advance !
I really appreciate it.

 

[synapsevampire]

You might manually edit the SQL to do this, but it's Crystal and database version dependent, please provide technical information.

The ugly alternative would be to use a subreport and create formulas by which to join the data.

-k

 

[antonx]

Thank you for your response.

I am fairly new to Crystal Reports.

I working with CR 8.5 and I do not think I can manually edit the SQL in Crystal. I think I will have to do it the ugly alternative you mention. Can you elaborate on the formula?

Thank you.

 

[MJRBIM]

1. ) You need to create a MAIN REPORT that contains only TableA and has the following Formula.

Name : {@YearMonth}
Formula : Date (Year ({TableA.Trans_date}),Month ({TableA.Trans_date}) ,1)

2.) Then create a SUB-REPORT that contains only TableB and has the following formula.

Name : {@YearMonth}
Formula : Date (Year ({TableB.Trans_date}),Month ({TableB.Trans_date}) ,1)

3.) Then insert the SUB-REPORT into the DEATIL section of the MAIN REPORT, linking them on the common {@YearMonth} field.

4.) You'll need to play around with the border and format setting of the SUB-REPORT, but with a little tweaking it will return data as listed below. (NOTE : The SUB-REPORT is in RED.


Trans_date Color YearMonth
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
10/21/2003 Red 10/1/2003
10/22/2003 Red 10/1/2003
10/23/2003 Red 10/1/2003
11/15/2003 Green 11/1/2003
11/16/2003 Green 11/1/2003
11/17/2003 Green 11/1/2003
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

 

[bdreed35]

What kind of database are you using? Can you create a view or Stored Procedure? If so, you can pull off the join in one of the above.

~Brian