Selecting records based on Time Zone and Arrival Time

[0sprey]

CR 11 Question:
My firm uses an 800 telephone number on a 24/7 basis and on Holidays to receive & process incoming purchases from clients located across the following 4 Time Zones:

Pacific Time Mountain Time Central Time Eastern Time

The Purchase Orders are entered into the database at the exact time of purchase and both the date and time are recorded.

We have Day Shift and a Night Shift.
Night Shift takes calls from 500PM to 700AM MON to FRI based on the customer`s location Time Zone and all day SAT & SUN and Holidays.

I need a technique to select only the Purchase Orders that arrive during the “Night Shift”.
The database contains the Customer`s State so I have a formula to determine the Time Zone based on the State the customer is calling from.

Not sure how to best to progress with this task and I thought that someone here may have already coded this type of selection formula(s) in CR and may wish to help me out a wee bit.

Thanks

 

[SkipVought]

TIMES >= 1700 or < 0700

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[0sprey]

How would you also select all Purchase Orders that arrive on SAT and SUN as well.... and allowing for the times zones situation... (SAT & SUN arriving sooner in Eastern that Pacific and so on.)

 

[SkipVought]

Or...

Any date in your list of holidays (you DO have a list, yes?)

Or...

Any date where the day is SAT or SUN (not a CR guy so don't know the syntax)

So the question is, what's the DATE/TIME based on; your location or the location of the order? And where is your location relative to the time zones?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[0sprey]

The database is common and accessible to all staff in the 4 time Zones, however any report drawn will display the local date and time for the person running the report.
(and that would be me in the Eastern Time Zone.)


So as an example, a staff member entering a Purchase Order for a California Client (Pacific Time) will see 4 PM and that will show as 700PM to me in Eastern... however this would not be considered
a Night Shift order because the Client was in California and placed the order before 500PM.
The database stores the client's state ( MA, CA, etc.) so can create a formula to calculate the Client Time Zone from this data. Then another formula to create a new field, perhaps something like {client_CREATON}.
The data and time in {client_CREATON} would be a function of the Time Zone for the client.


I think expression below will work to identify the SAT & SUN condition.
If
DayOfWeek ({client_CREATON}) = 1 then "SUN"
else if"
DayOfWeek ({client_CREATON}) = 7 then "SAT

And yes I have the Holiday listing in Excel. I think I need to create a formula and use the IN Operator to somehow account for these Holidays. (not sure on the details yet)

 

[SkipVought]

Really!???

So if I, in the Central time zone, and you were looking at the very same purchase order, we would see different order date/time?

Also what about

Idaho and Oregon are split between the Mountain and Pacific time zones.
Kansas, Nebraska, North Dakota, South Dakota, and Texas are divided between Central and Mountain time zones.
Florida, Indiana, Michigan, Kentucky, and Tennessee are split between Eastern and Central time zones.
Finally, Alaska is divided between the Alaska time zone and the Hawaii-Aleutian time zone.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[0sprey]

Yes... we would see different times and perhaps even the date would be different.... as the system displays and reports on date fields by altering the value based on the local time zone the viewer / reporter is working from.
(My guess is that the system stores the Date/Time data as Greenwich Mean Time (GMT)then the CPU operating system clock is referenced to display local time and date for who ever access the data... however I am really not sure.)


For the time being, I may just work with a report that involves states like CA, ME etc, were the entire state is in one only Time Zone. After the other aspects of the report are worked out I may then have to use zip codes to identify the Time Zone.
Quote:

Idaho and Oregon are split between the Mountain and Pacific time zones.
Kansas, Nebraska, North Dakota, South Dakota, and Texas are divided between Central and Mountain time zones.
Florida, Indiana, Michigan, Kentucky, and Tennessee are split between Eastern and Central time zones.
Finally, Alaska is divided between the Alaska time zone and the Hawaii-Aleutian time zone.

 

[SkipVought]

So are you working from a report, or are you working from the database? I can't see a database system changing data, whereas a reporting system might.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[0sprey]

I am using the OBDC connection from within CR11 to access the SQL database

 

[SkipVought]

So you're telling me that CR converts the Date/Time based in YOUR location, and then your report needs to convert it back to the originating time zone?

I've used Business Objects where a specially designed universe can be used to transform some things like Date/Time, I suppose.

The WHERE CLAUSE is where you would perform the transform of Date/Time to test for >=1700 or < 0700 and the other two criteria.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[0sprey]

yes.... as I need to know the client's local time when the staff entered their Purchase Order.... and select only those records where the client's local time was after hours ( TIMES >= 1700 or < 0700 or SAT or SUN local client time), so I can determine if it was processed by the Night Shift.

And I need CR11 to give me a report the shows, for example, all Purchase Orders preformed by Night Shift for the past week or month or quarter....etc.