April 23, 2008 at 10:03 am
I have the following code:
DECLARE @COnsultantID Char(20)
DECLARE @StartDate DateTime
Declare @EndDate Datetime
SET @COnsultantID= '0066939'
Set @StartDate ='11/01/2007'
Set @EndDate= '11/01/2007'
SET@StartDate = Convert(DateTime,Convert(nVarChar(10),@StartDate,101) + ' 00:00:00.000')
SET@EndDate = Convert(DateTime,Convert(nVarChar(10),@EndDate,101) + ' 23:59:59.997')
Select c.ConsultantID,
'AchieveDisplay'=
CASE
when MAx(c.CurrentLevelXID) <= 30 AND c.NACDate <= '11/01/2007' THEN 'Not Applicable'
WHEN Max(c.CurrentLevelXID) >= 40 AND c.NACDAte <= '11/01/2007' THen s.Title
WHEN c.NacDate >= '11/01/2007' THen 'Not Applicable'
END,
MAX(c.CurrentLevelXID) AS CurrentLevelXID,
c.NACDate,
MAX(c.EffectiveDate) As EffectiveDate
From Consultant c
LEFT OUTer Join SharedDimension..DimConsultantTitle s ON c.CurrentLevelXID = s.XID
WHERE c.EffectiveDate <= @StartDate-- AND c.EffectiveDate <=@EndDate
AND c.ConsultantID = @COnsultantID
AND c.NACDate IS NOT NULL
GROUP BY c.ConsultantID,s.Title,c.NacDate
It returns:
ConsultantID AchieveDisplay CurrentLevelXID NACDate EffectiveDate
------------- -------------------- --------------- ----------------------- -----------------------
0066939 Not Applicable 20 2007-07-26 00:00:00.000 2007-08-01 21:16:03.887
0066939 Not Applicable 30 2007-07-26 00:00:00.000 2007-10-04 11:25:59.853
Where it should only return one record (the last one). ANy ideas?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 23, 2008 at 10:48 am
You are grouping by the title, not the results of your case statement.
You probably want this (I'm not spending the time to clean up the format):
[font="Courier New"]Select c.ConsultantID,
'AchieveDisplay'=
CASE
when MAx(c.CurrentLevelXID) <= 30 AND c.NACDate <= '11/01/2007' THEN 'Not Applicable'
WHEN Max(c.CurrentLevelXID) >= 40 AND c.NACDAte <= '11/01/2007' THen s.Title
WHEN c.NacDate >= '11/01/2007' THen 'Not Applicable'
END,
MAX(c.CurrentLevelXID) AS CurrentLevelXID,
c.NACDate,
MAX(c.EffectiveDate) As EffectiveDate
From Consultant c
LEFT OUTer Join SharedDimension..DimConsultantTitle s ON c.CurrentLevelXID = s.XID
WHERE c.EffectiveDate <= @StartDate-- AND c.EffectiveDate <=@EndDate
AND c.ConsultantID = @COnsultantID
AND c.NACDate IS NOT NULL
GROUP BY c.ConsultantID,CASE
when MAx(c.CurrentLevelXID) <= 30 AND c.NACDate <= '11/01/2007' THEN 'Not Applicable'
WHEN Max(c.CurrentLevelXID) >= 40 AND c.NACDAte <= '11/01/2007' THen s.Title
WHEN c.NacDate >= '11/01/2007' THen 'Not Applicable'
END,c.NacDate[/font]
April 23, 2008 at 10:55 am
I recieve the following message:
Msg 144, Level 15, State 1, Line 13
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 23, 2008 at 10:56 am
This is how you end up when you don't use canonical date format (120) 🙂
Check this out:
DECLARE @StartDate DateTime
Declare @EndDate Datetime
Set @StartDate ='11/01/2007' --11th of Jan or 1st of Nov?
Set @EndDate= '11/01/2007'
SET @StartDate = Convert(DateTime,Convert(nVarChar(10),@StartDate,101) + ' 00:00:00.000')
SET @EndDate = Convert(DateTime,Convert(nVarChar(10),@EndDate,101) + ' 23:59:59.997')
select @StartDate, case when convert(datetime, '2007-07-26 00:00:00', 120) < @StartDate then 1 else 0 end
The date you pass to your query is not exactly what you probably expect.
Regards
Piotr
...and your only reply is slàinte mhath
April 23, 2008 at 11:09 am
I was not paying close attention...
You need to remove the MAX() out of the case statements in both the select list and group by clause. The case should read:
[font="Courier New"]CASE
when c.CurrentLevelXID <= 30 AND c.NACDate <= '11/01/2007' THEN 'Not Applicable'
WHEN c.CurrentLevelXID >= 40 AND c.NACDAte <= '11/01/2007' THen s.Title
WHEN c.NacDate >= '11/01/2007' THen 'Not Applicable'
END[/font]
Evaluate the case and then calculate aggregates.
April 23, 2008 at 11:26 am
Thanks a lot that worked like a champ.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 24, 2008 at 3:44 pm
I ran into a small issue. In one case I am returning 2 rows where it shoould only be a single: Code:
Select c.ConsultantID,
'AchieveDisplay'=
CASE
when c.CurrentLevelXID <= 30 AND c.NACDate <= '11/01/2007' THEN 'Not Applicable'
WHEN c.CurrentLevelXID >= 40 AND c.NACDAte <= '11/01/2007' THen s.Title
WHEN c.NacDate >= '11/01/2007' THen 'Not Applicable'
END,
MAX(c.CurrentLevelXID) AS CurrentLevelXID,
c.NACDate,
MAX(c.EffectiveDate) As EffectiveDate
From Consultant c
LEFT OUTer Join SharedDimension..DimConsultantTitle s ON c.CurrentLevelXID = s.XID
WHERE c.EffectiveDate <= @StartDate-- AND c.EffectiveDate <=@EndDate
AND c.ConsultantID = @COnsultantID
AND c.NACDate IS NOT NULL
GROUP BY c.ConsultantID,CASE
when c.CurrentLevelXID <= 30 AND c.NACDate <= '11/01/2007' THEN 'Not Applicable'
WHEN c.CurrentLevelXID >= 40 AND c.NACDAte <= '11/01/2007' THen s.Title
WHEN c.NacDate >= '11/01/2007' THen 'Not Applicable'
END,c.NacDate
Result sets:
ConsultantID AchieveDisplay CurrentLevelXID NACDate EffectiveDate
------------- -------------------- --------------- ----------------------- -----------------------
0000131 Senior Team Manager 60 1998-07-29 00:00:00.000 2007-10-10 23:39:39.160
0000131 Team Mentor 70 1998-07-29 00:00:00.000 2007-07-01 19:33:21.917
It should be returning the 1st record.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 25, 2008 at 6:59 am
Any ideas on this issue?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply