ajstickland
MIS
Hi
One of our programmers has recently left the company. I have been asked to update a report he did. However I have found that he extracted the information by creating a program that then fed the information into Crystal Reports XI as a temporary database linking to other tabes within the actual database the report is run from.
The first part of his program extracts the relevant contract numbers (grouped by):
SELECT ch.Contract_No
FROM Contract_Headers ch
>>>INNER JOIN Clients c ON c.Client_Ref = ch.Link_to_Client
INNER JOIN Time_Periods tp ON tp.TM_Code = ch.Duration
WHERE ch.Status = 'L' AND c.Area_Code IN ('1')
The second part of his program exports the engineers name and the amount of hours they have worked on this contract:
SELECT pmai.PMAI_Link_to_Contract, pmai.PMAI_Week_No,
pmai.PMAI_Scheduled_Est_Time,CONVERT(DATETIME,CASE UPPER
(tp.TM_Type)
WHEN 'M' THEN DATEADD(mm, tp.TM_Number,
ch.Last_Renewal_Date)
WHEN 'Y' THEN DATEADD(yy, tp.TM_Number,
ch.Last_Renewal_Date)
ELSE DATEADD(yy, 1, ch.Last_Renewal_Date)END) AS
Contract_End_Date, 'PPM' AS Service,
ca.CA_Area_Description, 'FULL' AS Support_Group_Code,
ISNULL(clc.Co_Name, 'Unknown') AS Contract_Name
FROM PM_Adv_Item_Tasks pmai
INNER JOIN Contract_Items ci ON ci.CI_ID =
pmai.PMAI_Link_to_Item
INNER JOIN Equipment e ON e.Equipment_Code =
ci.Equipment_Code
LEFT OUTER JOIN LU_Model m on m.Link_to_Make = e.Make
and m.Model = e.Model
INNER JOIN Contract_Headers ch ON ch.Contract_No =
pmai.PMAI_Link_to_Contract
INNER JOIN Time_Periods tp ON tp.TM_Code = ch.Duration
LEFT OUTER JOIN Clients cl ON cl.Client_Ref =
e.Link_to_Client
>>>LEFT OUTER JOIN Clients clc ON clc.Client_Ref =
ch.Link_to_Client
LEFT OUTER JOIN Company_Areas ca ON ca.CA_Area_Code =
cl.Area_Code
LEFT OUTER JOIN Support_Groups esg ON
esg.Support_Group_Code = e.Specialist_Support_Group
WHERE ch.Contract_No IN ('1860-2')
AND ISNULL(pmai.PMAI_Scheduled_Est_Time, '') <> ''
AND pmai.PMAI_Scheduled_Est_Time <> '00:00'
AND ch.Contract_No <> 'EQUIPH-1'
AND ch.Status = 'L'
AND ISNULL(esg.SubContractor, 0) = 0
AND cl.Area_Code IN ('1') OPTION (MAXDOP 1)
The problem I am experiencing is that there are two different join statements (See >>>) on the same field in the same table.
I am a newby to Crystal but as far as I can tell you cannot have multiple joins. I am guessing that you would need to have 2 seperate reports to:
1)Export the relevant contract numbers
2)Use this report and extract the contract engineers and time worked information
Is this correct and how do I do it? Also can anyone point me in the direction of a simple report that does this as well?
One of our programmers has recently left the company. I have been asked to update a report he did. However I have found that he extracted the information by creating a program that then fed the information into Crystal Reports XI as a temporary database linking to other tabes within the actual database the report is run from.
The first part of his program extracts the relevant contract numbers (grouped by):
SELECT ch.Contract_No
FROM Contract_Headers ch
>>>INNER JOIN Clients c ON c.Client_Ref = ch.Link_to_Client
INNER JOIN Time_Periods tp ON tp.TM_Code = ch.Duration
WHERE ch.Status = 'L' AND c.Area_Code IN ('1')
The second part of his program exports the engineers name and the amount of hours they have worked on this contract:
SELECT pmai.PMAI_Link_to_Contract, pmai.PMAI_Week_No,
pmai.PMAI_Scheduled_Est_Time,CONVERT(DATETIME,CASE UPPER
(tp.TM_Type)
WHEN 'M' THEN DATEADD(mm, tp.TM_Number,
ch.Last_Renewal_Date)
WHEN 'Y' THEN DATEADD(yy, tp.TM_Number,
ch.Last_Renewal_Date)
ELSE DATEADD(yy, 1, ch.Last_Renewal_Date)END) AS
Contract_End_Date, 'PPM' AS Service,
ca.CA_Area_Description, 'FULL' AS Support_Group_Code,
ISNULL(clc.Co_Name, 'Unknown') AS Contract_Name
FROM PM_Adv_Item_Tasks pmai
INNER JOIN Contract_Items ci ON ci.CI_ID =
pmai.PMAI_Link_to_Item
INNER JOIN Equipment e ON e.Equipment_Code =
ci.Equipment_Code
LEFT OUTER JOIN LU_Model m on m.Link_to_Make = e.Make
and m.Model = e.Model
INNER JOIN Contract_Headers ch ON ch.Contract_No =
pmai.PMAI_Link_to_Contract
INNER JOIN Time_Periods tp ON tp.TM_Code = ch.Duration
LEFT OUTER JOIN Clients cl ON cl.Client_Ref =
e.Link_to_Client
>>>LEFT OUTER JOIN Clients clc ON clc.Client_Ref =
ch.Link_to_Client
LEFT OUTER JOIN Company_Areas ca ON ca.CA_Area_Code =
cl.Area_Code
LEFT OUTER JOIN Support_Groups esg ON
esg.Support_Group_Code = e.Specialist_Support_Group
WHERE ch.Contract_No IN ('1860-2')
AND ISNULL(pmai.PMAI_Scheduled_Est_Time, '') <> ''
AND pmai.PMAI_Scheduled_Est_Time <> '00:00'
AND ch.Contract_No <> 'EQUIPH-1'
AND ch.Status = 'L'
AND ISNULL(esg.SubContractor, 0) = 0
AND cl.Area_Code IN ('1') OPTION (MAXDOP 1)
The problem I am experiencing is that there are two different join statements (See >>>) on the same field in the same table.
I am a newby to Crystal but as far as I can tell you cannot have multiple joins. I am guessing that you would need to have 2 seperate reports to:
1)Export the relevant contract numbers
2)Use this report and extract the contract engineers and time worked information
Is this correct and how do I do it? Also can anyone point me in the direction of a simple report that does this as well?