I'm trying to run a query and group by a calcuated column using its alias.
When I do, I get an error that says:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'WeekEnding'.
Here is the SQL code. Can someone tell me what is wrong with this.
select completionType,
(case datepart(dw,dateCompleted)
When 2 then dateAdd(dd,4,datecompleted)
When 3 then dateAdd(dd,3,datecompleted)
When 4 then dateAdd(dd,2,datecompleted)
When 5 then dateAdd(dd,1,datecompleted)
When 6 then dateAdd(dd,0,datecompleted)
end) as WeekEnding
--count(*)
From tblWorkQueue
where datecompleted is not null
group by completiontype, WeekEnding
order by weekendingThis is a multi-part message in MIME format.
--=_NextPart_000_00FE_01C396EF.A792ED00
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You cannot use an alias in that context. However, you can use a derived =table to do the same thing:
select
completionType,
WeekEnding,
count(*)
from
(select completionType,
(case datepart(dw,dateCompleted)
When 2 then dateAdd(dd,4,datecompleted)
When 3 then dateAdd(dd,3,datecompleted)
When 4 then dateAdd(dd,2,datecompleted)
When 5 then dateAdd(dd,1,datecompleted)
When 6 then dateAdd(dd,0,datecompleted)
end) as WeekEnding
From tblWorkQueue
where datecompleted is not null
) as x
group by completiontype, WeekEnding
order by weekending
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jeff Czyzewski" <jeff@.red5poductions.com_NOSPAM> wrote in message =news:umg0xBxlDHA.3700@.TK2MSFTNGP11.phx.gbl...
I'm trying to run a query and group by a calcuated column using its =alias.
When I do, I get an error that says:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'WeekEnding'.
Here is the SQL code. Can someone tell me what is wrong with this.
select completionType,
(case datepart(dw,dateCompleted)
When 2 then dateAdd(dd,4,datecompleted)
When 3 then dateAdd(dd,3,datecompleted)
When 4 then dateAdd(dd,2,datecompleted)
When 5 then dateAdd(dd,1,datecompleted)
When 6 then dateAdd(dd,0,datecompleted)
end) as WeekEnding
--count(*)
From tblWorkQueue
where datecompleted is not null
group by completiontype, WeekEnding
order by weekending
--=_NextPart_000_00FE_01C396EF.A792ED00
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You cannot use an alias in that =context. However, you can use a derived table to do the same thing:
select
completionType, WeekEnding,
count(*)from
(select =completionType, (case datepart(dw,dateCompleted) When 2 then dateAdd(dd,4,datecompleted) When 3 then dateAdd(dd,3,datecompleted) When 4 then dateAdd(dd,2,datecompleted) When 5 then dateAdd(dd,1,datecompleted) When 6 then dateAdd(dd,0,datecompleted) end) as WeekEndingFrom tblWorkQueuewhere datecompleted is not null) as =x
group by completiontype, WeekEndingorder by weekending
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jeff Czyzewski"
wrote in message news:umg0xBxlDHA.3700=@.TK2MSFTNGP11.phx.gbl...I'm trying to run a query and group by a calcuated column using its =alias.When I do, I get an error that says:Server: Msg 207, Level 16, State 3, =Line 2Invalid column name 'WeekEnding'.Here is the SQL code. =Can someone tell me what is wrong with this.select completionType, (case datepart(dw,dateCompleted) When =2 then dateAdd(dd,4,datecompleted) When 3 then dateAdd(dd,3,datecompleted) When 4 then dateAdd(dd,2,datecompleted) When 5 then dateAdd(dd,1,datecompleted) When 6 then dateAdd(dd,0,datecompleted) end) as WeekEnding --count(*)From tblWorkQueuewhere =datecompleted is not nullgroup by completiontype, WeekEndingorder by weekending
--=_NextPart_000_00FE_01C396EF.A792ED00--|||Jeff
Make a derived table
select completionType,WeekEnding
from
(
select completionType,
(case datepart(dw,dateCompleted)
When 2 then dateAdd(dd,4,datecompleted)
When 3 then dateAdd(dd,3,datecompleted)
When 4 then dateAdd(dd,2,datecompleted)
When 5 then dateAdd(dd,1,datecompleted)
When 6 then dateAdd(dd,0,datecompleted)
end) as WeekEnding
From tblWorkQueue
where datecompleted is not null
) as x
group by completionType,WeekEnding
--order by weekending
"Jeff Czyzewski" <jeff@.red5poductions.com_NOSPAM> wrote in message
news:umg0xBxlDHA.3700@.TK2MSFTNGP11.phx.gbl...
> I'm trying to run a query and group by a calcuated column using its alias.
> When I do, I get an error that says:
> Server: Msg 207, Level 16, State 3, Line 2
> Invalid column name 'WeekEnding'.
>
> Here is the SQL code. Can someone tell me what is wrong with this.
> select completionType,
> (case datepart(dw,dateCompleted)
> When 2 then dateAdd(dd,4,datecompleted)
> When 3 then dateAdd(dd,3,datecompleted)
> When 4 then dateAdd(dd,2,datecompleted)
> When 5 then dateAdd(dd,1,datecompleted)
> When 6 then dateAdd(dd,0,datecompleted)
> end) as WeekEnding
> --count(*)
> From tblWorkQueue
> where datecompleted is not null
> group by completiontype, WeekEnding
> order by weekending
>
