nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

agrigate columns

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

Find more posts tagged with

Comments

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: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT id,
absent,
MIN(date) AS start_date,
MAX(date) AS end_date
FROM your_table
GROUP BY id,
absent</pre>
 
 
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

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: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>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;</pre>
 
 
Then I wrote this query: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>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</pre>
 
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. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>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</pre>

qasmaoui

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="99524" data-time="1335166347" data-date="23 April 2012 - 12:32 AM">
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. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>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</pre></blockquote>
 
 
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 :-)