select t.workorder,t.productname,min(case stepname when '7110' then moveintime end) tm1,
min(case stepname when '7900' then moveintime end) tm2,t.ProductionQTY,datediff(day,min(case stepname when '7110' then moveintime end),min(case stepname when '7900' then moveintime end)) as intervalday
from (
select a.workorder,min(a.moveintime) moveintime,a.stepname,b.productname,b.ProductionQTY
from WaferHistory a,workorder b
where
a.WorkOrder=b.WorkOrderID and
a.STEPNAME='7110' and a.MoveInTime>'2013-05-01 00:00:00'
group by a.workorder,a.stepname,b.productname ,b.ProductionQTY
union
select a.workorder,min(a.moveintime) moveintime,a.stepname,b.productname,b.ProductionQTY
from WaferHistory a,workorder b
where
a.WorkOrder=b.WorkOrderID and
a.STEPNAME='7900' and a.MoveInTime>'2013-05-01 00:00:00'
group by a.workorder,a.stepname,b.productname,b.ProductionQTY ) t
group by t.workorder,t.productname ,t.ProductionQTY
having min(case stepname when '7110' then moveintime end) is not null
and min(case stepname when '7900' then moveintime end) is not null
结果为:
问题:我怎样对intervalday 大于 4天的再次进行帅选?
在having中添加: having min(case stepname when '7110' then moveintime end) is not null
and min(case stepname when '7900' then moveintime end) is not null
and (min(case stepname when '7900' then moveintime end)-min(case stepname when '7110' then moveintime end))>4
不能用别名????
不知道为什么 tm1,tm2这里不能使用别名 ?
min(case stepname when '7900' then moveintime end) tm2,t.ProductionQTY,datediff(day,min(case stepname when '7110' then moveintime end),min(case stepname when '7900' then moveintime end)) as intervalday
from (
select a.workorder,min(a.moveintime) moveintime,a.stepname,b.productname,b.ProductionQTY
from WaferHistory a,workorder b
where
a.WorkOrder=b.WorkOrderID and
a.STEPNAME='7110' and a.MoveInTime>'2013-05-01 00:00:00'
group by a.workorder,a.stepname,b.productname ,b.ProductionQTY
union
select a.workorder,min(a.moveintime) moveintime,a.stepname,b.productname,b.ProductionQTY
from WaferHistory a,workorder b
where
a.WorkOrder=b.WorkOrderID and
a.STEPNAME='7900' and a.MoveInTime>'2013-05-01 00:00:00'
group by a.workorder,a.stepname,b.productname,b.ProductionQTY ) t
group by t.workorder,t.productname ,t.ProductionQTY
having min(case stepname when '7110' then moveintime end) is not null
and min(case stepname when '7900' then moveintime end) is not null
结果为:
问题:我怎样对intervalday 大于 4天的再次进行帅选?
在having中添加: having min(case stepname when '7110' then moveintime end) is not null
and min(case stepname when '7900' then moveintime end) is not null
and (min(case stepname when '7900' then moveintime end)-min(case stepname when '7110' then moveintime end))>4
不能用别名????
不知道为什么 tm1,tm2这里不能使用别名 ?
from
(
select t.workorder,t.productname,min(case stepname when '7110' then moveintime end) tm1,
min(case stepname when '7900' then moveintime end) tm2,
t.ProductionQTY,datediff(day,min(case stepname when '7110' then moveintime end),min(case stepname when '7900' then moveintime end)) as intervalday
from (
select a.workorder,min(a.moveintime) moveintime,a.stepname,b.productname,b.ProductionQTY
from WaferHistory a,workorder b
where
a.WorkOrder=b.WorkOrderID and
a.STEPNAME='7110' and a.MoveInTime>'2013-05-01 00:00:00'
group by a.workorder,a.stepname,b.productname ,b.ProductionQTY
union
select a.workorder,min(a.moveintime) moveintime,a.stepname,b.productname,b.ProductionQTY
from WaferHistory a,workorder b
where
a.WorkOrder=b.WorkOrderID and
a.STEPNAME='7900' and a.MoveInTime>'2013-05-01 00:00:00'
group by a.workorder,a.stepname,b.productname,b.ProductionQTY ) t
group by t.workorder,t.productname ,t.ProductionQTY
having min(case stepname when '7110' then moveintime end) is not null
and min(case stepname when '7900' then moveintime end) is not null
)t
where tm1 - tm2 > 4
(select t.workorder,t.productname,min(case stepname when '7110' then moveintime end) tm1,
min(case stepname when '7900' then moveintime end) tm2,t.ProductionQTY,
datediff(day,min(case stepname when '7110' then moveintime end),
min(case stepname when '7900' then moveintime end)) as intervalday
from (
select a.workorder,min(a.moveintime) moveintime,a.stepname,b.productname,b.ProductionQTY
from WaferHistory a,workorder b
where
a.WorkOrder=b.WorkOrderID and
a.STEPNAME='7110' and a.MoveInTime>'2013-05-01 00:00:00'
group by a.workorder,a.stepname,b.productname ,b.ProductionQTY
union
select a.workorder,min(a.moveintime) moveintime,a.stepname,b.productname,b.ProductionQTY
from WaferHistory a,workorder b
where
a.WorkOrder=b.WorkOrderID and
a.STEPNAME='7900' and a.MoveInTime>'2013-05-01 00:00:00'
group by a.workorder,a.stepname,b.productname,b.ProductionQTY ) t
group by t.workorder,t.productname ,t.ProductionQTY
having min(case stepname when '7110' then moveintime end) is not null
and min(case stepname when '7900' then moveintime end) is not null
)
select * from t where intervalday>4
(select t.workorder,t.productname,min(case stepname when '7110' then moveintime end) tm1,
min(case stepname when '7900' then moveintime end) tm2,t.ProductionQTY,
datediff(day,min(case stepname when '7110' then moveintime end),
min(case stepname when '7900' then moveintime end)) as intervalday
from (
select a.workorder,min(a.moveintime) moveintime,a.stepname,b.productname,b.ProductionQTY
from WaferHistory a,workorder b
where
a.WorkOrder=b.WorkOrderID and
a.STEPNAME='7110' and a.MoveInTime>'2013-05-01 00:00:00'
group by a.workorder,a.stepname,b.productname ,b.ProductionQTY
union
select a.workorder,min(a.moveintime) moveintime,a.stepname,b.productname,b.ProductionQTY
from WaferHistory a,workorder b
where
a.WorkOrder=b.WorkOrderID and
a.STEPNAME='7900' and a.MoveInTime>'2013-05-01 00:00:00'
group by a.workorder,a.stepname,b.productname,b.ProductionQTY ) t
group by t.workorder,t.productname ,t.ProductionQTY
having min(case stepname when '7110' then moveintime end) is not null
and min(case stepname when '7900' then moveintime end) is not null
)
select * from t where intervalday>4
总觉得这段代码很繁琐,,有能精简点,"优雅点"么?
t.productname,
min(case stepname when '7110' then moveintime end) tm1,
min(case stepname when '7900' then moveintime end) tm2,
t.ProductionQTY,
datediff(day,
min(case stepname when '7110' then moveintime end),
min(case stepname when '7900' then moveintime end)) as intervalday
from
(select a.workorder,min(a.moveintime) moveintime,a.stepname,b.productname,b.ProductionQTY
from WaferHistory a,workorder b
where a.WorkOrder=b.WorkOrderID and a.STEPNAME='7110' and a.MoveInTime>'2013-05-01 00:00:00'
group by a.workorder,a.stepname,b.productname ,b.ProductionQTY
union
select a.workorder,min(a.moveintime) moveintime,a.stepname,b.productname,b.ProductionQTY
from WaferHistory a,workorder b
where a.WorkOrder=b.WorkOrderID and a.STEPNAME='7900' and a.MoveInTime>'2013-05-01 00:00:00'
group by a.workorder,a.stepname,b.productname,b.ProductionQTY
) t
group by t.workorder,t.productname ,t.ProductionQTY
having min(case stepname when '7110' then moveintime end) is not null
and min(case stepname when '7900' then moveintime end) is not null
and datediff(day,
min(case stepname when '7110' then moveintime end),
min(case stepname when '7900' then moveintime end))>4