CODEWITHSUNDEEP
sql
user2770323
user2770323

Reputation: 1

SQL - records with same date

I am trying to return records that occur on the same day, but can't figure out how to do this. Using the script below, I can see there are two perf_no's with the same date, I just don't know how to return only those two items.

select perf_no, perf_name, perf_dt
from LVS_TKT_HIST 
where customer_no=1046359
order by perf_dt desc

11038   2014-05-15 20:00:00.000 Patriotic Pops #1
13950   2014-05-15 20:00:00.000 Wine Tier 3
10927   2014-04-25 20:00:00.000 Pops #4 Fri
10833   2014-04-04 20:00:00.000 Evita #4
11269   2014-03-02 19:00:00.000 Lewis & Pizzarelli

Upvotes: 0

Views: 4019

Answers (3)

Adam Porad
Adam Porad

Reputation: 14471

If you're using SQL Server, this can be done using a Common Table Expression (CTE) and the COUNT function with the OVER clause to partition the rowset by the perf_dt column before the COUNT function is applied.

;WITH DATA AS
(
  SELECT perf_no, perf_name, perf_dt, COUNT(*) over (partition BY perf_dt) AS [count]
  FROM LVS_TKT_HIST
  WHERE customer_no=1046359
  GROUP BY perf_no, perf_name, perf_dt
)
SELECT * FROM DATA WHERE [count] > 1

Results:

PERF_NO   PERF_NAME          PERF_DT                     COUNT
-------   -----------------  --------------------------  -----
11038     Patriotic Pops #1  May, 15 2014 20:00:00+0000  2
13950     Wine Tier 3         May, 15 2014 20:00:00+0000  2

I setup this example on SQLFiddle.

Upvotes: 1

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE LVS_TKT_HIST
    ([customer_no] int, [perf_no] int, [perf_dt] datetime, [perf_name] varchar(18))
;

INSERT INTO LVS_TKT_HIST
    ([customer_no], [perf_no], [perf_dt], [perf_name])
VALUES
    (1046359, 11038, '2014-05-15 20:00:00', 'Patriotic Pops #1'),
    (1046359, 13950, '2014-05-15 20:00:00', 'Wine Tier 3'),
    (1046359, 10927, '2014-04-25 20:00:00', 'Pops #4 Fri'),
    (1046359, 10833, '2014-04-04 20:00:00', 'Evita #4'),
    (1046359, 11269, '2014-03-02 19:00:00', 'Lewis & Pizzarelli')
;

Query 1:

SELECT lth.perf_no, lth.perf_name, lth.perf_dt
FROM LVS_TKT_HIST lth
INNER JOIN (
    SELECT customer_no, CAST(perf_dt AS DATE) AS perf_dt
    FROM LVS_TKT_HIST
    GROUP BY customer_no, CAST(perf_dt AS DATE)
    HAVING count(*) > 1
) dt ON dt.customer_no = lth.customer_no AND
        CAST(lth.perf_dt AS DATE) = dt.perf_dt
WHERE lth.customer_no=1046359

Results:

| PERF_NO |         PERF_NAME |                    PERF_DT |
|---------|-------------------|----------------------------|
|   11038 | Patriotic Pops #1 | May, 15 2014 20:00:00+0000 |
|   13950 |       Wine Tier 3 | May, 15 2014 20:00:00+0000 |

Upvotes: 1

Hogan
Hogan

Reputation: 70523

You could mean many things but I'm going with "Return all records that have other records on the same date in SQL Server"

Like this:

  select perf_no, perf_name, perf_dt
   from LVS_TKT_HIST 
   where customer_no=1046359
     AND CAST(perf_dt AS DATE) in 
       (SELECT CAST(perf_dt AS DATE)
        FROM LVS_TKT_HIST 
        WHERE customer_no=1046359
        GROUP BY CAST(perf_dt AS DATE)
        HAVING COUNT(*) > 1
       )
   order by perf_dt desc

Upvotes: 0

Related Questions