June 11, 2002 at 10:07 am
I have this query that computes the sum of dispositions by Date. Although I receive an error - Column 'tblManualCallData.DateCreated' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
The 'DateCreated' column is originally of datetime datatype. And looks like this - 'mm/dd/yyyy hh:mm:ss'
I don't know why it asks me to group by 'DateCreated' when I use 'convert(varchar(10), DateCreated, 101)'.
The code looks like this:
Select (convert(varchar(10), DateCreated, 101)) as ReportDate, ManualCallDisposition as Disposition,
count(tblManualCallData.ManualCallDispositionID) as Total
FROM tblManualCallData inner join tblManualCallDispositions Disp on tblManualCallData.ManualCallDispositionID = Disp.ManualCallDispositionID
Where (convert(varchar(10), DateCreated, 101)) between convert(varchar(10), DateAdd(d, -7, GetDate()), 101) and convert(varchar(10), DateAdd(d, -1, GetDate()), 101)
and tblManualCallData.ManualCallTypeID =1
Group by (convert(varchar(10), DateCreated, 101)), ManualCallDisposition
Order by (convert(varchar(10), DateCreated, 101)), count(tblManualCallData.ManualCallDispositionID)
Compute sum(count(tblManualCallData.ManualCallDispositionID)) by (convert(varchar(10), DateCreated, 101))
When I add a 'DateCreated' to 'Group by' clause, I don't get the correct result, because instead of grouping by 'mm/dd/yy', it groups by 'mm/dd/yy hh:mm:ss'.
Does anybody know what's going on here?
Thanks,
Nat
June 11, 2002 at 10:31 am
Yes, you cannot GROUP BY a derived date format. I believe that I got round this once by deriving the date in a separate table then JOINing that back with the original data.
Code example follows (if you get my drift):
SELECT g.sortdate 'Date',
MONTH(g.sortdate) 'month',
DAY(g.sortdate) 'day',
YEAR(g.sortdate) 'year',
DATENAME(dw,g.sortdate) 'dow',
SUM(CASE WHEN g.actioncode=5 THEN 1 ELSE 0 END) 'loginok',
SUM(CASE WHEN g.actioncode=6 THEN 1 ELSE 0 END) 'loginfail',
SUM(CASE WHEN g.actioncode=7 THEN 1 ELSE 0 END) 'openpos',
SUM(CASE WHEN g.actioncode=8 THEN 1 ELSE 0 END) 'acctsumm',
SUM(CASE WHEN g.actioncode=9 THEN 1 ELSE 0 END) 'accthist',
SUM(CASE WHEN g.actioncode=10 THEN 1 ELSE 0 END) 'openstop',
SUM(CASE WHEN g.actioncode=38 THEN 1 ELSE 0 END) 'lockout',
SUM(CASE WHEN g.actioncode=40 THEN 1 ELSE 0 END) 'dealok',
SUM(CASE WHEN g.actioncode=41 THEN 1 ELSE 0 END) 'dealfail'
FROM (SELECT CONVERT(char(10),f.actiondate,7) 'sortdate',
f.actioncode
FROM fibshist.dbo.cliextact f
WHERE f.actioncode IN (5,6,7,8,9,10,38,40,41)) g
GROUP BY g.sortdate
ORDER BY YEAR(g.sortdate) desc,MONTH(g.sortdate) desc,DAY(g.sortdate) desc
Regards
Simon
June 11, 2002 at 11:25 am
Simon, thanks!
Works great!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply