I have a table called Tbltimes in an access database that consists of the following fields:
empnum, empname, Tin, Tout, Thrs
what I would like to do is populate a grid view the a select statement that does the following.
display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I’m trying to accomplish.
Tbltimes
|empnum | empname | Tin | Tout | Thrs |
| 1 | john | 2:00PM | 3:00PM |1hr |
| 1 | john | 2:00PM | 3:00PM | 1hr |
| 2 | joe | 1:00PM | 6:00PM | 5hr |
GridView1
| 1 | John | 2hrs |
| 2 | Joe | 5hrs |
im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.
Post a table structure if this doesn't quite work for you. I will assume that Thrs is actually stored and formatted as Nhr where N is a number.
SELECT empnum, empname,
sum(cast(replace(Thrs,'hr','') as int)) as TotalHours --add hrs on the UI side
FROM tblTimes
If the time isn't calculated, you could easily calculate that too. Remove the replace if it isn't actually formatted.
|||thanks louis, im gonna give this a try tonight some time and ill let ya know if it works out.|||
I think lack of experience and knowledge are standing in my way here but im not able to make this method work for me Louis. i have changed a few things and here is a little better look at what i have atm.
i have a table called tbltimes. it has the following columns.
empname | Tin | Tout |
john | 9:00AM | 5:00PM |
john | 7:00AM | 5:00PM |
joe | 9:00AM | 3:00PM |
what i would like to return on the page is something simular to the following:
John | 8hrs |
Joe | 6hrs |
I can select 1 row and figure the time in hours using the following code:
Dim mins = DateDiff(DateInterval.Minute, timein, timeout)
Lblhours.Text = mins / 60
but i have yet to figure out how to use this on a larger scale such as what im trying to accomplish above.
|||
Instead of just the time, a datetime in sql server includes the date also. This is a good idea for you too in case someone works past midnight for some reason. So I added July 12 to the times, and here is the query:
drop table test
go
create table test
(
empname varchar(10),
timeIn smalldatetime,
timeOut smalldatetime
)
insert into test
select 'john', '20060712 09:00:00', '20060712 17:00:00'
union all
select 'john', '20060712 07:00:00', '20060712 17:00:00'
union all
select 'joe', '20060712 09:00:00', '20060712 15:00:00'
select empName, sum(numberHours) as hours
--put the time conversion in a derived table (or perhaps a view/calculated column)
from ( select empname, datediff(hour,timeIn, timeOut) as numberHours
from test) as getHours
group by empName
|||
Louis,
Please correct me if I’m wrong. The code you supplied above, as I understand it, says to do the following:
1. Create a temporary table in the database called test with columns named empname, timein and time out.
2. Insert the following values into the temporary table
· select 'john', '20060712 09:00:00', '20060712 17:00:00
· select 'john', '20060712 07:00:00', '20060712 17:00:00
· select 'joe', '20060712 09:00:00', '20060712 15:00:00
(What if I have 200+ columns here, would I have to type this out for each entry in the table or could I just use (Select * from tbltimes)?)
The parts that are new to me and I don’t really understand are the following:
In the insert portion of your code you use "Union All" can you explain what this does?
In the last part of your code you use:
Select empName, sum(numberHours) as hours
from ( select empname, datediff(hour,timeIn, timeOut) as numberHours
from test) as getHours
group by empName
I can only understand bits and pieces of this last section of code. Any input into what this snippet is doing would be great. I’m sorry for the newbie questions here, i would just like to understand this better so i can retain as much as possible for future use.
|||
A "View" would be more of what you are looking for.
Instead of using a temp table, create a view so you can see the results. Use the View to populate your gridview.
Create View MyTestView
AS
select empName, sum(numberHours) as hours
--put the time conversion in a derived table (or perhaps a view/calculated column)
from ( select empname, datediff(hour,timeIn, timeOut) as numberHours
from test) as getHours
group by empName
You can then view the view the same way you would view a table
Select * from MyTestView
Adamus
|||
1. No, I was just building a table to approximate what you told me that your situation was. This is why we always suggest that you give us a table and data to work with when you ask questions.
2. Same thing here. I was just approximating your data soe I could write the query. UNION ALL just does the same thing as UNION but doesn't eliminate duplicates (so no need for a sort)
As for the query:
Select empName, sum(numberHours) as hours
from ( select empname, datediff(hour,timeIn, timeOut) as numberHours
from test) as getHours
group by empName
The bold part is known as a derived table. Basically I was taking your employee name and then applying the function to get the difference between the in time and out time. This set can then be used just like a table or a view.
Then the outer part sums the different hours together grouped by empName.
|||
thanks for the explination louis, that helps a bunch.
i have yet to try your suggestions (will be trying them today sometime) but i was wondering if the following Select statement could possibly get me close to what i want.
SELECT empname, TimeIn, TimeOut, SUM(DateDiff(DateInterval.Minute, TimeIn, TimeOut)) AS TotalHours FROM tbltimes GROUP BY empname
my thinking on this (and i have NOT tried it yet) was that if i made an sqldatasource in VWD and used this as the select statement, then i could just use a gridview to display the results.
am i way off here? as i said, i have not tried this yet and im not even sure the syntax on the above statement is correct.
|||
Yes, that makes perfect sense and is the same thing more or less that I did, but I was just thinking that I would do the dateDiff stuff in a view or calculated column.
Two issues: 1. You can't include timeIn and timeOut in the output list unless they are in the group by (which wouldn't make any sense here)
2. There is no DateInterval.Minute, it would just be Minute
3. (ok, three issues, but this one is really tiny) I would name TotalHours TotalMinutes if you are doing that calculation on the UI
SELECT empname, SUM(DateDiff(Minute, TimeIn, TimeOut)) AS TotalHours FROM tbltimes GROUP BY empname
returns
empname TotalHours
- --
joe 360
john 1080
|||yeah...sum(minutes) as [TotalHours] looks weird from sql.
btw, it might be acceptable to include the first TimeIn and the last TimeOut for each employee.
e.g.
SELECT empname, Min(TimeIn) as [first TimeIn], Max(TimeOut) as [last TimeOut],
SUM(DateDiff(Minute, TimeIn, TimeOut)) AS TotalHours
FROM tbltimes GROUP BY empname
|||
ok, i have tried the Select statement above and now im gettig a few different errors.
Select Statement
SELECT employeename, Sum(DateDiff(Minute, TimeIn, TimeOut)) As Totalhours FROM tbltimes Group By employeename
ERROR:
You tried to execute a query that does not include the specified expression 'employeename' as part of an aggregate function
ERROR:
No value given for one or more required parameters.
any ideas?
|||
I am really, really sorry, but I did not realize that you were using Access. When I read the first question, the lowercase access just didn't ring that bell. Duh. Sorry :)
I don't think there are Access forums here, but there are Access newsgroups on NNTP at msnews.microsoft.com or here on the web at: http://support.microsoft.com/newsgroups/ under your language (probably English) and then under Office.
|||
im using Visual Web Developer 2005 with an access database. so as i understand it this should still work shouldnet it?
the error im getting now is just this:
"No value given for one or more required parameters."
Select statement as follows:
SELECT employeename, SUM(DateDiff(Minute, TimeIn, TimeOut)) As Totalhours FROM tbltimes GROUP BY employeename
|||No, SQL Server has a different syntax. I can't move the post, but try this forum: http://www.asp.net/forums and they might be able to help.
