What Lynn has been trying to ask you so we can answer your question is the following:

1) What do your tables TABLE_LONG and TABLE_SHORT look like, i.e. the Data Definition Language (DDL) statements to create the tables.

2) Give us some input data that represents what you may encounter in your own data set (no confidential stuff please)

3) Give us the expected results given the above two, plus most importantly

4) Given the above, how do you get from point 2 to point 3, in other words, what is the exact algorithm to get there. That's what we call a "Business Specification".

Just one other thing, make sure that your test data (in point 2) represents all kinds of permutations you have to cater for, including edge cases. Think about your business cases so you don't leave any weird things that might happen but are "unlikely" out, because given Murphy's Law, they will bite you in the A at some point in time, trust me, I'm experienced enough to know.

So, after lots of talking, I'll help you along with points one and two:

1) DDL statement example

CREATE TABLE TABLE_LONG

(

DATE_START DATE NOT NULL,

idDGIG VARCHAR ( 10 ) NOT NULL

)

and whatever columns you have, with the correct datatypes, for all the tables that are required to solve your issue.

2) Test Data example

INSERT INTO TABLE_LONG ( DATE_START, idDGIG )

SELECT '2013-01-01', 'QMThisIsQM1' UNION ALL

SELECT '2013-01-17', 'QIThisIsQ1' UNION ALL

SELECT '2012-12-13', 'QOMaybe' UNION ALL

SELECT '2016-09-16', 'QS'

And that for all the tables you have in your problem

Points 3 and 4 you have to really supply by yourself.

BTW, I'm not meaning to offend you in any way, but you need to help us in order to help you. Guessing on our side doesn't get anyone anywhere. So please?