Time Calc
I need to do a small date calc. I need to set a field with a value if a Date field is greater than 36 hours but less than 48. I'm looking at this ( 'Type' = Normal) AND (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 48)) AND (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 36)) Then do my set field. Planned Change Date is a date in the future. Am I close? J Thanks, Dave ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: Time Calc
David, ( 'Type' = Normal) AND (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 48)) OR (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 36)) would cut it.. With AND instead of OR you will get no results to set your Type field as the result will always be FALSE.. Joe -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org]on Behalf Of Boylan, David Sent: Monday, October 19, 2009 1:50 PM To: arslist@ARSLIST.ORG Subject: Time Calc ** I need to do a small date calc. I need to set a field with a value if a Date field is greater than 36 hours but less than 48. I’m looking at this ( 'Type' = Normal) AND (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 48)) AND (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 36)) Then do my set field. Planned Change Date is a date in the future. Am I close? J Thanks, Dave ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: Time Calc
No, that’s not right. Putting OR will always cause it to be true, because every date will either be less than 48 hours in the future or greater than 36 hours in the future. It should stay AND. It looks correct to me with AND so long as it’s a Date Time field and not just a Date field (in which case you can’t do this anyway). Lyle From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Joe DeSouza Sent: Monday, October 19, 2009 12:25 PM To: arslist@ARSLIST.ORG Subject: Re: Time Calc ** David, ( 'Type' = Normal) AND (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 48)) OR (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 36)) would cut it.. With AND instead of OR you will get no results to set your Type field as the result will always be FALSE.. Joe -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org]on Behalf Of Boylan, David Sent: Monday, October 19, 2009 1:50 PM To: arslist@ARSLIST.ORG Subject: Time Calc ** I need to do a small date calc. I need to set a field with a value if a Date field is greater than 36 hours but less than 48. I’m looking at this ( 'Type' = Normal) AND (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 48)) AND (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 36)) Then do my set field. Planned Change Date is a date in the future. Am I close? ☺ Thanks, Dave _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers Are_ NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: Time Calc
I believe form a performance perspective it would be better to move $TIMESTAMP$ to the right side of the equation. Giving you ( 'Type' = Normal) AND (( 'Planned Change Date') = $TIMESTAMP$ + ((60 * 60) * 48)) AND (( 'Planned Change Date') = $TIMESTAMP$ + ((60 * 60) * 36)) From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Lyle Taylor Sent: Monday, October 19, 2009 2:31 PM To: arslist@ARSLIST.ORG Subject: Re: Time Calc No, that’s not right. Putting OR will always cause it to be true, because every date will either be less than 48 hours in the future or greater than 36 hours in the future. It should stay AND. It looks correct to me with AND so long as it’s a Date Time field and not just a Date field (in which case you can’t do this anyway). Lyle From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Joe DeSouza Sent: Monday, October 19, 2009 12:25 PM To: arslist@ARSLIST.ORG Subject: Re: Time Calc ** David, ( 'Type' = Normal) AND (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 48)) OR (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 36)) would cut it.. With AND instead of OR you will get no results to set your Type field as the result will always be FALSE.. Joe -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org]on Behalf Of Boylan, David Sent: Monday, October 19, 2009 1:50 PM To: arslist@ARSLIST.ORG Subject: Time Calc ** I need to do a small date calc. I need to set a field with a value if a Date field is greater than 36 hours but less than 48. I’m looking at this ( 'Type' = Normal) AND (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 48)) AND (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 36)) Then do my set field. Planned Change Date is a date in the future. Am I close? J Thanks, Dave _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers Are_ NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: Time Calc
David,
You might want also want to optimize your search while the Escalation (I'm
assuming you would use that) does what it needs to especially if you are
working against a large set of records.
Unoptimized:
( 'Type' = Normal) AND ((( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60)
* 48)) OR (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 36)))
will not optimize the search for dates meeting that criteria.. First of all you
would need to index your 'Planned Change Date' field. That by itself will do
only half the job to optimize the search criteria.. Change it to..
Optimized:
( 'Type' = Normal) AND (('Planned Change Date' = (((60 * 60) * 48) +
$TIMESTAMP$)) OR ('Planned Change Date' = (((60 * 60) * 36) + $TIMESTAMP$)))
That way the system will calculate the constant once, and equate it to an
indexed field, and find the results to set faster..
Joe
PS: I missed out a set of brackets in my statement in my previous mail below..
Anyway's that query while it would work will not be optimized. Perform the
Optimization steps I advised above to get better faster results..
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org]on Behalf Of Joe DeSouza
Sent: Monday, October 19, 2009 2:25 PM
To: arslist@ARSLIST.ORG
Subject: Re: Time Calc
**
David,
( 'Type' = Normal) AND (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60)
* 48)) OR (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 36))
would cut it.. With AND instead of OR you will get no results to set your Type
field as the result will always be FALSE..
Joe
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org]on Behalf Of Boylan, David
Sent: Monday, October 19, 2009 1:50 PM
To: arslist@ARSLIST.ORG
Subject: Time Calc
**
I need to do a small date calc. I need to set a field with a value if a Date
field is greater than 36 hours but less than 48. I’m looking at this
( 'Type' = Normal) AND (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60)
* 48)) AND (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 36))
Then do my set field. Planned Change Date is a date in the future. Am I close? J
Thanks,
Dave
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: Time Calc
Lyle,
You are correct. I was not thinking correctly.. Do not replace the AND with OR,
else it will always be true. (Not sure what I was thinking :-) ) So I apologize
for a wrong suggestion. Do change the position of $TIMESTAMP$ to avoid more
calculations than is necessary..
( 'Type' = Normal) AND (('Planned Change Date' = (((60 * 60) * 48) +
$TIMESTAMP$)) AND ('Planned Change Date' = (((60 * 60) * 36) + $TIMESTAMP$)))
ALSO, do not forget to index 'Planned Change Date' indexed if you are working
with a significantly large number of records.
Joe
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org]on Behalf Of Lyle Taylor
Sent: Monday, October 19, 2009 2:31 PM
To: arslist@ARSLIST.ORG
Subject: Re: Time Calc
No, that’s not right. Putting OR will always cause it to be true, because
every date will either be less than 48 hours in the future or greater than 36
hours in the future. It should stay AND. It looks correct to me with AND so
long as it’s a Date Time field and not just a Date field (in which case you
can’t do this anyway).
Lyle
From:Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org] On Behalf Of Joe DeSouza
Sent: Monday, October 19, 2009 12:25 PM
To: arslist@ARSLIST.ORG
Subject: Re: Time Calc
**
David,
( 'Type' = Normal) AND (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60)
* 48)) OR(( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 36))
would cut it.. With ANDinstead of ORyou will get no results to set your Type
field as the result will always be FALSE..
Joe
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org]on Behalf Of Boylan, David
Sent: Monday, October 19, 2009 1:50 PM
To: arslist@ARSLIST.ORG
Subject: Time Calc
**
I need to do a small date calc. I need to set a field with a value if a Date
field is greater than 36 hours but less than 48. I’m looking at this
( 'Type' = Normal) AND (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60)
* 48)) AND (( 'Planned Change Date' - $TIMESTAMP$) = ((60 * 60) * 36))
Then do my set field. Planned Change Date is a date in the future. Am I close? J
Thanks,
Dave
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
DST - Difference between 5.1.2 and 7.0 on Business Time calc
No wonder people get frustrated when trying to prepare for a change. I have been doing some testing on a business time calc we run that determine how the number of hours from when a service provider arrives at the customer and when they leave the customer. Have been using this calc for years and it's pretty straight forward and the result is # of hours. So with all the DST talk going around I decided to do some testing. On my 5.1.2 production box (no windows patch yet) when I did the following calc I received these results: In 3/2/07 9:00 am Out 3/3/07 9:00 am # hours = 24 In 3/12/07 9:00 am Out 3/14/07 9:00 am # hours = 24 In 3/8/07 9:00 am Out 3/12/07 9:00 am # hours = 96 These are all correct results based on our work (24 hr) and holiday schedules. When I did the same test on our dev box with is at v7 unpatched, Windows updates done, server time current date my results were In 3/2/07 9:00 am Out 3/3/07 9:00 am # hours = 23 In 3/12/07 9:00 am Out 3/14/07 9:00 am # hours = 23 In 3/8/07 9:00 am Out 3/12/07 9:00 am # hours = 95 Consistently one hour short. When I did the same test on that dev box with the date set to March 12th, the results were the same as the results above. What concerns me most is that the 3/2 to 3/3 calculations are wrong. That has nothing to do with DST. The dev system has the original workflow from before it was upgrade which is the same as on the prod system. There has been changes to this workflow in well over a year. And in actuality the only calc that should be wrong is the 3rd one that spans 3/11. Because once we're on DST even if 9:00 am is 8:00 am to the system, both 9:00 am's will be 8:00 am's and the calc will still be 24 hours. From what I can see I think we're all right on 5.1.2 for what we do with business time that is critical. If the times are wrong on some of the things it has minimal impact. Anyone see anything wrong with the premises above. Thanks, Susan Server: ARS 5.1.2 Patch 1428 OS: Windows NT 5.0 2CPU's 4G Memory Database: Oracle 9i2 User: ARS 5.1.2 Patch 1316 User OS: XP, NT, Win 2000 Admin: ARS 5.1.2 Patch 1289 Crystal that created reports: 9 Susan Palmer ShopperTrak 200 W Monroe St 11th Floor Chicago, IL 60606 Office: 312-529-5325 Cell: 302-502-7687 [EMAIL PROTECTED] ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
