sql select query remove rows by comparing dates

Question

I need to write a sql select query on a table, but I have 1 special condition that I have problem with. My table structure is like below:

ID       Name         Serial      CreatedOn
1         A            1000         2014-10-10
2         A            1000         2014-10-12
3         A            1000         2014-12-12
4         B            1023         2014-10-01

My query should compare createdon dates, if there is 1 or more records that names and serials are the same and the createdon date is less than 1 month older than the other ones, they need to be removed. For instance, in my example table, row with ID=2 should be removed from my select query because it has the same data as the first row and the date is less than 1 month older, but it should show the third row since the createdon date is more than 1month older.

How can I write this select query?

Thanks,

is it oracle or ms sql or mysql? – radar Oct 29 '14 at 20:49 — radar, Oct 29 '14 at 20:49

radar · Answer 1 · 2014-10-30T17:09:36.400

0

you can do it with UNION, where you can get data ahead of 1 month always and in the second select you can remove duplicates using row_number

The syntax is based on MS SQL, if it is other DMBS, then date functions need to be modified.

SELECT ID,Name, Serial, CreatedOn
FROM Table1
where DATEDIFF(M, CreatedOn,GETDATE())   >1
UNION
select ID,Name, Serial, CreatedOn from
(
SELECT *, ROW_NUMBER() over (partition by name order by createdon ) as seq
FROM Table1
where DATEDIFF(M, CreatedOn,GETDATE())  <=1
) T
where T.seq =1

edited Oct 30 '14 at 17:09

answered Oct 29 '14 at 21:06

radar

13,270
2
25
33

Hi, thanks for your response, but the fact is I don't want to compare the data with the data 1 month away from current date. Each item needs to be compared with its own records. Like item name A can have 1 record with createdon date 1 year ago and another one 2 month ago, so both of them should show up in the report and item B can have 1 record with createdon last week and another one 3 weeks ago, so the records from 2 weeks ago for item B should be removed. – Erfan Zarger Oct 30 '14 at 13:53
@ErfanZarger, you have one record with date as dec 2014 ? also is this sql server then above query will work, i am changing it with out assuming future dates. – radar Oct 30 '14 at 17:03
I just mentioned some sample data, my actual table has thousands of records, I still don't understand why are you comparing the cratedon date with current date, that is not right. – Erfan Zarger Oct 30 '14 at 19:05
@ErfanZarger, i am comparing with current date to see if it older than 1 month old. if it older than 1 month, reterving all records, other wise getting only oldest in the current month – radar Oct 30 '14 at 19:36

score 0 · Answer 2 · edited Aug 23 '16 at 21:49

Try this one :

Select bb.ID, aa.*
From (
    Select  Name, Serial, Min(CreatedOn) as CreatedOn
    From (Select Name, Serial, Left(CreatedOn, 4)+Substring(CreatedOn, 6, 2) as tPrd, CreatedOn From Table1 ) a
    Group By Name, Serial, tPrd
    ) aa
Left Join Table1 bb             --> to get column "ID"
    On bb.Name = aa.Name
    And bb.Serial = aa.Serial
    And bb.CreatedOn = aa.CreatedOn

If you don't need column "ID".

This is the query

Select  Name, Serial, Min(CreatedOn) as CreatedOn
From (Select Name, Serial, Left(CreatedOn, 4)+Substring(CreatedOn, 6, 2) as tPrd, CreatedOn From Table1 ) a
Group By Name, Serial, tPrd

this query is use, if the data type of column "CreatedOn" is string.

If data type of column "CreatedOn" is date, you have to change

Left(CreatedOn, 4)+Substring(CreatedOn, 6, 2) as tPrd

change to

Convert(Varchar(6), CreatedOn, 112) as tPrd

sql select query remove rows by comparing dates

2 Answers2