Display only the rows where the date is the same

I have a table (SSG_ADRPT) that contains it has a date column (ACQ_DATE) I only want to display the rows where the dates are the same
skull52Asked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
OK, let us assume we only want the DATE part of the datetime ACQ_DATE

;with cte_dupe_dates as
(
 select cast(ACQ_DATE as date) as Dupe_Date, count(*) over (partition by cast(ACQ_DATE as date)) as Dupe_Count, * 
 from SSG_ADRPT
 )
 select * 
 from cte_dupe_dates 
 where Dupe_Count > 1 
 order by Dupe_Date

Open in new window

Instead of cast( as date) we could also use convert() and use style codes to help format the ACQ_DATE, or, even use dateadd() / datediff() / datepart() to add days or hours or minutes to generate the granularity you need eg:

 select DATEADD(DAY, 0, DATEDIFF(DAY, 0, getdate())), convert(datetime, getdate(), 102)

Now, if there is an entity for which you need to check the dupe (such as product, or customer, or user etc, etc) you need to include them in your PARTITION BY e.g. lets say that the we want dupe date for the serialNo....

count(*) over (partition by SerialNo, cast(ACQ_DATE as date))

Also, in the CTE I use select * and you should ideally use the column names you actually want.

Does that make sense ?
0
 
Pawan KumarDatabase ExpertCommented:
Please use like below , Using row_number function...

SELECT * FROM
(
SELECT * , ROW_NUMBER() OVER (PARTITION BY ACQ_DATE ORDER BY ACQ_DATE DESC) rnk
FROM SSG_ADRPT
)r
WHERE rnk = 1
0
 
Pawan KumarDatabase ExpertCommented:
or use..
COUNT(*) OVER (PARTITION BY.....)

SELECT * FROM
(
    SELECT * , COUNT(*) OVER (PARTITION BY ACQ_DATE) rnk
    FROM SSG_ADRPT
)r
WHERE rnk >= 1  

/* You can adjust this clause ..... if you want records where we have multiple dates then use rnk > 1 */
/* You can adjust this clause ..... if you want records where we have 1 DATE or multiple dates  then use rnk >= 1 */
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mark WillsTopic AdvisorCommented:
What datatype is your ACQ_DATE and what granularity are you after - same day, same hour, etc...

Also is there some other boundary to dupe dates (like same account or product or entry type ot thing) ?

Do you want to see every row instance of the duplicate date?
1
 
Pawan KumarDatabase ExpertCommented:
sample trail for you -

--

CREATE TABLE SSG_ADRPT
(
	 ID INT
	,ACQ_DATE DATE
)
GO

INSERT INTO SSG_ADRPT VALUES ( 1,GETDATE() ) ,( 2, GETDATE() ) , ( 3,GETDATE() ) , (4,GETDATE()+1 ),(5,GETDATE()+1) , ( 6,GETDATE() )

INSERT INTO SSG_ADRPT VALUES ( 7, GETDATE() - 2 )

SELECT Id,ACQ_DATE FROM 
(
    SELECT * , COUNT(*) OVER (PARTITION BY ACQ_DATE) rnk 
    FROM SSG_ADRPT
)r
WHERE rnk > 1  

Open in new window


OUTPUT

/*------------------------

SELECT Id,ACQ_DATE FROM 
(
    SELECT * , COUNT(*) OVER (PARTITION BY ACQ_DATE) rnk 
    FROM SSG_ADRPT
)r
WHERE rnk > 1  
------------------------*/
Id          ACQ_DATE
----------- ----------
6           2017-12-08
1           2017-12-08
2           2017-12-08
3           2017-12-08
4           2017-12-09
5           2017-12-09

(6 row(s) affected)

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
we can also use simple GROUP BY and  HAVING Clause to get duplicate date date . Sample below-

--

SELECT a.* FROM 
SSG_ADRPT a 
INNER JOIN 
(
	SELECT ACQ_DATE
	FROM SSG_ADRPT
	GROUP BY ACQ_DATE
	HAVING COUNT(*) > 1
)k ON a.ACQ_DATE = k.ACQ_DATE

Open in new window


OUTPUT

/*------------------------
SELECT a.* FROM 
SSG_ADRPT a 
INNER JOIN 
(
	SELECT ACQ_DATE
	FROM SSG_ADRPT
	GROUP BY ACQ_DATE
	HAVING COUNT(*) > 1
)k ON a.ACQ_DATE = k.ACQ_DATE
------------------------*/
ID          ACQ_DATE
----------- ----------
6           2017-12-08
1           2017-12-08
2           2017-12-08
3           2017-12-08
4           2017-12-09
5           2017-12-09

(6 row(s) affected)

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
@Pawan,

Mate, I have asked a couple of questions above and would like to get an answer, your proliferation of posts might obfuscate my questions....
0
 
awking00Commented:
A set of sample data and your expected output would be most helpful in providing a solution.
1
 
skull52Author Commented:
Mark, the datatype is datetime, and the date is associated with a serial number and several other columns and I want to display the rows
 where the date is the same for that serial number.
0
 
awking00Commented:
As Mark wills requested, we still need to know the granularity required for defining the same date. For example, is an acq_date of 12/11/2017 2:38:09 PM considered to be the same date as 12/11/2017 3:06:23 PM since they are the same day (and hour, etc.) or must they have the exact same date and time period?
0
 
Pawan KumarDatabase ExpertCommented:
Hi Mark - I have already proposed the same solution long back. :) [ #a42396044 , #a42396083 ]

@Author - Have you tried my last solutions I gave?

Please try the 2 full tested solution below-

Explanation SOLUTION 1 - First I am removing the time part from the ACQ_DATE, then I am counting how many same rows we have for
each ACQ_DATE without time and then I am picking all the rows which are greater than 1 meaning they have duplicates.

Explanation SOLUTION 2 - In the second solution I have group by the data using ACQ_DATE without time and pick data where the dates count is greater than 1. After that JOIN this result set with the original data to fetch the required (all) columns.

If you want to include the Time also in the calculations then we can directly use this COUNT(*) OVER (PARTITION BY ACQ_DATE)

DATA GENERATION AND TABLE CREATION

--

CREATE TABLE SSG_ADRPT
(
	 ID INT
	,ACQ_DATE DATETIME
)
GO

INSERT INTO SSG_ADRPT VALUES ( 1,GETDATE() ) ;
INSERT INTO SSG_ADRPT VALUES ( 2, GETDATE() ) ;
INSERT INTO SSG_ADRPT VALUES ( 3,GETDATE() ) ;
INSERT INTO SSG_ADRPT VALUES (4,GETDATE()+1 ) ;
INSERT INTO SSG_ADRPT VALUES (5,GETDATE()+1) ;
INSERT INTO SSG_ADRPT VALUES ( 6,GETDATE() ) ;

INSERT INTO SSG_ADRPT VALUES ( 7, GETDATE() - 2 )

Open in new window


SOLUTION 1

--

SELECT Id,ACQ_DATE FROM 
(
    SELECT *, COUNT(*) OVER (PARTITION BY DATEADD(dd, DATEDIFF(dd, 0,ACQ_DATE), 0)) rnk 
    FROM SSG_ADRPT
)r
WHERE rnk > 1  

--

Open in new window


OUTPUT

/*------------------------

SELECT Id,ACQ_DATE FROM 
(
    SELECT *, COUNT(*) OVER (PARTITION BY DATEADD(dd, DATEDIFF(dd, 0,ACQ_DATE), 0)) rnk 
    FROM SSG_ADRPT
)r
WHERE rnk > 1 
------------------------*/
Id          ACQ_DATE
----------- -----------------------
6           2017-12-12 04:44:13.443
2           2017-12-12 04:44:17.933
1           2017-12-12 04:44:03.677
3           2017-12-12 04:44:31.223
5           2017-12-13 04:44:22.780
4           2017-12-13 04:44:27.567

(6 row(s) affected)

Open in new window


OR

SOLUTION 2

--

SELECT a.* FROM 
SSG_ADRPT a 
INNER JOIN 
(
	SELECT DATEADD(dd, DATEDIFF(dd, 0,ACQ_DATE), 0) ACQ_DATE
	FROM SSG_ADRPT
	GROUP BY DATEADD(dd, DATEDIFF(dd, 0,ACQ_DATE), 0)
	HAVING COUNT(*) > 1
)k ON DATEADD(dd, DATEDIFF(dd, 0,a.ACQ_DATE), 0) = k.ACQ_DATE

--

Open in new window


OUTPUT

--

/*------------------------

SELECT a.* FROM 
SSG_ADRPT a 
INNER JOIN 
(
	SELECT DATEADD(dd, DATEDIFF(dd, 0,ACQ_DATE), 0) ACQ_DATE
	FROM SSG_ADRPT
	GROUP BY DATEADD(dd, DATEDIFF(dd, 0,ACQ_DATE), 0)
	HAVING COUNT(*) > 1
)k ON DATEADD(dd, DATEDIFF(dd, 0,a.ACQ_DATE), 0) = k.ACQ_DATE
------------------------*/
ID          ACQ_DATE
----------- -----------------------
6           2017-12-12 04:44:13.443
2           2017-12-12 04:44:17.933
1           2017-12-12 04:44:03.677
3           2017-12-12 04:44:31.223
5           2017-12-13 04:44:22.780
4           2017-12-13 04:44:27.567

(6 row(s) affected)

--

Open in new window

1
 
skull52Author Commented:
Yes I am only interested in the date part, I will try both Marks, and Pawans Solutions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.