[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 947
  • Last Modified:

SQL Query to get call per week, create column based on first day of week

Hi,
I have a Phone Log table consist of CalledBy and CallOn.
I am trying to write a query that show each sales person calls per week.
The data looks like this

Bob Smith     1/7/2014
Bob Smith     1/8/2014
Bob Smith     1/17/2014
Mary Jane      1/8/2014
Mary Jane      1/9/2014

I want the result to look like this

Name           TotalCallsInWeekOf1/7/2014    TotalCallsInWeekOf1/14/2014
Bob Smith                            2                                             1
Mary Jane                             1                                             0

I have a query in progress but it doesn't really do what I needed.
Please help!

Select
A.CalledBy,
A.WeekOf,
sum(A.calls) AS NumOfCallsMade
from
(
    SELECT
    CONVERT(varchar, DATEADD(dd, -(DATEPART(dw, CalledOn)-2), CalledOn), 101) AS WeekOf,
    CalledBy
    ,1 AS calls
    FROM PhoneLog
   
) A
GROUP BY CalledBy, WeekOf
ORDER BY CalledBy, WeekOf
0
rskepton
Asked:
rskepton
1 Solution
 
Scott PletcherSenior DBACommented:
For just two weeks specifically, the code below should work ok.

If you really need the actual date in the column name, you will have to use dynamic SQL or a temp table for the results.



DROP TABLE #PhoneLog
CREATE TABLE #PhoneLog (
    CalledBy varchar(30),
    CalledOn datetime
    )
INSERT INTO #PhoneLog VALUES('Bob Smith',     '1/5/2014') --should be ignored, before start date
INSERT INTO #PhoneLog VALUES('Bob Smith',     '1/6/2014') --should be ignored, before start date
INSERT INTO #PhoneLog VALUES('Bob Smith',     '1/7/2014')
INSERT INTO #PhoneLog VALUES('Bob Smith',     '1/8/2014')
INSERT INTO #PhoneLog VALUES('Bob Smith',     '1/17/2014')
INSERT INTO #PhoneLog VALUES('Bob Smith',     '1/22/2014') --should be ignored, after start date + 2 weeks
INSERT INTO #PhoneLog VALUES('Mary Jane',     '1/8/2014')
INSERT INTO #PhoneLog VALUES('Mary Jane',     '1/9/2014')


DECLARE @start_date datetime
SET @start_date = '20140107'

----------------------------------------------------------------------------------------------------

SELECT
    pl.CalledBy AS Name,
    SUM(CASE WHEN pl.CalledOn >= @start_date AND pl.CalledOn < DATEADD(DAY, 8, @start_date) THEN 1 ELSE 0 END) AS Week1,
    SUM(CASE WHEN pl.CalledOn >= DATEADD(DAY, 7, @start_date) AND pl.CalledOn < DATEADD(DAY, 15, @start_date) THEN 1 ELSE 0 END) AS Week2
FROM #PhoneLog pl --dbo.PhoneLog pl
WHERE
    pl.CalledOn >= @start_date AND
    pl.CalledOn < DATEADD(DAY, 15, @start_date)
GROUP BY
    pl.CalledBy
0

Featured Post

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now