Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

A complex Oracle PL/SQL query #2

Please refer to this question if needed as this is a continuation of that:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_27838468.html

for vColKey in
(
     select ColKey
     from
     (
      select colkey, sum(colcount) colcount from
      (select
        colkey
       ,colstartdt
       ,colenddt
       ,case when lead(colstartdt) over (partition by colkey order by colstartdt) > colenddt
             then 0
             else 1
        end colcount
       from yourtable)
      group by colkey
      order by colkey
     )
     where ColCount>1
)
loop
      select min(ColStartDt) into vMinColStartDt
      from tblMyTable where ColKey=vColKey;

       for vColPk in
      (
       select ColPk
         from tblMyTable
       order by ColKey, ColStartDt
      )
      loop
           update tblMyTable
              set ColStartDt=vMinColStartDt + cur_rownum-1 * 3,
                  ColEndDt=ColStartDt+3-1/(24*3600)
            where ColPK=vColPk;
      end loop;
end loop;

For the first row, ColStartDt will be vMinColStartDt as cur_rownum will be 1. For the next row, ColStartDt will be exactly 3 days greater than previous row's ColStartDt. IMPORTANT: the updates should happen in increasing ColStartDt order.

I don't want to write an UPDATE statement for every line.

What would be the right SQL for this?
0
soccerplayer
Asked:
soccerplayer
  • 5
  • 4
1 Solution
 
lwadwellCommented:
It might be better is you described what you are trying to achieve by that code.  If (and that is a big IF) I am reading it right ... you are trying to update the rows to have contiguous 3 day date ranges for the same colKey value starting from the minimum value currently in the table.
Having a different tablename in the first cursor to what is in the loop is confusing too.
0
 
soccerplayerAuthor Commented:
My apologies for the typo. The table name is the same, i.e., yourtable and tblMytable is the same. Only one table is involved in the whole process.

Yes, you read it right.

So for the picture below:

ColKey           ColStartDt                                ColEnd Dt
1                  01-JAN-2012                   03-JAN-2012 11:59:59 PM
2                  04-JAN-2012                   06-JAN-2012 11:59:59 PM
3                  07-JAN-2012                   09-JAN-2012 11:59:59 PM
3                  06-JAN-2012                   11-JAN-2012 11:59:59 PM
4                  12-JAN-2012                   14-JAN-2012 11:59:59 PM
4                  14-JAN-2012                   17-JAN-2012 11:59:59 PM
5                  18-JAN-2012                   20-JAN-2012 11:59:59 PM

The output I would like to get is:

ColKey           ColStartDt                                ColEnd Dt
1                  01-JAN-2012                   03-JAN-2012 11:59:59 PM
2                  04-JAN-2012                   06-JAN-2012 11:59:59 PM
3                  07-JAN-2012                   09-JAN-2012 11:59:59 PM
3                  10-JAN-2012                   12-JAN-2012 11:59:59 PM
4                  12-JAN-2012                   14-JAN-2012 11:59:59 PM
4                  15-JAN-2012                   17-JAN-2012 11:59:59 PM
5                  18-JAN-2012                   20-JAN-2012 11:59:59 PM

Thanks.
0
 
lwadwellCommented:
Having had time to read through the other question ... is it possible the data be like below, and if yes, what would you like then?

ColKey           ColStartDt                                ColEnd Dt
1                  01-JAN-2012                   03-JAN-2012 11:59:59 PM
2                  04-JAN-2012                   06-JAN-2012 11:59:59 PM
3                  07-JAN-2012                   09-JAN-2012 11:59:59 PM
3                  06-JAN-2012                   11-JAN-2012 11:59:59 PM
4                  12-JAN-2012                   14-JAN-2012 11:59:59 PM
4                  14-JAN-2012                   17-JAN-2012 11:59:59 PM
5                  18-JAN-2012                   20-JAN-2012 11:59:59 PM
2                  18-JAN-2012                   19-JAN-2012 11:59:59 PM
3                  19-JAN-2012                   21-JAN-2012 11:59:59 PM
3                  20-JAN-2012                   23-JAN-2012 11:59:59 PM
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
soccerplayerAuthor Commented:
ColKey              ColStartDt                                ColEnd Dt
1                  01-JAN-2012                   03-JAN-2012 11:59:59 PM
2                  04-JAN-2012                   06-JAN-2012 11:59:59 PM
2                  07-JAN-2012                   09-JAN-2012 11:59:59 PM
3                  07-JAN-2012                   09-JAN-2012 11:59:59 PM
3                  10-JAN-2012                   12-JAN-2012 11:59:59 PM
3                  13-JAN-2012                   15-JAN-2012 11:59:59 PM
3                  16-JAN-2012                   18-JAN-2012 11:59:59 PM
4                  12-JAN-2012                   14-JAN-2012 11:59:59 PM
4                  14-JAN-2012                   16-JAN-2012 11:59:59 PM
5                  18-JAN-2012                   20-JAN-2012 11:59:59 PM

I can write a looping UPDATE but it seems highly inefficient to me.
0
 
lwadwellCommented:
In the other question you said "ColKey 2 falls in two different date ranges as well but they are NOT overlapping so the count for ColKey 2 will be 1".  This indicates that you want them all updated whether overlapping or not  ... is that true.  I included two different ColKey3 sets with overlaps because I thought you would want them treated separately.
if overlapping does not matter:
begin
    for vColKey in (select rowid
                         , colkey
                         , colstartdt
                         , colenddt
                         , row_number() over(partition by colkey order by colstartdt) as rn
                         , min(colstartdt) over(partition by colkey order by colstartdt) as minstart
                      from tblMyTable)
    loop
        update tblMyTable
           set colstartdt = vColKey.minstart + (vColKey.rn-1)*3
             , colEndDt   = vColKey.minstart + vColKey.rn*3 - 1/(24*3600)
        where rowid = vColKey.rowid;
    end loop;
end;

Open in new window

0
 
soccerplayerAuthor Commented:
lwadwell, the first tblMyTable in the for loop in your query above would be:

   select ColKey
     from
     (
      select colkey, sum(colcount) colcount from
      (select
        colkey
       ,colstartdt
       ,colenddt
       ,case when lead(colstartdt) over (partition by colkey order by colstartdt) > colenddt
             then 0
             else 1
        end colcount
       from yourtable)
      group by colkey
      order by colkey
     )
     where ColCount>1

Would you agree?
0
 
soccerplayerAuthor Commented:
We only need to include ColKeys where overlapping date ranges appear.
0
 
awking00Commented:
It looks like another variable has been added here that has me confused. In your first example, you show start dates of 12-JAN and 14-JAN, but wanted the output to be 12-JUN and 15-JAN, but using lwadwell's data, the output you wanted went back to 12-JAN and 14-JAN. Also, ColKey 3 shows a start date of 6-JAN and a start date of 7-JAN, but you want the output to start with 7-JAN. Is that because the start date of 7-JAN already has a 3-day (minus 1 second) range and the start date 0f 6-JAN does not, so the length of the range needs to take precedence over the start date?
0
 
lwadwellCommented:
Yup ... completely confused.
>>We only need to include ColKeys where overlapping date ranges appear.
yet in my example/your expected results ... non-overlapping ColKeys
ColKey           ColStartDt                                ColEnd Dt
2                  04-JAN-2012                   06-JAN-2012 11:59:59 PM
2                  18-JAN-2012                   19-JAN-2012 11:59:59 PM
became:
ColKey              ColStartDt                                ColEnd Dt
2                  04-JAN-2012                   06-JAN-2012 11:59:59 PM
2                  07-JAN-2012                   09-JAN-2012 11:59:59 PM
I was also surprised by
ColKey           ColStartDt                                ColEnd Dt
3                  07-JAN-2012                   09-JAN-2012 11:59:59 PM
3                  06-JAN-2012                   11-JAN-2012 11:59:59 PM
and separate non-overlapping pair to above
3                  19-JAN-2012                   21-JAN-2012 11:59:59 PM
3                  20-JAN-2012                   23-JAN-2012 11:59:59 PM
becoming
ColKey              ColStartDt                                ColEnd Dt
3                  07-JAN-2012                   09-JAN-2012 11:59:59 PM
3                  10-JAN-2012                   12-JAN-2012 11:59:59 PM
3                  13-JAN-2012                   15-JAN-2012 11:59:59 PM
3                  16-JAN-2012                   18-JAN-2012 11:59:59 PM
0
 
soccerplayerAuthor Commented:
The data I put in my 4th post from the bottom is incorrect. awking00, thank you for reading into this deeper than I anticipated. I wanted the data as follows:

ColKey   ColStartDate     ColEndDate
3            12-JAN-2012     14-JAN-2012 11:59:59PM
3             15-JAN-2012     17-JAN-2012 11:59:59PM
.. and so on.

lwadwell, your SQL seems to work, however, I am still trying to see if my data has a situation like awking00 mentioned in the first post, i.e., DateRange1 overlaps DateRange2 and DateRange3 and DateRange2 and DateRange3 overlap each other. That'll throw the party out of whack.

In the meantime, I'll accept both answers. Thank you both.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now