在Excel中安排车辆报告 [英] Arrangement of vehicle report in Excel

问题描述

亲爱的所有专家



我在使用excel报告时遇到问题。



我的问题是：



我有一个excel文件，其中列是

Dear All Experts

I have a problem regarding making a report in excel.

My problem is :

I have a excel file where columns are

Sno Date. From  To Mode_of_Transport Depart. Arrival Name  Remarks

所以我的工作是找到30分钟到达的人，这样车辆就可以了安排给他们。



我必须在Excel中这样做但如果需要我可以使用Oracle



请帮助我



如果需要更多信息，请随时联系。

So my job is to find the persons who arrived at interval of 30 min so that vehicle can be arranged for them.

I have to do it in Excel but if needed I can use Oracle

Please help me

If any more information needed please feel free to contact.

推荐答案

由SQL解决在Oracle中查询但在Excel中未能这样做



我在SQL查询中提供我的解决方案

Solved by SQL query in Oracle but failed to do so in Excel

I am giving my solution in SQL Query

/******************************* This is for minutes break *******************************************************/
  Select (CASE
           WHEN (cast(to_char(arrivaltime, 'mi') as int) < 30) THEN
            (to_char(arrivaltime, 'hh:') || '00' || to_char(arrivaltime, ' PM') ||
            ' - ' || to_char(arrivaltime, 'hh:') || '29' ||
            to_char(arrivaltime, ' PM'))
           ELSE
            (to_char(arrivaltime, 'hh:') || '29' || ' ' ||
            to_char(arrivaltime, ' PM') || ' - ' ||
            to_char(arrivaltime + interval '1' hour, 'hh:') || '00' ||
            to_char(arrivaltime, ' PM'))
         END) ServiceTime,
         ArrivalDate,
         name,
         count(*) ServicedPeople
    From (select sysdate as arrivaltime,'test' as name,trunc(sysdate) as ArrivalDate from dual)
   group by (CASE
              WHEN (cast(to_char(arrivaltime, 'mi') as int) < 30) THEN
               (to_char(arrivaltime, 'hh:') || '00' || to_char(arrivaltime, ' PM') ||
               ' - ' || to_char(arrivaltime, 'hh:') || '29' ||
               to_char(arrivaltime, ' PM'))
              ELSE
               (to_char(arrivaltime, 'hh:') || '29' || ' ' ||
               to_char(arrivaltime, ' PM') || ' - ' ||
               to_char(arrivaltime + interval '1' hour, 'hh:') || '00' ||
               to_char(arrivaltime, ' PM'))
            END, ArrivalDate, name)
   order by ServiceTime

/**************************** This is for Hour Break *******************/
Select (to_char(arrivaltime, 'hh:') || '00' || ' ' || to_char(arrivaltime, ' PM') ||
       ' - ' || to_char(arrivaltime + interval '2' hour, 'hh:') || '00' ||
       to_char(arrivaltime, ' PM')) ServiceTime,
       ArrivalDate,
       name,
       count(*) ServicedPeople
  From (select sysdate as arrivaltime,'test' as name,trunc(sysdate) as ArrivalDate from dual)
 group by (to_char(arrivaltime, 'hh:') || '00' || ' ' ||
          to_char(arrivaltime, ' PM') || ' - ' ||
          to_char(arrivaltime + interval '2' hour, 'hh:') || '00' ||
          to_char(arrivaltime, ' PM'), ArrivalDate, name)
 order by ArrivalDate, ServiceTime

在表单条款中，我使用了

In the form clause I have used

select '''','''','''' from dual union select '''','''','''' from dual

等等。



希望这能帮到别人

谢谢大家

and so on.

Hope this help others
Thank You all

这篇关于在Excel中安排车辆报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！