Displaying certain totals - SQL Server Forums

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.

All Forums

Development Tools

Reporting Services Development

Displaying certain totals

Author

Topic

bkana
Starting Member

18 Posts

Posted - 2007-02-07 : 07:56:18

I have a report that displays a person's transcript record including all the credits they have earned, the classes taken and a sum of their total credits earned. Each perosn has there own page. How do I display people who have only earned 5.0 or more credits. I am at a loss as to where to do this. I assume it would be better to do it in the SQL statement, but how?

ARC
Starting Member

10 Posts

Posted - 2007-02-07 : 08:02:03

select from table where credits > 5

bkana
Starting Member

18 Posts

Posted - 2007-02-07 : 08:20:11

What I actually need is the people who have a TOTAL of 5.0 or more credits. I have a SUM statement in one of the cells in my table that adds up thier credits, some have less then 5.0 and some have more. I need to pull only those who have earned 5.0 or more in their total. Is there a way to display the reports of people who only have 5.0 or more using SRS or do I need to modify my SQL statement, and if so, how?

jhermiz

3564 Posts

Posted - 2007-02-07 : 09:09:20

quote:
Originally posted by bkana

What I actually need is the people who have a TOTAL of 5.0 or more credits. I have a SUM statement in one of the cells in my table that adds up thier credits, some have less then 5.0 and some have more. I need to pull only those who have earned 5.0 or more in their total. Is there a way to display the reports of people who only have 5.0 or more using SRS or do I need to modify my SQL statement, and if so, how?

Hi bkana,

Easiest way to do this is via the dataset (meaning from your Query) and not directly in RS.

I wrote you up an example:



CREATE TABLE #test1
(
 name1 varchar(50),
 credits int
)

INSERT INTO #test1(name1, credits)
VALUES ('Jon', 1)

INSERT INTO #test1(name1, credits)
VALUES ('Jon', 1)

INSERT INTO #test1(name1, credits)
VALUES ('Jon', 1)

INSERT INTO #test1(name1, credits)
VALUES ('Joe', 5)

INSERT INTO #test1(name1, credits)
VALUES ('Joe', 3)

INSERT INTO #test1(name1, credits)
VALUES ('Jon', 1)


SELECT name1, SUM(credits)  FROM #test1 
GROUP BY name1 HAVING(SUM(credits)) > 5


drop table #test1

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

bkana
Starting Member

18 Posts

Posted - 2007-02-07 : 09:22:24

I think I understand your example, but I think my query is a little more complex. I'm assuming your just creating a table that hols only the relevant information correct and then running a query against it? Here is my query below, how do I incorporate your selct statement with what I have given:

DECLARE @from datetime, @to datetime
SET @from = CONVERT(varchar(11), @MinDate, 1)
SET @to = CONVERT(varchar(11), @Maxdate, 1)
SELECT DISTINCT
EducationCreditHistory.ContactID, EducationCreditHistory.CourseID, contactbase.FirstName, contactbase.LastName,
Courses.CourseCode, Courses.CourseName, Courses.StartDate, EdCategoryXref.Category, CategoryTable.Description,
EdCategoryXref.CEUs, OpportunityBase.Name, Contact.ContactId AS Expr1, Contact.Address1_Name, Contact.Address1_Line1,
Contact.Address1_Line2, Contact.Address1_Line3, Contact.Address1_City, Contact.Address1_StateOrProvince, Contact.Address1_Country,
Contact.Address1_PostalCode
FROM EducationCreditHistory INNER JOIN
contactbase ON EducationCreditHistory.ContactID = contactbase.ContactId INNER JOIN
Courses ON EducationCreditHistory.CourseID = Courses.CourseID INNER JOIN
EdCategoryXref ON EducationCreditHistory.TransactionID = EdCategoryXref.TransactionID INNER JOIN
CategoryTable ON EdCategoryXref.Category = CategoryTable.Category INNER JOIN
OpportunityBase ON EducationCreditHistory.ContactID = OpportunityBase.ContactId INNER JOIN
Contact ON contactbase.ContactId = Contact.ContactId
WHERE (CONVERT(varchar(11), Courses.StartDate, 1) >= @from) AND (CONVERT(varchar(11), Courses.StartDate, 1) <= @to) AND (OpportunityBase.Name = N'AAAFEE05 - CE REGISTRY FEE')
ORDER BY contactbase.LastName, Courses.CourseCode

I need to retain the date parameters and the where clause.

jhermiz

3564 Posts

Posted - 2007-02-07 : 11:51:47

quote:
Originally posted by bkana

I think I understand your example, but I think my query is a little more complex. I'm assuming your just creating a table that hols only the relevant information correct and then running a query against it? Here is my query below, how do I incorporate your selct statement with what I have given:

DECLARE @from datetime, @to datetime
SET @from = CONVERT(varchar(11), @MinDate, 1)
SET @to = CONVERT(varchar(11), @Maxdate, 1)
SELECT DISTINCT
EducationCreditHistory.ContactID, EducationCreditHistory.CourseID, contactbase.FirstName, contactbase.LastName,
Courses.CourseCode, Courses.CourseName, Courses.StartDate, EdCategoryXref.Category, CategoryTable.Description,
EdCategoryXref.CEUs, OpportunityBase.Name, Contact.ContactId AS Expr1, Contact.Address1_Name, Contact.Address1_Line1,
Contact.Address1_Line2, Contact.Address1_Line3, Contact.Address1_City, Contact.Address1_StateOrProvince, Contact.Address1_Country,
Contact.Address1_PostalCode
FROM EducationCreditHistory INNER JOIN
contactbase ON EducationCreditHistory.ContactID = contactbase.ContactId INNER JOIN
Courses ON EducationCreditHistory.CourseID = Courses.CourseID INNER JOIN
EdCategoryXref ON EducationCreditHistory.TransactionID = EdCategoryXref.TransactionID INNER JOIN
CategoryTable ON EdCategoryXref.Category = CategoryTable.Category INNER JOIN
OpportunityBase ON EducationCreditHistory.ContactID = OpportunityBase.ContactId INNER JOIN
Contact ON contactbase.ContactId = Contact.ContactId
WHERE (CONVERT(varchar(11), Courses.StartDate, 1) >= @from) AND (CONVERT(varchar(11), Courses.StartDate, 1) <= @to) AND (OpportunityBase.Name = N'AAAFEE05 - CE REGISTRY FEE')
ORDER BY contactbase.LastName, Courses.CourseCode

I need to retain the date parameters and the where clause.

Where's peter :)

bkana, you need to rewrite that entire SQL Statement correctly. You are throwing in the DISTINCT keyword to make your returned result look right, its going to bite you in the ( Y ) if you know what I mean.

First try indenting so we can actually read it and use [code] tags to help us read the code. Secondly you have to consider getting the sums from the relevant information then joining this relevant information into another sql statement by joining the key information.

You are doing too much at once and its making it more difficult for you. You're also not making use of the GROUP BY clause and depending too much on the DISTINCT keyword. Even if my sample doesn't contain additional information like you stated you could do as I said and query the RELEVANT info first. Then take that relevant info (the SQL statement) and join it to other tables or the same table to get further information (such as the addressing).

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

bkana
Starting Member

18 Posts

Posted - 2007-02-07 : 12:19:35

Thank you for the constuctive input jhermiz, my mistake. I have simplified my query (as suggested) to include only the information that I need. If I remove the DISTINCT statement then I end up with a lot of double entries, which is probably why you suggested to use the GROUP BY clause. Please excuse my ignorance on the subject - but could really use your help.

Would I drop the DISTINCT clause and GROUP BY the contactID, since it is the primary key?
The only items I need from the query is the name, courses, corse dates, CEUs and of course some kind of total for the number of CEUs earned and then only the people who have earned a total of 5.0 or more

DECLARE @from datetime, @to datetime
SET @from = CONVERT(varchar(11), @MinDate, 1)
SET @to = CONVERT(varchar(11), @Maxdate, 1)
SELECT DISTINCT
EducationCreditHistory.ContactID, EducationCreditHistory.CourseID, contactbase.FirstName, contactbase.LastName,
Courses.CourseCode, Courses.StartDate, EdCategoryXref.Category, EdCategoryXref.CEUs, OpportunityBase.Name, Contact.ContactId AS Expr1

FROM EducationCreditHistory INNER JOIN
contactbase ON EducationCreditHistory.ContactID = contactbase.ContactId INNER JOIN
Courses ON EducationCreditHistory.CourseID = Courses.CourseID INNER JOIN
EdCategoryXref ON EducationCreditHistory.TransactionID = EdCategoryXref.TransactionID INNER JOIN
OpportunityBase ON EducationCreditHistory.ContactID = OpportunityBase.ContactId INNER JOIN
Contact ON contactbase.ContactId = Contact.ContactId
WHERE (CONVERT(varchar(11), Courses.StartDate, 1) >= @from) AND (CONVERT(varchar(11), Courses.StartDate, 1) <= @to) AND (OpportunityBase.Name = N'AAAFEE05 - CE REGISTRY FEE')
ORDER BY contactbase.LastName, Courses.CourseCode

bkana
Starting Member

18 Posts

Posted - 2007-02-07 : 14:10:52

I'm going to repost this issue with updated data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources