描述:
数据库表中包含完税时间、产权证时间、交易时间等三个时间,首先将完税时间与产权证时间进行对比,选出较早的一个时间,之后将改时间与交易时间进行对比,看是否在2年之内。
数据表截图:
主要思路:
分别取完税时间、产权证时间与交易时间的年份做差,并比较两个差值的大小,取较大的差值,并将较大的差值与±2进行比较:
(1) 差值=2时:判断月时间差值:
① 月时间差=0时:判断日时间差:
a.日时间差 >0时:显示‘否’;
b.日时间差 <=0时:显示‘是’;
② 月时间差<0时:显示‘是’;
③ 月时间差>0时:显示‘否’;
(2) 差值=-2时:判断月时间差值:
① 月时间差=0时:判断日时间差:
a.日时间差 <0时:显示‘否’;
b.日时间差 >=0时:显示‘是’;
② 月时间差 <0时:显示‘是’;
③ 月时间差 >0时:显示‘否’;
(3) 差值在-2~2之间时:显示‘是’;
(4) 差值>2 或 <-2时:显示‘否’;
SELECT [Id]
,[TitleCertificateNum]
,[TaxPaymentProveDateTime]
,(select CONVERT(varchar(100),TaxPaymentProveDateTime,23)) as 完税证明时间
,[TitleCertificateDateTime]
,(select CONVERT(varchar(100),TitleCertificateDateTime,23))as 产权证时间
,[TransactionDateTime]
,(select CONVERT(varchar(100),TransactionDateTime,23))as 交易日期
,(select CONVERT(varchar(100),(select (case
when [TaxPaymentProveDateTime]<[TitleCertificateDateTime]
then [TaxPaymentProveDateTime]
else [TitleCertificateDateTime]
end
)
),23))as 证明时间短
,(select
(
case
--年时间差为2时
when
(
(select
(
case
when ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,TaxPaymentProveDateTime)))) > ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,[TitleCertificateDateTime]))))
then ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,TaxPaymentProveDateTime))))
else ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,[TitleCertificateDateTime]))))
end
)
)
) = 2
then
(
case
--月时间差为0时
when
(
(select
(
case
when ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,TaxPaymentProveDateTime)))) > ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,[TitleCertificateDateTime]))))
then ( Max(convert(int,DateName(MONTH,TransactionDateTime)))- Max(convert(int,DateName(MONTH,TaxPaymentProveDateTime))))
else ( Max(convert(int,DateName(MONTH,TransactionDateTime)))- Max(convert(int,DateName(MONTH,[TitleCertificateDateTime]))))
end
)
)
) = 0
then
(
case
--日时间差大于0时
when
(
(select
(
case
when ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,TaxPaymentProveDateTime)))) > ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,[TitleCertificateDateTime]))))
then ( Max(convert(int,DateName(DAY,TransactionDateTime)))- Max(convert(int,DateName(DAY,TaxPaymentProveDateTime))))
else ( Max(convert(int,DateName(DAY,TransactionDateTime)))- Max(convert(int,DateName(DAY,[TitleCertificateDateTime]))))
end
)
)
) > 0
then '否'
else '是'
end
)
--月时间差小于0时
when
(
(select
(
case
when ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,TaxPaymentProveDateTime)))) > ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,[TitleCertificateDateTime]))))
then ( Max(convert(int,DateName(MONTH,TransactionDateTime)))- Max(convert(int,DateName(MONTH,TaxPaymentProveDateTime))))
else ( Max(convert(int,DateName(MONTH,TransactionDateTime)))- Max(convert(int,DateName(MONTH,[TitleCertificateDateTime]))))
end
)
)
) < 0
then '是'
else '否'
end
)
--年时间差为-2时
when
(
(select
(
case
when ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,TaxPaymentProveDateTime)))) > ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,[TitleCertificateDateTime]))))
then ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,TaxPaymentProveDateTime))))
else ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,[TitleCertificateDateTime]))))
end
)
)
) = -2
then
(
case
--月时间差为0时
when
(
(select
(
case
when ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,TaxPaymentProveDateTime)))) > ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,[TitleCertificateDateTime]))))
then ( Max(convert(int,DateName(MONTH,TransactionDateTime)))- Max(convert(int,DateName(MONTH,TaxPaymentProveDateTime))))
else ( Max(convert(int,DateName(MONTH,TransactionDateTime)))- Max(convert(int,DateName(MONTH,[TitleCertificateDateTime]))))
end
)
)
) = 0
then
(
case
--日时间差不为0时
when
(
(select
(
case
when ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,TaxPaymentProveDateTime)))) > ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,[TitleCertificateDateTime]))))
then ( Max(convert(int,DateName(DAY,TransactionDateTime)))- Max(convert(int,DateName(DAY,TaxPaymentProveDateTime))))
else ( Max(convert(int,DateName(DAY,TransactionDateTime)))- Max(convert(int,DateName(DAY,[TitleCertificateDateTime]))))
end
)
)
) < 0
then '否'
else '是'
end
)
--月时间差小于0时
when
(
(select
(
case
when ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,TaxPaymentProveDateTime)))) > ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,[TitleCertificateDateTime]))))
then ( Max(convert(int,DateName(MONTH,TransactionDateTime)))- Max(convert(int,DateName(MONTH,TaxPaymentProveDateTime))))
else ( Max(convert(int,DateName(MONTH,TransactionDateTime)))- Max(convert(int,DateName(MONTH,[TitleCertificateDateTime]))))
end
)
)
) < 0
then '是'
else '否'
end
)
--年时间差在-2~2之间时
when
(
(select
(
case
when ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,TaxPaymentProveDateTime)))) > ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,[TitleCertificateDateTime]))))
then ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,TaxPaymentProveDateTime))))
else ( Max(convert(int,DateName(YEAR,TransactionDateTime)))- Max(convert(int,DateName(YEAR,[TitleCertificateDateTime]))))
end
)
)
) between -2 and 2
--then '是'
--年时间差为-2时
then '是'
else '否'
end
)
) as 是否2年内
FROM [TimeComparison]
group by Id,[TitleCertificateNum],[TaxPaymentProveDateTime],[TitleCertificateDateTime],[TransactionDateTime]
查询结果: