Click here to Skip to main content
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 20: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 Mathew Soji 270
1 BillWoodruff 260
2 Sergey Alexandrovich Kryukov 240
3 OriginalGriff 236
4 Afzaal Ahmad Zeeshan 208
0 OriginalGriff 6,168
1 Sergey Alexandrovich Kryukov 5,853
2 DamithSL 5,028
3 Manas Bhardwaj 4,539
4 Maciej Los 3,845


Advertise | Privacy | Mobile
Web03 | 2.8.1411019.1 | Last Updated 30 Jan 2013
Copyright © CodeProject, 1999-2014
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