How to return results grouped by date and type?

[Dustin Houck]

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

this query returns to me a list of statuses and the date of the end of the week in which that record was created, and there are multiple records. for example:

Submitted 2010-11-27 00:00:00.000
Special 2010-11-27 00:00:00.000
Submitted 2010-11-27 00:00:00.000
Special 2010-11-27 00:00:00.000
Submitted 2010-11-27 00:00:00.000
Under Review 2010-11-20 00:00:00.000
Further Review Required 2010-11-20 00:00:00.000
Application Received 2010-11-20 00:00:00.000
...


I need it to count the group by date and status. So all of the records which are in a status and have the same date will be counted:


Status / count / date
Submitted / 5 / 010-11-27 00:00:00.000


any words of wisdom, dear internet?

[code green]

This doesn't make any sense

I need it to count the group by date and status

There is no COUNT and no GROUP BY in your query.

So all of the records which are in a status and have the same date will be counted

Have you tried using a GROUP BY date and COUNT(status)?

[Oralloy]

Dustin,

I may have some syntax wrong here, but try this:

SELECT COUNT(*),
       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) 
  GROUP BY 'Status', 'Last Day Of Week'
  ORDER BY 'Last Day Of Week' DESC