0

Table1 

ID  fromdate   todate

001 23/02/2012 27/02/2012
002 23/02/2012 27/02/2012
003 28/02/2012 09/09/2013
....

Condition: I don't want to show duplicate values, so from date validate with other from date, if it's equal then it should not repeat, to date validate with other to date, if it's equal then to date should not repeat,

Expected Output

ID  fromdate   todate

001 23/02/2012 27/02/2012
003 28/02/2012 09/09/2013
....

From the above output, 002 is not showing because fromdate and todate is same with 001, so from and to date comparison should not repeat.

How to write a query for the above condition?

Improve this question
4
  • from and to date comparision should not repeat.... Commented Feb 27, 2012 at 8:39
  • you should explain more clearly what you mean by duplicate. may be you need to add more sample data and expected output. you should also add what query you have tried. Commented Feb 27, 2012 at 8:40
  • 1
    MicSim and my queries are same based on what sample output you gave. They are the answers to be accepted. If they are not the answers then you need to provide more input (sample data) and output (expected output) Commented Feb 27, 2012 at 8:57
  • Sounds like you want to avoid overlapping periods. Are you looking for a database constraint (i.e. stop bad data getting into the database) or a query to scrub data (i.e. to cleanse bad data already in the database)? Commented Feb 27, 2012 at 10:29

4 Answers 4

Reset to default
4

Try this:

SELECT MIN(ID), fromDate, toDate FROM table
GROUP BY fromDate, toDate
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

what would happen here if you left out the MIN?
@MicSim: This will work for the test data but not for the OP's requirement. If one of both dates is not unique, it should not be repeated. Change one of both 27/02/2012 to 26/02/2012 and you get three records instead of the required two.
@TimSchmelter can you edit (add more sample data) if you undertood the question well?
@NoobASThreeDeveloper: He wants to avoid that fromdate is repeating in another record as well as todate. A simple group by both would avoid repeating records where both dates are the same but not where one of both is the same but the other not. Quote: "I don't want to show duplicate values, so from date validate with other from date, if it's equal then it should not repeat, to date validate with other to date, if it's equal then to date should not repeat". So replace the second 27/02/2012 with 26/02/2012 and ensure that 2 records are returned instead of three because fromdate isn't unique.
I've deleted my cte-row_number approach since OP is using sql-server 2000.
2

A ROW_NUMBER solution would be easier to write but following works with SQL Server 2000.

In a nutshell

  • get all lowest fromdates in a subselect
  • get all lowest todates in a subselect for the remaining lowest fromdates by JOINING with our previous subselect.
  • get all records from the actual table for the remaining todates by JOINING with our previous subselect.

SQL Statement

SELECT  Table1.*
FROM    Table1
        INNER JOIN (
          SELECT  ID = MIN(Table1.ID), todate
          FROM    Table1
                  INNER JOIN (
                    SELECT  ID = MIN(ID), fromdate
                    FROM    Table1
                    GROUP BY
                            fromdate
                  ) UniqueFromDates ON UniqueFromDates.ID = Table1.ID
          GROUP BY
                  todate        
        ) UniqueToDates ON UniqueToDates.ID = Table1.ID
Improve this answer

Comments

1

from your comments if "from and to date comparison should not repeat" is your requirement, its very simple

In case there are multiple "ID"s with same fromdata and todate and you want to show the FIRST ID then use this

  select MIN(id) id,fromdata, todate from tablename group by fromdata, todate

In case there are multiple "ID"s with same fromdata and todate and you want to show the LAST ID then use this

  select MAX(id) id,fromdata, todate from tablename group by fromdata, todate
Improve this answer

5 Comments

This will return 3 records but OP wants 2.
add more sample data then we can give the correct query instead of queries based on assumptions
@TimSchmelter it will return only two.
@Noob: Change one of both 27/02/2012 to 26/02/2012 and you get three records instead of the required two.
I leave it to the asker. I have been asked personally question like this many times. They first reject saying "this will result like this and like that" explaining various scenarios, then when I ask "forget the SQL and Queries, tell me how you pick manually based on your conditions" then they realize "oops this is not HOW I want" and explain it more clearly. This often happens when they mixup things with what they want and how they want
0

You can also use PARTITION BY like ↓

SELECT id,
       fromdate,
       todate
FROM   (SELECT id,
               fromdate,
               todate,
               Row_number() OVER (PARTITION BY fromdate, todate ORDER BY id) AS
               row
        FROM   table) t
WHERE  t.row = 1
Improve this answer

2 Comments

even if this is what asker wants, this wont work in sql server 2000 (per tags, he wants in MSSQL2K)
@Noob AS Three Developer OH！So sorry , I haven't paid attention to the Tag!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.