[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Open
  • Priority: High
  • Security: Public
  • Views: 31
  • Last Modified:

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
0
skull52
Asked:
skull52
  • 4
  • 3
  • 2
  • +1
10 Comments
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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 userid....

count(*) over (partition by USERID, 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

Join & Write a Comment

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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