LittlBUGer
04-27-2006, 12:37 PM
Hello. I've looked around the internet for 2 full days now on how to get certain data I'm pulling from a SQL database into a datagrid the way that I want it. Basically, I'm pulling all data from several tables with a couple where clauses and then binding it to a datagrid and displaying it. The data is basically information from our internal company's time/hour keeping web program. Some columns include: date, hours, comments, etc. The way it is now is all of the relevent information is displayed in the datagrid with a footer row having the total amount of hours.
The problem with this is that there can be multiple entries of hours for each day. I want to rollup the hours for each day and display that either as a subtotal in the datagrid or in a whole new datagrid. Meaning that I would have a row with one date that has the total amounts of hours entered for that day, then the next day and total hours, and so on and so forth.
I've tried doing this through my SQL statements with a group by and having clause but I can't have ALL of the Select data in the group by clause. I only want the date in the group by and then the sum of the hours for that day. I've also tried putting multiple Select statements together and almost have what I want, but not quite. I'm not sure how I could get a Rollup statement to work or not...
Here's the original SQL query displaying everything without any group by:
Select * From Clin JOIN Hours ON Clin.Clin = Hours.Clin JOIN Task ON Hours.TaskKey = Task.TaskKey JOIN Employee ON Hours.EmpID = Employee.EmpID where Hours.EmpID ='" & EmpID & "' and Hours.complete='N' order by Hours.wdate ASC
Here's what I've gotten to so far that doesn't pull all data but at least gets close to displaying the data pulled as I want:
Select wd.wdate, empid, complete, (Select SUM(whours) From Hours where wdate=wd.wdate and complete='N') AS whours From hours AS wd where empid='" & EmpID & "' and complete='N' order by wdate ASC
What I really want is the following statement but because of the damn group by clause, it wont let me:
Select *, SUM(Hours.whours) From Clin JOIN Hours ON Clin.Clin = Hours.Clin JOIN Task ON Hours.TaskKey = Task.TaskKey JOIN Employee ON Hours.EmpID = Employee.EmpID where Hours.EmpID ='" & EmpID & "' and Hours.complete='N' Group By Hours.wdate order by Hours.wdate ASC
Can anyone help me with fixing my SQL syntax so that I get the data in the format I require? Maybe there's an entirely differnt way to do this that I'm unaware of? I'm sorry for the long post, but I thought as much information as possible would be best. Thanks! :)
Also, to add a bit more to the above:
I am using SQL Server 2000, not mySQL (a datagrid is part of ASP.NET). I know about trying not to use the "evil select star" but I came to this web program with it already being partially built, and thus I'm improving it though without trying to break it, so I'm try to change as little as possible.
I realize the "Select *, SUM(Hours.whours)..." is incorrect but I was trying to illustrate what I WANTED to do, not neccessarily correct syntax. If I wanted correct syntax, I would have had a VERY long select statement, so I instead placed a *.
I also know that all of the non-aggregates SHOULD be placed in the group by field, but as I said, when I've done that, the results displayed ARE NOT what I want, which is a total of hours per day.
And what I mean by "hours per day" is that each entry has a date and the amount of hours worked for a certain task. There can be several entries per day and thus by default, the datagrid displays all entries and thus the same day in multiple entries. I want to rollup or subtotal all of the hours for a SINGLE day, even though a single day may span over several entries. Thus, I want to do a Group By for the field wdate and also sum the total hours which is the field whours. This is where all of the problems occur, as I can't get the syntax to work as I need it to.
I hope this further explanation can help with your suggestions. Thanks again! :)
The problem with this is that there can be multiple entries of hours for each day. I want to rollup the hours for each day and display that either as a subtotal in the datagrid or in a whole new datagrid. Meaning that I would have a row with one date that has the total amounts of hours entered for that day, then the next day and total hours, and so on and so forth.
I've tried doing this through my SQL statements with a group by and having clause but I can't have ALL of the Select data in the group by clause. I only want the date in the group by and then the sum of the hours for that day. I've also tried putting multiple Select statements together and almost have what I want, but not quite. I'm not sure how I could get a Rollup statement to work or not...
Here's the original SQL query displaying everything without any group by:
Select * From Clin JOIN Hours ON Clin.Clin = Hours.Clin JOIN Task ON Hours.TaskKey = Task.TaskKey JOIN Employee ON Hours.EmpID = Employee.EmpID where Hours.EmpID ='" & EmpID & "' and Hours.complete='N' order by Hours.wdate ASC
Here's what I've gotten to so far that doesn't pull all data but at least gets close to displaying the data pulled as I want:
Select wd.wdate, empid, complete, (Select SUM(whours) From Hours where wdate=wd.wdate and complete='N') AS whours From hours AS wd where empid='" & EmpID & "' and complete='N' order by wdate ASC
What I really want is the following statement but because of the damn group by clause, it wont let me:
Select *, SUM(Hours.whours) From Clin JOIN Hours ON Clin.Clin = Hours.Clin JOIN Task ON Hours.TaskKey = Task.TaskKey JOIN Employee ON Hours.EmpID = Employee.EmpID where Hours.EmpID ='" & EmpID & "' and Hours.complete='N' Group By Hours.wdate order by Hours.wdate ASC
Can anyone help me with fixing my SQL syntax so that I get the data in the format I require? Maybe there's an entirely differnt way to do this that I'm unaware of? I'm sorry for the long post, but I thought as much information as possible would be best. Thanks! :)
Also, to add a bit more to the above:
I am using SQL Server 2000, not mySQL (a datagrid is part of ASP.NET). I know about trying not to use the "evil select star" but I came to this web program with it already being partially built, and thus I'm improving it though without trying to break it, so I'm try to change as little as possible.
I realize the "Select *, SUM(Hours.whours)..." is incorrect but I was trying to illustrate what I WANTED to do, not neccessarily correct syntax. If I wanted correct syntax, I would have had a VERY long select statement, so I instead placed a *.
I also know that all of the non-aggregates SHOULD be placed in the group by field, but as I said, when I've done that, the results displayed ARE NOT what I want, which is a total of hours per day.
And what I mean by "hours per day" is that each entry has a date and the amount of hours worked for a certain task. There can be several entries per day and thus by default, the datagrid displays all entries and thus the same day in multiple entries. I want to rollup or subtotal all of the hours for a SINGLE day, even though a single day may span over several entries. Thus, I want to do a Group By for the field wdate and also sum the total hours which is the field whours. This is where all of the problems occur, as I can't get the syntax to work as I need it to.
I hope this further explanation can help with your suggestions. Thanks again! :)