Production Data Per User (Query Issue)
-
I have a database with several tables: 1. UserAccounts - Stores information about each user 2. WholeUnits - Stores information about "whole" units (product) 3. UnitPassLogs - Stores information about Whole Units which have been marked "Passed" (who passed it, when it was passed, etc.) 4. FgiLogs - Stores information about finished goods (Whole Units) which have been processed (who processed it, when, etc.) 5. ScrapUnits - Stores information about Whole Units which have been scrapped (who scrapped it, when, etc.) 6. AWPUnits - Stores information about Whole Units which have been designated an "AWP" status 7. RPIPartsOrders - Stores information about orders for parts for Whole Units What I am trying to solve is generating a report which will display each user's production numbers for a given date. The problem, however, is that it results in highly inaccurate numbers. For example, John Doe has 6 records in the "UnitPassLogs" table, but the query I'm about to provide displays 164 for "QtyPassed" but then again, some users' data displays correctly. The query is as follows:
USE PCSFFDEV
SELECT u.FullName,
COUNT(wu.UnitId) AS QtyReceived,
COUNT(upl.PassId) AS QtyPassed,
COUNT(fgi.FgiLogId) AS FgiQty,
COUNT(su.ScrapUnitId) AS QtyScrapped,
(
SELECT COUNT(*)
FROM AWPUnits awp INNER JOIN
WholeUnits awu ON awu.UnitId = awp.WholeUnitId
WHERE awp.TechUserId = u.UserAccountId
AND CONVERT(DATE, awp.DateLogged, 101) = '2013-01-28'
AND NOT EXISTS
(
SELECT *
FROM RPIPartsOrders ro
WHERE ro.WholeUnitId = awu.UnitId
)
) AS QtyToAwp
FROM UserAccounts u LEFT OUTER JOIN
WholeUnits wu ON wu.ReceivedBy = u.UserAccountId AND CONVERT(DATE, wu.DateReceived, 101) = '2013-01-28' LEFT OUTER JOIN
UnitPassLogs upl ON upl.PassedBy = u.UserAccountId AND CONVERT(DATE, upl.DatePassed, 101) = '2013-01-28' LEFT OUTER JOIN
FgiLogs fgi ON fgi.ScannedBy = u.UserAccountId AND CONVERT(DATE, fgi.DateScanned, 101) = '2013-01-28' LEFT OUTER JOIN
ScrapUnits su ON su.ScrappedBy = u.UserAccountId AND CONVERT(DATE, su.DateScrapped, 101) = '2013-01-28'
GROUP BY u.UserAccountId, u.FullNameAnother issue, unless it's the only way to do it, is with the "QtyToAwp" subquery. A subquery is the ONLY way I could find to get the correct "QtyToAwp" value because if I joined it in the main query I would get massive numbers (e.g. 20,000+) and queries that would run for 10+ seconds. I must be doing something wrong but I can't, for the life of me, figure i