I have been given a query to create a report from. This query has subqueries in the select and where sections.
How do I implement this into a report?
I will post the query below with the subqueries separated by a few blank lines to make them easier to see. I was going to start linking tables needed for the report and ran into a subquery in the table linking. This confused me.
QUERY:
SELECT
H.swLastName AS 'Repair
Manager',
B.swPartNumber AS 'Part
#',
B.swName AS
'Description',
B.fzShortName AS 'Short
Name',
A.fzSerialNumber AS
'Serial #',
A.fzRepairOrderId AS
'Repair Order #',
CONVERT(char(10),A.fzRepairReceiptDate,101) AS 'Receipt Date',
J.fzDMTProblem AS 'DMT
Problem',
A.fzRepairDMTProblemConfirm AS 'DMT Problem Confirmed',
M.swSiteName AS 'API',
A.fzBouncedRepairOrderId
AS 'Previous Repair Order #',
CONVERT(char(10),C.fzDateClosed,101) AS 'Previous RO Closed Date',
K.fzDMTProblem AS
'Previous DMT Problem',
C.fzRepairDMTProblemConfirm AS 'Previous DMT Problem Confirmed',
N.swSiteName AS 'Previous
API',
DATEDIFF(dd
,CONVERT(char(10),C.fzDateClosed,101),
CONVERT(char(10),A.fzRepairReceiptDate,101)) AS 'Days Since Previous Repair',
(SELECT S1d.swFirstName +
' ' + S1d.swLastName
FROM
fz_REPAIR_PROCESS_LOG S1a, fz_REPAIR_PROCESS S1b, fz_REPAIR_SUBPROCESS S1c,
SW_PERSON S1d
WHERE
S1a.fzRepairOrderId = C.fzRepairOrderId
AND
S1a.fzProcessAreaId = S1b.fzRepairProcessId
AND
S1a.fzSubProcessId = S1c.fzRepairSubProcessId
AND S1b.fzRepairProcess =
'REPAIR'
AND
S1c.fzRepairSubProcess = 'REPAIR TECH'
AND
S1a.fzProcessDoneByPersonId = S1d.swPersonId
AND
S1a.fzProcessLogId = (
SELECT
MAX(S2a.fzProcessLogId)
FROM fz_REPAIR_PROCESS_LOG S2a, fz_REPAIR_PROCESS S2b,
fz_REPAIR_SUBPROCESS S2c
WHERE S2a.fzRepairOrderId = C.fzRepairOrderId
AND
S2a.fzProcessAreaId = S2b.fzRepairProcessId
AND S2a.fzSubProcessId = S2c.fzRepairSubProcessId
AND S2b.fzRepairProcess = 'REPAIR'
AND S2c.fzRepairSubProcess = 'REPAIR TECH'
)
) AS 'Last Repair
Technician',
D.fzCategory AS 'Bouncer
Category',
E.fzReason AS 'Bouncer
Reason',
CASE
WHEN E.fzControllable = 'N' then 'No'
WHEN
E.fzControllable = 'Y' then 'Yes'
ELSE
E.fzControllable
END AS 'Controllable?',
G.swFirstName + ' ' +
G.swLastName AS 'Bouncer Reason Entered By'
FROM
fz_REPAIR_ORDER A, --
Bouncer RO
SW_PROD_RELEASE B, --
Bouncer Part Data
fz_REPAIR_ORDER C, --
Bounced RO
fz_REPAIR_BOUNCER_CATEGORY D, -- Bouncer Category
fz_REPAIR_BOUNCER_REASON
E, -- Bouncer Reason
SW_AUDIT_TRAIL F, -- To
Get Bouncer Reason Person
SW_PERSON G, -- Bouncer
Reason Person
SW_PERSON H, --
Responsible Manager,
fz_REPAIR_DMT_PROBLEM J,
-- Bouncer DMT Problem
fz_REPAIR_DMT_PROBLEM K,
-- Bounced DMT Problem
SW_SITE M, -- Current RO
API
SW_SITE N -- Previous RO
API
WHERE
A.swProdReleaseId =
B.swProdReleaseId
AND
A.fzBouncedRepairOrderId = C.fzRepairOrderId
AND
A.fzRepairBouncerCategoryId = D.fzRepairBouncerCategoryId
AND
A.fzRepairBouncerReasonId = E.fzRepairBouncerReasonId
AND (A.fzRepairOrderId =
F.swObjectId AND
F.swColumnName='fzRepairBouncerReasonId' AND F.swAuditId = (
SELECT
MAX(swAuditId) FROM SW_AUDIT_TRAIL WHERE swObjectId = A.fzRepairOrderId AND
swColumnName='fzRepairBouncerReasonId')
)
AND F.swChangedBy =
G.swLogin
AND A.fzRespRepairMgrId =
H.swPersonId
AND
A.fzRepairDMTProblemId *= J.fzRepairDMTProblemId
AND
C.fzRepairDMTProblemId *= K.fzRepairDMTProblemId
AND A.swSiteId *=
M.swSiteId
AND C.swSiteId *=
N.swSiteId
AND A.fzRepairReceiptDate
>= '31-DEC-12' AND A.fzRepairReceiptDate < '06-JAN-13'
AND A.fzBouncer = 1
ORDER BY
H.swLastName,
DATEDIFF(dd
,CONVERT(char(10),C.fzDateClosed,101),
CONVERT(char(10),A.fzRepairReceiptDate,101))
IP Logged
