Category: SQL Server

04 Dec 2018 21:23

Joined:	21/07/2010
Level:	Bronze
Points:	27

Points:	5

Find recurred records in a table

I have a table for orders:

id date amount
------------------------
101 01/10/2018 1000
102 04/10/2018 2000
103 15/10/2018 500
104 25/10/2018 6000
105 28/10/2018 100
106 04/11/2018 2000
107 15/11/2018 500
108 28/11/2018 100
109 29/11/2018 200
110 30/11/2018 100

I need result like below:

id date amount
--------------------------
102 04/10/2018 2000
103 15/10/2018 500
105 28/10/2018 100

i need only those rows which are recurred in next month and have same amount value and DD part of the date.

#769832

05 Feb 2019 12:45

Joined:	30/07/2007
Level:	Gold
Points:	624

Points:	3

i can not give you the sql statement but I can give you the steps that will help you to write the query
1. Write a query to get the amount of all records of next month.
Select amount from table where month(28/11/2018)=11
2. Now select all the records of current month where any record amount equal to the output of Step 1 like
Select * from table where amount exist (Select amount from table where month(28/11/2018)=11) and month = current month.
hope you will get the desire output

#769845

11 Mar 2019 16:47

Joined:	08/06/2011
Level:	Gold
Points:	1350

Points:	4

create table #test (id int,Dates Date,amount int)
insert into #test values(101,'10/01/2018',1000)
insert into #test values(102,'10/04/2018',2000)
insert into #test values(103,'10/15/2018',500 )
insert into #test values(104,'10/25/2018',6000)
insert into #test values(105,'10/28/2018',100 )
insert into #test values(106,'11/04/2018',2000)
insert into #test values(107,'11/15/2018',500 )
insert into #test values(108,'11/28/2018',100 )
insert into #test values(109,'11/29/2018',200 )
insert into #test values(110,'11/30/2018',100 )

select a.* from #test a join #test b on a.amount=b.amount and b.Dates=DATEADD(MM,1,a.Dates)

Find recurred records in a table

About dotnetspider.com

Quick Links

General

Services