/// <summary>
/// Returns the source query for the specified table and partition begin date
/// </summary>
/// <param name="tableName">Table to partition</param>
/// <param name="partitionBeginDate">Begin date for partition</param>
/// <returns></returns>
public static string GetSourceQueryBasedOnDate(
string tableName,
DateTime?partitionBeginDate = null,
PartitionGranularity partitionGranularity = PartitionGranularity.Monthly)
{
string sourceQuery = null;
if (!partitionBeginDate.HasValue || partitionBeginDate == DateTime.MinValue)
{
partitionBeginDate = DateTime.Today;
}
string queryFormat = TableProcessQuerySet[tableName.ToLower()];
// Determine if the query requires calculation of a date range
bool dateRangeParametersRequired = queryFormat != null &&
queryFormat.Contains("{0}") &&
queryFormat.Contains("{1}");
if (dateRangeParametersRequired)
{
sourceQuery = BuildDateRangeParameterizedQuery(partitionBeginDate, partitionGranularity, queryFormat);
}
else
{
sourceQuery = queryFormat;
}
return(sourceQuery);
}
/// <summary>
/// Helper function to build the source query to include date ranges based on specified granularity
/// </summary>
/// <param name="partitionBeginDate"></param>
/// <param name="partitionGranularity"></param>
/// <param name="queryFormat"></param>
/// <returns></returns>
private static string BuildDateRangeParameterizedQuery(
DateTime?partitionBeginDate,
PartitionGranularity partitionGranularity,
string queryFormat)
{
string sourceQuery;
int beginDateKey = 0;
int endDateKey = int.MaxValue;
switch (partitionGranularity)
{
case PartitionGranularity.Yearly:
// January 1 of the year of the specified date
beginDateKey = partitionBeginDate.Value.Year * 10000
+ 1 * 100
+ 1;
// December 31 of the year of the specified date
endDateKey = partitionBeginDate.Value.Year * 10000
+ 12 * 100
+ 31;
break;
case PartitionGranularity.Monthly:
// first day of the month of the specified date
beginDateKey = partitionBeginDate.Value.Year * 10000
+ partitionBeginDate.Value.Month * 100
+ 1;
// last day of the month of the specified date
endDateKey = partitionBeginDate.Value.Year * 10000
+ partitionBeginDate.Value.Month * 100
+ DateTime.DaysInMonth(partitionBeginDate.Value.Year, partitionBeginDate.Value.Month);
break;
case PartitionGranularity.Daily:
// Specified date
beginDateKey = partitionBeginDate.Value.Year * 10000
+ partitionBeginDate.Value.Month * 100
+ partitionBeginDate.Value.Day;
// Next day from the of the specified date
DateTime nextDay = partitionBeginDate.Value.AddDays(1);
endDateKey = nextDay.Year * 10000
+ nextDay.Month * 100
+ nextDay.Day;
break;
}
sourceQuery = String.Format(queryFormat, beginDateKey, endDateKey);
return(sourceQuery);
}
/// <summary>
/// Generates the partition tool by the given date.
/// </summary>
/// <param name="partitionBeginDate">Begin date for partition</param>
/// <param name="partitionGranularity">Partition Granularity - Yearly, Monthly (default), Daily or undefined </param>
/// <returns>Generates the partition date and granularity</returns>
public static string GeneratePartitionKey(
DateTime?partitionBeginDate = null,
PartitionGranularity partitionGranularity = PartitionGranularity.Monthly)
{
if (!partitionBeginDate.HasValue || partitionBeginDate == DateTime.MinValue)
{
partitionBeginDate = DateTime.Today;
}
int partitionKey = 0;
switch (partitionGranularity)
{
case PartitionGranularity.Yearly:
partitionKey = partitionBeginDate.Value.Year;
break;
case PartitionGranularity.Monthly:
partitionKey = partitionBeginDate.Value.Year * 100 + partitionBeginDate.Value.Month;
break;
case PartitionGranularity.Daily:
partitionKey = partitionBeginDate.Value.Year * 10000
+ partitionBeginDate.Value.Month * 100
+ partitionBeginDate.Value.Day;
break;
case PartitionGranularity.Undefined:
partitionKey = -1;
break;
default:
partitionKey = 0;
break;
}
return(partitionKey.ToString());
}
