asked on

Selection between two dates and time range.

Hi Experts

I have two fields. 1--is a date field (create date) and 1 is a time (but is actually a string format). What I need to do is two things. 1--convert the time (create time) from 15:00:00 (string) to 15:00:00 (time). 2---I need to make a selection range that is something like this:
Yesterday at 3 pm through today at 9 am. I've have tried many things but just can't seem to figure it out. Your help is once again needed and appreciated.

Andy

James0628

If the format of the time string is consistent ("15:00:00", "9:15:30", etc.), you can use the Time function to convert it to a time. Then you can use the DateTime function to combine the date field and the converted time -- DateTime ({date field}, Time ({time field}))

If time field does not always contain a valid time (eg. if it could be blank), you can use the IsTime function to test for that, and maybe replace it with a default time, like midnight.

As for your range, is that something that the users will select using parameters, or is that a fixed range and it will always be from 3 PM yesterday to 9 AM today?

If it's fixed, you can use something like:

DateTime ({date field}, Time ({time field})) >= DateTime (CurrentDate - 1, Time (15, 0, 0)) and
DateTime ({date field}, Time ({time field})) <= DateTime (CurrentDate, Time (9, 0, 0))

Edit:
I just wanted to add that using DateTime to combine your separate date and time fields might affect the report's performance, if CR is not able to pass that part of the test to the server (ie. if CR does not know how to tell the server to convert the time string and combine the date and time). If that's a problem, you may need to change the report's datasource, and use a query that combines the date and time, so that CR doesn't have to do it.

James

spudmcc

ASKER

Yes it is a fixed range and will be the same every day. I tried your formula and it came back with error message "Time field Required". Remember that the time field is a string type and not a time type. I don't know why the date is correct but the time isn't but that is what I have to deal with.

A

James0628

I assume that you replaced {date field} and {time field} with your actual field names.

The Time function is there to convert the time string in your field into a time.

If you're still getting an error, post your formula.

James

spudmcc

ASKER

{HDHIERARCHY.Zone Name} = "WHITEOAKWASHINGTONPOST" and
{SubscriptionTran1.TranTypeID} in ["Complaint"] and
{OccupantPhone1.PhoneNumberTypeID} = "HOME" and
not ({Complaint1.ComplaintCodeID} in ["Z_FREEWK"]) and
DateTime ({Complaint1.ComplaintDate}, Time ({Complaint1.ComplaintTime}) >= DateTime (CurrentDate - 1, Time (15, 0, 0)) and
DateTime ({Complaint1.ComplaintDate}, Time ({Complaint1.ComplaintTime}) <= DateTime (CurrentDate, Time (9, 0, 0))))

Mike McCracken

Do all records have a valid time?
Can any of the time fields be NULL?

You might create a report with just the Date and time fields. You could filter it for dates after 1 Dec 2016.
Verify all time fields are of the form hh:mm:ss and have valid time;

If they all look good then add a filter for just the date and modify your selection formula to

{Complaint1.ComplaintDate} >= CurrentDate - 1 AND
{HDHIERARCHY.Zone Name} = "WHITEOAKWASHINGTONPOST" and
{SubscriptionTran1.TranTypeID} in ["Complaint"] and
{OccupantPhone1.PhoneNumberTypeID} = "HOME" and
not ({Complaint1.ComplaintCodeID} in ["Z_FREEWK"]) and 
DateTime ({Complaint1.ComplaintDate}, Time ({Complaint1.ComplaintTime}) >= DateTime (CurrentDate - 1, Time (15, 0, 0)) and
DateTime ({Complaint1.ComplaintDate}, Time ({Complaint1.ComplaintTime}) <= DateTime (CurrentDate, Time (9, 0, 0))))

Open in new window

If you see any bad times or NULL time then you need to filter them out.

mlmcc

spudmcc

ASKER

No null values at all. The error message says Date-Time field required here and it highlights the "time" field.

Mike McCracken

What version of Crystal?
As I recall the string option was not in some early versions.

mlmcc

spudmcc

ASKER

Crystal 2013

James0628

You're missing a ) on the DateTime functions for your fields. You have

DateTime ({Complaint1.ComplaintDate}, Time ({Complaint1.ComplaintTime})

That should be

DateTime ({Complaint1.ComplaintDate}, Time ({Complaint1.ComplaintTime}))

I had both )'s in my formula. I guess you lost one somewhere. :-)

Don't forget to change both places where you used that.

Also, I think you've got 2 (?) extra )'s at the end.

James

spudmcc

ASKER

Making the changes you noted eliminated the error message but now another issue. Before it was taking about a minute to run the report and now it is taking hours. Thoughts???

SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

spudmcc

ASKER

Yes I did try the selection query. It is still taking hours. Yes, the times are static and everything is two digits.

James0628

As mlmcc said, it sounds like the date and time tests aren't being passed to the server. The possibility had occurred to me, but I was hoping that CR would be able to pass them.

Out of curiosity, what db are you using? That might affect your options (eg. Does it have a function that combines a date and time into a datetime?).

Just to make sure, did you include the {Complaint1.ComplaintDate} >= CurrentDate - 1 part of the tests that mlmcc posted? It seems like that should be passed to the server, in which case it's hard to see how the report could take that long, unless you have something like millions of records every day.

You could go to Database > Show SQL Query, check the Where clause, and post it here, so that we can see exactly what is being sent to the server.

James

spudmcc

ASKER

prod-cm
SELECT "SubscriptionTran1"."TranTypeID", "SubscriptionTran1"."TranDate", "SubscriptionTran1"."SubscriptionID", "SubscriptionTran1"."TranNumber", "Subscription1"."DeliveryScheduleID", "Complaint1"."ComplaintCodeID", "Complaint1"."Redeliver", "Complaint1"."CreateDate", "Complaint1"."CreateTime", "Complaint1"."ComplaintDate", "Complaint1"."ComplaintTime", "Complaint1"."DaysAdjust", "RouteSubscription1"."RouteID", "Subscription1"."OccupantID", "Subscription1"."AddressID"
FROM (("CM"."PUB"."SubscriptionTran" "SubscriptionTran1" INNER JOIN "CM"."PUB"."Subscription" "Subscription1" ON "SubscriptionTran1"."SubscriptionID"="Subscription1"."SubscriptionID") INNER JOIN "CM"."PUB"."RouteSubscription" "RouteSubscription1" ON "SubscriptionTran1"."SubscriptionID"="RouteSubscription1"."SubscriptionID") INNER JOIN "CM"."PUB"."Complaint" "Complaint1" ON ("SubscriptionTran1"."SubscriptionID"="Complaint1"."SubscriptionID") AND ("SubscriptionTran1"."TranNumber"="Complaint1"."TranNumber")
WHERE "Complaint1"."ComplaintCodeID"<>'Z_FREEWK' AND "SubscriptionTran1"."TranTypeID"='Complaint'
EXTERNAL JOIN RouteSubscription1.RouteID={?AOPCNYPSQLDB1.win.dowjones.net: HDHIERARCHY.Route Code} AND Subscription1.OccupantID={?Prod-Address: Occupant1.OccupantID} AND Subscription1.AddressID={?Prod-Address: Address1.AddressID}

AOPCNYPSQLDB1.win.dowjones.net
SELECT "HDHIERARCHY"."District Code", "HDHIERARCHY"."Zone Name", "HDHIERARCHY"."Route Code"
FROM "NYPDELIVERY"."dbo"."Hierarchy" "HDHIERARCHY"
WHERE "HDHIERARCHY"."Route Code"={?prod-cm: RouteSubscription1.RouteID} AND "HDHIERARCHY"."Zone Name"=N'WHITEOAKWASHINGTONPOST'

Prod-Address
SELECT "Occupant1"."DeliveryInstructions", "Occupant1"."FirstName", "Occupant1"."MiddleInitial", "Occupant1"."LastName", "Occupant1"."OccupantID"
FROM "CMADDRESS"."PUB"."Occupant" "Occupant1"
WHERE "Occupant1"."OccupantID"={?prod-cm: Subscription1.OccupantID}
EXTERNAL JOIN Occupant1.OccupantID={?Prod-Address: OccupantPhone1.OccupantID}

Prod-Address
SELECT "OccupantPhone1"."PhoneNumberTypeID", "OccupantPhone1"."AreaCode", "OccupantPhone1"."Phone", "OccupantPhone1"."OccupantID"
FROM "CMADDRESS"."PUB"."OccupantPhone" "OccupantPhone1"
WHERE "OccupantPhone1"."OccupantID"={?Prod-Address: Occupant1.OccupantID} AND "OccupantPhone1"."PhoneNumberTypeID"='HOME'

Prod-Address
SELECT "Address1"."HouseNumber", "Address1"."PreDirectional", "Address1"."StreetName", "Address1"."StreetSuffixID", "Address1"."PostDirectional", "Address1"."UnitDesignatorID", "Address1"."UnitNumber", "Address1"."CityID", "Address1"."StateID", "Address1"."ZipCode", "Address1"."AddressID"
FROM "CMADDRESS"."PUB"."Address" "Address1"
WHERE "Address1"."AddressID"={?prod-cm: Subscription1.AddressID}

James0628

That seems to be several separate queries. What is the datasource for the report? Is it just some tables, or a CR Command, or something else?

James

spudmcc

ASKER

We have a relational database server where our primary system is stored. It is composed of 600 different tables in 6 different databases. Prod-CM and Prod-Address. This query also goes out to a SQL server (aopcnypsqldb1) to pull in another table.

All of this has worked in the past without any issues. The only thing that has changed was the request to add a "time element" where we could pull transactions from 3 pm on the day before through 9 am on the current day. We used to just pull all current day and current day-1 without any consideration for time.

James0628

If you change the record selection back the way it was, so that it's only checking the date and not the time, does the report performance go back to normal? If so, can you check "Show SQL Query" and post what is in the Where after that change?

James

spudmcc

ASKER

When I went back to the original query (without the time selection) it ran in less than 1 minute.

prod-cm
SELECT "SubscriptionTran1"."TranTypeID", "SubscriptionTran1"."TranDate", "SubscriptionTran1"."SubscriptionID", "SubscriptionTran1"."TranNumber", "Subscription1"."DeliveryScheduleID", "Complaint1"."ComplaintCodeID", "Complaint1"."Redeliver", "Complaint1"."CreateDate", "Complaint1"."CreateTime", "Complaint1"."ComplaintDate", "Complaint1"."ComplaintTime", "Complaint1"."DaysAdjust", "RouteSubscription1"."RouteID", "Subscription1"."OccupantID", "Subscription1"."AddressID"
FROM (("CM"."PUB"."SubscriptionTran" "SubscriptionTran1" INNER JOIN "CM"."PUB"."Subscription" "Subscription1" ON "SubscriptionTran1"."SubscriptionID"="Subscription1"."SubscriptionID") INNER JOIN "CM"."PUB"."RouteSubscription" "RouteSubscription1" ON "SubscriptionTran1"."SubscriptionID"="RouteSubscription1"."SubscriptionID") INNER JOIN "CM"."PUB"."Complaint" "Complaint1" ON ("SubscriptionTran1"."SubscriptionID"="Complaint1"."SubscriptionID") AND ("SubscriptionTran1"."TranNumber"="Complaint1"."TranNumber")
WHERE "Complaint1"."ComplaintCodeID"<>'Z_FREEWK' AND "SubscriptionTran1"."TranTypeID"='Complaint' AND "Complaint1"."ComplaintDate">={d '2016-12-29'}
EXTERNAL JOIN RouteSubscription1.RouteID={?AOPCNYPSQLDB1.win.dowjones.net: HDHIERARCHY.Route Code} AND Subscription1.OccupantID={?Prod-Address: Occupant1.OccupantID} AND Subscription1.AddressID={?Prod-Address: Address1.AddressID}

AOPCNYPSQLDB1.win.dowjones.net
SELECT "HDHIERARCHY"."District Code", "HDHIERARCHY"."Zone Name", "HDHIERARCHY"."Route Code"
FROM "NYPDELIVERY"."dbo"."Hierarchy" "HDHIERARCHY"
WHERE "HDHIERARCHY"."Route Code"={?prod-cm: RouteSubscription1.RouteID} AND "HDHIERARCHY"."Zone Name"=N'WHITEOAKWASHINGTONPOST'

Prod-Address
SELECT "Occupant1"."DeliveryInstructions", "Occupant1"."FirstName", "Occupant1"."MiddleInitial", "Occupant1"."LastName", "Occupant1"."OccupantID"
FROM "CMADDRESS"."PUB"."Occupant" "Occupant1"
WHERE "Occupant1"."OccupantID"={?prod-cm: Subscription1.OccupantID}
EXTERNAL JOIN Occupant1.OccupantID={?Prod-Address: OccupantPhone1.OccupantID}

Prod-Address
SELECT "OccupantPhone1"."PhoneNumberTypeID", "OccupantPhone1"."AreaCode", "OccupantPhone1"."Phone", "OccupantPhone1"."OccupantID"
FROM "CMADDRESS"."PUB"."OccupantPhone" "OccupantPhone1"
WHERE "OccupantPhone1"."OccupantID"={?Prod-Address: Occupant1.OccupantID} AND "OccupantPhone1"."PhoneNumberTypeID"='HOME'

Prod-Address
SELECT "Address1"."HouseNumber", "Address1"."PreDirectional", "Address1"."StreetName", "Address1"."StreetSuffixID", "Address1"."PostDirectional", "Address1"."UnitDesignatorID", "Address1"."UnitNumber", "Address1"."CityID", "Address1"."StateID", "Address1"."ZipCode", "Address1"."AddressID"
FROM "CMADDRESS"."PUB"."Address" "Address1"
WHERE "Address1"."AddressID"={?prod-cm: Subscription1.AddressID}

ASKER CERTIFIED SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

spudmcc

ASKER

Gentlemen we have success. I so much appreciate both of your time, patience and sharing of your knowledge. It is now working great with the time element that was requested by my end users.

Thank you so very much and have a fantastic 2017.

James0628

You're welcome. I'm glad that worked for you.

James