SQL函数，用于检查cdate是否在tdate之前或之后 [英] SQL Function that checks if a cdate is before or after a tdate

问题描述

我需要一个接受三个变量并检查三个表的功能



@Lnumber varchar（max），

@OType varchar （最大），

@ CDate datetime



T1.number varchar（max）

T1.date datetime

T1.ctps int



T2.number varchar（max）

T2.addedDate date

T2.RemovedDate date

T2.ctps int



T3.OType varchar（max）

T3.LNumber varchar（max）

 如果 @LNumber = T1.Number 
 如果 @OType = '  LTD'或'PLC' 
 如果 T1.CTPS =  1  
 如果 @CDate< T1。日期 
 返回  否 
 
 如果 @LNumber = T1.Number 
 如果 @OType = '  LTD'或'PLC' 
 如果 T1.CTPS =  1  
 if @  CDate > T1。日期 
 返回  是 
 ----------------------------- ------------------------------------------- 
 如果 @LNumber = T1.Number 
  if  @OType =  '  LTD'或'PLC' 
 如果 T1.CTPS =  0  
  if  @CDate< T1。日期 
 返回  否 
 
 如果 @LNumber = T1.Number 
 如果 @OType = '  LTD'或'PLC' 
 如果 T1.CTPS =  0  
  if  @CDate> T1。日期 
 返回  否 
 ----------------------------- ------------------------------------------------- 
 如果 @LNumber = T1.Number 
  if  @OType！= '  LTD'或'PLC' 
  if  T1.CTPS =  1  
  if  @CDate< T1。日期 
 返回  否 
 
 如果 @LNumber = T1.Number 
 如果 @OType！= '  LTD'或'PLC'  
 如果 T1.CTPS =  1  
 如果 @CDate> T1。日期 
 返回  是 
 ----------------------------- -------------------- 
 如果 @LNumber = T1.Number 
 < span class =code-keyword> if  @OType！= '  LTD'或'PLC' 
 如果 T1.CTPS =  0  
  if  @CDate< T1。日期 
 返回  否 
 
 如果 @LNumber = T1.Number 
 如果 @OType！= '  LTD'或'PLC'  
 如果 T1.CTPS =  0  
  if  @CDate> T1。日期 
 返回  是 
 ----------------------------- --------------------- 
 如果 @LNumber = T2.Number 
 如果 @OType = '  LTD'或'PLC' 
 如果 T2.CTPS =  0  
 如果 @CDate BETWEEN T2.DateAdded  AND  T2.DateRemoved 
  return   否 
 
 如果 @LNumber = T2.Number 
 如果 @OType = '  LTD'或'PLC' 
 如果 T2.CTPS =  0  
 如果 @CDate  NOT  BETWEEN T2.DateAdded  AND  T2.DateRemoved 
  return    否 
 
 --------------------- --------------------------------- 
 如果 @LNumber = T2.Number 
 如果 @OType = '  LTD'或'PLC' 
 如果 T2.CTPS =  1  
 如果 @CDate BETWEEN T2.DateAdded  AND  T2.DateRemoved 
  return   是 
 
 如果 @LNumber = T2.Number 
  If  @OType = '  LTD'或'PLC' 
 如果 T2.CTPS =  1  
 如果 @CDate  NOT  BETWEEN T2.DateAdded  AND  T2.DateRemoved 
 返回 
 
 ------------------------------------ ------------------ 
 如果 @LNumber = T2.Number 
 如果 @OType！= '  LTD'或'PLC' 
 如果 T2.CTPS =  1  
  if  @CDate BETWEEN T2.DateAdded  AND  T2.DateRemoved 
  return   是 
 
  if  @LNumber = T2.Number 
  if  @OType！= '  LTD'或'PLC' 
 如果 T2.CTPS =  1  
  if  @CDate  NOT  BETWEEN T2.DateAdded  AND  T2.DateRemoved 
  return   否 
 ----------------------- --------------------------- 
 如果 @LNumber = T2 .Number 
  if  @OType！= '  LTD'或'PLC' 
 如果 T2.CTPS =  0  
 如果 @CDate BETWEEN T2.DateAdded  AND  T2.DateRemoved 
  return   是 
 
 如果 @LNumber = T2.Number 
  if  @OType！= '  LTD'或'PLC' 
  if  T2.CTPS =  0  
  if  @CDate < span class =code-keyword> NOT  BETWEEN T2.DateAdded  AND  T2.DateRemoved 
 返回  否 
 ---------- ---------------------------------------- 

解决方案

任何具有许多开放和关闭括号的代码都将成为解开的噩梦！

您需要做的第一件事是简化您的条件...

例如如果@OType！='LTD'或'PLC'可以更好地表示为

如果@OType NOT IN（'LTD'，'PLC'）

下一页 @CDate 只能 BETWEEN T2.DateAdded AND T2。 DateRemoved 或不 - 没有其他选项。所以你可以简化

 如果 @LNumber = T2.Number 
  if  @OType！= '  LTD'或'PLC' 
  if  T2.CTPS =  1  
  if  @CDate BETWEEN T2.DateAdded  AND  T2.DateRemoved 
  return   是 
 
 如果 @LNumber = T2。编号
 如果 @OType  NOT   IN （'  LTD'，'PLC'） 
  if  T2.CTPS =  1  
  if  @CDate 不是 BETWEEN T2.DateAdded  AND  T2.DateRemoved 
  return   否 

成为

< pre lang =vb> 如果 @LNumber = T2.Number
if @OType！ = ' LTD'或'PLC'
如果 T2.CTPS = 1
如果 @CDate BETWEEN T2.DateAdded < span class =code-keyword> AND T2.DateRemoved
return 是 else return 否

最后，考虑建立一个真相表（google it if you不能确定），看你所有的条件 - 有时你会发现其中的规律，使整个事情更简单，更易于阅读。你也可以发现你没有照顾的条件......比如如果 @CDate == T1.Date 该怎么办？事实上你有一个返回，它根本不会返回任何内容。



给它一个去，希望你会成为能够自己解决问题

I need a fucntion that takes in three variables and checks three tables

@LNumber varchar(max),
@OType varchar(max),
@CDate datetime

T1.number varchar(max)
T1.date datetime
T1.ctps int

T2.number varchar(max)
T2.addedDate date
T2.RemovedDate date
T2.ctps int

T3.OType varchar(max)
T3.LNumber varchar(max)

If @LNumber = T1.Number
if @OType = 'LTD' or 'PLC'
if T1.CTPS = 1
if @CDate  < T1.Date
return "No"

if @LNumber = T1.Number
if @OType = 'LTD' or 'PLC'
if T1.CTPS = 1
if@ CDate  > T1.Date
return "Yes"
------------------------------------------------------------------------
If @LNumber = T1.Number
if @OType = 'LTD' or 'PLC'
if T1.CTPS = 0
if @CDate  < T1.Date
return "No"

if @LNumber = T1.Number
if @OType = 'LTD' or 'PLC'
if T1.CTPS = 0
if @CDate > T1.Date
return "No"
------------------------------------------------------------------------------
If @LNumber = T1.Number
if @OType != 'LTD' or 'PLC'
if T1.CTPS = 1
if @CDate < T1.Date
return "No"

if @LNumber = T1.Number
if @OType != 'LTD' or 'PLC'
if T1.CTPS = 1
If @CDate > T1.Date
return "Yes"
-------------------------------------------------
If @LNumber = T1.Number
if @OType != 'LTD' or 'PLC'
if T1.CTPS = 0
if @CDate < T1.Date
return "No"

if @LNumber = T1.Number
if @OType != 'LTD' or 'PLC'
if T1.CTPS = 0
if @CDate > T1.Date
return "Yes"
--------------------------------------------------
If @LNumber = T2.Number
If @OType = 'LTD' or 'PLC'
If T2.CTPS = 0
If @CDate BETWEEN T2.DateAdded AND T2.DateRemoved
return "No"

If @LNumber = T2.Number
If @OType = 'LTD' or 'PLC'
If T2.CTPS = 0
If @CDate NOT BETWEEN T2.DateAdded AND T2.DateRemoved
return "No"

------------------------------------------------------
If @LNumber = T2.Number
If @OType = 'LTD' or 'PLC'
If T2.CTPS = 1
If @CDate BETWEEN T2.DateAdded AND T2.DateRemoved
return "Yes"

If @LNumber = T2.Number
If @OType = 'LTD' or 'PLC'
If T2.CTPS = 1
If @CDate NOT BETWEEN T2.DateAdded AND T2.DateRemoved
return 

------------------------------------------------------
If @LNumber = T2.Number
if @OType != 'LTD' or 'PLC'
if T2.CTPS = 1
if @CDate BETWEEN T2.DateAdded AND T2.DateRemoved
return "Yes"

if @LNumber = T2.Number
if @OType != 'LTD' or 'PLC'
if T2.CTPS = 1
if @CDate NOT BETWEEN T2.DateAdded AND T2.DateRemoved
return "No"
--------------------------------------------------
If @LNumber = T2.Number
if @OType != 'LTD' or 'PLC'
if T2.CTPS = 0
if @CDate BETWEEN T2.DateAdded AND T2.DateRemoved
return "Yes"

if @LNumber = T2.Number
if @OType != 'LTD' or 'PLC'
if T2.CTPS = 0
if @CDate NOT BETWEEN T2.DateAdded AND T2.DateRemoved
return "No"
--------------------------------------------------

解决方案

Any code with that many open and close brackets is going to be a nightmare to untangle!
First thing you need to do is simplify your conditions ...
For example if @OType != 'LTD' or 'PLC' could be better expressed as

if @OType NOT IN ('LTD','PLC')

Next @CDate can only be BETWEEN T2.DateAdded AND T2.DateRemoved or not - there are no other options. So you can simplify

If @LNumber = T2.Number
if @OType != 'LTD' or 'PLC'
if T2.CTPS = 1
if @CDate BETWEEN T2.DateAdded AND T2.DateRemoved
return "Yes"

if @LNumber = T2.Number
if @OType NOT IN ('LTD','PLC')
if T2.CTPS = 1
if @CDate NOT BETWEEN T2.DateAdded AND T2.DateRemoved
return "No"

To become

If @LNumber = T2.Number
if @OType != 'LTD' or 'PLC'
if T2.CTPS = 1
if @CDate BETWEEN T2.DateAdded AND T2.DateRemoved
return "Yes" else return "No"

Finally, consider building a "Truth Table" (google it if you're not sure) to look at all of your conditions - sometimes you will spot patterns that make the whole thing simpler and easier to read. You may also spot the conditions you haven't catered for ... such as what to do if @CDate == T1.Date and the fact you have a return which returns nothing at all.

Give that a go and hopefully you'll be able to solve the problem yourself

这篇关于SQL函数，用于检查cdate是否在tdate之前或之后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！