k*****n
发帖数: 361 | 1 032811003660 86 2011-03-28 12:16:16.953 PE
032811003660 86 2011-10-06 11:39:20.357 PE
032811003660 FR 2011-10-06 11:39:28.090 PE
032811003660 FR 2012-05-29 10:33:06.893 PE
032811003660 FO 2012-08-06 11:40:39.520 PE
032811003660 FO 2012-08-27 09:02:12.580 PE
032811003660 FR 2012-08-27 09:02:15.223 PE
I want to calculate the age(in days) of this sequence not in FR period.
For this case will be from 2011-03-28 12:16:16.953 to 2011-10-06 11:39:28.
090 plus from 2012-08-06 11:40:39.520 to 2012-08-27 09:02:15.223. | s**********o
发帖数: 14359 | | k*****n
发帖数: 361 | 3 我知道datediff的，但是条件是除去date range在FR里的 | s**********o
发帖数: 14359 | 4 你问题没说清楚吧，谁知道你的FR FO是啥，从啥到啥的RANGE，为什么不是从86到FO的
RANGE呢，别假设别人知道你的问题，首先要把问题讲清楚。
【在 k*****n 的大作中提到】
: 我知道datediff的，但是条件是除去date range在FR里的
| y****w
发帖数: 3747 | 5 伪码，你自己接着改出来吧，
with t1 ( select code, time, rownumber()over() as rn from t order by time ),
t2(tm1, tm2)(
select t1.time, t1.time from t1 where time = (select min(time) from t1
where code <> 'FR')
union all
select case when t1.code <> 'FR' then t1.time else NULL end, t1.time
from t1,t2 where t1.rn = t2.rn+1
)
select tm1, max(tm2) from t2 where tm1 is not null group by tm1
【在 k*****n 的大作中提到】
: 032811003660 86 2011-03-28 12:16:16.953 PE
: 032811003660 86 2011-10-06 11:39:20.357 PE
: 032811003660 FR 2011-10-06 11:39:28.090 PE
: 032811003660 FR 2012-05-29 10:33:06.893 PE
: 032811003660 FO 2012-08-06 11:40:39.520 PE
: 032811003660 FO 2012-08-27 09:02:12.580 PE
: 032811003660 FR 2012-08-27 09:02:15.223 PE
: I want to calculate the age(in days) of this sequence not in FR period.
: For this case will be from 2011-03-28 12:16:16.953 to 2011-10-06 11:39:28.
: 090 plus from 2012-08-06 11:40:39.520 to 2012-08-27 09:02:15.223.
| y*****n
发帖数: 11251 | 6 老大你在哪里混工资？老板的email就这么直接贴啊。 |
|
