需要在sql server中帮助查询 [英] need to help a query in sql server

问题描述

我有一个关于SQL Server的问题。

表：emp

I have a question about SQL Server.
Table : emp

id      |    name     | sdate       | edate 
1       |    abc      | 2003-11-13  | 2003-11-26
1       |    def      | 2000-04-08  | 2000-04-11
1       |    har      | 2003-08-01  | 2003-08-31
1       |    ka       | 2003-10-01  | 2003-10-31

Table: emp1
id  | locname | date    
1   | a       | 2003-10-01          
1   | b       | 2003-08-01              
1   | c       | 2000-04-08  
1   | d       | 2000-04-10

这里emp1表相关日期列数据介于emp表sdate和edate之间。

如果我们有多个那么我们需要将min（date）相关的旧记录和最大（日期）记录视为新记录。

如果我们得到单个记录的条件，那么我们需要考虑相同的记录作为该日期的新记录和新记录。



如果我们没有任何记录，那么我们需要考虑emp1表相关列数据



默认值，基于此表我想要输出如下

Here emp1 table related date column data fall between emp table sdate and edate.
If we got multiple then we need to consider min(date) related as old record and max(date) records as new records.
If we got single records follow condition then we need to consider same records as old and new record for that date.

If we don't have any records, then we need to consider emp1 table related columns data

Default values and based on this table I want output like below

id     |    name    |  sdate       |   edate    | Filter   |  locname   | date 
   1  |    abc     |    2003-11-13  |2003-11-26  | new      |   NA       | 1800-01-01(defaultdate)
   1  |    def      |   2000-04-08  |2000-04-11  | new      |   d        | 2000-04-10
   1  |    har      |   2003-08-01  |2003-08-31  | new      |   b        | 2003-08-01   
   1  |    ka         | 2003-10-01  |2003-10-31  | new      |   a        | 2003-10-01
   1  |    abc      |   2003-11-13  |2003-11-26  | old      |   NA       | 1800-01-01(defaultdate) 
   1  |    def      |   2000-04-08  |2000-04-11  | old      |   c        | 2000-04-08   
   1  |    har      |   2003-08-01  |2003-08-31  | old      |   b        | 2003-08-01
   1  |    ka         | 2003-10-01  |2003-10-31  | old      |   a        | 2003-10-01

我试过这个查询：

I tried with this query:

select  
    max(date) as date, id, name,  
    sdate, edate, 'New' as Filter, locname   
from 
    (select   
         a.id, a.name, a.sdate, a.edate, 'New'as Filter,
         b.locname, b.date 
     from
         emp a  
     join  
         emp1 b on aid = b.id
                and CONVERT(VARCHAR(10), CAST(b.date AS DATE), 120) between CONVERT(VARCHAR(10), CAST(ltrim(rtrim(a.sdate)) AS DATE), 120) 
 and CASE WHEN coalesce(ltrim(rtrim(a.edate)),'') = '' THEN CONVERT(VARCHAR(10), CAST(getdate() AS DATE), 120)
 else CONVERT(VARCHAR(10), CAST(ltrim(rtrim(a.edate)) AS DATE), 120)  end 
       ) a
group by
    date, id, name, sdate, edate, 'New' as Filter, locname 

union all

select  min(date)as date ,id , name ,  sdate ,edate,'old'as Filter ,locname   from 
(select   a.id , a.name ,  a.sdate ,a.edate,'old'as Filter ,b.locname ,b.date from
  emp a  join  emp1 b on aid=b.id
  and CONVERT(VARCHAR(10), CAST(b.date AS DATE), 120) between  CONVERT(VARCHAR(10), CAST(ltrim(rtrim(a.sdate)) AS DATE), 120) 
 and  CASE WHEN coalesce(ltrim(rtrim(a.edate)),'') = '' THEN CONVERT(VARCHAR(10), CAST(getdate() AS DATE), 120)
 else CONVERT(VARCHAR(10), CAST(ltrim(rtrim(a.edate)) AS DATE), 120)  end 
  )a
 group by
 date ,id , name ,  sdate ,edate,'New'as Filter ,locname 

以上查询没有qiven预期结果。请告诉我如何在sql servr中执行查询以执行此任务

above query not qiven expected result .please tell me how to write query to achive this task in sql servr

推荐答案

总之，不要将日期存储为字符串，您将省去很多麻烦。您的代码很难阅读，因为所有转换都是如此。假设日期是实际日期，您可以这样做：

First of all, do not store dates as strings, you will save yourself a lot of trouble. You code is hard to read because all of the conversions. Assuming dates are real dates you can do this:

with cte as
(
  select emp.id, emp1.id as id1
  from emp
  left join emp1 on emp1.[date] between emp.sdate and emp.edate
)
, cte2 as
(
  select id, min(id1) as id1, 'old' as filter
  from cte
  group by id
  
  union all
  
  select id, max(id1) as id1, 'new' as filter
  from cte
  group by id  
)
select * from cte2 -- join to emp and emp1 to get the other columns

我认为代码不需要太多评论。如果你需要将字符串转换为日期，请将所有这些转换为另外一个cte。这是 SQL小提琴 [ ^ ]，玩得开心。



编辑 - ID不是唯一的

如果您的ID不是唯一的，例如名称，那么您可以这样做：

I don't think the code needs much comment. If you need to convert strings to dates prepend all this with another cte where you do just that. Here is the SQL fiddle[^], have fun.

Edit - ids are not unique
If your ids are not unique and for example names are then you can do this:

with cte as
(
  select name, locname
  from emp
  left join emp1 on emp1.[date] between emp.sdate and emp.edate
)
, cte2 as
(
  select name, min(locname) as locname, 'old' as filter
  from cte
  group by name
  
  union all
  
  select name, max(locname) as locname, 'new' as filter
  from cte
  group by name 
)
select * from cte2

SQL小提琴 [ ^ ]

这篇关于需要在sql server中帮助查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！