SQL Server 中的日期比较函数

Question

我试图显示 2010 年 10 月 1 日之后创建的记录。但我的查询似乎不起作用。它还显示 2004 年至 2010 年 9 月的记录，这是不需要的。

下面的查询有什么问题？

select Distinct app.app_id,
  (convert(varchar, creation_date,101) + ' ' +  convert(varchar,creation_date ,108)) as creation_date,
  dbo.oea_fn_get_amc_mem_name(app.app_id,primary_msn,getdate(), 'EN', 30000) PIName,
  dbo.oea_fn_get_pid_countyname(app.app_id,primary_msn,'OC')as PIpid,
  primary_msn,
  zip,
  home_tel,
  work_tel,
  work_extn,
  other_contact,
  other_ext,
  cell_tel,

  dbo.oea_fn_get_amc_mem_name(app.app_id,mem.msn,getdate(), 'EN', 30000)as Kname,
  dbo.oea_fn_get_pid_countyname(app.app_id,mem.msn,'OC')as Knamepid,
  mem.msn as Kmsn,
  (select count(reminder_id) from reminders (nolock) where app_id=app.app_id) as reminder

from app_application app  (nolock)
inner join app_member mem  with (nolock) on  app.app_id=mem.app_id 
--left outer join Oea_App_Program_Disposition disp with (nolock) on mem.app_id = disp.app_id and mem.msn=disp.msn
inner join app_address aadd with (nolock) on app.app_id=aadd.app_id
--inner join app_calc_results calc  with (nolock) on mem.app_id=calc.app_id and calc.msn=mem.msn
left outer join app_member_benefits ben with (nolock) on mem.app_id = ben.app_id and mem.msn=ben.msn

where  
 isnull(mem.coverage_required,0) = 1
 and app.app_status = 's'
     and ben.ins_end_date < getdate()
     and app.client_id = 30000
     and app.app_id  not in (select app_id from app_renewal) 
     and (mem.msn in (select calc.msn from app_calc_results calc 
                 inner join app_application app on calc.app_id = app.app_id and calc.prog_id = 'CK' and calc.opt_out = 1))
     and (mem.msn in (select msn from app_calc_results where app_id=app.app_id and status not in ('A','X')))
     or (mem.msn in (select msn from Oea_App_Program_Disposition where app_id=app.app_id and disp_status not in ('A','P')) )
     and app.creation_date >= '10/01/2010'

感谢您的所有帮助。

Answer 1

您可能想要这样：

 and (
     (mem.msn in (select calc.msn from app_calc_results calc 
                 inner join app_application app on calc.app_id = app.app_id and calc.prog_id = 'CK' and calc.opt_out = 1))
    or (mem.msn in (select msn from app_calc_results where app_id=app.app_id and status not in ('A','X')))
    or (mem.msn in (select msn from Oea_App_Program_Disposition where app_id=app.app_id and disp_status not in ('A','P')) )
     )
 and app.creation_date >= '10/01/2010'

问题出在 where 子句中的 or 背后的逻辑。

Answer 2

正如其他人所说，问题可能出在 Where 子句中的 Or 子句。实际上，您的查询是：

Select ...
From ..
Where (A And B And C 
    And D And E 
    And F And G
    And app.creation_date >= '10/01/2010'
    )
    Or mem.msn In   (
                    Select msn 
                    From Oea_App_Program_Disposition 
                    Where app_id=app.app_id 
                        And disp_status not in ('A','P')
                    )

因此，如果对于任何行，如果 Or 为 true，则其余的“And”将被忽略。我假设 Or 应该与 And 子句之一配对。