计算账户交易明细前后交易时间的间隔天数

本文主要是介绍计算账户交易明细前后交易时间的间隔天数，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

表结构

将记录上下两条合并成一行

SQL语句

1.详细记录

SELECT *,DATEDIFF(DAY, K.JYSJ, B.JYSJ) AS TS
FROM (
SELECT B1.YE, B1.JYSJ, ROW_NUMBER() OVER (ORDER BY JYSJ) AS R
FROM (
SELECT A.YE, A.JYSJ
FROM FIXF_SVR_XZHJYMX A
WHERE A.ZHID = '000090000000044076'
AND CAST(LEFT(convert(varchar, A.JYSJ, 120), 10) AS datetime) >= CAST('2016-12-30' AS datetime)
AND CAST(LEFT(convert(varchar, A.JYSJ, 120), 10) AS datetime) <= CAST('2017-12-30' AS datetime)
UNION ALL
SELECT 0, CAST('2017-12-30' AS datetime)
) B1
) K, 
(
SELECT B1.YE, B1.JYSJ, ROW_NUMBER() OVER (ORDER BY JYSJ) AS R
FROM (
SELECT A.YE, A.JYSJ
FROM FIXF_SVR_XZHJYMX A
WHERE A.ZHID = '000090000000044076'
AND CAST(LEFT(convert(varchar, A.JYSJ, 120), 10) AS datetime) >= CAST('2016-12-30' AS datetime)
AND CAST(LEFT(convert(varchar, A.JYSJ, 120), 10) AS datetime) <= CAST('2017-12-30' AS datetime)
UNION ALL
SELECT 0, CAST('2017-12-30' AS datetime)
) B1
) B
WHERE K.R = B.R - 1

2.删除余额和天数为0的记录

SELECT Z.YE, Z.TS
FROM (
SELECT K.YE, DATEDIFF(DAY, K.JYSJ, B.JYSJ) AS TS
FROM (
SELECT B1.YE, B1.JYSJ, ROW_NUMBER() OVER (ORDER BY JYSJ) AS R
FROM (
SELECT A.YE, A.JYSJ
FROM FIXF_SVR_XZHJYMX A
WHERE A.ZHID = '000090000000044076'
AND CAST(LEFT(convert(varchar, A.JYSJ, 120), 10) AS datetime) >= CAST('2016-12-30' AS datetime)
AND CAST(LEFT(convert(varchar, A.JYSJ, 120), 10) AS datetime) <= CAST('2017-12-30' AS datetime)
UNION ALL
SELECT 0, CAST('2017-12-30' AS datetime)
) B1
) K, 
(
SELECT B1.YE, B1.JYSJ, ROW_NUMBER() OVER (ORDER BY JYSJ) AS R
FROM (
SELECT A.YE, A.JYSJ
FROM FIXF_SVR_XZHJYMX A
WHERE A.ZHID = '000090000000044076'
AND CAST(LEFT(convert(varchar, A.JYSJ, 120), 10) AS datetime) >= CAST('2016-12-30' AS datetime)
AND CAST(LEFT(convert(varchar, A.JYSJ, 120), 10) AS datetime) <= CAST('2017-12-30' AS datetime)
UNION ALL
SELECT 0, CAST('2017-12-30' AS datetime)
) B1
) B
WHERE K.R = B.R - 1
) Z
WHERE Z.TS > 0 AND Z.YE > 0

这篇关于计算账户交易明细前后交易时间的间隔天数的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---