You are here:  Home / Blog / Latest Entries

Protect Your Data

Safe & Secure

Protecting Your DataMost people think of a “disaster” as something catastrophic and geographically widespread— an earthquake, fire, flood, or tornado, for example. Truth is, the disasters that cost global business the most don’t fit the traditional definition of catastrophe.

Contact Us

Please feel confident sending us an email, or giving us a call.contact-us
p: 403.695.1317
t: 1.855.667.1317
f: 403.770.8145
e: info@ravenbay.com
Subscribe to feed Latest Entries

Oracle - Difference Between Dates in Different Rows

by Mathew Bruneau
Mathew Bruneau
Guest has not set their biography yet
User is currently offline
Wednesday, 09 November 2011 Category Uncategorized 0 Comment
Ever needed to know the difference between dates within groups of rows in Oracle?
An example:  you need to calculate the processing time between status' for a given invoice:

Transaction_Table
Invoice_ID Status Time_Stamp
1 Submit 2011-01-01 7:00:00am
1 Code 2011-01-01 9:00:00am
1 Approve 2011-01-01 1:00:00pm
1 Close 2011-01-02 7:00:00am
3 Submit 2011-01-01 7:05:00am


You may be inclined to do multiple joins back to the transaction_table to get the next status and subtract the dates, ie:

Select invoice_id,

(Invoice2.time_stamp - invoice1.time_stampe) * 24 as coded_time,

(Invoice3.time_stamp – invoice2.time_stamp) * 24 as approved_time,

(Invoice4.time_stamp – invoice3.time_stamp) * 24 as closed_time

From Transaction_table invoice1

Left join Transaction_table invoice2 on invoice1.invoice_id = invoice2.invoice_id

And invoice2.status = ‘Code’

Left join Transaction_table invoice3 on invoice1.invoice_id = invoice3.invoice_id

And invoice3.status = ‘Approve’

Left join Transaction_table invoice4 on invoice1.invoice_id = invoice4.invoice_id

And invoice4.status = ‘Close’

Where invoice1.status = ‘Submit’

Invoice_ID Coded_time Approved_time Closed_time
1 2 4 18
3 Null Null null


Oracle has provided two functions that can help alleviate these unnecessary joins.  The Lead() and Lag() functions were introduced to Oracle in version 8.1.6 making this query much simpler.
The Lead() function allows us to get the next row relative to the current row and the Lag() function allows us to get the previous row relative to the current row.

LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)


We can retrieve the same information in a more normalized form allowing the query to handle more status’ as they are added (ie, multiple approvals etc) and eliminate the joins.

Select invoice_id,

status

(time_stamp - lag(time_stamp,1,null) over (partition by invoice_id order by time_stamp)) * 24 as time

from transaction_table

Invoice_id Status Time
1 Submit Null
1 Code 2
1 Approve 3
1 Close 18
3 Submit Null
Tags: Data Warehouse, Oracle, Business Analytics, Business Intelligence
Read More Hits: 216
0 vote
by Dion Dicks
Dion Dicks
http://www.ravenbay.com/company/our-team/dion-dicks.htm
User is currently offline
Thursday, 23 June 2011 Category Uncategorized 0 Comment

Growing companies that go through consistent internal growth and acquisitions, generate an enormous amount of records during the course of business.  The volume of these records for any given company is growing exponentially.  In the light of new and more frequent e-discovery requests, the time has come for a lot of these companies with no formal policy to address how long records need to be retained.  This has led to a multi-year records retention and information management project where Raven Bay is assigned to manage this project for one of our clients.

With the rising cost of physical storage, due to outsourced providers, legal departments are concerned that some critical records might be destroyed earlier than they should. Other records are retained longer than they should be which could be used in legal discovery action. All contribute to the impact of ineffective records management and create corporate risk. In the initiative by our client to reduce these risks, Raven Bay and the Legal Department teamed up with IT to analyze the business records. We’ve come up with a suitable records retention policy and records retention schedule to mitigate these risks.

The first phase of the project was to determine what record types are used within the business. Discussing with employees at all levels of the business, we found out what's out there.  Next step was to obtain some legal research from outside experts as to what legislation, statutes and regulations exist that might be applicable to the client's business. With a detailed inventory of existing record types, plus the list of legal citations the client's, records were then matched to the legislation.  The client confirmed if they agreed with the suggested retention periods or revised them to meet any operational business needs.

Raven Bay's Project Manager and the VP of legal used a guideline for the policy provided by the external legal advisors and compared it to the two available ISO standards on records management as well as other records management best practice documentation.  The result was a records retention policy that the client's executive team could hang their hat on.

About seven months after the initial roll out the various departments have felt empowered to destroy copious amounts of documentation that have outlived their useful life while still retaining the company’s vital records.  Training materials were created by Raven Bay and delivered by the company’s Legal team and was well received with representatives from all functional areas in Canada and the US.

The project continues today with a more focused approach to electronic records management where the ultimate goal is solution investigation for ensuring policy compliance at the electronic records level including network files and emails.

Tags: Untagged
Read More Hits: 317
0 vote
by Steve Wong
Steve Wong
Guest has not set their biography yet
User is currently offline
Thursday, 23 June 2011 Category Uncategorized 0 Comment

Cisco Unity server is a flexible tool which an Administrator can use to easily provide unique services for the Business sector. One such service has been discovered when a problem was presented regarding a business phone conversation turned hostile. Subsequently, I was asked if there were any way to record and archive a live conversation for possible future legalities. After some research, I was able to configure a built-in feature of the Cisco Unity software called Live Record.

The Live Record feature is a great tool that allows the user to save a recorded message/conversation to his or her voice mailbox. The only drawback of this feature is that it is dependent on the phone model for the ability to program one of the phone’s option buttons to automatically dial the routing number that initiates the recording. The client is currently using the Cisco 7945 models for their offices which made this not possible, a programmed speed dial was the alternative solution.   

Once Live Record was setup, I found a way to archive the recorded messages to a network repository which is where the Cisco Unity email client integration comes in. My client is using MS Outlook, although it would need to be tested, it is likely that Unity integrates with Lotus Notes in the same manner. By setting up an IMAP account and directing my voicemail account to Exchange, I was able to view the recordings as WAV files and save them to any network location.

There is also a plug-in for the email client called ViewMail. By installing this plug-in the user has access to create and listen to voice messages, all within the email client. This added feature currently serves the purpose of turning your phone into a personal voice recorder. Who knows what new situations may arise that a business may request such a service?

It is also notable that this solution and feature is not intended solely for recording disagreeable phone dialogues. I was also asked to prepare a solution for the Marketing department so that they could make business deals on the phone, record and archive them, and later share them among the department as required.

By combining several features from a Cisco Unity server, this solution shows how Raven Bay can use the available technologies and add-ins to give our clients the results they require to suit their business needs, today and in the future.

 

N.B. Please note that it is imperative to always follow company policies and ensure legal regulations are followed, pertaining to privacy rights and confidentiality for any voice recording.

Tags: Untagged
Read More Hits: 163
0 vote

Developed by  Late Night Tonight Live