OraFAQ Forum: SQL & PL/SQL » Help needs to get the SQL Query...........

Home » SQL & PL/SQL » SQL & PL/SQL » Help needs to get the SQL Query...........

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help needs to get the SQL Query........... [message #6968]

Wed, 14 May 2003 03:06

BABU SRSB
Messages: 42
Registered: June 2002

Member

Hi All,
can any one help me to get the below SQL Query.The table below belongs to the data of transport company.

- DESC TRANSPORT -

- DATE_REPORT - - DATE -

- OWNER - - VARCHAR2(50) -

- VAGON - - VARCHAR2(50) -

- SHIFTTIME - - VARCHAR2(4)

SELECT * FROM TRANSPORT

- DATE_REPORT - - OWNER - - VAGAON - - SHIFTTIME -

- 01-APR-03 - - OWNER A - - VAG1 - - 0800 -

- 01-APR-03 - - OWNER A - - VAG1 - - 1400 -

- 01-APR-03 - - OWNER A - - VAG1 - - 2000 -

- 01-APR-03 - - OWNER A - - VAG2 - - 2000 -

- 01-APR-03 - - OWNER A - - VAG2 - - 2030 -

- 01-APR-03 - - OWNER A - - VAG2 - - 2130 -

- 01-APR-03 - - OWNER B - - VAG1 - - 2030 -

- 01-APR-03 - - OWNER B - - VAG1 - - 2200 -

- 01-APR-03 - - OWNER B - - VAG1 - - 0030 (Showing next day, but still count in same day) -

- 01-APR-03 - - OWNER B - - VAG2 - - 2100 -

- 01-APR-03 - - OWNER B - - VAG2 - - 0000 (Showing next day, but still count in same day) -

- 01-APR-03 - - OWNER B - - VAG2 - - 0330 (Showing next day, but still count in same day) -

- 01-APR-03 - - OWNER C - - VAG1 - - 1400 -

- 01-APR-03 - - OWNER C - - VAG1 - - 2000 -

- 01-APR-03 - - OWNER C - - VAG1 - - 2330 -

- 01-APR-03 - - OWNER C - - VAG2 - - 2130 -

- 01-APR-03 - - OWNER C - - VAG2 - - 2330 -

- 01-APR-03 - - OWNER C - - VAG2 - - 0200 (Showing next day, but still count in same day) -

- 02-APR-03 - - OWNER A - - VAG1 - - 1600 -

- 02-APR-03 - - OWNER A - - VAG1 - - 1900 -

- 02-APR-03 - - OWNER A - - VAG1 - - 2300 -

- 02-APR-03 - - OWNER A - - VAG2 - - 2030 -

- 02-APR-03 - - OWNER A - - VAG2 - - 2300 -

- - 02-APR-03 - - OWNER A - - VAG2 - - 0400 (Showing next day, but still count in same day) -

- 02-APR-03 - - OWNER B - - VAG1 - - 2000 -

- 02-APR-03 - - OWNER B - - VAG1 - - 2200 -

- 02-APR-03 - - OWNER B - - VAG1 - - 0330 -

- 02-APR-03 - - OWNER B - - VAG2 - - 0900 -

- 02-APR-03 - - OWNER B - - VAG2 - - 2100 -

- 02-APR-03 - - OWNER B - - VAG2 - - 0500 (Showing next day, but still count in same day) -

- - 02-APR-03 - - OWNER C - - VAG1 - - 1500 -

- 02-APR-03 - - OWNER C - - VAG1 - - 2100 -

- 02-APR-03 - - OWNER C - - VAG1 - - 0130 (Showing next day, but still count in same day) -

- 02-APR-03 - - OWNER C - - VAG2 - - 2100 -

- 02-APR-03 - - OWNER c - - VAG2 - - 0300 (Showing next day, but still count in same day) -

- 02-APR-03 - - OWNER c - - VAG2 - - 0600 (Showing next day, but still count in same day)

The Report format:

- Date_report - - OWNER - - VAGON - - FIRST_SHIFT -

- 01-APR-03 - - OWNER A - - VAG1 - - 0800 -

- 01-APR-03 - - OWNER A - - VAG2 - - 2000 -

- 01-APR-03 - - OWNER B - - VAG1 - - 2030 -

- 01-APR-03 - - OWNER B - - VAG2 - - 2100 -

- 01-APR-03 - - OWNER C - - VAG1 - - 1400 -

- 01-APR-03 - - OWNER C - - VAG2 - - 2130 -

- 02-APR-03 - - OWNER A - - VAG1 - - 1600 -

- 02-APR-03 - - OWNER A - - VAG2 - - 2030 -

- 02-APR-03 - - OWNER B - - VAG1 - - 2000 -

- 02-APR-03 - - OWNER B - - VAG2 - - 0900 -

- 02-APR-03 - - OWNER C - - VAG1 - - 1500 -

- 02-APR-03 - - OWNER C - - VAG2 - - 2100

Awaiting your valuble responses.

Regards,

Babu SRSB

Report message to a moderator

Re: Help needs to get the SQL Query........... [message #6983 is a reply to message #6968]

Wed, 14 May 2003 12:41

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Simple aggregate function query:

select date_report, owner, vagon, min(shifttime) first_shift
  from transport
 group by date_report, owner, vagon
 order by date_report, owner, vagon;

Report message to a moderator

Re: Help needs to get the SQL Query........... [message #7015 is a reply to message #6983]

Thu, 15 May 2003 07:41

BABU SRSB
Messages: 42
Registered: June 2002

Member

Hi,
Thanks for your quick reply,

But the above once is not working. The shifttimes i am showing with marked are in next day, but they are representing the previous day in the table.

So, when ever vagon will come, that vagon day will start from that shift onwards. That vagon day will finish, when ever, the first shifttime in will cross.

So, ever after midnight of the vagon will shoow the previous date only, because of the constraint.

If i am firing the Query you provided given, is considering the shifttime, instead of the above constarint.

Can anyone suggest the better one.

Yours,

Babu.

Report message to a moderator

Previous Topic:	long sql
Next Topic:	SQL and PL/SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 03:52:06 CDT 2024