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);