gpt4 book ai didi

sql-server - SQL Server : Find records with closest Date to CurrentDate based on conditions

转载 作者:行者123 更新时间:2023-12-04 02:26:58 25 4
gpt4 key购买 nike

我正在使用 SQL Server 2012,我正在尝试创建一个 VIEW,它将根据以下条件返回记录:

  • 查询需要根据日期检索最适用的记录
  • 对于内部日期范围内的日期,将返回最接近 CurrentDate 的记录
  • 对于内部日期范围之外的日期,将返回最接近 CurrentDate 的记录

  • 数据库中的示例表:

    table :
    pId     | Name
    ----------------------
    01 | Person 1
    02 | Person 2
    ----------------------

    人员日期 table :
    dId     |  pId      | StartDate     | EndDate
    ---------------------------------------------------
    A1 | 01 | 2014-01-08 | 2018-01-08
    A2 | 01 | 2016-11-23 | 2016-12-01
    A3 | 01 | 2016-12-03 | 2016-12-08
    A4 | 02 | 2016-10-10 | 2016-12-31
    A5 | 02 | 2016-12-01 | 2016-12-05

    如果我运行这个查询和 CurrentDate是 2016 年 11 月 28 日:
    select p.name, d.startdate, d.enddate
    from Person p, PersonDate d
    where p.pId = d.pId
    and d.StartDate = (select max(sl.StartDate)
    from PersonDate sl
    where d.pId = s1.pId)

    返回的记录是:
    name        | startdate     | enddate
    -------------------------------------------
    Person 1 | 2016-12-03 | 2016-12-08 --> PersonDate Table row A3
    Person 2 | 2016-12-01 | 2016-12-05 --> PersonDate Table row A5
    -------------------------------------------

    根据我试图返回的条件,返回的两个记录都不正确。我明白为什么我会得到返回的记录,这是由于在我的子查询中使用了 Max() 函数,但我不知道如何编写查询/子查询。

    我想要返回的正确记录是(CurrentDate 是 2016-11-28):
    name        | startdate     | enddate
    -------------------------------------------
    Person 1 | 2016-11-23 | 2016-12-01
    Person 2 | 2016-10-10 | 2016-12-31
    -------------------------------------------
  • PersonDate表行 A2,因为这个内部日期范围最接近 CurrentDate (条件#2)
  • PersonDate表行 A4,因为内部日期范围 (A5) 尚未到来(条件 #3)

  • CurrentDate是 2016-12-02:
    name        | startdate     | enddate
    ---------------------------------------------
    Person 1 | 2014-01-08 | 2018-01-08
    Person 2 | 2016-12-01 | 2016-12-05
    ---------------------------------------------
  • PersonDate表行 A1,自 CurrentDate位于 A2 行和 A3 行内部日期范围之外
  • PersonDate表行 A5,自 CurrentDate位于日期范围内

  • 如何编写一个 VIEW 来根据上述条件返回记录?

    最佳答案

    create table #temp(did varchar(10),pid int,startdate datetime,enddate datetime)

    insert into #temp values('A1',01,'2014-01-08','2018-01-08')
    insert into #temp values('A2',01, '2016-11-23' , '2016-12-01' )
    insert into #temp values('A3',01, '2016-12-03' , '2016-12-08' )
    insert into #temp values('A4',02, '2016-10-10' , '2016-12-31' )
    insert into #temp values('A5',02, '2016-12-01' , '2016-12-05' )


    select b.pid,b.startdate,b.enddate
    from
    (
    select ROW_NUMBER()over(partition by pid order by id desc) as SID , a.*
    from
    (
    select
    ROW_NUMBER()over(partition by pid order by startdate,enddate desc) as ID
    , * from #temp
    --to identify whether it is inner or outer
    --1 means outer
    --2 means inner
    )a
    where '2016-12-02' between startdate and enddate
    --to find date lies in outer or inner range and select the required
    )b
    where b.SID=1

    关于sql-server - SQL Server : Find records with closest Date to CurrentDate based on conditions,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/40835873/

    25 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com