Multiple field mappings?

24 Views

Post Author: cr_nub

CA Forum: Data Connectivity and SQL

I am new to CR and SQL so I hope I don't sounds too noobish with this problem.

I am trying to pull a dataset that will yield 3 sets of data to be used for running totals using CR XIWhile there are other fields in the tables, the ones shown are the ones I need to use to select my data.

Table 1 - INCIDENTS Table 2 - REPS Table 3 - HISTORY_REPID_AUTHOR ID ID_OWNERID_CLOSED_BY INCIDENT_NUMBERNUMBER DT_CREATEDDT_CREATEDDT_CLOSED

I want to run a report with 3 seperate running totals. Each RT is set to reset on change of group REPS.ID

Opened: Distinct count of incidents.number where incidents.dt_created is between two dates and reps.id is in a list{INCIDENTS.DT_CREATED} in DateTime (2007, 08, 01, 00, 00, 00) to DateTime (2007, 08, 31, 23, 59, 59) and{INCIDENTS.ID_AUTHOR} in [135, 382, 383, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89]

Closed: Distinct count of incidents.number where incidents.dt_closed is between two dates and reps.id is in a list{INCIDENTS.DT_CLOSED} in DateTime (2007, 08, 01, 00, 00, 00) to DateTime (2007, 08, 31, 23, 59, 59) and{INCIDENTS.ID_CLOSED_BY} in [135, 382, 383, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89]

Modified: Distinct count of history_rep.incident_number where history_rep.dt_created is between two dates and history_rep.id_owner is in a list{HISTORY_REP.DT_CREATED} in DateTime (2007, 08, 01, 00, 00, 00) to DateTime (2007, 08, 31, 23, 59, 59) and{HISTORY_REP.ID_OWNER} in [135, 382, 383, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89]

My problem is how to link my fields so that my resulting data can yield data that meets all of the criteria:INCIDENTS.ID_AUTHOR = REPS.ID orINCIDENTS.ID_CLOSED_BY = REPS.ID orHISTORY_REP.ID_OWNER = REPS.ID

I also need an inner join between INCIDENTS.NUMBER and HISTORY_REP.DENT_NUMBER since not all tickets have history entries.

Inner joins only yield results which match all of the linked criteria which does not help.

I tried adding an additional alias for the INCIDENTS table and used INCIDENTS to link the ID_AUTHOR field and then the INCIDENTS_1 table to link the ID_CLOSED_BY field.This worked to give me the first 2 running totals (with increased record searching), but adding the third set resulted in way to many records being read.

Is there a way to do this purely through SQL so I can bypass linking the tables? Again, I'm new to SQL and still waiting for boss to get me training.