SQL Query Group By with Days of the Current Week

Hi Guys,

I am trying to write a query in SQL 2005 that will be able to aggregate numbers from the current week day by day.I already use the below code to get working days with the current week:

(CONVERT(VARCHAR(8), view_calls_quality_1.StartTime, 112) 
                         >= DATEADD(week, DATEDIFF(day, 0, GETDATE()) / 7, 0)) AND (CONVERT(VARCHAR(8), view_calls_quality_1.StartTime, 108) BETWEEN '07:00:00' AND 
                         '19:00:00') AND (CONVERT(VARCHAR(8), view_calls_quality_1.StartTime, 112) <= DATEADD(week, DATEDIFF(day, 0, GETDATE()) / 7, 4))

Open in new window


This works fine. Now what I need to accomplish to to make a view that will extract all StartTime transactions within the results and count daily usage, grouped by user and each day of the current week appearing as a column.

I have attached a spreadsheet sample to show you the kind of thing I would like to achieve. Could someone please kindly assist me with transforming my data into my desired result as per attachment?

Many thanks for your help
databarracksAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
E.g.

WITH Data AS 
	(
		SELECT	U.user_full_name,
				COUNT(view_calls_quality.CallType) AS Total,
				DATENAME(dw, CQ.StartTime) AS [DayName]
		FROM	dbo.view_users U
			INNER JOIN dbo.view_calls_quality CQ ON U.user_full_name = CQ.OriginationName
		WHERE	CQ.CallType = 'Outbound'
			AND CQ.Qualify = 'Qualified'
			AND CONVERT(VARCHAR(8), CQ.StartTime, 112) >= DATEADD(week, DATEDIFF(day, 0, GETDATE()) / 7, 0)
			AND CONVERT(VARCHAR(8), CQ.StartTime, 108) BETWEEN '07:00:00' AND '19:00:00'
			AND CONVERT(VARCHAR(8), CQ.StartTime, 112) <= DATEADD(week, DATEDIFF(day, 0, GETDATE()) / 7, 4)
		GROUP BY U.user_full_name
	)
	SELECT	*
	FROM	Data D
	PIVOT	( SUM(Total) FOR [DayName] IN ( [Monday], [Tuesday], [Wednsday], [Thursday], [Friday] ) ) P;
	                                          

Open in new window

0
 
ste5anSenior DeveloperCommented:
Please rephrase your question. Post you entire SELECT statement, partial WHERE or JOIN conditions are not enough. And add your sheet.
0
 
databarracksAuthor Commented:
Ah just realised I didn't attach the spreadsheet, apologies for that. The spreadsheet does the explaining in itself.
Sample.xlsx
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
databarracksAuthor Commented:
My Query

SELECT        dbo.view_users.user_full_name, COUNT(view_calls_quality.CallType) AS [Total], DATENAME(dw, view_calls_quality_1.StartTime) AS DayName
FROM            dbo.view_users INNER JOIN
                         dbo.view_calls_quality AS view_calls_quality_1 ON 
                         dbo.view_users.user_full_name = view_calls_quality_1.OriginationName
WHERE        (view_calls_quality_1.CallType = 'Outbound') AND (view_calls_quality_1.Qualify = 'Qualified') AND (CONVERT(VARCHAR(8), view_calls_quality_1.StartTime, 112) 
                         >= DATEADD(week, DATEDIFF(day, 0, GETDATE()) / 7, 0)) AND (CONVERT(VARCHAR(8), view_calls_quality_1.StartTime, 108) BETWEEN '07:00:00' AND 
                         '19:00:00') AND (CONVERT(VARCHAR(8), view_calls_quality_1.StartTime, 112) <= DATEADD(week, DATEDIFF(day, 0, GETDATE()) / 7, 4))
GROUP BY dbo.view_users.user_full_name

Open in new window


Which produces totals for each user in each day of the week.All that’s left is to create a view for which the days of the week are the columns and the name of the user. Please see attachment for concept again
0
 
dsackerContract ERP Admin/ConsultantCommented:
This is a great candidate for a PIVOT. You can use your query, as is. Try this query. If you like it, it should be no problem to turn it into a view.
;WITH myCTE (user_full_name, Total, DayName) AS (
SELECT	dbo.view_users.user_full_name,
		COUNT(view_calls_quality.CallType) AS [Total],
		DATENAME(dw, view_calls_quality_1.StartTime) AS DayName
FROM    dbo.view_users INNER JOIN
        dbo.view_calls_quality AS view_calls_quality_1 ON 
        dbo.view_users.user_full_name = view_calls_quality_1.OriginationName
WHERE   (view_calls_quality_1.CallType = 'Outbound') AND (view_calls_quality_1.Qualify = 'Qualified') AND (CONVERT(VARCHAR(8), view_calls_quality_1.StartTime, 112) 
                         >= DATEADD(week, DATEDIFF(day, 0, GETDATE()) / 7, 0)) AND (CONVERT(VARCHAR(8), view_calls_quality_1.StartTime, 108) BETWEEN '07:00:00' AND 
                         '19:00:00') AND (CONVERT(VARCHAR(8), view_calls_quality_1.StartTime, 112) <= DATEADD(week, DATEDIFF(day, 0, GETDATE()) / 7, 4))
GROUP BY dbo.view_users.user_full_name
)
SELECT	user_full_name,
		[MON],
		[TUE],
		[WED],
		[THU],
		[FRI]
FROM	MyCTE
PIVOT ( SUM(Total) FOR DayName IN ([MON],[TUE],[WED],[THU],[FRI] ) ) pvt

Open in new window

0
 
databarracksAuthor Commented:
Hi Guys,

I like both of your queries would it be possible to pivot another field? What I mean is could I pivot the SUM(Total) and another one called SUM(Duration)?
0
 
ste5anSenior DeveloperCommented:
D'oh? But sounds like we need a CASE/GROUP BY pivot. Can you describe the input and output?
0
 
databarracksAuthor Commented:
OK so, your code is perfect but I have another field called duration which is an integer type that has the total seconds for a call. So I would like the same query you had but have two columns for the day of the week which shows the quantity and duration like the  below table:

Name      Monday      MondayTime      Tuesday      TuesdayTime      Wednesday      WednesdayTime      Thursday      ThursdayTime      Friday      FridayTime
Person A      2      125      3      399      0      74      2      19      1      95
Person B      2      100      2      201      0      6      1      78      1      100
Person C      0      66      1      199      3      10      0      146      1      20

Also you can check this link out http://www.sqlsoldier.com/wp/sqlserver/smarpivottricks
0
 
dsackerContract ERP Admin/ConsultantCommented:
I used a field name called "Duration." Substitute that name for whatever truly is your field name. Try this:
;WITH myCTE AS (
SELECT	dbo.view_users.user_full_name,
		COUNT(view_calls_quality.CallType) AS [Total],
		SUM(Duration)       AS Duration,
		DATENAME(dw, view_calls_quality_1.StartTime) AS DayName,
		DATENAME(dw, view_calls_quality_1.StartTime) + 'Time' AS DayName2
FROM    dbo.view_users INNER JOIN
        dbo.view_calls_quality AS view_calls_quality_1 ON 
        dbo.view_users.user_full_name = view_calls_quality_1.OriginationName
WHERE   (view_calls_quality_1.CallType = 'Outbound') AND (view_calls_quality_1.Qualify = 'Qualified') AND (CONVERT(VARCHAR(8), view_calls_quality_1.StartTime, 112) 
                         >= DATEADD(week, DATEDIFF(day, 0, GETDATE()) / 7, 0)) AND (CONVERT(VARCHAR(8), view_calls_quality_1.StartTime, 108) BETWEEN '07:00:00' AND 
                         '19:00:00') AND (CONVERT(VARCHAR(8), view_calls_quality_1.StartTime, 112) <= DATEADD(week, DATEDIFF(day, 0, GETDATE()) / 7, 4))
GROUP BY dbo.view_users.user_full_name
)
SELECT	user_full_name,
		[MON],
		[TUE],
		[WED],
		[THU],
		[FRI]
FROM	MyCTE
PIVOT  (SUM(Total) FOR DayName IN ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday])) AS pvt1
PIVOT  (SUM(Duration) FOR DayName2 IN ([MondayTime],[TuesdayTime],[WednesdayTime],[ThursdayTime],[FridayTime])) AS pvt2

Open in new window

0
 
databarracksAuthor Commented:
Hi dsacker,

I get "Invalid column name 'DayName2'." error?
0
 
dsackerContract ERP Admin/ConsultantCommented:
Don't forget to add that in your SELECT statement. Note carefully how I added it in the SELECT in my example.
0
 
databarracksAuthor Commented:
Ok got it now
0
 
Scott PletcherSenior DBACommented:
I'd opt instead for code below.  I think it's easier to maintain.  [Btw, I changed the StartTime comparisons to avoid using a function on the column except when absolutely necessary; otherwise, you could drastically reduce the usefulness of any index(es) on that column.]


SELECT      vu.user_full_name,
        SUM(CASE WHEN Day_Of_Week = 0 THEN 1 ELSE 0 END) AS Mon_Total,
        SUM(CASE WHEN Day_Of_Week = 0 THEN DATEDIFF(MINUTE, vcq1.StartTime, vcq1.EndTime) ELSE 0 END) AS Mon_Duration,
        SUM(CASE WHEN Day_Of_Week = 1 THEN 1 ELSE 0 END) AS Tue_Total,
        SUM(CASE WHEN Day_Of_Week = 1 THEN DATEDIFF(MINUTE, vcq1.StartTime, vcq1.EndTime) ELSE 0 END) AS Tue_Duration,
        SUM(CASE WHEN Day_Of_Week = 2 THEN 1 ELSE 0 END) AS Wed_Total,
        SUM(CASE WHEN Day_Of_Week = 2 THEN DATEDIFF(MINUTE, vcq1.StartTime, vcq1.EndTime) ELSE 0 END) AS Wed_Duration,
        SUM(CASE WHEN Day_Of_Week = 3 THEN 1 ELSE 0 END) AS Thu_Total,
        SUM(CASE WHEN Day_Of_Week = 3 THEN DATEDIFF(MINUTE, vcq1.StartTime, vcq1.EndTime) ELSE 0 END) AS Thu_Duration,
        SUM(CASE WHEN Day_Of_Week = 4 THEN 1 ELSE 0 END) AS Fri_Total,
        SUM(CASE WHEN Day_Of_Week = 4 THEN DATEDIFF(MINUTE, vcq1.StartTime, vcq1.EndTime) ELSE 0 END) AS Fri_Duration
FROM    dbo.view_users vu INNER JOIN
        dbo.view_calls_quality AS vcq1 ON
            vu.user_full_name = vcq1.OriginationName
--assign alias name to day calc to avoid repeating it throughout the SELECT and make code easier to follow
CROSS APPLY (    
    SELECT DATEDIFF(DAY, 0, vcq1.StartTime) % 7 AS Day_Of_Week --0=Mon;1=Tue;...;4=Fri.
) AS ca1
WHERE   (vcq1.CallType = 'Outbound') AND
        (vcq1.Qualify = 'Qualified') AND
        (vcq1.StartTime >= DATEADD(HOUR, 7, DATEADD(WEEK, DATEDIFF(DAY, 0, GETDATE()) / 7, 0))) AND
        (vcq1.StartTime < DATEADD(HOUR, 19, DATEADD(WEEK, DATEDIFF(DAY, 0, GETDATE()) / 7, 5))) AND
        (DATEPART(HOUR, vcq1.StartTime) <= 18)
GROUP BY vu.user_full_name
--ORDER BY vu.user_full_name
0
 
databarracksAuthor Commented:
Very good job and thanks to the experts
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.