[qasmaoui]

in the example i want to regroupe the columns in one and instand of multiple date for the same ID i want to have a start_date and end_date


thnks

Attached File(s)

•  exemple.png (9.27K)
  Number of downloads: 0

[qasmaoui]

No Help ??? is it tooo HArd ??? i'm just a beginner

[Hans_vd]

Hi qasmaoui,

If the data comes from a database, you could do it in the query:

SELECT id,
       absent,
       MIN(date) AS start_date,
       MAX(date) AS end_date
FROM   your_table
GROUP  BY id,
          absent

Or you could select all records and create a table grouping on columns ID and absent and create a MIN aggregation and a MAX aggregation in the header row. You can remove or hide the detail row.

Hope this helps
Hans

[qasmaoui]

thanks Hans

i have already try it it
but the problem is
if i use your query

lets have this example

the problem is that the query search for the max for all the date but me i want the max of successive dates only as shown in the example

SO MORE HELP PLEAZ

Attached File(s)

•  exemple3.png (15.2K)
  Number of downloads: 0

[Hans_vd]

Hi,

If your database supports analytical functions (i tested in an Oracle database), you could write the query like this:

First I recreated your table:

CREATE TABLE your_table (
   ID NUMBER(9),
   absent VARCHAR2(5),
   date_field DATE
);

INSERT INTO your_table (ID, absent, date_field) VALUES (11, 'oui', to_date('12022012', 'ddmmyyyy'));
INSERT INTO your_table (ID, absent, date_field) VALUES (11, 'oui', to_date('13022012', 'ddmmyyyy'));
INSERT INTO your_table (ID, absent, date_field) VALUES (11, 'oui', to_date('14022012', 'ddmmyyyy'));
INSERT INTO your_table (ID, absent, date_field) VALUES (11, 'oui', to_date('15022012', 'ddmmyyyy'));
INSERT INTO your_table (ID, absent, date_field) VALUES (11, 'oui', to_date('25022012', 'ddmmyyyy'));
INSERT INTO your_table (ID, absent, date_field) VALUES (12, 'oui', to_date('12022012', 'ddmmyyyy'));
INSERT INTO your_table (ID, absent, date_field) VALUES (12, 'oui', to_date('13022012', 'ddmmyyyy'));
INSERT INTO your_table (ID, absent, date_field) VALUES (12, 'oui', to_date('14022012', 'ddmmyyyy'));

COMMIT;

Then I wrote this query:

WITH succesive_marker_date AS (
   SELECT id,
          absent,
          date_field,
          date_field + row_number () over (ORDER BY id, absent, date_field DESC) marker_date
   FROM   your_table
   ORDER  BY ID, date_field
)
SELECT ID,
       absent,
       MIN (date_field) start_date,
       MAX (date_field) end_date
FROM   succesive_marker_date
GROUP  BY ID, absent, marker_date;


        ID ABSENT               START_DATE END_DATE
---------- -------------------- ---------- ----------
        11 oui                  12-02-2012 15-02-2012
        11 oui                  25-02-2012 25-02-2012
        12 oui                  12-02-2012 14-02-2012

Hope it helps

[Hans_vd]

Hi Qasmaoui,

I took a second look at it and here is a version of the query that has no with clause and also no analytical function (both are not supported in every database)

The ROWNUM function in Oracle just generates a row number. I'm sure that every possible database has a fuction that does that.

SELECT ID,
       absent,
       MIN (date_field) start_date,
       MAX (date_field) end_date
FROM   (SELECT id,
               absent,
               date_field,
               date_field + ROWNUM * -1 AS marker_date
        FROM   your_table
       )
GROUP  BY ID, absent, marker_date

[qasmaoui]

Hans_vd, on 23 April 2012 - 12:32 AM, said:

Hi Qasmaoui,

I took a second look at it and here is a version of the query that has no with clause and also no analytical function (both are not supported in every database)

The ROWNUM function in Oracle just generates a row number. I'm sure that every possible database has a fuction that does that.

SELECT ID,
       absent,
       MIN (date_field) start_date,
       MAX (date_field) end_date
FROM   (SELECT id,
               absent,
               date_field,
               date_field + ROWNUM * -1 AS marker_date
        FROM   your_table
       )
GROUP  BY ID, absent, marker_date

Hans you'r fisrt answer( with the with clause ) worked for me vry wellll thanks a lot man it's awsome

Great thanks ( i just adapted it for me )

[Hans_vd]

Glad I could help.

And it is a nice piece of SQL, isn't it :-)