Click here to Skip to main content
11,641,087 members (69,263 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Oracle 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

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
Comments
Rahul Dhoble at 25-Jan-13 1:39am
   
Explain with an example including some live data

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  

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

  Print Answers RSS
0 Mika Wendelius 400
1 OriginalGriff 205
2 DamithSL 203
3 CPallini 185
4 Afzaal Ahmad Zeeshan 129
0 Mika Wendelius 400
1 OriginalGriff 205
2 DamithSL 203
3 CPallini 185
4 Afzaal Ahmad Zeeshan 129


Advertise | Privacy | Mobile
Web01 | 2.8.150731.1 | Last Updated 30 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100