Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2010
    Posts
    26

    Unanswered: 'group by' issues!

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dah97765 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2010
    Posts
    26
    Quote Originally Posted by r937 View Post
    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

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2010
    Posts
    26
    Quote Originally Posted by pootle flump View Post
    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?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?'
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Aug 2010
    Posts
    26
    Quote Originally Posted by pootle flump View Post
    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?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My apologies - both Rudy and I made the same error
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Aug 2010
    Posts
    26
    Quote Originally Posted by pootle flump View Post
    My apologies - both Rudy and I made the same error
    No worries!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •