Jump to content

bkana

Members
  • Posts

    9
  • Joined

  • Last visited

bkana's Achievements

Newbie

Newbie (1/7)

0

Reputation

  1. bkana

    SUMS and Totals

    Below is my query that returns information about certain people, courses they have taken and their credits for each course. Certain people have earned more than 5.0 credits total and some have not. I need to return only those people who have a total of 5.0 or more earned credits (CEUs). I have tried playing around with the SUM and GROUP BY statements, but have had no luck getting them to work. DECLARE @from datetime, @to datetimeSET @from = CONVERT(varchar(11), @MinDate, 1)SET @to = CONVERT(varchar(11), @Maxdate, 1)SELECT DISTINCTEducationCreditHistory.ContactID, EducationCreditHistory.CourseID, contactbase.FirstName, contactbase.LastName, Courses.CourseCode, Courses.StartDate, EdCategoryXref.Category, EdCategoryXref.CEUs, OpportunityBase.Name, Contact.ContactId AS Expr1FROM 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
  2. companyname Address boothnumber boothspace A 11 s st. A 1 100 B 22 b st. B 2 200 Sorry about the first post, I had to include "code" to properly display what I was talking about. Thanks Cronthenoob, I'll give that a try. I may have to post the actual table I am using so that it makes more sense. Actaully, there are hundreds of company names in this table that I need this info for. And, the company name might be listed more then twice for other information. One row could hold the address, one could hold the contact at the company, and an another could hold the boothnumber and boothspace. I need to pull the company name once, while also pulling the address, contact name and booth info. Hope that made sense.
  3. Hello all,I'm new to this particular forum, so bear with me.I have the following table called "Info":companyname Address boothnumber boothspace A 11 s st. A 1 100 B 22 b st. B 2 200 I use the following statment:Select companyname, Address, boothnumber, boothspaceFrom InfoWhen I run this it gives two results for the same company, the address and then the boothnumber and boothspaceHow do I combine the output so that I can have the companyname, address, boothnumber and boothspace all on the same line? In other words, how do I pull the company name once, it's address, boothnumber and bothspace when the data is in different rows?Thanks in advance? B
×
×
  • Create New...