ORACLE will point data into a period of time-CodePudding

PC E D
Work 5 2016-09-01
Work 6 2016-09-31
The male 5 2016-09-01
The male 2017-09-31

Part of the data above, join query start date is 2016-09-01, end date 2016-10-07

Hope the above data into
PC E S N
Work 5 2016-09-01 2016-09-31
Work 6 2016-09-31 2016-10-07
The male 5 2016-09-01 2016-10-07

CodePudding user response:

Don't understand, the original poster said the rules in detail,

CodePudding user response:

reference 1st floor selling fruit net reply:

didn't understand, the original poster said the rules in detail,

is the data, it is a point in time on September 1, September 31 there are two data about PC for work, now I want to turn her into September 1st to September 31, PC for data for what values of e, 31st PC data for workers back in September, the value of e for how much, this time I'll become a range of display

CodePudding user response:

refer to the second floor qq_40742753 response:

Quote: refer to 1st floor selling fruit net reply:
didn't understand, the original poster said the rules in detail,

is the data, it is a point in time on September 1, September 31 there are two data about PC for work, now I want to turn her into September 1st to September 31, PC for data for what values of e, 31st PC data for workers back in September, the value of e for how much, this time I'll become a range of display

Male 6 2017-09-31 how to deal with the data?

CodePudding user response:

reference 3 floor springs of water faint reply:

Quote: refer to the second floor qq_40742753 response:

Quote: refer to 1st floor selling fruit net reply:
didn't understand, the original poster said the rules in detail,

is the data, it is a point in time on September 1, September 31 there are two data about PC for work, now I want to turn her into September 1st to September 31, PC for data for what values of e, 31st PC data for workers back in September, the value of e for how much, this time I'll become a range of display

Male 6 2017-09-31 how to deal with the data?

the data by querying the dates have been filtered out, only the above three data

CodePudding user response:

You study the lag and lead the two functions, cooperate with the NVL function, achieve your requirements, there is a problem to ask again,

CodePudding user response:

September no 31 ~

CodePudding user response:

reference 5 floor selling fruit net reply:

I study the lag and lead these two functions, cooperate with the NVL function, achieve your requirements, there is a problem to ask again,

there is a problem, when the PC is different, the data of different PC together

CodePudding user response:

reference 5 floor selling fruit net reply:

I study the lag and lead these two functions, cooperate with the NVL function, achieve your requirements, there is a problem to ask again,

is when down to find the data using lead function will spell PC among different data

CodePudding user response:

Try this:

 
SELECT the PC, E, CASE WHEN TO_DATE (' 2016-09-01 ', '- DD YYYY - MM) & gt;=D THEN TO_DATE (' 2016-09-01 ', '- DD YYYY - MM) END S, NVL (LEAD (D, 1) OVER (PARTITION BY PC ORDER BY D), TO_DATE (' 2016-10-07', '- DD YYYY - MM)) AS D FROM TMP 
WHERE D<=TO_DATE (' 2016-10-07 ', '- DD YYYY - MM) 
The ORDER BY PC, S;

CodePudding user response:

Under the simplified:

 
SELECT the PC, E, D AS S, NVL (LEAD (D, 1) OVER (PARTITION BY PC ORDER BY D), TO_DATE (' 2016-10-07 ', '- DD YYYY - MM)) AS N the FROM TMP WHERE D & lt;=TO_DATE (' 2016-10-07 ', '- DD YYYY - MM) 
The ORDER BY PC, S;

The TMP replace ~ with you the name of the table

CodePudding user response:

Last reiterated, in September 31,
You change it to September 30,

Note: the above my statement, field D for the DATE type,

CodePudding user response:

11 references, faint blue water fountain response:

last reiterated, in September 31,
You change it to September 30,

Note: the above my statement, field D for the DATE type,

ok, I see the partition by has solved the problem

CodePudding user response:

reference 5 floor selling fruit net reply:

I study the lag and lead these two functions, cooperate with the NVL function, achieve your requirements, there is a problem to ask again,

problem solved, thank you

CodePudding user response:

refer to 12 floor qq_40742753 reply:

Quote: reference 11 floor springs of water faint reply:
last reiterated, in September 31,
You change it to September 30,

Note: the above my statement, field D for the DATE type,

ok, I see the partition by has solved the problem

Can knot stick!