Lets say your timecards table is called timecards and your list of
users is in a table called users. Your query for suggestion 3 would
look like this:
select u.UserID, u.UserName,
SUM(tc1.Hours) as mon, SUM(tc2.Hours) as tue,
SUM(tc3.Hours) as wed, SUM(tc4.Hours) as thu, SUM(tc5.Hours) as fri
from users u
inner join timecards tc1 on tc1.UserID = u.UserID and tc1.DateWorked =
'1/1/2006'
inner join timecards tc2 on tc2.UserID = u.UserID and tc2.DateWorked =
'1/2/2006'
inner join timecards tc3 on tc3.UserID = u.UserID and tc3.DateWorked =
'1/3/2006'
inner join timecards tc4 on tc4.UserID = u.UserID and tc4.DateWorked =
'1/4/2006'
inner join timecards tc5 on tc5.UserID = u.UserID and tc5.DateWorked =
'1/5/2006'
group by u.UserID, u.UserName
You'd need to set the actual dates for the week you are interested in
for the join clauses.
I suggestion 2, yes you could use a DataReader or a DataAdapter to get
the data, and you can put it into a dataset (generic or strongly typed)
as you please. However, once the data is retrieved, it is still in the
form:
UserID, DateWorked, HoursWorkedOnThatDate
So now you want to create a new DataSet and add a new DataTable to it.
Manually add 6 (or 8 if you count weekends) DataColumns to the table -
the first column is the user and the other 5 (or 7) columns will
contain the hours for that user for each day. Now loop over the users
in your raw data. For each user, add a new DataRow to the DataTable
and set the first column value to the user id. Next, within the user
loop, loop over your 5 (or 7) dates and add up the hours (in memory,
using your raw data that you have already retrieved from the database)
for that user for each of the days and set the appropriate column of
the DataRow to the number of hours. Finally, bind your datagrid to
this new DataSet.
I don't have time right now to do a code example that's specific for
your case. However, below is an example of the same thing but more
generalized for any number of columns. In this example, your "users"
are my "limits", your "days" are my "samples", and your "hours" are my
"results" (although I'm doing some other stuff like comparing the
result in each cell of the matrix to a predefined limit value). The
main logic is in the CompareCrossTab method, where I create a new
DataSet, add a DataTable with the appropriate columns, then loop
through and add DataRows.
HTH,
John H.
== Example Code - Crosstab.cs ==
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using mystuff.common;
namespace mystuff.crosstab
{
public class Crosstab
{
public Crosstab()
{
}
public DataSet CompareCrossTab(SqlConnection conn, ArrayList
samplePKs, int limitTypeId)
{
DataSet ds = CreateEmptyCompareDataSet(samplePKs);
DataTable dt = ds.Tables["compare"];
if (conn.State != ConnectionState.Open)
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
ArrayList limits = GetLimits(cmd, limitTypeId);
foreach (LimitInfo li in limits)
{
DataRow dr = dt.NewRow();
SetLimitColumns(dr, li);
foreach (SamplePK sample in samplePKs)
{
AddSampleData(cmd, dr, li, sample);
}
dt.Rows.Add(dr);
}
return ds;
}
private DataSet CreateEmptyCompareDataSet(ArrayList samplePKs)
{
DataSet ds;
DataTable dt;
DataColumn dc;
ds = new DataSet();
dt = new DataTable("compare");
dc = new DataColumn("matrix", Type.GetType("System.String"));
dt.Columns.Add(dc);
dc = new DataColumn("analyte_class", Type.GetType("System.String"));
dt.Columns.Add(dc);
dc = new DataColumn("analyte", Type.GetType("System.String"));
dt.Columns.Add(dc);
dc = new DataColumn("cas_number", Type.GetType("System.String"));
dt.Columns.Add(dc);
dc = new DataColumn("operator", Type.GetType("System.String"));
dt.Columns.Add(dc);
dc = new DataColumn("limit", Type.GetType("System.Decimal"));
dt.Columns.Add(dc);
dc = new DataColumn("limit_units", Type.GetType("System.String"));
dt.Columns.Add(dc);
foreach (SamplePK sample in samplePKs)
{
string colName;
colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|R";
dc = new DataColumn(colName, Type.GetType("System.String"));
dt.Columns.Add(dc);
colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|U";
dc = new DataColumn(colName, Type.GetType("System.String"));
dt.Columns.Add(dc);
colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|Q";
dc = new DataColumn(colName, Type.GetType("System.String"));
dt.Columns.Add(dc);
}
ds.Tables.Add(dt);
return ds;
}
private ArrayList GetLimits(SqlCommand cmd, int limitTypeId)
{
ArrayList limits = new ArrayList();
string sql;
sql = "SELECT analyte, matrix, analyte_class, cas_number, operator,
limit, limit_units ";
sql += "FROM limits ";
sql += "WHERE limit_type_id = " + limitTypeId + " ";
sql += "AND limit IS NOT NULL ";
sql += "AND cas_number IS NOT NULL ";
sql += "ORDER BY matrix, analyte_class, analyte";
cmd.CommandText = sql;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
LimitInfo li = new LimitInfo();
li.analyte = rdr.GetString(0);
li.matrix = rdr.GetString(1);
li.analyteClass = rdr.GetString(2);
li.casNum = rdr.GetString(3);
li.op = rdr.GetString(4);
li.limit = rdr.GetDecimal(5);
li.units = rdr.GetString(6);
limits.Add(li);
}
rdr.Close();
return limits;
}
private void SetLimitColumns(DataRow dr, LimitInfo li)
{
dr["matrix"] = li.matrix;
dr["analyte_class"] = li.analyteClass;
dr["analyte"] = li.analyte;
dr["cas_number"] = li.casNum;
dr["operator"] = li.op;
dr["limit"] = li.limit;
dr["limit_units"] = li.units;
}
private void AddSampleData(SqlCommand cmd, DataRow dr, LimitInfo li,
SamplePK sample)
{
string sql;
sql = "SELECT result_text, qualifier_combined, units, PQL_text ";
sql += "FROM lab_results ";
sql += "INNER JOIN matrix_xref ON matrix_xref.lab_matrix =
lab_results.matrix ";
sql += "WHERE field_sample_id = '" +
AppUtils.EncodeForDB(sample.SampleID) + "' ";
sql += "AND collection_date = '" +
sample.SampleDate.ToShortDateString() + "' ";
sql += "AND limit_matrix = '" + AppUtils.EncodeForDB(li.matrix) +
"' ";
sql += "AND cas_number = '" + AppUtils.EncodeForDB(li.casNum) +
"' ";
sql += "AND QAQC_type IS NULL ";
sql += "AND surrogate_compound IS NULL ";
cmd.CommandText = sql;
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
string resultText = "";
if (!(rdr.IsDBNull(0)))
resultText = rdr.GetString(0);
string qualifierCombined = "";
if (!(rdr.IsDBNull(1)))
qualifierCombined = rdr.GetString(1);
string resultUnits = rdr.GetString(2);
string pqlText = "";
if (!(rdr.IsDBNull(0)))
pqlText = rdr.GetString(3);
string colValue = "";
if (qualifierCombined.ToUpper().IndexOf("U") != -1)
colValue = "< " + pqlText;
else
colValue = resultText;
string colName;
colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|R";
dr[colName] = colValue;
colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|U";
dr[colName] = resultUnits;
colName = sample.SampleID + "|" +
sample.SampleDate.ToShortDateString() + "|Q";
dr[colName] = qualifierCombined;
}
rdr.Close();
}
private string GetList(ArrayList a)
{
if (a.Count == 0)
return "";
string s = "(";
foreach (string id in a)
{
s += "'" + id + "',";
}
s = s.Substring(0, s.Length - 1) + ")";
return s;
}
}
public class ColumnInfo
{
public string fieldSampleId;
public DateTime sampleDate;
}
public class LimitInfo
{
public string analyte;
public string matrix;
public string analyteClass;
public string casNum;
public string op;
public decimal limit;
public string units;
}
}