HI All
I am having a problem with a group by: Column 'dbo.Accounts.AccountDateOpen' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
If I add 'dbo.Accounts.AccountDateOpen' to the Group BY , it doesnt group by!
SELECT TOP 100 PERCENT g.GROF,
COUNT_BIG(*)OpenedAccounts,
(YEAR(a.AccountDateOpen)*100)+ DATEPART(week,a.AccountDateOpen)YearWeek,
CASE WHEN DATEPART(week,a.AccountDateOpen) = 27
THEN 200903
ELSE
((YEAR(a.AccountDateOpen)*100)+ DATEPART(quarter,a.AccountDateOpen))
END
YearQuarter
FROM dbo.Grof AS g
INNER JOIN dbo.Accounts a ON a.FKtoCustomerGrof = g.Grof
GROUP BY g.Grof,((YEAR(a.AccountDateOpen)*100)+ DATEPART(week,a.AccountDateOpen))
ORDER BY 1,3