Hi
I am using a CRM table which houses contract records and I have a calendar script which links to the expiry date. I cannot get the dates to link together because of the source data.
When I pull through the data from CRM the "expiry date" is in the format dd/mm/yyyy hh:mm. This in itself does not cause the problem. The problem I have is in all the data the time is set to 23:59. So what I would see is 01/10/2011 23:59.
The calendar script (which I have pasted below) created a list of dates with 00:00 as the time.
I didn't think that would cause a problem but I think it does. The tables link together but there is no association. It might be a red herring but I don't think so.
Thanks for any help.
Range:
LOAD
min(ConExpiryDate) as contractscalstartdate,
max(ConExpiryDate) as contractscalenddate
resident CrmContracts;
//Peek out the values for later use
let vStart = peek('contractscalstartdate',-1,'Range')-1;
let vEnd = peek('contractscalenddate',-1,'Range');
let vRange = $(vEnd) - $(vStart);
//Remove Range table as no longer needed
Drop table Range;
//Generate a table with a row per date between the range above
ContractExpiryDateTable:
Load
$(vStart)+recno() as ConExpiryDate
autogenerate $(vRange);
//Calculate the Parts you need to examine
ContractsExpiryCalendar:
load
ConExpiryDate as ConExpiryDate,
// date(,'dd/mm/yyyy') as Cal_FullDate,
Year(ConExpiryDate) as ConCalendarYear,
'Q'&ceil(Month(ConExpiryDate)/3) AS ConCal_Quarter,
// right(yearname(Date,0,$(vFiscalMonthStart)),4) as Cal_FiscalYear,
// if(InYear (Date, $(vToday), -1),1) as Cal_FULL_LY, // All Dates Last Year
// if(InYear (Date, $(vToday), 0),1) as Cal_FULL_TY, // All Dates This Year
if(InYearToDate (ConExpiryDate, today(), 0),1,0) as ConCal_YTD_TY, // All Dates to Date this Year
if(InYearToDate (ConExpiryDate, today(), -1),1,0) as ConCal_YTD_LY, // All Dates to Date Last Year
if(InQuarterToDate (ConExpiryDate, today(), 0),1,0) as ConCal_QTR_TQ,
if(InQuarterToDate (ConExpiryDate, today(), -1),1,0) as ConCal_QTR_LQ,
if(InMonthToDate(ConExpiryDate, today(), 0),1,0) as ConCal_MNTH_TM,
if(InMonthToDate(ConExpiryDate, today(), -1),1,0) as ConCal_MNTH_LM,
// YTD_LY, used in Expressions Ex. Sum(Sales*YTD_LY)
quartername(ConExpiryDate) as ConCal_CalendarQuarter,
// quartername(Date,0,$(vFiscalMonthStart)) as Cal_FiscalQuarter,
// Month(Date)&'-'&right(yearname(Date,0,11),4) as Cal_FiscalMonthYear, //Fiscal!
Month(ConExpiryDate)&'-'&right(year(ConExpiryDate),4) as ConCal_MonthYear,
Month(ConExpiryDate) as ConCal_Month,
Day(ConExpiryDate) as ConCal_Day,
Week(ConExpiryDate) as ConCal_Week,
Weekday(ConExpiryDate) as ConCal_WeekDay
resident ContractExpiryDateTable;
//Tidy up
Drop table ContractExpiryDateTable;
