June 10, 2009 at 8:14 am
Thanks to the help of the nice people here I use:
CASE
When Max(vtbl_Job_Descript_Master.START) <> 0 then YEAR(dateadd(mm, -3, Convert(smalldatetime,Str(Max(vtbl_Job_Descript_Master.START)))))
When ISNULL(Max(vtbl_TCH_First_Labor.First_Week_Ending), 0) <> 0 THEN YEAR(dateadd(mm, -3, Convert(smalldatetime,Str(Max(vtbl_TCH_First_Labor.First_Week_Ending)))))
When Max(vtbl_Job_Descript_Master.Closed) <> 0 Then YEAR(dateadd(mm, -3, Convert(smalldatetime,Str(Max(vtbl_Job_Descript_Master.Closed)))))
ELSE 0
END
AS Job_Start_Year
However I would like to use 'Job_Start_Year as part of the Group by section.
If I group by Start, Closed, etc. I get too many records. If I use MAX on each of them I get too few.
Any ideas?
Thank you for your help,
June 10, 2009 at 10:12 am
The easiest method would be to place your case statements in a derived table and then perform a grouping of the results.
select year, max(yada)...
from
( yourqueryhere
) myDerivedTable
GROUP BY year
June 10, 2009 at 11:00 am
Actually I'm a little embarrased to admit it.
I took a second look at the query and I am not adding (or finding the min, max, avg, etc...) anything.
So, feeling a bit foolish I added Distinct to the Select clause and removed all Group by references.
I am way to new at this still.
Thank you,
June 10, 2009 at 1:45 pm
Ken (6/10/2009)
Actually I'm a little embarrased to admit it.I took a second look at the query and I am not adding (or finding the min, max, avg, etc...) anything.
So, feeling a bit foolish I added Distinct to the Select clause and removed all Group by references.
I am way to new at this still.
Thank you,
Don't worry - being new is not a problem. We all had to learn at some point.
Now, when I hear someone has used DISTINCT in a query - my first response is that there is something wrong with how the tables are joined that is causing additional data to be returned.
For someone who is new to this - that is definitely a possibility. The problem with this is that SQL Server has to perform a lot of work to eliminate duplicates using DISTINCT and performance generally suffers.
I would recommend that you review the query to make sure the tables are joined correctly before using DISTINCT to eliminate duplicates. If you need help, you can post back and we will be happy to help you further.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply