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_1
Posting Yak Master
129 Posts |
 Posted - 2010-09-29 : 06:27:07
|
This query works without doing the condition involving if TotalPoints >= pta.TargetPoints. But why does it not show any results when the condition is included, even though the condition logically is true?quote:
SELECT a.AuditID, c.CompanyName, u.EmailAddress, a.AuditType, a.DateLogged, a.[Resource], pt.[PartnerTypeName], (SELECT SUM(ua.TotalPoints) AS [TotalPoints] FROM UserAttributes ua INNER JOIN Users u ON u.UserID = ua.UserID WHERE u.CompanyID = c.CompanyID) AS [TotalPoints] FROM Audit a LEFT OUTER JOIN Company c ON a.CompanyID = c.CompanyID 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 INNER JOIN PartnerTypeAttributes pta ON pta.PartnerTypeID = c.PartnerTypeID WHERE a.DateLogged BETWEEN '03/29/2010' AND '10/10/2010' AND TotalPoints >= pta.TargetPoints /*this here*/ ORDER BY a.DateLogged ASC
the above query does not show any results but when AND TotalPoints >= pta.TargetPoints is taken out, the results show (and yes, TotalPoints is more than the value of 20)What am I trying to achieve?basically, each user has points associated with them. each user is associated with a company.each company has "targets" to hit. the total points for the company is just a sum of the user points for that companyI want to get the results based on if the company has reached their targets. |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-09-29 : 06:57:14
|
| [code]SELECT * FROM( SELECT a.AuditID, c.CompanyName, u.EmailAddress, a.AuditType, a.DateLogged, a.[Resource], pt.[PartnerTypeName], (SELECT SUM(ua.TotalPoints) AS [TotalPoints] FROM UserAttributes ua INNER JOIN Users u ON u.UserID = ua.UserID WHERE u.CompanyID = c.CompanyID) AS [TotalPoints], pta.TargetPoints FROM Audit a LEFT OUTER JOIN Company c ON a.CompanyID = c.CompanyID 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 INNER JOIN PartnerTypeAttributes pta ON pta.PartnerTypeID = c.PartnerTypeID WHERE a.DateLogged BETWEEN '03/29/2010' AND '10/10/2010' /*this here*/ )T WHERE TotalPoints >= TargetPoints ORDER BY T.DateLogged ASC [/code] |
 |
|
|
tech_1
Posting Yak Master
129 Posts |
Posted - 2010-09-29 : 07:28:34
|
| Thanks. I was almost right! |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-09-29 : 07:34:45
|
| You were right???PBUH |
|
|
|
|
|
|
|
|
Why does this not display results? [calc]