October 25, 2006 at 6:54 am
I am a total novice re: SQL Server and database programing. I am currently writing an app in visual basic 2005 that requires a timesheet where the total hours (HH:mm) per day (Finishtime - Starttime) has to be stored in the database for later calculations. These are regression calculations that I have already figured out how to query. However for the life of me I cannot figure out how to do this in SQL Server and have being trying to solve it for the past week. If I was doing the calculation in Visual Basic with an unbound column it would look like this:
Private Sub DutyDataGridView_CellValueNeeded(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellValueEventArgs) Handles DutyDataGridView.CellValueNeeded
Try
'Calculate unbound Total hours column
With DutyDataGridView
'test for nulls, column 5 Total Hours, column 3 StartTime, column 4 Finish Time
If e.ColumnIndex = 5 And _
Not (TypeOf (.Rows(e.RowIndex).Cells(3).Value) Is DBNull _
OrElse TypeOf (.Rows(e.RowIndex).Cells(4).Value) Is DBNull) Then
Dim Starttime As Date 'stored in sql server as smalldate
Dim Finishtime As Date 'stored in sql server as smalldate
Dim intTime As Integer
Dim intHour As Integer
Dim intmin As Integer
Starttime = (.Rows(e.RowIndex).Cells(3).Value)
Finishtime = (.Rows(e.RowIndex).Cells(4).Value)
intTime = DateDiff(DateInterval.Minute, Starttime, Finishtime)
intHour = Math.Truncate(intTime / 60)
intmin = Math.Truncate(intTime - (intHour * 60))
e.Value = CDate(intHour & ":" & intmin)
End If
End With
Catch ex As Exception
MsgBox("Something's buggered in calculating Total Time")
End Try
End Sub
I know this is probably a complicated question, but I would appreciate any assistance as I do need to sleep sometime this year.
Thanks in anticipation***Rob F
October 25, 2006 at 7:38 am
Much simpler than what you think.
Store a start date and end date in sql server.
Then the calculation becomes this :
Select datediff(N, StartDate, EndDate) / 60.0 AS Hours
October 25, 2006 at 8:46 am
Tks Ninja
Stupid question, but where does the query go? Remembering I need to store the value in SQL Server. I guess not as calculated column. Do I generate the column somewhere in SQL Server with a statement like "dbo.Duty.TotalHours: Select datediff(N, StartDate, EndDate) / 60.0 AS Hours"?
October 25, 2006 at 8:51 am
Depends on what you do with the data. Normally you simply calculate that information and display it to the user. However if you have for exemple a Wage table, then you can calculate the total once and save it in the table.
October 25, 2006 at 8:55 am
lol that's what I need to do. Where does the calculation reside and how do I store the result?
October 25, 2006 at 9:02 am
You do something like this. The total is kept in the wage table (assuming you are using a "standard" table design). The total is counted from the TimeSheets table and kept in WAGE.
Insert into dbo.WAGE (EmpID, Paydate, nbHours,,,) Select EmpID, GetDate(), SUM(datediff...),,, FROM dbo.TimeSheets GROUP BY EmpID,,
October 25, 2006 at 9:29 am
Mate you're a lifesaver, i think i can work with that. It's only 1.25am here, so early night tonight. Thanks for your assistance, greatly appreciated.
October 25, 2006 at 9:34 am
Alright, good luck with that.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply