Arrangement of vehicle report in Excel

Question

1.00/5 (1 vote)

See more:

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

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.

Posted 14-Jan-13 19:54pm

Banerjee Tanmoy

Add a Solution

Comments

RDBurmon 25-Jan-13 1:39am

Explain with an example including some live data

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Banerjee Tanmoy · Accepted Answer · 2013-01-29T18:21:00

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

I am giving my solution in SQL Query

SQL

/******************************* 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

Arrangement of vehicle report in Excel

1 solution

Solution 1

Add your solution here

Preview 0

Arrangement of vehicle report in Excel

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us