Not getting all my Order_IDs in C# While loop

  • Thread starter Thread starter .Net Sports
  • Start date Start date
N

.Net Sports

I am trying to get all the sales reps and their combined sales totals
for a given queried date, in which i loop thru (using C# while loop)
the available sales reps to get their rep IDs, then match it up with
their sales results for the day. The portion of my code below is
successful in retrieving all the necessary rep IDs; now I have to loop
thru the rep IDs and match their sales total for the given day, whereas
the each individual sale is represented with the Order_ID. When running
this in my Query Analyzer, i notice that it only gives me just one sale
per sales rep on the given date, and not the second or third sale if
multiple sales exist for the sales rep on this date. Since this is
being looped with the While control statement (and it reads the data
reader until there are no more available sales rep IDs), what can I add
to the While statement to make sure it grabs all the Order_Ids, and
then adds them up in the aggregate SUM statement?
--

string sqlRep = "SELECT SalesRep.ID as repID , SalesRep.LName " +
"FROM SalesRep " +
"WHERE (Terminated IS NULL) AND (tblSalesRep.StartDate < '" +
QueriedDay + "') " +
"order by SalesRep.ID asc ";

SqlCommand objCommandDR = new SqlCommand(sqlRep, objConn);
objConn.Open();
///
////-/ = SqlCommand.ExecuteReader();
SqlDataReader drRep = objCommandDR.ExecuteReader();




// main query

while (drRep.Read())
{
repID = drRep.GetInt32(drRep.GetOrdinal("repID"));
LName = drRep.GetString(drRep.GetOrdinal("LName"));
strSQL = "SELECT SalesRep.ID, SalesRep.LName,
SUM(Accounts.totalsales) AS totalsalesQueriedDay " +
"FROM SalesRep INNER JOIN " +
"Orders ON SalesRep.ID = Orders.SalesRep_ID INNER JOIN " +
"Accounts ON Orders.ID = Accounts.Order_ID " +
"WHERE Accounts.TDate = '" + QueriedDay + "' AND SalesRep.ID =
'" + repID + "' " +
"GROUP BY SalesRep.ID, SalesRep.LName " +
"HAVING (SUM(Accounts.totalsales) >= 0) " +
"ORDER BY SalesRep.LName";
////-/Debug.WriteLine(strSQL);

// create an instance of the command-connxt object
SqlCommand objCommand = new SqlCommand(strSQL, objConn2);
objConn2.Open();

SqlDataReader drTotalsales = objCommand.ExecuteReader();


}
 
Hi!

..Net Sports wrote:
[...snip...]
When running
this in my Query Analyzer, i notice that it only gives me just one sale
per sales rep on the given date, and not the second or third sale if
multiple sales exist for the sales rep on this date.
Click to expand...

You'd be better off asking this in a SQL group, since the problem might be
related to your second query.
what can I add
to the While statement to make sure it grabs all the Order_Ids, and
then adds them up in the aggregate SUM statement?
Click to expand...
[...snip...]

You will not have to since you do this in your query. You query for
retrieving the sales for the current SalesRep.ID looks like:

SELECT
SalesRep.ID,
SalesRep.LName,
SUM(Accounts.totalsales) AS totalsalesQueriedDay
FROM
SalesRep
INNER JOIN
Orders
ON
SalesRep.ID = Orders.SalesRep_ID
INNER JOIN
Accounts
ON
Orders.ID = Accounts.Order_ID
WHERE
Accounts.TDate = <QueriedDay>
AND
SalesRep.ID = <repID>
GROUP BY
SalesRep.ID,
SalesRep.LName
;

You are grouping by the SalesRep, so you will only get one result row for
each SalesRep!
If you'd group by Orders.ID, too, you might see more than one row, if I'm
not mistaken.

HTH
Michael
 
Back
Top