CODEWITHSUNDEEP
mysqlsqlsql-serverdate
Sadia
Sadia

Reputation: 181

Select unique record from specific dates excluding the duplicate of it in previous dates

I have to select distinct UC_Name from a table that exist in year 1 and year 2 for which i am using the following query:

(select DISTINCT(UC_NAME) from [table]
where cast([DATE_FORMATION] as date) <= '06-30-2018 00:00:00') as Year1

(select DISTINCT(UC_NAME) from [table]
where cast([DATE_FORMATION] as date) BETWEEN '06-30-2018 00:00:00' AND '06-30-2019 00:00:00')  as Year2

As far as the distinct values are concerned it is giving me the right values but what i want to get the UC_Names that uniquely exist only in year 2 and are not present in the year1, even if they are unique in year2 dates i want to remove the UC_Names that are existing in year1. I hope i made it clear to understand what i want to get.

It gives me result like this:

**Year1:**
Balicha
Kunchiti
Sangahi
Solband
Tijaban

**Year2**
Balicha
Khairabad
Kuddan
Kunchiti
Nasirabad
Nodiz
Sami
Sangahi
Shahrak
Solband
Tijaban

some of the values of year 1 are present in year 2 as well which are unique in year 2 but i want values from year 2 that are unique and also not repeat from year 1 as well.

Upvotes: 1

Views: 659

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272136

The date range looks wrong to me. The date 2018-06-30 is present in both groups. Keeping this in mind, here is NOT EXISTS solution:

SELECT UC_NAME
FROM t
WHERE DATE_FORMATION >= '2018-07-01'
AND   DATE_FORMATION <  '2019-07-01' -- made exclusive
AND   NOT EXISTS (
    SELECT 1
    FROM t AS x
    WHERE UC_NAME = t.UC_NAME
    AND DATE_FORMATION < '2018-07-01' -- made exclusive
)
GROUP BY UC_Names

You can also use aggregation:

SELECT UC_NAME
FROM t
WHERE  DATE_FORMATION      <  '2019-07-01'
GROUP BY UC_NAME
HAVING MIN(DATE_FORMATION) >= '2018-07-01'

Upvotes: 3

Related Questions