Learn how to a build a cloud-first strategyRegister Now

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

Ordering dated records from an arbritrary point in time

Hi, I could use some help figuring out some code to order seasonal address records in our database for a report. We have an Oracle 11 database, so I'm looking for Oracle SQL code.

First, I'll explain how our data is stored. We can have multiple (unlimited) active address records for a given person. Some people have more than one address due to seasonal relocations. Each address record has a "from" date to indicate when the address became, or will become, active and a "to" date to indicate when it ends.

For example, take these two snowbirds:

ID#  Address #  From Date    To Date             State       Nation
------------------------------------------------------------------------------------------------
101           1         4/15/2014     9/15/2014        MO           USA
101           2         9/16/2014     4/14/2015        FL              USA

201           1         5/1/2014       9/1/2014           MI            USA
201           2         9/2/2014       11/15/2014      CA            USA
201           3         11/16/2014   4/30/2015                         New Zealand

Currently, they are ordered how a generic sort would order them, from "least" to "greatest".

Notice that they are also in order based on the current date: the system date falls between the "from" and "to" dates of the first record, followed, in order, by the remaining addresses.

The latter order is what I need to duplicate, but for an arbitrary point in time.

For example, say I wanted to know who would be at their seasonal address on Dec 1. I need the addresses that will be active on that date to be the first address returned, followed by the remaining addresses in chronological order due to month and day (year is disregarded). So the results would look like this:

ID#  Address #  From Date    To Date             State       Nation
------------------------------------------------------------------------------------------------
101           2         9/16               4/14                  FL            USA
101           1         4/15               9/15                  MO          USA

201           3         11/16             4/30                                  New Zealand
201           1         5/1                 9/1                    MI            USA
201           2         9/2                 11/15                CA           USA


Let me know if there are any questions to clarify what I am trying to do.

TIA.
0
prinprog
Asked:
prinprog
  • 7
  • 5
  • 4
  • +1
2 Solutions
 
John_VidmarCommented:
SELECT  *
FROM	YourAddressTable
WHERE	FromDate <= 'Dec 1, 2014'
AND	(	ToDate >= 'Dec 1, 2014'
	OR	ToDate IS NULL
	)
ORDER
BY	ID
,	FromDate DESC

Open in new window

0
 
prinprogAuthor Commented:
Thanks for responding, John. Your script returns the two records that will be active during Dec. 1, but only those two. I need all 5 records listed, with the records you return listed first for each ID, then the remaining records following, in chronological order for each ID.
0
 
PortletPaulCommented:
Use a case expression that compares a given date to the range from_date/to_date. I have included the same expression in the output to help demonstrate how it works.
SELECT
      CASE WHEN sysdate BETWEEN from_date AND to_date THEN '*' ELSE '' END as x
    , id
    , address
    , from_date
    , to_date
    , state
    , nation
FROM yourtable
ORDER BY
      id
      , CASE WHEN sysdate BETWEEN from_date AND to_date THEN 1 ELSE 2 END
;

SELECT
      CASE WHEN to_date('2014-12-31','yyyy-mm-dd') BETWEEN from_date AND to_date THEN '*' ELSE '' END as x
    , id
    , address
    , from_date
    , to_date
    , state
    , nation
FROM yourtable
ORDER BY
      id
      , CASE WHEN to_date('2014-12-31','yyyy-mm-dd') BETWEEN from_date AND to_date THEN 1 ELSE 2 END
;

Open in new window

results:
|      X |  ID | ADDRESS |                        FROM_DATE |                          TO_DATE |  STATE |      NATION |
|--------|-----|---------|----------------------------------|----------------------------------|--------|-------------|
|      * | 101 |       1 |     April, 15 2014 00:00:00+0000 | September, 15 2014 00:00:00+0000 |     MO |         USA |
| (null) | 101 |       2 | September, 16 2014 00:00:00+0000 |     April, 14 2015 00:00:00+0000 |     FL |         USA |
|      * | 201 |       1 |       May, 01 2014 00:00:00+0000 | September, 01 2014 00:00:00+0000 |     MI |         USA |
| (null) | 201 |       2 | September, 02 2014 00:00:00+0000 |  November, 15 2014 00:00:00+0000 |     CA |         USA |
| (null) | 201 |       3 |  November, 16 2014 00:00:00+0000 |     April, 30 2015 00:00:00+0000 | (null) | New Zealand |

|      X |  ID | ADDRESS |                        FROM_DATE |                          TO_DATE |  STATE |      NATION |
|--------|-----|---------|----------------------------------|----------------------------------|--------|-------------|
|      * | 101 |       2 | September, 16 2014 00:00:00+0000 |     April, 14 2015 00:00:00+0000 |     FL |         USA |
| (null) | 101 |       1 |     April, 15 2014 00:00:00+0000 | September, 15 2014 00:00:00+0000 |     MO |         USA |
|      * | 201 |       3 |  November, 16 2014 00:00:00+0000 |     April, 30 2015 00:00:00+0000 | (null) | New Zealand |
| (null) | 201 |       2 | September, 02 2014 00:00:00+0000 |  November, 15 2014 00:00:00+0000 |     CA |         USA |
| (null) | 201 |       1 |       May, 01 2014 00:00:00+0000 | September, 01 2014 00:00:00+0000 |     MI |         USA |

http://sqlfiddle.com/#!4/792cb/6

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
prinprogAuthor Commented:
Thank you too, PortletPaul. Your solution is closer, but still not sorting the 3-address group correctly. I tried a CASE statement as well, which works for 2-address snowbirds, but not for 3. If you notice your output, ID 201's final order is 3-2-1. To be chronological (according to the From-To dates, specifically by month and day), it needs to be 3-1-2. That's where I'm getting hung up.
0
 
awking00Commented:
SQL> select * from locations;

        ID  ADDRESSID FROMDATE  TODATE    ST NATION
---------- ---------- --------- --------- -- ---------------
       101          1 15-APR-14 15-SEP-14 MO USA
       101          2 16-SEP-14 14-APR-15 FL USA
       201          1 01-MAY-14 01-SEP-14 MI USA
       201          2 02-SEP-14 15-NOV-14 CA USA
       201          3 16-NOV-14 30-APR-15    New Zealand

SQL> select id, addressid, fromdate, todate, state, nation
  2  from locations
  3  order by id,
  4  case when to_date(to_char(sysdate,'yyyy')||'&day','yyyymmdd')
  5            between fromdate and todate then 0
  6       else addressid
  7  end;
Enter value for day: 1201  => MMDD for December 1st
old   4: case when to_date(to_char(sysdate,'yyyy')||'&day','yyyymmdd')
new   4: case when to_date(to_char(sysdate,'yyyy')||'1201','yyyymmdd')

        ID  ADDRESSID FROMDATE  TODATE    ST NATION
---------- ---------- --------- --------- -- --------------------
       101          2 16-SEP-14 14-APR-15 FL USA
       101          1 15-APR-14 15-SEP-14 MO USA
       201          3 16-NOV-14 30-APR-15    New Zealand
       201          1 01-MAY-14 01-SEP-14 MI USA
       201          2 02-SEP-14 15-NOV-14 CA USA

SQL> / => repeats the query
Enter value for day: 1010 => MMDD for October 10th
old   4: case when to_date(to_char(sysdate,'yyyy')||'&day','yyyymmdd')
new   4: case when to_date(to_char(sysdate,'yyyy')||'1010','yyyymmdd')

        ID  ADDRESSID FROMDATE  TODATE    ST NATION
---------- ---------- --------- --------- -- --------------------
       101          2 16-SEP-14 14-APR-15 FL USA
       101          1 15-APR-14 15-SEP-14 MO USA
       201          2 02-SEP-14 15-NOV-14 CA USA
       201          1 01-MAY-14 01-SEP-14 MI USA
       201          3 16-NOV-14 30-APR-15    New Zealand

SQL> / => repeat again
Enter value for day: 0715 => for July 15th
old   4: case when to_date(to_char(sysdate,'yyyy')||'&day','yyyymmdd')
new   4: case when to_date(to_char(sysdate,'yyyy')||'0715','yyyymmdd')

        ID  ADDRESSID FROMDATE  TODATE    ST NATION
---------- ---------- --------- --------- -- --------------------
       101          1 15-APR-14 15-SEP-14 MO USA
       101          2 16-SEP-14 14-APR-15 FL USA
       201          1 01-MAY-14 01-SEP-14 MI USA
       201          2 02-SEP-14 15-NOV-14 CA USA
       201          3 16-NOV-14 30-APR-15    New Zealand
0
 
prinprogAuthor Commented:
Thanks awking00 - also very close. However, if you look at your results for October 10, here is the order for ID 201:

201          2 02-SEP-14 15-NOV-14 CA USA
201          1 01-MAY-14 01-SEP-14 MI USA
201          3 16-NOV-14 30-APR-15    New Zealand

The first address ends in November, but the second address starts in May. So the last two records should be reversed in this instance. They are correct in the other 2 examples.

This is also as close as I've been able to get: it sorts right in 2 instances, but not the 3rd.
0
 
PortletPaulCommented:
add a further condition to the order by

ORDER BY
      id
      , CASE WHEN to_date('2014-12-31','yyyy-mm-dd') BETWEEN from_date AND to_date THEN 1 ELSE 2 END
, ADDRESS -- that id field!
;

or

ORDER BY
      id
      , CASE WHEN to_date('2014-12-31','yyyy-mm-dd') BETWEEN from_date AND to_date THEN 1 ELSE 2 END
, FROM_DATE
, TO_DATE

;
0
 
prinprogAuthor Commented:
It's like squeezing a balloon; pinch one side and the other pooches out. Pinch that and it pooches out another place...

Now your code works for May 1 and Dec 1 test dates, but it doesn't work when testing for an Oct 1 date.

Same problem as me - I can get it to work for 2/3 times, but never all 3.

Thanks for a second try!
0
 
PortletPaulCommented:
Can you provide some results where it is not working, I don't follow.
0
 
prinprogAuthor Commented:
Yep - in both examples you've given me, running the code works 2/3 times, meaning I'm testing it with May, October, and December dates to find and order each address for the 3-address snowbird.

Your first script works right for May and October, but December produces the incorrect result:

SELECT
      CASE WHEN to_date('2014-12-20','yyyy-mm-dd') BETWEEN from_date AND to_date THEN '*' ELSE '' END as x
    , id
    , address
    , from_date
    , to_date
    , state
    , nation
FROM yourtable
ORDER BY
      id
      , CASE WHEN to_date('2014-12-20','yyyy-mm-dd') BETWEEN from_date AND to_date THEN 1 ELSE 2 END
;

Open in new window


X         ID    ADDRESS FROM_DATE TO_DATE   STATE NATION                        
- ---------- ---------- --------- --------- ----- ------------------------------
*        101          2 16-SEP-14 14-APR-15 FL    USA                           
         101          1 15-APR-14 15-SEP-14 MO    USA                           
*        201          3 16-NOV-14 30-APR-15       New Zealand                   
         201          2 02-SEP-14 15-NOV-14 CA    USA                           
         201          1 01-MAY-14 01-SEP-14 MI    USA                           

Open in new window


In result above, the "active" address for 201 will be #3, which ends in April. Chronologically, the next address that will be active after that is the one that starts in May, which should be listed 2nd for 201 but is listed 3rd. So sequentially, the 2nd and 3rd addresses above should be reversed. After the 4/30 To Date arrives on that address, the address with the 5/1 From Date will kick in.

The second script, with the ORDER BY modified, works right for May and December, but not October:

SELECT
      CASE WHEN to_date('2014-10-20','yyyy-mm-dd') BETWEEN from_date AND to_date THEN '*' ELSE '' END as x
    , id
    , address
    , from_date
    , to_date
    , state
    , nation
FROM yourtable
ORDER BY
      id
      , CASE WHEN to_date('2014-10-20','yyyy-mm-dd') BETWEEN from_date AND to_date THEN 1 ELSE 2 END
, FROM_DATE
, TO_DATE
;

Open in new window


X         ID    ADDRESS FROM_DATE TO_DATE   STATE NATION                        
- ---------- ---------- --------- --------- ----- ------------------------------
*        101          2 16-SEP-14 14-APR-15 FL    USA                           
         101          1 15-APR-14 15-SEP-14 MO    USA                           
*        201          2 02-SEP-14 15-NOV-14 CA    USA                           
         201          1 01-MAY-14 01-SEP-14 MI    USA                           
         201          3 16-NOV-14 30-APR-15       New Zealand      

Open in new window


In the result above, for 201 the "active" address will be #2, but you can see that the subsequent order of addresses is reversed, as above. After 11/15 arrives on the To Date, the address with the 11/16 From Date will kick in so it should be listed second.

The code can't depend on the years and the linear-chain order that produces. When the time comes, they'll have rolled over to advance the years, so this code I'm writing only cares about the circular-chain order of the month/day dates. The From and To date month and day values form a "circle" when taken without respect to the year; where one To date leaves off, the next From date picks up. I'm trying to keep that chain in order based on the month/day, regardless of where I pick in that circle to start ordering them from.

Hope that makes sense. And thanks for all your help so far!
0
 
PortletPaulCommented:
*        201          3 16-NOV-14 30-APR-15       New Zealand                   
         201          2 02-SEP-14 15-NOV-14 CA    USA                           
         201          1 01-MAY-14 01-SEP-14 MI    USA   

Open in new window

... the "active" address for 201 will be #3, which ends in April. Chronologically, the next address that will be active after that is the one that starts in May, which should be listed 2nd for 201 but is listed 3rd.

You are assuming a "cycle", or set of calculations. This has not been explained (as far as I know).

What you see is what you get for the data (not the calculations you expect

We have arbitrarily place record 3 first in that list, the other dates are in their correct order (as data).

Record 2 would only be next in order if you add  1 year to it
0
 
PortletPaulCommented:
try these
SELECT
      CASE WHEN to_date('2014-10-20','yyyy-mm-dd') BETWEEN from_date AND to_date THEN '*' ELSE '' END as x
    , id
    , address
    , '2014-OCT-20'
    , from_date
    , to_date
    , state
    , nation
FROM yourtable
where id = 201
ORDER BY
        id
      , CASE
          WHEN to_date('2014-10-20','yyyy-mm-dd') BETWEEN from_date AND to_date THEN from_date
          when from_date < to_date('2014-10-20','yyyy-mm-dd') then add_months(from_date,12)
          ELSE from_date
        END ASC
;

SELECT
      CASE WHEN to_date('2014-12-20','yyyy-mm-dd') BETWEEN from_date AND to_date THEN '*' ELSE '' END as x
    , id
    , address
    , '2014-DEC-20'
    , from_date
    , to_date
    , state
    , nation
FROM yourtable
where id = 201
ORDER BY
        id
      , CASE
          WHEN to_date('2014-12-20','yyyy-mm-dd') BETWEEN from_date AND to_date THEN from_date
          when from_date < to_date('2014-10-20','yyyy-mm-dd') then add_months(from_date,12)
          ELSE from_date
        END ASC
;

cREATE TABLE YOURTABLE
	("ID" int, "ADDRESS" int, "FROM_DATE" timestamp, "TO_DATE" timestamp, "STATE" varchar2(4), "NATION" varchar2(11))
;

INSERT ALL 
	INTO YOURTABLE ("ID", "ADDRESS", "FROM_DATE", "TO_DATE", "STATE", "NATION")
		 VALUES (101, 1, '15-Apr-2014 12:00:00 AM', '15-Sep-2014 12:00:00 AM', 'MO', 'USA')
	INTO YOURTABLE ("ID", "ADDRESS", "FROM_DATE", "TO_DATE", "STATE", "NATION")
		 VALUES (101, 2, '16-Sep-2014 12:00:00 AM', '14-Apr-2015 12:00:00 AM', 'FL', 'USA')
	INTO YOURTABLE ("ID", "ADDRESS", "FROM_DATE", "TO_DATE", "STATE", "NATION")
		 VALUES (201, 1, '01-May-2014 12:00:00 AM', '01-Sep-2014 12:00:00 AM', 'MI', 'USA')
	INTO YOURTABLE ("ID", "ADDRESS", "FROM_DATE", "TO_DATE", "STATE", "NATION")
		 VALUES (201, 2, '02-Sep-2014 12:00:00 AM', '15-Nov-2014 12:00:00 AM', 'CA', 'USA')
	INTO YOURTABLE ("ID", "ADDRESS", "FROM_DATE", "TO_DATE", "STATE", "NATION")
		 VALUES (201, 3, '16-Nov-2014 12:00:00 AM', '30-Apr-2015 12:00:00 AM', NULL, 'New Zealand')
SELECT * FROM dual
;

Open in new window

http://sqlfiddle.com/#!4/832a2/1
0
 
awking00Commented:
I've tested this case for dates in all 12 months and I think it does the trick. This was toughie.

with tbl as
(select distinct c.id, c.compdate,l.addressid, l.fromdate, l.todate, l.state, l.nation, l.addno from
 (select id, lpad(to_char(&day),4,'0') compdate from locations) c
 inner join
 (select id, addressid, fromdate, todate, state, nation,
  last_value(addressid) over (partition by id order by addressid
  range between unbounded preceding and unbounded following) addno
  from locations) l
 on c.id = l.id
 order by c.id, l.addressid)
select t.id, t.addressid, to_char(t.fromdate,'mm/dd') fromdt, to_char(t.todate,'mm/dd') todt,
t.state, t.nation from
tbl t left join
(select id, addressid
 from tbl
 where to_char(fromdate,'yy') = to_char(todate,'yy')
   and to_date(compdate||to_char(fromdate,'yy'),'mmddyy') between
       fromdate and todate
    or to_char(fromdate,'yy') < to_char(todate,'yy')
       and (to_date(compdate||to_char(fromdate,'yy'),'mmddyy') between
            fromdate and to_date(to_char('1231'||to_char(fromdate,'yy')),'mmddyy'))
    or to_char(fromdate,'yy') < to_char(todate,'yy')
       and (to_date(compdate||to_char(to_number(to_char(fromdate,'yy') + 1)),'mmddyy') between
            to_date('0101'||to_char(to_number(to_char(fromdate,'yy') + 1)),'mmddyy') and todate)) x
on t.id = x.id
order by id,
case when t.addressid = x.addressid then 0
     when t.addressid < x.addressid then (t.addressid + t.addno)
     else t.addressid
end
;
0
 
prinprogAuthor Commented:
I really appreciate the obvious time spent by the contributors to this tricky question. Both final posts by PortletPaul and awking00 work as well as needed, though neither is exactly the solution I was looking for. Both sets of code work for the exact set of data I gave (up to 3 linked addresses), but both break down when I expanded the 3-address person to 4 linked addresses in testing. But since that will be rare -- if it ever occurs -- I won't loose any sleep over it, nor withhold any of my points for it!

Since PortletPaul's solution is simpler and easier to work with, I'm giving his the majority of the points. However, awking00's code is heading more towards the month/day autonomy that I was looking for and will probably use some of that code in the future to hopefully merge the two into an ultimate solution.

Many thanks to you both!!
0
 
awking00Commented:
Can you post the id, addressid, fromdate and todate just for the case where four linked addresses broke down?
0
 
awking00Commented:
Was I dreaming or did I not see a response to my last question yesterday? I didn't have time to look at it then but I wanted to see if I could discover the reason for the failure to produce correct results with multiple (>= 4) address links since my solution should have worked under that scenario.
0
 
prinprogAuthor Commented:
Haven't had time to re-test and put a response together. I intend to follow up and will adjust points if warranted. I prefer to keep my posts meaty and on-topic and not clutter my questions with posts like this, but for the sake of communication, if I can't get back to this today, it'll be early next week. I do appreciate your help and time.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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