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 |
|
muzzettemm
Posting Yak Master
212 Posts |
 Posted - 2009-07-14 : 14:52:59
|
Hi I have a problem. I have three tables one is the [Student Activity], the [Student] and the [Services] I had to add the services because the client wants to see all the services even if they dont provide them to the student. The [state services] are already in the [Student Acitivity] table and for the dates 10/01/2008 and 06/15/2009. the client wanted to see all of the services whether they provide it or not, even if there are no [Total Time] hours for it. So I created a table called Services with the extra [State Services] as a filler, now its summing all of the [state services] so my totals are too high. How do I make it so it shows the true totals, because the purpose of the Services table was just to show the rest of the [state services] whether the student did them or not. Does that make sense? Can anyone help plsSELECT CASE WHEN [serviceId] = [serviceId] THEN [Total Time] END AS TotalHours, Services.name, Student.[Student ID], Student.schoolID, Student.[Studentschool Id], [Student Activity].[Activity Date], [Student Activity].[State Category], [Student Activity].[State Services], [Student Activity].[Total Time]FROM [Student Activity] LEFT OUTER JOIN Student ON [Student Activity].[Student ID] = Student.[Student ID] CROSS JOIN ServicesWHERE (Student.schoolID IS NOT NULL) AND (Student.[Studentschool Id] IS NOT NULL) AND ([Student Activity].[Activity Date] BETWEEN @Beginning_ActivityDate AND @End_ActivityDate)ORDER BY [Student Activity].[State Category] |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-14 : 14:59:37
|
What is this?WHEN [serviceId] = [serviceId]it is always true!
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
muzzettemm
Posting Yak Master
212 Posts |
Posted - 2009-07-14 : 15:09:19
|
| oops I here SELECT Services.name, Student.[Student ID], Student.schoolID, Student.[Studentschool Id], [Student Activity].[Activity Date], [Student Activity].[State Category], [Student Activity].[State Services], [Student Activity].[Total Time]FROM [Student Activity] LEFT OUTER JOIN Student ON [Student Activity].[Student ID] = Student.[Student ID] CROSS JOIN ServicesWHERE (Student.schoolID IS NOT NULL) AND (Student.[Studentschool Id] IS NOT NULL) AND ([Student Activity].[Activity Date] BETWEEN @Beginning_ActivityDate AND @End_ActivityDate)ORDER BY [Student Activity].[State Category] |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-14 : 15:20:55
|
I am not sure about your problem.First shot:Don't use cross join.select blablafrom Serviceleft join [Student Activity] on Service.name = [Student Activity].[State Services]left join Student on [Student Activity].[Student ID] = Student.[Student ID]
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-15 : 14:22:12
|
| you need to give some data from your tables if you need solution to this as we cant see your tables, nor does we know how they are related. |
|
|
|
|
|
|
|
|
totals are too high