BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

 
Lead By Knowing (Opens a new window)  

General Notice: No events within the next 45 days.

Date Difference using Teradata database


 
Search this topic... | Search Desktop Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Desktop Intelligence  Previous TopicPrint TopicNext Topic
Author Message
Pave
Forum Member
Forum Member



Joined: 13 Sep 2004

Posts: 14



PostPosted: Mon Oct 04, 2004 6:03 pm 
Post subject: Date Difference using Teradata database

I need to calculate the difference between two timestamps in business objects (verison is 6.1b). Database using here is Teradata (version is V2R5). Can anyone suggest me how to do so...
Date format is in mm/dd/yyyy hh:mm:ss

_________________
Good LUCK...!
Pave
Back to top
charlie
Forum Associate
Forum Associate



Joined: 20 Aug 2002

Posts: 550
Location: Woodinville, WA USA


flag
PostPosted: Tue Oct 05, 2004 12:45 pm 
Post subject: Re: Date Difference using Teradata database

That depends. How do you need the difference: do you just want the number of days between the timestamps, or do you need the time portion as well?
_________________
Regards,

Charlie
Back to top
Pave
Forum Member
Forum Member



Joined: 13 Sep 2004

Posts: 14



PostPosted: Tue Oct 05, 2004 3:34 pm 
Post subject: Re: Date Difference using Teradata database

I need the difference in minutes.
_________________
Good LUCK...!
Pave
Back to top
charlie
Forum Associate
Forum Associate



Joined: 20 Aug 2002

Posts: 550
Location: Woodinville, WA USA


flag
PostPosted: Tue Oct 05, 2004 4:30 pm 
Post subject: Re: Date Difference using Teradata database

To get the difference in minutes, you'll need something like
Code:
select ((timestamp2 - timestamp1)  MINUTE(4)) AS diff_time from mytable
Teradata uses Intervals (like MINUTE) to do timestamp arithmatic. Be aware, though, that Intervals are restrictive and can fail due to field overflows (hence the MINUTE(4) in the sample code). The following sample code
Code:
select ((timestamp '2004-10-02 23:00:00' - timestamp '2004-10-01 20:00:00')   MINUTE(4)) AS diff_time
gives a result of 1620, while this
Code:
select ((timestamp '2004-10-01 23:00:00' - timestamp '2004-10-02 20:00:00')   MINUTE(4)) AS diff_time
gives -1260
_________________
Regards,

Charlie
Back to top
Pave
Forum Member
Forum Member



Joined: 13 Sep 2004

Posts: 14



PostPosted: Tue Oct 05, 2004 5:11 pm 
Post subject: Re: Date Difference using Teradata database

I created an object with the same query. The query never ends.
_________________
Good LUCK...!
Pave
Back to top
Michele Pinti
Forum Enthusiast
Forum Enthusiast



Joined: 17 Jun 2002

Posts: 1471
Location: Dayton, Ohio



PostPosted: Tue Oct 05, 2004 5:25 pm 
Post subject: Re: Date Difference using Teradata database

Quote:
To get the difference in minutes, you'll need something likeCode:
Quote:
select ((timestamp2 - timestamp1) MINUTE(4)) AS diff_time from mytable


This is the syntax we use to calculate minutes. Can you post your code?

_________________
Who's Your Friend? Search is Your Friendô

Infoview XI 3.1 SP3
Full Client Business Objects 5.1.6
Repository: SQL Server 2000 Database: Teradata 2.6

Michele Pinti
Standard Register Co.

Back to top
Pave
Forum Member
Forum Member



Joined: 13 Sep 2004

Posts: 14



PostPosted: Wed Oct 06, 2004 9:00 am 
Post subject: Re: Date Difference using Teradata database

Do I need to do this using Free-Hand SQL.
I am not sure how to use such a query in B.O reporter.

1. Created and object in the universe with the format you gave me but its not parsing. so i used

cast(begin_date_time) - cast(end_date_time)

This worked. The problem is when i am dragging this object into my reporter, the query is never ending.

2. Is there a way where i can get the difference in minutes only between those two dates(the date format is mm/dd/yyyy hh:mm:ss)

3. They need the difference in business minutes. for example: There is an entry on sep 29 2004 at 4.30 p.m and was ended on sep 30 2004 at 10.30 a.m then difference must be 30+150 =180minutes (business hours are 8.00 a.m to 5.00 p.m)

_________________
Good LUCK...!
Pave
Back to top
Michele Pinti
Forum Enthusiast
Forum Enthusiast



Joined: 17 Jun 2002

Posts: 1471
Location: Dayton, Ohio



PostPosted: Wed Oct 06, 2004 9:34 am 
Post subject: Re: Date Difference using Teradata database

Pave wrote:
Do I need to do this using Free-Hand SQL.

No, you should be able to set this up in Designer
Quote:
1. Created and object in the universe with the format you gave me but its not parsing. so i used
cast(begin_date_time) - cast(end_date_time)

Please post your entire SQL. After creating the query open the SQL window and capture the SQL and post it here.

Quote:
2. Is there a way where i can get the difference in minutes only between those two dates(the date format is mm/dd/yyyy hh:mm:ss)

The code suggested previously will do this.

Quote:
3. They need the difference in business minutes. for example: There is an entry on sep 29 2004 at 4.30 p.m and was ended on sep 30 2004 at 10.30 a.m then difference must be 30+150 =180minutes (business hours are 8.00 a.m to 5.00 p.m)
There are not any built in functions to do this. You will have to calculate this yourself as each business will have different rules for what consititues business hours
_________________
Who's Your Friend? Search is Your Friendô

Infoview XI 3.1 SP3
Full Client Business Objects 5.1.6
Repository: SQL Server 2000 Database: Teradata 2.6

Michele Pinti
Standard Register Co.

Back to top
Pave
Forum Member
Forum Member



Joined: 13 Sep 2004

Posts: 14



PostPosted: Wed Oct 06, 2004 1:00 pm 
Post subject: Re: Date Difference using Teradata database

Thansk Michele Pinti I'll try this out.....
_________________
Good LUCK...!
Pave
Back to top
holmes
Forum Member
Forum Member



Joined: 27 Apr 2004

Posts: 23



PostPosted: Tue Nov 16, 2004 11:52 am 
Post subject: Re: Date Difference using Teradata database

This query demonstrates the components required for what you're trying to do (if I understand your situation correctly). I included a test for submissions AFTER business hours but not before - you'll have to add that to the START_TIME and END_TIME columns (if it's even required). To make this work you'll need to reference some table that stores information about your clients' business hours and join it in accordingly - that is, unless all your clients work on a 8am to 5pm work day and you can hard code the values.

I didn't include the code for the end object - what I wanted to do was show the pieces-parts that make up the end result. Also, I didn't want to put the end product together...you can decide how you want to do that.

You should be able to submit this query as-is using Queryman. Tweak the constants to see how it affects the end result. Then start piecing in the real columns. Note that MINUTE(4) is the largest minute precision you can use. That's about a week so if you have intervals greater than a week you'll have to calculate minutes a little differently, but it shouldn't be that big of a deal.

Good Luck!

Code:
SELECT
   --CONSTANTS USED IN THIS QUERY
   CAST('2004-09-29 16:30:00' AS TIMESTAMP(0)) START_DATE
   ,
   CAST('2004-09-30 10:30:00' AS TIMESTAMP(0)) END_DATE
   ,
   CAST('08:00:00' AS TIME(0)) AS BUS_START
   ,
   CAST('17:00:00' AS TIME(0)) AS BUS_END
   ,
   --THIS GETS YOUR TRUE START DATE,TIME
   CASE WHEN EXTRACT(HOUR FROM START_DATE) >= EXTRACT(HOUR FROM BUS_END)
   THEN CAST(CAST(CAST(START_DATE + INTERVAL '1' DAY AS DATE FORMAT 'YYYY-MM-DD') AS CHAR(10)) || ' ' || CAST(BUS_START AS CHAR(8)) AS TIMESTAMP(0))
   ELSE START_DATE
   END START_TIME
   ,
   --THIS GETS YOUR TRUE END DATE,TIME
   CASE WHEN EXTRACT(HOUR FROM END_DATE) >= EXTRACT(HOUR FROM BUS_END)
   THEN CAST(CAST(CAST(END_DATE + INTERVAL '1' DAY AS DATE FORMAT 'YYYY-MM-DD') AS CHAR(10)) || ' ' || CAST(BUS_START AS CHAR(8)) AS TIMESTAMP(0))
   ELSE END_DATE
   END END_TIME
   ,
   --THIS CALCULATES MINUTES ELAPSED
   CAST((END_TIME-START_TIME MINUTE(4)) AS INTEGER) MIN_ELAPSED
   ,
   --THIS CALCULATES DAYS ELAPSED
   CAST(END_TIME AS DATE)-CAST(START_TIME AS DATE) DAYS_ELAPSED
   ,
   --THIS CALCULATES MINUTES IN A BUSINESS DAY
   BUS_END - BUS_START MINUTE(4) BUS_DAY_MINUTES
   ,
   --THIS CALCULATES MINUTES NOT IN A BUSINESS DAY
   1440 - CAST(BUS_DAY_MINUTES AS INTEGER) NON_BUS_DAY_MINUTES
   ,
   --THIS CALCULATES TOTAL NUMBER OF MINUTES TO SUBTRACT FROM TOTAL NUMBER OF MINUTES ELAPSED
   DAYS_ELAPSED * NON_BUS_DAY_MINUTES NON_WORK_MINUTES
   ,
   --THIS CALCULATES TOTAL NUMBER OF MINUTES ELAPSED TAKING INTO ACCOUNT BUSINESS WORK DAY
   MIN_ELAPSED - NON_WORK_MINUTES TOTAL_ELAPSED_MINUTES
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Desktop Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0477 seconds using 17 queries. (SQL 0.0028 Parse 0.0347 Other 0.0102)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo