I have a problem, my company has a customer satisfaction program which fetches a lot of data from different tables in our database systems.
The problem is I want to show customer satisfaction percentage of a specific department only, so only employees from this specific department are revealed.
The problem is in the table that i fetch the employees from, there are duplicates of the same employee if they have switched department, so if they worked in 1 department 1 year ago, and moved to another today, it will show me both as they are registered with the same ID even though they have switched department, the logic code in our SQL program is as follows:
"KS_DRIFT.V_AGENT_ALLOCATION age ON " + (queryParams.JoinOnFirstAgent ? "FØRSTE_AGENT" : "SIDSTE_AGENT") + " = age.AGENT_INITIALS AND TRUNC(TIDSPUNKT) BETWEEN ALLOCATION_START AND ALLOCATION_END " +
"WHERE " +
"TRUNC(TIDSPUNKT) BETWEEN " + OracleConversion.ToOracleDate(queryParams.Interval.Lower) + " AND " + OracleConversion.ToOracleDate(queryParams.Interval.Upper)
I am not sure how to make sure that it only fetches the department the employee is active in today based on this sql query? I hope anyone can help me closer to a solution.
EDIT:
COMPLETE QUERY
var sql = "SELECT " +
"SPØRGSMÅL_ID, " +
"KARAKTER, " +
"COUNT(*) AS COUNT " +
"FROM " +
"KS_DRIFT.KT_KARAKTER kara " +
"LEFT JOIN " +
"KS_DRIFT.KT_BESVARELSE besv ON kara.BESVARELSE_ID = besv.EKSTERN_ID AND kara.TYPE = besv.TYPE " +
"LEFT JOIN " +
"KS_DRIFT.V_AGENT_ALLOCATION age ON " + (queryParams.JoinOnFirstAgent ? "FØRSTE_AGENT" : "SIDSTE_AGENT") + " = age.AGENT_INITIALS AND TRUNC(TIDSPUNKT) BETWEEN ALLOCATION_START AND ALLOCATION_END " +
"WHERE " +
"TRUNC(TIDSPUNKT) BETWEEN " + OracleConversion.ToOracleDate(queryParams.Interval.Lower) + " AND " + OracleConversion.ToOracleDate(queryParams.Interval.Upper) + " AND " +
"SPØRGSMÅL_ID = " + queryParams.QuestionId +
(!queryParams.IncludeCDNs.IsNullOrEmpty() ? "AND CDN IN (" + queryParams.IncludeCDNs.ToDelimitedString(", ") + ") " : "") +
(!queryParams.ExcludeCDNs.IsNullOrEmpty() ? "AND CDN NOT IN (" + queryParams.ExcludeCDNs.ToDelimitedString(", ") + ") " : "") +
(!queryParams.AgentIds.IsNullOrEmpty() ? " AND AGENT_ID IN (" + queryParams.AgentIds.ToDelimitedString(", ") + ") " : "") +
(!queryParams.TeamIds.IsNullOrEmpty() ? " AND TEAM_ID IN (" + queryParams.TeamIds.ToDelimitedString(", ") + ") " : "") +
"GROUP BY " +
"SPØRGSMÅL_ID, " +
"KARAKTER";
Error message when using the suggested query by Gerard
ORA-00932: inkonsistente datatyper: forventede DATE, fik NUMBER ved Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck) ved Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck) ved Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) ved Oracle.DataAccess.Client.OracleCommand.ExecuteReader()
ved NDDataProvider.Database.Oracle.Connection.DBConnection.ExecuteQuery(String query) i S:\s0book\ND Udviklings Dokumentation\Git Repository dump - THOMAS HER\NDSolution\DOPe Data Provider\Database\Oracle\Connection\DBConnection.cs:linje 456
OracleConversion.cs
public class OracleConversion {
public static string ToOracleValue(object value) {
if (value == null)
return "null";
if (value is string)
return OracleConversion.ToOracleString((String)value);
if (value is Enum)
return OracleConversion.ToOracleInteger((int)value);
int integerValue;
if (Int32.TryParse(value.ToString(), out integerValue))
return OracleConversion.ToOracleInteger(integerValue);
decimal decimalValue;
if (Decimal.TryParse(value.ToString(), out decimalValue))
return OracleConversion.ToOracleDecimal(decimalValue);
if (value is Boolean)
return OracleConversion.ToOracleBoolean((Boolean)value);
if (value is DateTime)
return OracleConversion.ToOracleDateTime((DateTime)value);
if (value is NDUtilities.Time)
return OracleConversion.ToOracleInteger(((NDUtilities.Time)value).SecondsOfDay);
if (value is SolutionsEntity)
return OracleConversion.ToOracleInteger(((SolutionsEntity)value).Id);
return OracleConversion.ToOracleString(value.ToString());
}
public static string ToOracleBoolean(Nullable<bool> booleanValue) {
string returnValue;
if (booleanValue == null)
returnValue = "null";
else
returnValue = (booleanValue.Value) ? "1" : "0";
return returnValue;
}
public static string ToOracleInteger(Nullable<int> integerValue) {
string returnValue;
if (integerValue == null)
returnValue = "null";
else
returnValue = integerValue.ToString();
return returnValue;
}
public static string ToOracleDecimal(Nullable<decimal> decimalValue) {
string returnValue;
if (decimalValue == null)
returnValue = "null";
else
returnValue = decimalValue.Value.ToString().Replace(",", ".");
return returnValue;
}
public static string ToOracleString(string stringValue) {
string returnValue;
if (stringValue == null)
returnValue = "null";
else {
returnValue = stringValue.Replace("'", "''");
returnValue = "'" + returnValue + "'";
}
return returnValue;
}
public static string ToOracleDate(DateTime? dateTimeValue) {
string returnValue;
if (dateTimeValue == null)
returnValue = "null";
else
returnValue = OracleConversion.ToOracleString(dateTimeValue.Value.ToString("yyyy-MM-dd"));
return returnValue;
}
public static string ToOracleDateTime(DateTime? dateTimeValue) {
string returnValue;
if (dateTimeValue == null)
returnValue = "null";
else
returnValue = "TO_DATE(" + OracleConversion.ToOracleString(dateTimeValue.Value.ToString("yyyy-MM-dd HH:mm:ss")) + ", " + OracleConversion.ToOracleString("yyyy-mm-dd hh24:mi:ss") + ")";
return returnValue;
}
}
}
