select td_line,td_part,CONVERT(integer,datediff(n,td_Createtime ,getdate())/pt_machinetime) as toNow_qty, td_date,pt_yield
FROM today_Model a inner join pt_mstr b on a.td_sbu=b.pt_sbu and a.td_part=b.pt_part
where td_sbu='SBU4' AND td_date='12/4/2007'
AND td_updatetime = (select max(td_updatetime) from today_Model c where c.td_sbu=a.td_sbu and c.td_line=a.td_line)
AND td_line='SMTM3' AND td_part='r3493-a'出来记录:
td_line td_part toNow_qty td_date pt_yield
SMTM3 R3493-A 579 2007-12-04 00:00:00.000 98.5现在有下面情况需修改上面的SQL语句.
1.td_Createtime < 2007-12-04 07:10:00.000 时
2007-12-04 11:50:00.000>= getdate() > 2007-12-04 07:10:00.000,上面的时间差为getdate() - 2007-12-04 07:10:00.000 ;
2007-12-04 18:10:00.000>= getdate() > 2007-12-04 12:50:00.000,上面的时间差为getdate() - 2007-12-04 07:10:00.000 -1。
2.2007-12-04 11:50:00.000>td_Createtime >= 2007-12-04 07:10:00.000 时
2007-12-04 11:50:00.000>= getdate() > 2007-12-04 07:10:00.000,上面的时间差为getdate() - td_Createtime 值不变;
2007-12-04 18:10:00.000>= getdate() > 2007-12-04 12:50:00.000,上面的时间差为getdate() - td_Createtime -1。
3.td_Createtime >= 2007-12-04 12:50:00.000 时
2007-12-04 18:10:00.000>= getdate() > 2007-12-04 12:50:00.000,上面的时间差为getdate() - td_Createtime 值不变。
FROM today_Model a inner join pt_mstr b on a.td_sbu=b.pt_sbu and a.td_part=b.pt_part
where td_sbu='SBU4' AND td_date='12/4/2007'
AND td_updatetime = (select max(td_updatetime) from today_Model c where c.td_sbu=a.td_sbu and c.td_line=a.td_line)
AND td_line='SMTM3' AND td_part='r3493-a'出来记录:
td_line td_part toNow_qty td_date pt_yield
SMTM3 R3493-A 579 2007-12-04 00:00:00.000 98.5现在有下面情况需修改上面的SQL语句.
1.td_Createtime < 2007-12-04 07:10:00.000 时
2007-12-04 11:50:00.000>= getdate() > 2007-12-04 07:10:00.000,上面的时间差为getdate() - 2007-12-04 07:10:00.000 ;
2007-12-04 18:10:00.000>= getdate() > 2007-12-04 12:50:00.000,上面的时间差为getdate() - 2007-12-04 07:10:00.000 -1。
2.2007-12-04 11:50:00.000>td_Createtime >= 2007-12-04 07:10:00.000 时
2007-12-04 11:50:00.000>= getdate() > 2007-12-04 07:10:00.000,上面的时间差为getdate() - td_Createtime 值不变;
2007-12-04 18:10:00.000>= getdate() > 2007-12-04 12:50:00.000,上面的时间差为getdate() - td_Createtime -1。
3.td_Createtime >= 2007-12-04 12:50:00.000 时
2007-12-04 18:10:00.000>= getdate() > 2007-12-04 12:50:00.000,上面的时间差为getdate() - td_Createtime 值不变。
select case when 条件1 then 值1 when 条件2 then 值2 when 条件3 then 值3 end 字段名 from tablename
select case when 条件1 then 值1 when 条件2 then 值2 when 条件3 then 值3 end 字段名 from tablename