Group By Issue 1

[LittlBUGer]

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!

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

http://www.littlbuger.info

 

[LittlBUGer]

For my real query, the following works for the most part:

SELECT UserName, wdate, H.EmpID, Complete, ProjectName, ProjectID, PhaseName, TaskName, ClinDesc, SUM(whours) AS whours FROM Clin C JOIN Hours H ON C.Clin = H.Clin JOIN Task T ON H.TaskKey = T.TaskKey JOIN Employee E ON H.EmpID = E.EmpID Group By UserName, wdate, H.EmpID, Complete, ProjectName, ProjectID, PhaseName, TaskName, ClinDesc Having H.EmpID='" & EmpID & "' AND H.complete='N' ORDER BY H.wdate ASC

But, a vital piece of information is missing, which is the user's "comment", which is basically just what they entered for what they did that day for that task. If I put the "comment" in on the Select statement and then in the Group By, I completely lose the grouping by date as I originally wanted, as each comment is different, so of course it will show all entries. I want to be able to kind of "ignore" the comment in the group by clause, if at all possible, which is why I'm looking for a different solution.

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

http://www.littlbuger.info

 

[PHV]

Perhaps this ?
SELECT UserName, wdate, H.EmpID, Complete, ProjectName, ProjectID, PhaseName, TaskName, ClinDesc, whours, comment
FROM Clin C
JOIN Hours H ON C.Clin = H.Clin
JOIN Task T ON H.TaskKey = T.TaskKey
JOIN Employee E ON H.EmpID = E.EmpID
WHERE H.EmpID='" & EmpID & "' AND H.complete='N'

UNION ALL

SELECT UserName, wdate, H.EmpID, Complete, ProjectName, ProjectID, PhaseName, TaskName, ClinDesc, SUM(whours), 'Total'
FROM Clin C
JOIN Hours H ON C.Clin = H.Clin
JOIN Task T ON H.TaskKey = T.TaskKey
JOIN Employee E ON H.EmpID = E.EmpID
WHERE H.EmpID='" & EmpID & "' AND H.complete='N'
GROUP BY UserName, wdate, H.EmpID, Complete, ProjectName, ProjectID, PhaseName, TaskName, ClinDesc
HAVING COUNT(*) > 1

ORDER BY 2, 10

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LittlBUGer]

Hey, now we're getting somewhere! Thanks! That works by adding another row with all of the same information as the previous except for the Comment now contains "Total" and then the total hours is displayed for that day in the Hours column. That's getting where I need to be. Now I just need to figure out how to get this to be formatted in a better way in the datagrid I'm displaying it all in, and to also fix the footer of the datagrid (which shows the grand total of hours) so that it only includes the "subtotals" instead both the subtotals and non-total hours.

I've seen union once before but I would have never guessed how to use it like you did with the order by clause. Thanks! If I continue to have other issues, I will post back.

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

http://www.littlbuger.info