Go Premium for a chance to win a PS4. Enter to Win

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

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






0
johnnyg123
Asked:
johnnyg123
  • 4
  • 4
  • 3
  • +2
1 Solution
 
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
 
BrandonGalderisiCommented:
Oops.... 101 is the correct format code, not 109.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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
 
Brendt HessSenior 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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now