'group by' issues!

dah97765 · #1 (**permalink**) 11-22-10, 01:17

This query:

Code:

select 
EndVal as Status
, DATEADD(DAY , 7-DATEPART(WEEKDAY,convert(datetime,convert(char(10),Created,101))),convert(datetime,convert(char(10),Created,101))) AS 'Last Day Of Week'
from tblFieldAudit
Where EndVal IN (select distinct Status from tblStatus)
order by 'Last Day Of Week' desc, Created desc

Yields this (sample):

Status / Date of the End of the Week

Submitted 2010-11-27 00:00:00.000
Special 2010-11-27 00:00:00.000
Submitted 2010-11-27 00:00:00.000
Submitted 2010-11-27 00:00:00.000
Further Review Required 2010-11-20 00:00:00.000
Under Review 2010-11-20 00:00:00.000
Further Review Required 2010-11-20 00:00:00.000
Rejection Not Eligible 2010-11-20 00:00:00.000
...

I would like to group and count the statuses for each created date:

Submitted 3 2010-11-27
Special 1 2010-11-27
Further Review Required 2 2010-11-20
Under Review 1 2010-11-20
Rejection Not Eligible 1 2010-11-20
...

I have not been able to correctly do this! Any help?

r937 · #2 (**permalink**) 11-22-10, 02:49

Quote:

Originally Posted by dah97765

order by 'Last Day Of Week' desc

you're sorting by the same string for all rows

try it this way --

Code:

SELECT EndVal AS Status
     , DATEADD(DAY,7-DATEPART(WEEKDAY,CONVERT(DATETIME,CONVERT(CHAR(10),Created,101))),CONVERT(DATETIME,CONVERT(CHAR(10),Created,101))) 
          AS LastDayOfWeek
  FROM tblFieldAudit
 WHERE EndVal IN (SELECT DISTINCT Status FROM tblStatus)
ORDER 
    BY LastDayOfWeek DESC
     , Created DESC

dah97765 · #3 (**permalink**) 11-23-10, 09:24

Quote:

Originally Posted by r937

you're sorting by the same string for all rows

try it this way --

Code:

SELECT EndVal AS Status
     , DATEADD(DAY,7-DATEPART(WEEKDAY,CONVERT(DATETIME,CONVERT(CHAR(10),Created,101))),CONVERT(DATETIME,CONVERT(CHAR(10),Created,101))) 
          AS LastDayOfWeek
  FROM tblFieldAudit
 WHERE EndVal IN (SELECT DISTINCT Status FROM tblStatus)
ORDER 
    BY LastDayOfWeek DESC
     , Created DESC

Thanks for the (quick) response. I ended up calling it a night and finishing in the morning. You were close to what I needed:

Code:

select 
distinct EndVal as Status
, COUNT( datepart(DAY,Created)) as TotalStatusDayClaims
--, convert(datetime,convert(char(10),Created,101)) as Created
, DATEADD(DAY , 7-DATEPART(WEEKDAY,convert(datetime,convert(char(10),Created,101))),convert(datetime,convert(char(10),Created,101))) AS 'Last Day Of Week'
from tblFieldAudit
Where EndVal IN (select distinct Status from tblStatus)
Group by EndVal
, convert(datetime,convert(char(10),Created,101))
order by 'Last Day Of Week' desc, Status

pootle flump · #4 (**permalink**) 11-23-10, 09:51

Don't use apostrophes for enclosing column names - use either " or [. This is the source of the error.
Furthermore, very few (if any) people on the forum would recommend you use spaces in your column names either.

dah97765 · #5 (**permalink**) 11-23-10, 09:56

Quote:

Originally Posted by pootle flump

Don't use apostrophes for enclosing column names - use either " or [. This is the source of the error.
Furthermore, very few (if any) people on the forum would recommend you use spaces in your column names either.

Thanks, its been updated accordingly, though it wasnt throwing any error for the apostrophes. Should it have?

pootle flump · #6 (**permalink**) 11-23-10, 10:07

No, part of the problem is it won't throw an error. Apostrophes are much, much more commonly used to enclose strings. It is this dual purpose that can be a source of bugs, as above.

As an exercise, is the below valid? If so, what do you think this will return?

Code:

SELECT  'data or metadata?' 'metadata or data?'

dah97765 · #7 (**permalink**) 11-23-10, 10:26

Quote:

Originally Posted by pootle flump

No, part of the problem is it won't throw an error. Apostrophes are much, much more commonly used to enclose strings. It is this dual purpose that can be a source of bugs, as above.

As an exercise, is the below valid? If so, what do you think this will return?

Code:

SELECT  'data or metadata?' 'metadata or data?'

it would return two different things, whatever is literal to the 'string' you're selecting.

I understand the difference. I tried it and it worked though, so I left it. I understand it isn't best practice, so I went back and changed it. I dont think I have ever had to order by an aliased column which has whitespace in it (in my vast experience of 3 months).

In this case though, it doesn't change anything since it isn't in a SELECT statement, just in an order by clause, right?

ex:
select something from something order by [Last Day Of Week]
returns the same thing as
select something from something order by 'Last Day Of Week'

Its a moot point since it isn't best practice anyway and I wont be doing it in the future (Using [] initiates intellisense anyway), but I dont think it changes the query as far as 'bugs' or logic errors. Yeah?