SQL Query Group By with Days of the Current Week

databarracks
databarracks used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
Please rephrase your question. Post you entire SELECT statement, partial WHERE or JOIN conditions are not enough. And add your sheet.

Author

Commented:
Ah just realised I didn't attach the spreadsheet, apologies for that. The spreadsheet does the explaining in itself.
Sample.xlsx

Author

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
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Senior Developer
Commented:
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

dsackerContract ERP Admin/Consultant

Commented:
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

Author

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)?
ste5anSenior Developer

Commented:
D'oh? But sounds like we need a CASE/GROUP BY pivot. Can you describe the input and output?

Author

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
dsackerContract ERP Admin/Consultant

Commented:
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

Author

Commented:
Hi dsacker,

I get "Invalid column name 'DayName2'." error?
dsackerContract ERP Admin/Consultant

Commented:
Don't forget to add that in your SELECT statement. Note carefully how I added it in the SELECT in my example.

Author

Commented:
Ok got it now
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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

Author

Commented:
Very good job and thanks to the experts

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial