May 4, 2005 at 10:05 pm
I have the following query:
SELECT result_date, result_by, tasktype, action, Count(*) AS [Completed Tasks]
FROM ac_activity
WHERE result_code = 'Completed' AND type = 'Task' AND role like 'Client Service Representative%'
AND result_date >= '2/1/05'
GROUP BY result_date, result_by, tasktype, action
ORDER BY result_by, result_date, tasktype, action
This works fine but what I need to do is group the dates together and just give the counts by result_by, tasktype and action for an entire week rather than by each day. I've looked for a function that would help but haven't had any success. Any guideance would be appreciated. Thanks.
May 4, 2005 at 10:16 pm
groub by DATEDIFF(ww, result_date, getdate())
getdate() may be replaced with any given date. For example, '1900-01-01 00:00'.
Anyway difference in weeks between that date and dates for all same week tasks will be the same integer.
_____________
Code for TallyGenerator
May 4, 2005 at 10:27 pm
That gets me almost there I think. What if I wanted the results to return the actual date of the Monday of every week?
May 4, 2005 at 11:15 pm
result_date - DATEPART (dw, result_date) + 1
This is made for @@DATEFIRST = 1 (Monday is first day of the week)
Check your server settings.
_____________
Code for TallyGenerator
May 5, 2005 at 12:38 pm
Thanks. That works.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply