SQL Query Group By with Days of the Current Week

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

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

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

Select allOpen 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

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

Select allOpen in new window

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

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

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

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

Select allOpen in new window

Hi dsacker,

I get "Invalid column name 'DayName2'." error?

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

Ok got it now

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

Very good job and thanks to the experts