return rows with count > 1

I have the following sql server 2005 table


case_#     customer_code          call_date_time
1                1234                          09/09/2008 10:12:12
2                1234                          09/09/2008 13:12:12
3                1234                          09/09/2008 14:12:12
4                1234                          09/10/2008 14:14:12
5                2456                          09/12/2008 14:14:12
6                2457                          09/12/2008 15:20:12
7                2457                          09/12/2008 16:14:12


I am trying to write a query that will return all rows in which the customer has called more than once in a given day.

Using the above data, I am looking for the output to be as follows:

1                1234                          09/09/2008 10:12:12
2                1234                          09/09/2008 13:12:12
3                1234                          09/09/2008 14:12:12
6                2457                          09/12/2008 15:20:12
7                2457                          09/12/2008 16:14:12






johnnyg123Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Brendt HessConnect With a Mentor Senior DBACommented:
Heh - thanks for the reminder on the table alias in the SELECT statement, indianguru2.  Unfortunately, your query will not succeed, since it would return record #4 as well
SELECT
    t.case_#,
    t.customer_code,
    t.call_date_time
FROM MyTable t
INNER JOIN (
    SELECT
        customer_code,
        Cast(Convert(varchar(8), call_date_time, 112) as datetime) as Dt,
        Count(*) As Ct
    FROM MyTable
    GROUP BY
        customer_code,
        Cast(Convert(varchar(8), call_date_time, 112) as datetime)
    HAVING count(*) > 1
    ) Filter
    ON t.customer_code = filter.customer_code
    AND t.call_date_time >= filter.dt
    AND t.call_date_time < Dateadd(day, 1, filter.dt)

Open in new window

0
 
BrandonGalderisiCommented:
select customercode, convert(datetime,convert(varchar(11),call_date_time,109),109)
from YourCustomers
group by customercode, convert(datetime,convert(varchar(11),call_date_time,109),109)
having count(*)>1
0
 
chapmandewCommented:
select customercode, convert(varchar(10),call_date_time,101), count(*)
from tablename
group by customercode, convert(varchar(10),call_date_time,101)
having count(*)>1
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
BrandonGalderisiCommented:
Oops.... 101 is the correct format code, not 109.
0
 
Brendt HessSenior DBACommented:
This will return the rows you are looking for:

SELECT
    case_#,
    customer_code,
    call_date_time
FROM MyTable t
INNER JOIN (
    SELECT
        customer_code,
        Cast(Convert(varchar(8), call_date_time, 112) as datetime) as Dt,
        Count(*) As Ct
    FROM MyTable
    GROUP BY
        customer_code,
        Cast(Convert(varchar(8), call_date_time, 112) as datetime)
    HAVING count(*) > 1
    ) Filter
    ON t.customer_code = filter.customer_code
    AND t.call_date_time >= filter.dt
    AND t.call_date_time < Dateadd(day, 1, filter.dt)
0
 
indianguru2Commented:

SELECT  t.case_#,
        t.customer_code,
        t.call_date_time
FROM    Table_1 t
        INNER JOIN ( SELECT customer_code
                     FROM   dbo.Table_1
                     GROUP BY customer_code
                     HAVING COUNT(customer_code) > 1
                   ) i ON t.customer_code = i.customer_code

Open in new window

0
 
indianguru2Commented:
Thanks for the info ... but I already tried it and works.
0
 
BrandonGalderisiCommented:
indianguru2:

No it won't.  your grouping  has NO aggregation to ensure that the calls occurred on the same day.  Yours just checks for more than one call which was not the requirement.
0
 
johnnyg123Author Commented:
hmmmm....

I was hoping to get case_id in the result set instead of the count but when I try to add case_id I get the dreaded

case_id is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I need the actual case numbers not just how many times it occurs
0
 
indianguru2Commented:
Check his output that he needs :)
0
 
indianguru2Commented:
Sorry .. my mistake ... :)
0
 
johnnyg123Author Commented:
oops....looks like I posted my commment before I hit refresh
0
 
BrandonGalderisiCommented:
What case # do you want?  All of them for a given day?

if so, use bhess1's solution but you may need to wrap t.case_# in brackets like this...


t.[case_#]
0
 
Brendt HessSenior DBACommented:
Please check my code - it returns the case_id etc.
0
 
johnnyg123Author Commented:
bhess1,

you are correct....your code is exactly what I was looking for!!!!!!!
0
All Courses

From novice to tech pro — start learning today.