Not reading data beyond a specific date

[eliner801]

I have a report that looks at the start and end dates of trip.
Calculates the # of days between them. The parameter for looking at the data is the start and end date. The date that the booking was made in not important for this report. For some reason the report no longer looks at any data passed the 20th of Nov 09. When I run the report for the period 01.01.10-06.30.10 it comes up blank. I have been running the report for a couple of years with no problems. The database itself has not changed other then new invoices added. Other reports are working fine. Any ideas as to why it no longer is reading the complete database?
Thanks in advance for your input.

 

[lbass]

Have you verified the database? Database->verify database? I would also check the SQL query to see if that helps identify the issue (database->show SQL query). What version of CR are you using?

-LB

 

[eliner801]

Morning - yes the database has been verified. On Crystal XI.
Here is the SQL query. I cannot see anything that would seems out of place, however, a 2nd set of eyes are always good. Thanks for your help.

SELECT "VENPROFILE"."NAME", "INVOICE"."INVOICETYPE_LINKCODE", "INVOICE"."BRANCH_LINKNO", "CLTPROFILE"."NAME", "RESCARD"."TRIPSOURCE", "RESCARDRESERVATION"."CONFIRMATIONNUMBER", "RESCARD"."RESCARDNO", "RESCARD"."CREATEDATE", "BOOKING"."VENDORPAYSTATUS_LINKCODE", "AGTPROFILE"."NAME", "RESCARDRESERVATION"."NOOFPAX", "RESCARD"."RESCYCLE", "BOOKING"."SUBMITTO_LINKCODE", "RESCARDRESERVATION"."STATUS", "CLTPROFILETYPE"."PROFILETYPE", "RESCARD"."DESTINATIONBYREGION", "RESCARDRESERVATION"."ENDDATE", "RESCARDRESERVATION"."STARTDATE", "RESCARDPROVIDER"."STARTDATE"
FROM ((((((("INVOICE" "INVOICE" INNER JOIN "BOOKING" "BOOKING" ON "INVOICE"."INVOICENO"="BOOKING"."INVOICE_LINKNO") INNER JOIN "PROFILE" "CLTPROFILE" ON "INVOICE"."CLIENT_LINKNO"="CLTPROFILE"."PROFILENO") INNER JOIN "RESCARD" "RESCARD" ON "CLTPROFILE"."PROFILENO"="RESCARD"."PROFILE_LINKNO") INNER JOIN "PROFILETYPE" "CLTPROFILETYPE" ON "CLTPROFILE"."PROFILETYPE_LINKCODE"="CLTPROFILETYPE"."PROFILETYPECODE") INNER JOIN "PROFILE" "VENPROFILE" ON "BOOKING"."VENDOR_LINKNO"="VENPROFILE"."PROFILENO") INNER JOIN "RESCARDRESERVATION" "RESCARDRESERVATION" ON (("BOOKING"."VENDOR_LINKNO"="RESCARDRESERVATION"."VENDOR_LINKNO") AND ("BOOKING"."RESERVATION_LINKNO"="RESCARDRESERVATION"."RESERVATIONNO")) AND ("RESCARD"."RESCARDNO"="RESCARDRESERVATION"."RESCARD_LINKNO")) INNER JOIN "RESCARDPROVIDER" "RESCARDPROVIDER" ON "BOOKING"."RESERVATION_LINKNO"="RESCARDPROVIDER"."RESERVATION_LINKNO") INNER JOIN "PROFILE" "AGTPROFILE" ON "RESCARD"."CREATEAGENT_LINKNO"="AGTPROFILE"."PROFILENO"
WHERE ("INVOICE"."BRANCH_LINKNO"=1 OR "INVOICE"."BRANCH_LINKNO"=2) AND "BOOKING"."VENDORPAYSTATUS_LINKCODE"<>'V' AND NOT ("VENPROFILE"."NAME"='Csa Travel Protection' OR "VENPROFILE"."NAME"='Foremost Travel & Tours' OR "VENPROFILE"."NAME"='Hollywood Days Inn' OR "VENPROFILE"."NAME"='Hotels.Com' OR "VENPROFILE"."NAME"='Rail Europe' OR "VENPROFILE"."NAME"='Travel Guard International' OR "VENPROFILE"."NAME"='Travelnow') AND "RESCARD"."TRIPSOURCE"<>'Travelnow' AND "RESCARD"."RESCYCLE"<>'Cancelled' AND ("RESCARDPROVIDER"."STARTDATE">={ts '2010-01-01 00:00:00'} AND "RESCARDPROVIDER"."STARTDATE"<{ts '2010-01-31 00:00:00'}) AND ("BOOKING"."SUBMITTO_LINKCODE"='C' OR "BOOKING"."SUBMITTO_LINKCODE"='S') AND "RESCARDRESERVATION"."STATUS"<>'Cancelled' AND "CLTPROFILETYPE"."PROFILETYPE"<>'Corporate' AND "RESCARD"."DESTINATIONBYREGION"='Riu Resorts'
ORDER BY "RESCARD"."DESTINATIONBYREGION"

 

[lbass]

I would check to make sure the values used in the where clause are still valid, e.g., branch link numbers, and also I wonder about the case of the values used in the where clause. You might want to check in file->report options and see if you have "Database server is case insensitive" checked.

-LB

 

[eliner801]

Hi - just wanted to thank you for your assistance. After many hours of wasted time came to find out that in Nov the co that writes the database did an update and changed the field in question, failed to notify users of the change. Spent 1.5 hrs on a webex with the report writer there and that is how we found the problem. Had to upload my database detail to them as when we corrected the crystal report it kept crashing. Am waiting for them to find out what the program problem is and then fix the report. Again, thanks for you help.