July 1, 2005 at 2:22 pm
I have the following query (I also wrote another one that produced the same results.)
SELECT (result_date - DATEPART (dw, result_date) + 2),
CASE result_by WHEN 'Bushaw' THEN Count(*) ELSE '' END AS [Bushaw],
CASE result_by WHEN 'Dunkel' THEN Count(*) ELSE '' END AS [Dunkel]
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 - DATEPART (dw, result_date) + 2), result_by
ORDER BY result_by, (result_date - DATEPART (dw, result_date) + 2)
Here is a sample of the results:
Column 1 Bushaw Dunkel
2005-01-31 00:00:00.000 26 0
2005-02-07 00:00:00.000 38 0
2005-02-14 00:00:00.000 14 0
2005-02-21 00:00:00.000 15 0
2005-02-28 00:00:00.000 47 0
2005-03-07 00:00:00.000 38 0
2005-03-14 00:00:00.000 59 0
2005-03-21 00:00:00.000 45 0
It goes through all the dates for Bushaw and then the dates start over and it gives the numbers for Dunkel. Is there a way to have the dates just listed once and all the number for Bushaw and Dunkel showing up under the respective column header for each date?
July 1, 2005 at 3:54 pm
Please post a your table with Sample data (Or a table like it.)
and then what you want the result to look like.
http://www.aspfaq.com/etiquette.asp?id=5006
July 1, 2005 at 9:01 pm
Ray thanks for the great link. I will stick this one in my pocket and use it often
Mike
July 2, 2005 at 5:56 am
Remove result_by from your group by and order by clauses.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
July 7, 2005 at 5:05 pm
Here is the table with the parts needed for this query:
CREATE TABLE [dbo].[ac_activity] (
[acid] [int] IDENTITY (1, 1) NOT NULL ,
[result_code] [char] (30) NULL ,
[result_date] [datetime] NULL ,
[result_by] [char] (20) NULL ,
[type] [char] (30) NULL ,
[role] [char] (50) NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Here is some sample data:
INSERT ac_activity VALUES
(895, 'Completed', '7/5/2005 12:00:00 AM', 'Bushaw', 'Client Service Representative')
(1967, 'Completed', '7/5/2005 12:00:00 AM', 'Bushaw', 'Client Service Representative')
(2870, 'Completed', '7/5/2005 12:00:00 AM', 'Bushaw', 'Client Service Representative')
(20178, 'Completed', '7/5/2005 12:00:00 AM', 'Bushaw', 'Client Service Representative')
(25696, 'Completed', '7/5/2005 12:00:00 AM', 'Dunkel', 'Client Service Representative/Purchasing Agent')
(31104, 'Completed', '7/5/2005 12:00:00 AM', 'Dunkel', 'Client Service Representative/Purchasing Agent')
(36212, 'Completed', '7/5/2005 12:00:00 AM', 'Dunkel', 'Client Service Representative/Purchasing Agent')
(42717, 'Completed', '7/5/2005 12:00:00 AM', 'Bushaw', 'Client Service Representative')
(46334, 'Completed', '7/1/2005 12:00:00 AM', 'Dunkel', 'Client Service Representative/Purchasing Agent')
(46390, 'Completed', '7/5/2005 12:00:00 AM', 'Bushaw', 'Client Service Representative')
(46430, 'Completed', '7/1/2005 12:00:00 AM', 'Dunkel', 'Client Service Representative/Purchasing Agent')
(46444, 'Completed', '7/1/2005 12:00:00 AM', 'Dunkel', 'Client Service Representative/Purchasing Agent')
(46457, 'Completed', '7/1/2005 12:00:00 AM', 'Bushaw', 'Client Service Representative')
(46460, 'Completed', '7/1/2005 12:00:00 AM', 'Dunkel', 'Client Service Representative/Purchasing Agent')
(46469, 'Completed', '7/1/2005 12:00:00 AM', 'Dunkel', 'Client Service Representative/Purchasing Agent')
(46475, 'Completed', '7/1/2005 12:00:00 AM', 'Dunkel', 'Client Service Representative/Purchasing Agent')
(46487, 'Completed', '7/1/2005 12:00:00 AM', 'Dunkel', 'Client Service Representative/Purchasing Agent')
(46494, 'Completed', '7/1/2005 12:00:00 AM', 'Dunkel', 'Client Service Representative/Purchasing Agent')
(46522, 'Completed', '7/1/2005 12:00:00 AM', 'Bushaw', 'Client Service Representative')
(46536, 'Completed', '7/1/2005 12:00:00 AM', 'Dunkel', 'Client Service Representative/Purchasing Agent')
(46537, 'Completed', '7/1/2005 12:00:00 AM', 'Dunkel', 'Client Service Representative/Purchasing Agent')
(46549, 'Completed', '7/1/2005 12:00:00 AM', 'Bushaw', 'Client Service Representative')
(46550, 'Completed', '7/1/2005 12:00:00 AM', 'Bushaw', 'Client Service Representative')
(46554, 'Completed', '7/1/2005 12:00:00 AM', 'Bushaw', 'Client Service Representative')
(46558, 'Completed', '7/1/2005 12:00:00 AM', 'Bushaw', 'Client Service Representative')
(46562, 'Completed', '7/5/2005 12:00:00 AM', 'Bushaw', 'Client Service Representative')
(46588, 'Completed', '7/5/2005 12:00:00 AM', 'Bushaw', 'Client Service Representative')
(46648, 'Completed', '7/5/2005 12:00:00 AM', 'Dunkel', 'Client Service Representative/Purchasing Agent')
(46649, 'Completed', '7/5/2005 12:00:00 AM', 'Dunkel', 'Client Service Representative/Purchasing Agent')
(46663, 'Completed', '7/5/2005 12:00:00 AM', 'Bushaw', 'Client Service Representative')
(46712, 'Completed', '7/5/2005 12:00:00 AM', 'Bushaw', 'Client Service Representative')
(46732, 'Completed', '7/5/2005 12:00:00 AM', 'Dunkel', 'Client Service Representative/Purchasing Agent')
(46738, 'Completed', '7/5/2005 12:00:00 AM', 'Bushaw', 'Client Service Representative')
And here is what I want to see in the results:
Column 1 Bushaw Dunkel
2005-06-27 00:00:00.000 6 10
2005-07-04 00:00:00.000 11 6
July 7, 2005 at 5:22 pm
SELECT (result_date - DATEPART (dw, result_date) + 2) as [Date],
sum(CASE result_by WHEN 'Bushaw' THEN 1 END) AS [Bushaw],
sum(CASE result_by WHEN 'Dunkel' THEN 1 END) AS [Dunkel]
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 - DATEPART (dw, result_date) + 2)
ORDER BY (result_date - DATEPART (dw, result_date) + 2)
Removed result_by, from the OrderBY, NO need for it here.
count(*) is not appropriate iin the Case statement.
Sum will ignore the NULLS
Result
Date,Bushaw,Dunkel
2005-06-27 00:00:00.000,6,10
2005-07-04 00:00:00.000,11,6
July 7, 2005 at 5:38 pm
Thanks Ray
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply