Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Query Group By with Days of the Current Week

Posted on 2014-08-08
14
Medium Priority
?
426 Views
Last Modified: 2014-08-14
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
0
Comment
Question by:databarracks
  • 7
  • 3
  • 3
  • +1
14 Comments
 
LVL 36

Expert Comment

by:ste5an
ID: 40248351
Please rephrase your question. Post you entire SELECT statement, partial WHERE or JOIN conditions are not enough. And add your sheet.
0
 

Author Comment

by:databarracks
ID: 40248358
Ah just realised I didn't attach the spreadsheet, apologies for that. The spreadsheet does the explaining in itself.
Sample.xlsx
0
 

Author Comment

by:databarracks
ID: 40248362
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 36

Accepted Solution

by:
ste5an earned 2000 total points
ID: 40248378
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
 
LVL 20

Expert Comment

by:dsacker
ID: 40248379
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
 

Author Comment

by:databarracks
ID: 40248430
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
 
LVL 36

Expert Comment

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

Author Comment

by:databarracks
ID: 40248448
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
 
LVL 20

Expert Comment

by:dsacker
ID: 40248524
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
 

Author Comment

by:databarracks
ID: 40248559
Hi dsacker,

I get "Invalid column name 'DayName2'." error?
0
 
LVL 20

Expert Comment

by:dsacker
ID: 40248578
Don't forget to add that in your SELECT statement. Note carefully how I added it in the SELECT in my example.
0
 

Author Comment

by:databarracks
ID: 40248670
Ok got it now
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40249270
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
 

Author Closing Comment

by:databarracks
ID: 40260794
Very good job and thanks to the experts
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Loops Section Overview

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question