Solved

list of days between dates - oracle sql

Posted on 2010-11-19
5
942 Views
Last Modified: 2013-12-18
I have a number of assessments which cover periods of time.

the attachment shows -

fig 1. the current output of o_assessments table
fig 2. desired output.

I need to be able to list all days which the assessments cover, (ie the days between the start and end date). Hopefully my requirments are clear from the sample data.

example-table.xls
0
Comment
Question by:tonMachine100
  • 4
5 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
try this

SELECT     asm_start_date + n asm_date, asm_type, asm_id
      FROM o_assessments,(select level - 1 n from dual connect by level < 100)
      where asm_start_date + n <= asm_end_date
  ORDER BY asm_date, asm_type, asm_id  
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
adjust the 100 to some number big enough to cover your largest date span (but not grossly over sized)

you could generate with a sub query so it would be an exact fit but that would require an extra hit on your table to generate,  so probably not worth it if you can determine an upper limit on your own.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
alternate method,  requires a couple more internal sort operations (use autotrace to confirm) but doesn't require extra IO to the table or need you to guess at a maximum range


alternate method,  requires a couple more sort operations but doesn't require a guess of how many rows you might need


SELECT   asm_start_date + COLUMN_VALUE asm_date, asm_type, asm_id
    FROM (SELECT o_assessments.*,
                 (SELECT     COLLECT(LEVEL - 1)
                        FROM DUAL
                  CONNECT BY asm_start_date + LEVEL - 1 <= asm_end_date)
                     n
            FROM o_assessments) x,
         TABLE(n)
ORDER BY asm_date, asm_type
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
yet another method, should be equivalent to previous post, but slightly more compact

SELECT   asm_start_date + COLUMN_VALUE asm_date, asm_type, asm_id
    FROM (SELECT *
            FROM o_assessments,
                 TABLE(SELECT     COLLECT(LEVEL - 1)
                             FROM DUAL
                       CONNECT BY asm_start_date + LEVEL - 1 <= asm_end_date))
ORDER BY asm_date, asm_type

0
 

Author Closing Comment

by:tonMachine100
Comment Utility
This is great thanks.

(I'm just about to ask another question which is related to this one - so if you could check this one out too that would be appreciated)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sql to get orphans 7 50
Invalid Identifier Error 3 65
query returning everything 11 65
sql query 5 51
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now