XinYu_LZMES/.svn/pristine/25/257a237d4da71c55147a9c9d1f3da0996935743b.svn-base

<?xml version="1.0" encoding='UTF-8'?>

<queryMap desc="班产">

<query id="UIC020220_01.SELECT" desc="热轧班产">
  <![CDATA[
  select to_char(to_date(t3.TIME_SLOT,'yyyymmdd:hh24'),'yyyymmdd-hh24') TIME_SLOT,
       t3.BLOCKS,
       t3.WEIGHT,
       t3.BREAKDOWN_TIME,
       case
         when 3600 - t3.BREAKDOWN_TIME = 0 then
          0
         else
          round(t3.WEIGHT / ((3600 - t3.BREAKDOWN_TIME) / 3600), 2)
       end HOUR_YIELD,
       case
         when 3600 - t3.BREAKDOWN_TIME = 0 then
          0
         else
          round(((3600 - t3.BREAKDOWN_TIME)/t3.BLOCKS ), 2)
       end AVERAGE_RHYTHM
  from (select substr(t2.MILL_DTIME, 0, 10) TIME_SLOT,
               count(*) BLOCKS,
               sum(t2.ACT_WGT) / 1000 WEIGHT,
               round(to_char((sum(t2.k) * 86400) + sum(t2.sjc))) BREAKDOWN_TIME
          from (select t1.MILL_DTIME,
                       t1.MILL_DTIME2,
                       t1.leadd,
                       t1.lagg,
                       case
                         when to_date(t1.leadd, 'YYYYMMDDHH24MISS') -
                              to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') > 0 and
                              ((to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                               to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')) +
                               (to_date(t1.Leadd, 'YYYYMMDDHH24MISS') -
                               to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS'))) *
                              86400 > 180 and t1.leadd is not null then
                          to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                          to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')
                         when (to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                              to_date(t1.Lagg, 'YYYYMMDDHH24MISS')) * 86400 >
                              3600.1 and
                              (to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS') -
                              to_date(t1.Lagg, 'YYYYMMDDHH24MISS')) * 86400 > 180 then
                          to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS') -
                          (to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                           numtodsinterval(1, 'hour'))
                         when t1.leadd is null and
                              (to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                              to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')) *
                              86400 > 180 then
                          to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                          to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')
                         when t1.lagg is null and
                              (to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS') -
                              (to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                              numtodsinterval(1, 'hour'))) * 86400 > 180 then
                          to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS') -
                          (to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                           numtodsinterval(1, 'hour'))
                         else
                          0
                       end k,
                       case
                         when substr(t1.Leadd, 0, 10) =
                              substr(t1.MILL_DTIME, 0, 10) and
                              (to_date(t1.Leadd, 'YYYYMMDDHH24MISS') -
                               to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')) *
                              86400 > 180 then
                          (to_date(t1.Leadd, 'YYYYMMDDHH24MISS') -
                          to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')) * 86400
                         else
                          0
                       end sjc,
                       t1.ACT_WGT
                  from (select t.MILL_DTIME,
                               substr(to_char(to_date(t.MILL_DTIME,
                                                      'YYYYMMDDHH24MISS') +
                                              numtodsinterval(1, 'hour'),
                                              'YYYYMMDDHH24MISS'),
                                      0,
                                      10) || '0000' MILL_DTIME2,
                               Lead(t.MILL_DTIME) OVER(ORDER BY t.MILL_DTIME) leadd,
                               lag(t.MILL_DTIME) OVER(ORDER BY t.MILL_DTIME) lagg,
                               t.ACT_WGT
                          from tbh02_coil_comm t
                         where substr(t.MILL_DTIME,0,8) between ? and ?
                         ORDER BY t.MILL_DTIME) t1) t2
         group by substr(t2.MILL_DTIME, 0, 10)
         order by substr(t2.MILL_DTIME, 0, 10)) t3

]]>
</query>

<query id="UIC020220_02.SELECT" desc="连退班产">
	<![CDATA[
	select to_char(to_date(t3.TIME_SLOT,'yyyymmdd:hh24'),'yyyymmdd-hh24') TIME_SLOT,
       t3.BLOCKS,
       t3.WEIGHT,
       t3.BREAKDOWN_TIME,
       case
         when 3600 - t3.BREAKDOWN_TIME = 0 then
          0
         else
          round(t3.WEIGHT / ((3600 - t3.BREAKDOWN_TIME) / 3600), 2)
       end HOUR_YIELD,
       case
         when 3600 - t3.BREAKDOWN_TIME = 0 then
          0
         else
          round(((3600 - t3.BREAKDOWN_TIME)/t3.BLOCKS ), 2)
       end AVERAGE_RHYTHM
  from (select substr(t2.MILL_DTIME, 0, 10) TIME_SLOT,
               count(*) BLOCKS,
               sum(t2.ACT_WGT) / 1000 WEIGHT,
               round(to_char((sum(t2.k) * 86400) + sum(t2.sjc))) BREAKDOWN_TIME
          from (select t1.MILL_DTIME,
                       t1.MILL_DTIME2,
                       t1.leadd,
                       t1.lagg,
                       case
                         when to_date(t1.leadd, 'YYYYMMDDHH24MISS') -
                              to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') > 0 and
                              ((to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                               to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')) +
                               (to_date(t1.Leadd, 'YYYYMMDDHH24MISS') -
                               to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS'))) *
                              86400 > 180 and t1.leadd is not null then
                          to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                          to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')
                         when (to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                              to_date(t1.Lagg, 'YYYYMMDDHH24MISS')) * 86400 >
                              3600.1 and
                              (to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS') -
                              to_date(t1.Lagg, 'YYYYMMDDHH24MISS')) * 86400 > 180 then
                          to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS') -
                          (to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                           numtodsinterval(1, 'hour'))
                         when t1.leadd is null and
                              (to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                              to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')) *
                              86400 > 180 then
                          to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                          to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')
                         when t1.lagg is null and
                              (to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS') -
                              (to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                              numtodsinterval(1, 'hour'))) * 86400 > 180 then
                          to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS') -
                          (to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                           numtodsinterval(1, 'hour'))
                         else
                          0
                       end k,
                       case
                         when substr(t1.Leadd, 0, 10) =
                              substr(t1.MILL_DTIME, 0, 10) and
                              (to_date(t1.Leadd, 'YYYYMMDDHH24MISS') -
                               to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')) *
                              86400 > 180 then
                          (to_date(t1.Leadd, 'YYYYMMDDHH24MISS') -
                          to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')) * 86400
                         else
                          0
                       end sjc,
                       t1.ACT_WGT
                  from (select t.MILL_DTIME,
                               substr(to_char(to_date(t.MILL_DTIME,
                                                      'YYYYMMDDHH24MISS') +
                                              numtodsinterval(1, 'hour'),
                                              'YYYYMMDDHH24MISS'),
                                      0,
                                      10) || '0000' MILL_DTIME2,
                               Lead(t.MILL_DTIME) OVER(ORDER BY t.MILL_DTIME) leadd,
                               lag(t.MILL_DTIME) OVER(ORDER BY t.MILL_DTIME) lagg,
                               t.ACT_WGT
                          from C_TBC02_COIL_COMM t
                         where t.LINE_TP = 'L'
                           and t.COIL_STAT in ('2', '3')
                           and t.DIVIDE_YN not in ('X', 'Y')
                           and substr(t.MILL_DTIME,0,8) between ?  and  ? 
                         ORDER BY t.MILL_DTIME) t1) t2
         group by substr(t2.MILL_DTIME, 0, 10)
         order by substr(t2.MILL_DTIME, 0, 10)) t3
	
]]>
</query>

<query id="UIC020220_03.SELECT" desc="酸扎班产">
	<![CDATA[
	select to_char(to_date(t3.TIME_SLOT,'yyyymmdd:hh24'),'yyyymmdd-hh24') TIME_SLOT,
       t3.BLOCKS,
       t3.WEIGHT,
       t3.BREAKDOWN_TIME,
       case
         when 3600 - t3.BREAKDOWN_TIME = 0 then
          0
         else
          round(t3.WEIGHT / ((3600 - t3.BREAKDOWN_TIME) / 3600), 2)
       end HOUR_YIELD,
       case
         when 3600 - t3.BREAKDOWN_TIME = 0 then
          0
         else
          round(((3600 - t3.BREAKDOWN_TIME)/t3.BLOCKS ), 2)
       end AVERAGE_RHYTHM
  from (select substr(t2.MILL_DTIME, 0, 10) TIME_SLOT,
               count(*) BLOCKS,
               sum(t2.ACT_WGT) / 1000 WEIGHT,
               round(to_char((sum(t2.k) * 86400) + sum(t2.sjc))) BREAKDOWN_TIME
          from (select t1.MILL_DTIME,
                       t1.MILL_DTIME2,
                       t1.leadd,
                       t1.lagg,
                       case
                         when to_date(t1.leadd, 'YYYYMMDDHH24MISS') -
                              to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') > 0 and
                              ((to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                               to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')) +
                               (to_date(t1.Leadd, 'YYYYMMDDHH24MISS') -
                               to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS'))) *
                              86400 > 180 and t1.leadd is not null then
                          to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                          to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')
                         when (to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                              to_date(t1.Lagg, 'YYYYMMDDHH24MISS')) * 86400 >
                              3600.1 and
                              (to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS') -
                              to_date(t1.Lagg, 'YYYYMMDDHH24MISS')) * 86400 > 180 then
                          to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS') -
                          (to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                           numtodsinterval(1, 'hour'))
                         when t1.leadd is null and
                              (to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                              to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')) *
                              86400 > 180 then
                          to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                          to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')
                         when t1.lagg is null and
                              (to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS') -
                              (to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                              numtodsinterval(1, 'hour'))) * 86400 > 180 then
                          to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS') -
                          (to_date(t1.MILL_DTIME2, 'YYYYMMDDHH24MISS') -
                           numtodsinterval(1, 'hour'))
                         else
                          0
                       end k,
                       case
                         when substr(t1.Leadd, 0, 10) =
                              substr(t1.MILL_DTIME, 0, 10) and
                              (to_date(t1.Leadd, 'YYYYMMDDHH24MISS') -
                               to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')) *
                              86400 > 180 then
                          (to_date(t1.Leadd, 'YYYYMMDDHH24MISS') -
                          to_date(t1.MILL_DTIME, 'YYYYMMDDHH24MISS')) * 86400
                         else
                          0
                       end sjc,
                       t1.ACT_WGT
                  from (select t.MILL_DTIME,
                               substr(to_char(to_date(t.MILL_DTIME,
                                                      'YYYYMMDDHH24MISS') +
                                              numtodsinterval(1, 'hour'),
                                              'YYYYMMDDHH24MISS'),
                                      0,
                                      10) || '0000' MILL_DTIME2,
                               Lead(t.MILL_DTIME) OVER(ORDER BY t.MILL_DTIME) leadd,
                               lag(t.MILL_DTIME) OVER(ORDER BY t.MILL_DTIME) lagg,
                               t.ACT_WGT
                          from C_TBL02_COIL_COMM t
                         where substr(t.MILL_DTIME,0,8) between ?  and  ? 
                         ORDER BY t.MILL_DTIME) t1) t2
         group by substr(t2.MILL_DTIME, 0, 10)
         order by substr(t2.MILL_DTIME, 0, 10)) t3
	
]]>
</query>

</queryMap>