[Webinar] Learn how to a build a cloud-first strategyRegister Now

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

pl/qsl splitting single record by date time

Hello Experts,

Below is a sample of the records I need to split by 12 hour shift.  The records for 10/21 and 10/14 span at least two shift I am charge with creating Crystal Reports that detail in formation by shift.  My reports will look back more 12 months, I am stumped.  It seems I need to create a temp table for the current report range with a records each shift date and time and then loop through the delays records pulling out delays portions for each shift.

DOES ANYONE HAVE A BETTER IDEA?  Or any suggestions would be rewarded.

DELAY_START                  DELAY_END                      DATE             DELAY_MINUTES
10/21/2011 6:00:06      10/22/2011 23:40:06      21-Oct-11      1439.9
10/16/2011 6:00:02      10/16/2011 18:00:05      16-Oct-11      720.05
10/14/2011 7:30:15      10/15/2011 22:38:25      14-Oct-11      1349.75

Thanks!
0
Michael
Asked:
Michael
  • 6
  • 5
  • 4
  • +2
1 Solution
 
sdstuberCommented:
given the 3 rows above,  what do you expect the output to be?
0
 
MichaelAuthor Commented:
Thanks for reply.  The delay minutes where limited to the DATE.

The expected output:

DELAY_START          DELAY_END                 DELAY_MINUTES
10/21/2011 6:00:06    10/21/2011 18:00:00     719.90
10/21/2011 18:00:01  10/22/2011 06:00:00     720
10/22/2011 06:00:01  10/22/2011 18:00:00     720
10/22/2011 18:00:01  10/22/2011 23:40:06     339.98

10/16/2011 06:00:02  10/16/2011 18:00:00     719.97
10/16/2011 18:00:01  10/16/2011 18:00:05     0.07

10/14/2011 07:30:15  10/14/2011 18:00:00     629.75
10/14/2011 18:00:01  10/15/2011 06:00:00     720
10/15/2011 06:00:01  10/15/2011 18:00:00     720
10/15/2011 18:00:01  10/15/2011 22:38:25     278.40
0
 
mlmccCommented:
Look at this report
Note your times for the first and 3rd records are off by 1440 minutes

mlmcc
Q-27416026.rpt
Q-27416026.xls
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Mark GeerlingsDatabase AdministratorCommented:
This looks like another report request that is something like: Give me a report that includes some information from the database but also include some data that is *NOT* in the database (like days or shifts in between the start and end times).

The SQL language is very powerful and flexible.  It is very good at presenting information that exists, but asking SQL (or a SQL-based report) to include things that aren't there, is a challenge.

Producing a report that contains exactly as many rows as there are records in the database with two additional columns per row to indicate how many days and shifts each record spans would be quite easy.

Producing a report that has more rows of output than there are records in the database, and with varying numbers of rows of output for each input record based on the varying times between the start and end dates will be much more challenging.  (I didn't say impossible, but it will be complex.)
0
 
sdstuberCommented:
your delay minutes don't seem to coincide with your times.

10/21/2011 6:00:06    10/21/2011 18:00:00     719.90   --- this one makes sense

10/21/2011 18:00:01  10/22/2011 06:00:00     720   --- this one does not,  it should be 719.98 when I do the math.


here's my take on it.

if you can explain the minutes-math exceptions , I'll try to work that in
SELECT   delay_start, delay_end, ROUND(1440 * (delay_end - delay_start), 2)
    FROM (SELECT o,
                 CASE
                     WHEN h = 0 THEN delay_start
                     ELSE TRUNC(delay_start) + h / 24 + 6 / 24 + 1 / 86400
                 END
                     delay_start,
                 CASE
                     WHEN TRUNC(delay_start) + h / 24 + 18 / 24 > delay_end THEN delay_end
                     ELSE TRUNC(delay_start) + h / 24 + 18 / 24
                 END
                     delay_end
            FROM (SELECT delay_start o, delay_start, delay_end, delay_minutes, COLUMN_VALUE h
                    FROM yourtable,
                         TABLE(SELECT     COLLECT((LEVEL - 1) * 12)
                                     FROM DUAL
                               CONNECT BY delay_start + (LEVEL - 1) * 12 / 24 <= delay_end)))
ORDER BY o DESC, delay_start

Open in new window

0
 
sdstuberCommented:
Here's a modified version of the above that will force 720 minutes for a full shift

the start/end  partial shifts will use date math to derive the results


SELECT   delay_start,
         delay_end,
         CASE
             WHEN    (    TO_CHAR(delay_start, 'hh24:mi:ss') = '06:00:01'
                      AND TO_CHAR(delay_end, 'hh24:mi:ss') = '18:00:00')
                  OR (    TO_CHAR(delay_start, 'hh24:mi:ss') = '18:00:01'
                      AND TO_CHAR(delay_end, 'hh24:mi:ss') = '06:00:00')
             THEN
                 720
             ELSE
                 ROUND(1440 * (delay_end - delay_start), 2)
         END
             delay_minutes
    FROM (SELECT o,
                 CASE
                     WHEN h = 0 THEN delay_start
                     ELSE TRUNC(delay_start) + h / 24 + 6 / 24 + 1 / 86400
                 END
                     delay_start,
                 CASE
                     WHEN TRUNC(delay_start) + h / 24 + 18 / 24 > delay_end THEN delay_end
                     ELSE TRUNC(delay_start) + h / 24 + 18 / 24
                 END
                     delay_end
            FROM (SELECT delay_start o, delay_start, delay_end, delay_minutes, COLUMN_VALUE h
                    FROM yourtable,
                         TABLE(SELECT     COLLECT((LEVEL - 1) * 12)
                                     FROM DUAL
                               CONNECT BY delay_start + (LEVEL - 1) * 12 / 24 <= delay_end)))
ORDER BY o DESC, delay_start
0
 
mlmccCommented:
The times my produces are correct.

Changing the start time of the shift to 18:00:01 causes the 1 sec to not be counted.

mlmcc
0
 
mlmccCommented:
The delay sown in the sample is correct.  The issue is changing the start time.

The shift ends at 18:00:00 and starts at 18:00:00 otherwise you don't count that second.

mlmcc
0
 
sdstuberCommented:
no, my results are only off by rounding on most of them

I'm getting tiny differences  0.02 minutes due to the shift starting 1 second after the hour

there is only one that differs by more

these results are from my first query...

2011/10/21 06:00:06      2011/10/21 18:00:00      719.9
2011/10/21 18:00:01      2011/10/22 06:00:00      719.98
2011/10/22 06:00:01      2011/10/22 18:00:00      719.98
2011/10/22 18:00:01      2011/10/22 23:40:06      340.08    --- this one isn't just rounding
2011/10/16 06:00:02      2011/10/16 18:00:00      719.97
2011/10/16 18:00:01      2011/10/16 18:00:05      0.07
2011/10/14 07:30:15      2011/10/14 18:00:00      629.75
2011/10/14 18:00:01      2011/10/15 06:00:00      719.98
2011/10/15 06:00:01      2011/10/15 18:00:00      719.98
2011/10/15 18:00:01      2011/10/15 22:38:25      278.4


these are the results of my second query where I force 720 for full shifts.

                     
2011/10/21 06:00:06 2011/10/21 18:00:00 719.9                                  
2011/10/21 18:00:01 2011/10/22 06:00:00 720                                    
2011/10/22 06:00:01 2011/10/22 18:00:00 720                                    
2011/10/22 18:00:01 2011/10/22 23:40:06 340.08         --- again this one isn't just rounding                          
2011/10/16 06:00:02 2011/10/16 18:00:00 719.97                                
2011/10/16 18:00:01 2011/10/16 18:00:05 0.07                                  
2011/10/14 07:30:15 2011/10/14 18:00:00 629.75                                
2011/10/14 18:00:01 2011/10/15 06:00:00 720                                    
2011/10/15 06:00:01 2011/10/15 18:00:00 720                                    
2011/10/15 18:00:01 2011/10/15 22:38:25 278.4                                  

0
 
sdstuberCommented:
I can't see your results, your rpt file isn't readable for me.
0
 
MichaelAuthor Commented:
Some my times in the sample were not calculated.

If I wasn't a Christian I think I would bow or something.  That is perfect!  
0
 
mlmccCommented:
Here is a pdf

mlmcc
q-27416026.pdf
0
 
sdstuberCommented:
" That is perfect!  "


which one is "that"  ? There are multiple posts above

do you need anything else?
0
 
MichaelAuthor Commented:
Thanks!
0
 
awking00Commented:
See attached.
query.txt
0
 
awking00Commented:
Guess I shouldn't have gone to lunch before submitting my response. Sorry for the late post.
0
 
mlmccCommented:
Was there something wrong with the report approach?

You put this in the Crystal zone which would indicate that a report based solution is a good one.

mlmcc
0
 
MichaelAuthor Commented:
Thanks mlmcc:

In my quick scan I decided to review and tested the first strait query approach and went with it after verying it.  There are three greast solutions listed above for my problem.  And I appreciate your time and effort!

Thanks again!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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