SELECT DISTINCT but up to 3? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT DISTINCT but up to 3?

Author

Topic

mr_max
Starting Member

10 Posts

Posted - 2014-09-29 : 18:04:31

Hello, I'm trying to extract data from our database for the number of phone calls our reps are doing.
In counting the calls I only want to include up to 3 calls to the same customer (field name is CompanyID) per day - anything more than this is ignored.
The query at the moment is something like:
SELECT COUNT(CallID) AS CallCount FROM Sales_Calls WHERE CallDate >= '2014-09-01' AND CallDate <= '2014-09-30' AND RepID = 1
Using MSSQL 2012. Any ideas?
Thanks!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-09-29 : 18:38:53

[code]SELECT CompanyID, case when COUNT(CallID) >= 3 then 3 else COUNT(CallID) end AS CallCount
FROM Sales_Calls
WHERE CallDate >= '2014-09-01' AND CallDate <= '2014-09-30' -- BETWEEN is more performant
AND RepID = 1
GROUP BY CompanyID[/code]

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

mr_max
Starting Member

10 Posts

Posted - 2014-09-29 : 19:09:10

[quote]Originally posted by Bustaz Kool

SELECT CompanyID, case when COUNT(CallID) >= 3 then 3 else COUNT(CallID) end AS CallCount 
FROM Sales_Calls 
WHERE CallDate >= '2014-09-01' AND CallDate <= '2014-09-30' -- BETWEEN is more performant
AND RepID = 1
GROUP BY CompanyID

Thanks Bustaz, will that be up to 3 per month or per day?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-09-29 : 19:24:42

Ahhhhhhh..... Twas blind but now I see...

SELECT PerDay.CompanyID, sum(PerDay.CallCountPerDay) AS CallCount 
FROM (
    SELECT
        CompanyID, cast(CallDate as date), case when COUNT(CallID) >= 3 then 3 else COUNT(CallID) end AS CallCountPerDay
    from Sales_Calls 
    WHERE CallDate >= '2014-09-01' AND CallDate <= '2014-09-30' -- BETWEEN is more performant
    AND RepID = 1
    GROUP BY CompanyID, cast(CallDate as date)
    ) PerDay
group by
    PerDay.CompanyID

Or maybe I'm still blind...

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

mr_max
Starting Member

10 Posts

Posted - 2014-09-29 : 20:44:08

Getting this error:

No column name was specified for column 2 of 'PerDay'.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-30 : 00:49:05

Slightly modified query of Bustaz Kool:

SELECT PerDay.CompanyID, PerDay.CallDate, sum(PerDay.CallCountPerDay) AS CallCount 
FROM (
    SELECT
        CompanyID, cast(CallDate as date) as CallDate, case when COUNT(CallID) >= 3 then 3 else COUNT(CallID) end AS CallCountPerDay
    from Sales_Calls 
    WHERE CallDate >= '2014-09-01' AND CallDate <= '2014-09-30' -- BETWEEN is more performant
    AND RepID = 1
    GROUP BY CompanyID, cast(CallDate as date)
    ) PerDay
group by
    PerDay.CompanyID

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

mr_max
Starting Member

10 Posts

Posted - 2014-10-01 : 20:41:33

Sorry, is there some way to aggregate all those results into one figure? Just need the sum of all the CallCount column!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-02 : 14:34:01

In addition to the per Customer count? Or in place of it?

No amount of belief makes something a fact. -James Randi

mr_max
Starting Member

10 Posts

Posted - 2014-10-02 : 18:09:10

In place of it. The application is a scoreboard to show how many calls the rep has done.
Thanks v much for your help.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-02 : 19:19:53

[code]SELECT sum(PerDay.CallCountPerDay) AS CallCount
FROM (
SELECT
CompanyID, cast(CallDate as date), case when COUNT(CallID) >= 3 then 3 else COUNT(CallID) end AS CallCountPerDay
from Sales_Calls
WHERE CallDate >= '2014-09-01' AND CallDate <= '2014-09-30' -- BETWEEN is more performant
AND RepID = 1
GROUP BY CompanyID, cast(CallDate as date)
) PerDay[/code]This still limits the number of calls to a single customer to three per day.

No amount of belief makes something a fact. -James Randi

mr_max
Starting Member

10 Posts

Posted - 2014-10-03 : 00:27:59

Arrgh - now getting this:

No column name was specified for column 2 of 'PerDay'.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-03 : 09:29:25

...so give it a name...e.g.

...cast(CallDate as date) as foobar, ..

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-03 : 11:15:05

Amen...

No amount of belief makes something a fact. -James Randi

mr_max
Starting Member

10 Posts

Posted - 2014-10-05 : 16:30:25

Perfect - thanks a lot :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources