Home » SQL & PL/SQL » SQL & PL/SQL » consecutive dates
consecutive dates [message #448522] Tue, 23 March 2010 23:50 Go to next message
ellehcnohm
Messages: 8
Registered: March 2010
Location: philippines
Junior Member

SELECT DISTINCT a.emp_id, a.cal_id, TO_CHAR(a.ts_date, 'DD/MM/YYYY') tsdate, a.ts_date, 1 as days
FROM tmsh_timesheet a
INNER JOIN project b ON TO_CHAR(b.proj_id) = a.proj_id
INNER JOIN tmsh_ts_calendar c ON c.cal_id = a.cal_id
INNER JOIN (SELECT a.cal_id, a.emp_id, MAX(a.status) as status, a.create_dt, a.create_by FROM tmsh_stat_hist a
INNER JOIN tmsh_timesheet b ON a.emp_id = b.emp_id AND a.cal_id = b.cal_id
WHERE a.create_dt = (SELECT MAX(create_dt) FROM tmsh_stat_hist WHERE emp_id = b.emp_id AND cal_id = b.cal_id)
GROUP BY a.cal_id, a.emp_id, a.create_dt, a.create_by) e
ON e.emp_id = a.emp_id AND e.cal_id = a.cal_id
WHERE e.status IN (3,4,5,7,8,9) AND a.master_cd = 'DPI' AND a.emp_id = '04828'
ORDER BY a.ts_date


this query results

EMP_ID CAL_ID TSDATE

04828 34 04/10/2010
04828 35 02/11/2010
04828 35 03/11/2010
04828 35 04/11/2010
04828 35 08/11/2010

i need the ts date to be in like this

04/10/2010
02/11/2010 - 04/11/2010
08/11/2010

pls help..

Re: consecutive dates [message #448523 is a reply to message #448522] Tue, 23 March 2010 23:52 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
1/ Please follow Posting Guidelines - OraFAQ Forum Guide
2/ Please provided DDL (CREATE TABLE ......) for ALL tables involved.
3/ Please provided DML (INSERT INTO .......) for Test data.
4/ Please provided expected/desired results.
5/ Please Preview Message / Spell-Check Before Posting.
Re: consecutive dates [message #448530 is a reply to message #448523] Wed, 24 March 2010 00:33 Go to previous messageGo to next message
ellehcnohm
Messages: 8
Registered: March 2010
Location: philippines
Junior Member

is my post wrong???
Re: consecutive dates [message #448531 is a reply to message #448530] Wed, 24 March 2010 00:36 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>is my post wrong???
Not wrong; just incomplete


Please realize that we don't have your tables & we don't have your data.
Therefore we can't run, test or improve your posted SQL.
Re: consecutive dates [message #448540 is a reply to message #448522] Wed, 24 March 2010 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See how I did it for numbers in http://www.orafaq.com/forum/t/156187/102589/ a nd come back if you can't achieve it.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Updated on: Wed, 24 March 2010 00:48]

Report message to a moderator

Re: consecutive dates [message #448560 is a reply to message #448531] Wed, 24 March 2010 01:35 Go to previous messageGo to next message
ellehcnohm
Messages: 8
Registered: March 2010
Location: philippines
Junior Member

yup i know and i'm sorry but the table is so big so i cant post all the tables needed.

i want to know how can i make the sample date below
if consecutive date should be like
02/11/2010 - 04/11/2010


sample date
04/10/2010
02/11/2010 - 04/11/2010
08/11/2010

thanks
Re: consecutive dates [message #448562 is a reply to message #448540] Wed, 24 March 2010 01:44 Go to previous messageGo to next message
ellehcnohm
Messages: 8
Registered: March 2010
Location: philippines
Junior Member

thanks for the help but i want to be like this
at the same column.
02/11/2010 - 04/11/2010
Re: consecutive dates [message #448566 is a reply to message #448560] Wed, 24 March 2010 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
yup i know and i'm sorry but the table is so big so i cant post all the tables needed.

This is not what is asked. What is asked is a test case, you can create a table and fill with the data you posted, you have to post your actual tables only something that represents your question.

What did you try from the link I posted?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand
Re: consecutive dates [message #448573 is a reply to message #448566] Wed, 24 March 2010 02:14 Go to previous messageGo to next message
ellehcnohm
Messages: 8
Registered: March 2010
Location: philippines
Junior Member

Oracle Home: OraDb10g_home2(version: 10.2.0.1.0)


I didnt try it i just look in to it, sorry i'm not good with the terminology, and i'm newbie in oracle query, i don't understand that much what you want to do.

CREATE TABLE TMSH_TIMESHEET
(
CAL_ID NUMBER(10),
EMP_ID VARCHAR2(10 BYTE),
TS_DATE DATE

)

Insert into TMSH_TIMESHEET
(CAL_ID,EMP_ID, TS_DATE )
Values
('35', 04828, 11/3/2010)
Insert into TMSH_TIMESHEET
(CAL_ID,EMP_ID, TS_DATE )
Values
('35', 04828, 11/2/2010);
Insert into TMSH_TIMESHEET
(CAL_ID,EMP_ID, TS_DATE )
Values
('35', 04828, 11/4/2010);
Insert into TMSH_TIMESHEET
(CAL_ID,EMP_ID, TS_DATE )
Values
('35', 04828, 10/4/2010);
Insert into TMSH_TIMESHEET
(CAL_ID,EMP_ID, TS_DATE )
Values
('35', 04828, 11/18/2010);

is that ok as test case?
Re: consecutive dates [message #448576 is a reply to message #448573] Wed, 24 March 2010 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is that ok as test case?

Except that wa have to modify it to make it work (no blank in a statement, a statement ends with / or ;), it is ok.

Quote:
What did you try from the link I posted?



Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

Edited by LF: disabled smilies in this post so that ;) doesn't show up as a wink

[Updated on: Wed, 24 March 2010 05:26] by Moderator

Report message to a moderator

Re: consecutive dates [message #448580 is a reply to message #448576] Wed, 24 March 2010 02:35 Go to previous messageGo to next message
ellehcnohm
Messages: 8
Registered: March 2010
Location: philippines
Junior Member

:blush: so can you help me ?

"Except that wa have to modify it to make it work (no blank in a statement, a statement ends with / or Wink, it is ok."

what do you mean with
"(no blank in a statement, a statement ends with / or Wink"

thanks,
mhonzh
Re: consecutive dates [message #448591 is a reply to message #448580] Wed, 24 March 2010 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what do you mean with
"(no blank in a statement, a statement ends with / or Wink"

It was not wink but ";" followed by ")" which have been interpreted by forum engine.

"no blank line in a statement": you CREATE TABLE statement contains a blank line (sorry miss this word), SQL*Plus does not like it.

" statement ends with / or ;": your INSERT statements have no end.

Quote:
so can you help me ?

Yes, I can, can you help you and try the link I posted?
Read my signature.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand
Re: consecutive dates [message #448606 is a reply to message #448591] Wed, 24 March 2010 05:12 Go to previous messageGo to next message
ellehcnohm
Messages: 8
Registered: March 2010
Location: philippines
Junior Member

i tried the link you posted

MIN(BOOK_NO) FROM_NO TO_NO MIN(STATUS)

B1 101 102 BOOKED
B1 103 104
B1 105 108 BOOKED
B1 109 110
B2 111 120


i need something like consecutive dates in one column
based on the sample what i want the output to be

example the from_no have 101,102,103,105,107,108

so the output of from no should be

from_no

101-103
105
107-108
Re: consecutive dates [message #448612 is a reply to message #448606] Wed, 24 March 2010 05:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What you need is some way to group consecutive rows together.

There is a very neat trick that you can do with analytic functions to do this.
The key to it is this - the consecutive rows have a value that goes up by 1 each row. If you use the row_number function, this can give each row in the set a value that goes up by 1 each row as well.
So, if you subtract the row_number from the row's value, then for each set of consecutive rows, you get a unique number - each row in the set has the same number and no other row gets the number.

Something like this:
select col_1
      ,col_1 - row_number() over (order by col_1) grp
from (select column_value col_1 
      from   table(ty_num_tab(101,102,103,105,107,108))
      );


You may be able to work out the solution from there yourself - it can involve the MIN & MAX, or FIRST_VALUE and LAST_VALUE analytic functions.

If you need more help, just ask (but remember - @Michel's sig is absolutely true)
Re: consecutive dates [message #448707 is a reply to message #448612] Wed, 24 March 2010 21:24 Go to previous messageGo to next message
ellehcnohm
Messages: 8
Registered: March 2010
Location: philippines
Junior Member

yah thanks alot, but still have 1 one question Embarassed

in the query

select col_1
,col_1 - row_number() over (order by col_1) grp
from (select column_value col_1
from table(ty_num_tab(101,102,103,105,107,108))
);

i don't get the ty_num_tab? this is not function right? so what it is, an alias???

thanks,
mhonzh
Re: consecutive dates [message #448721 is a reply to message #448707] Thu, 25 March 2010 00:25 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a type that simulates a real table.

Once more read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Updated on: Thu, 25 March 2010 00:26]

Report message to a moderator

Re: consecutive dates [message #448766 is a reply to message #448707] Thu, 25 March 2010 02:31 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Sorry - I missed that line from the script:
CREATE OR REPLACE TYPE ty_num_tab AS TABLE OF number;
/
Previous Topic: column wise Data after wmsys.wm_concat
Next Topic: extract only number part of column
Goto Forum:
  


Current Time: Thu Sep 29 07:22:40 CDT 2016

Total time taken to generate the page: 0.08820 seconds