Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tech
Starting Member
32 Posts |
 Posted - 2011-04-06 : 16:58:08
|
Hi.I have this query running for another type of "report" but because of new additions to the system, its not quite doing what I want.background:There is an audit table which has the following:AuditType (int)AuditDate (datetime)AuditID (int)Resource (nvarchar)UserID (int, NOT nullable)CompanyID (int, nullable)DistributorID (int, nullable) **New column**Now, the old query works just fine. bad SQL but works.quote:
SELECT * FROM (SELECT a.AuditID, u.Firstname, u.Lastname, c.PointOfContact, c.CompanyName, d.DistributorName, u.EmailAddress, a.AuditType, a.DateLogged, a.[Resource], pt.[PartnerTypeName], ua.TotalPoints, (SELECT SUM(ua.TotalPoints) AS UserTotalPoints FROM UserAttributes ua INNER JOIN Users u ON u.UserID = ua.UserID INNER JOIN Company c1 ON c1.CompanyID = u.CompanyID WHERE c1.CompanyID = c.CompanyID) AS [CompanyTotalPoints], pta.TargetPoints FROM Audit a LEFT OUTER JOIN Company c ON a.CompanyID = c.CompanyID LEFT OUTER JOIN DistributorCompanyLink dcl ON dcl.CompanyID = c.CompanyID LEFT OUTER JOIN Distributor d ON d.DistributorID = dcl.DistributorID LEFT OUTER JOIN PartnerType pt ON pt.PartnerTypeID = c.PartnerTypeID LEFT OUTER JOIN Users u ON u.UserID = a.UserID LEFT OUTER JOIN UserAttributes ua ON ua.UserID = u.UserID LEFT OUTER JOIN PartnerTypeAttributes pta ON pta.PartnerTypeID = c.PartnerTypeID WHERE a.DateLogged BETWEEN '03/01/2011' AND '05/01/2011' ) T ORDER BY T.DateLogged ASC
and yes, gets the correct information including the distributor name.Distributor info:This is a new segment. We have the following:Table: Distributor.Fields: ID (int), DistributorName (nvarchar)Table: DistributorCompanyLinkFields: ID (int), CompanyID (FK int), DistributorID (int FK)so a distributor has many companies.Finally, this segment is somewhat new - a scoring system for distributors to monitor the totals of each distributor. So each distributor has x companies and x companies has y users which have scores. This is calculated just fine with the following:quote:
SELECT c.CompanyID, c.CompanyName, ISNULL(SUM(ua.TotalPoints), 0) AS CompanyPoints, ROW_NUMBER() OVER (ORDER BY c.CompanyID ASC) AS RowNumber FROM Company c LEFT OUTER JOIN Users u ON u.CompanyID = c.CompanyID LEFT OUTER JOIN UserAttributes ua ON ua.UserID = u.UserID GROUP BY c.CompanyID, c.CompanyName
PROBLEMI want to some how merge the distributor scores in the main audit results. but not sure how. Current query I have doesnt quite do it as it shows the SUM of all the scores for the distributors irrespective of the results:quote:
SELECT * FROM (SELECT a.AuditID, d.DistributorName, a.AuditType, a.DateLogged, a.[Resource], (SELECT ISNULL(SUM(ua.TotalPoints), 0) AS TotalPoints FROM Company c1 LEFT OUTER JOIN Users u ON u.CompanyID = c1.CompanyID LEFT OUTER JOIN UserAttributes ua ON ua.UserID = u.UserID INNER JOIN DistributorCompanyLink dc1 ON dc1.CompanyID = c1.CompanyID INNER JOIN Distributor d1 ON d1.DistributorID = dc1.DistributorID ) AS [DistributorTotalPoints] FROM Audit a LEFT OUTER JOIN Distributor d ON d.DistributorID = a.DistributorID WHERE a.DateLogged BETWEEN '03/15/2011' AND '06/01/2011' ) T ORDER BY T.DateLogged ASC
I know. complicated. its how they want it. |
|
|
tech
Starting Member
32 Posts |
Posted - 2011-04-06 : 17:54:18
|
| Just to try and make it clearer.They want a list of "audits" for a distributor (which is fine. Match Audit.DistributorID On Distributor.DistributorID) but at the same time, they want a calc of the total score for the companies belonging to that company.Tables available:AuditUsersUserAttributesCompanyDistributorsDistributorCompanyLink |
 |
|
|
tech
Starting Member
32 Posts |
Posted - 2011-04-07 : 11:16:15
|
| stupid me - turned out to be just inner joins and refreshed my mind about what I was meant to be doing! |
|
|
|
|
|
|
|
|
Complicated query