public override string GetDatePartOfColumn(AxisIncrement increment, string columnSql)
{
switch (increment)
{
case AxisIncrement.Day:
return(columnSql);
case AxisIncrement.Month:
return($"to_char({columnSql},'YYYY-MM')");
case AxisIncrement.Year:
return($"to_number(to_char({columnSql},'YYYY'))");
case AxisIncrement.Quarter:
return($"to_char({columnSql},'YYYY') || 'Q' || to_char({columnSql},'Q')");
default:
throw new ArgumentOutOfRangeException("increment");
}
}
public override string GetDatePartOfColumn(AxisIncrement increment, string columnSql)
{
switch (increment)
{
case AxisIncrement.Day:
return(columnSql + "::date");
case AxisIncrement.Month:
return($"to_char({columnSql},'YYYY-MM')");
case AxisIncrement.Year:
return($"date_part('year', {columnSql})");
case AxisIncrement.Quarter:
return($"to_char({columnSql},'YYYY\"Q\"Q')");
default:
throw new ArgumentOutOfRangeException(nameof(increment), increment, null);
}
}
/// <summary>
/// Gives you the equivalency check for the given axis joined to column1 column. Use this in the JOIN SQL generated by AggregateBuilder
/// </summary>
/// <param name="column1">The column name or transform from the dataset</param>
/// <param name="column2">The axis column e.g. axis.dt</param>
/// <returns></returns>
public string GetDatePartBasedEqualsBetweenColumns(AxisIncrement increment, string column1, string column2)
{
switch (increment)
{
case AxisIncrement.Day:
return(GetDatePartOfColumn(increment, column1) + "=" + column2); //truncate any time off column1, column2 is the axis column which never has time anyway
case AxisIncrement.Month:
return(string.Format("YEAR({0}) = YEAR({1}) AND MONTH({0}) = MONTH({1})", column1, column2)); //for performance
case AxisIncrement.Year:
return(GetDatePartOfColumn(increment, column1) + "=" + GetDatePartOfColumn(increment, column2));
case AxisIncrement.Quarter:
return(string.Format("YEAR({0}) = YEAR({1}) AND DATEPART(QUARTER, {0}) = DATEPART(QUARTER, {1})", column1, column2));
default:
throw new ArgumentOutOfRangeException();
}
}
/// <summary>
/// Takes the fieldname/transform from the dataset and wraps it with the date adjustment function specified by the AxisIncrement
/// </summary>
/// <param name="increment"></param>
/// <param name="columnSql"></param>
/// <returns></returns>
public override string GetDatePartOfColumn(AxisIncrement increment, string columnSql)
{
switch (increment)
{
case AxisIncrement.Day:
return(" Convert(date, " + columnSql + ")"); //Handles when there are times in the field by always converting to date
case AxisIncrement.Month:
return(" CONVERT(nvarchar(7)," + columnSql + ",126)"); //returns 2015-01
case AxisIncrement.Year:
return(" YEAR(" + columnSql + ")"); //returns 2015
case AxisIncrement.Quarter:
return(" DATENAME(year, " + columnSql + ") +'Q' + DATENAME(quarter," + columnSql + ")"); //returns 2015Q1
default:
throw new ArgumentOutOfRangeException();
}
}
public override string GetDatePartOfColumn(AxisIncrement increment, string columnSql)
{
switch (increment)
{
case AxisIncrement.Day:
return("DATE(" + columnSql + ")");
case AxisIncrement.Month:
return("DATE_FORMAT(" + columnSql + ",'%Y-%m')");
case AxisIncrement.Year:
return("YEAR(" + columnSql + ")");
case AxisIncrement.Quarter:
return("CONCAT(YEAR(" + columnSql + "),'Q',QUARTER(" + columnSql + "))");
default:
throw new ArgumentOutOfRangeException("increment");
}
}
public abstract string GetDatePartOfColumn(AxisIncrement increment, string columnSql);
