Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
netsports
Starting Member
11 Posts |
 Posted - 2005-10-11 : 13:10:18
|
| 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();} |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-10-11 : 13:53:23
|
| It sure seems like the totaling should all be done in SQL Server, and your second query looks like a good attempt at doing that. If you run the second query in QA, but remove the "AND SalesRep.ID = ..." part of the WHERE clause, what result do you get? Are all of your dates (Accounts.TDate) entered with 00:00:00 time portions? If not, you'll need to switch to a BETWEEN or a CONVERT to get everything for that day.If you run the query without the aggregate (no SUM, no GROUP BY) do you get all the records you want for details?And by the way, shouldn't you be opening objConn2 BEFORE you use it in objCommand?---------------------------EmeraldCityDomains.com |
 |
|
|
|
|
|
|
|
Not getting all my Order_IDs in CSharp While loop