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

问题:

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.


回答1:

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'
  • 发表于 2019-02-15 17:02
  • 阅读 ( 160 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除