SUM Hours For One Day


I am currently writing a module for the Time Clock we use. Right now I am using the following SQL:

Code: SELECT EmployeeID, DATEDIFF(Minute,TimeIn,TimeOut) AS TotalHoursDay, TimeIn, TimeOut, JobCode FROM EmployeeHours WHERE EmployeeID = '" & LoginID & "' AND TimeIn BETWEEN '" & GetStartWeek & "' AND '" & GetEndWeek & "' ORDER BY TimeIn ASC I later take TotalHoursDay and divide it by 60 to give me the hours.

What this shows me is the total time for each time that an employee clocks and and clocks out. This can be multiple times in a day as an employee will clock out for lunch. For instance:

10/19/2006 8:00 to 12:00 4 hours
10/19/2006 1:00 to 5:00 4 hours

I want to add up both of these entries to give me the total of 8 hours for the day.
Posted On: Wednesday 7th of November 2012 02:20:47 PM Total Views:  57
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




Need help to combine 2 records into one

Here is the table: CREATE TABLE [tBenefit] ( [emp_obenefit_id] [int] IDENTITY (1, 1) NOT NULL , [empplan_id] [int] NOT NULL , [obenefit_id] [int] NOT NULL , [cost_per_month] [float] NOT NULL CONSTRAINT [DF_EmployeeOBenefitApproved_cost_per_month] DEFAULT (0), [coverage_start_date] [datetime] NOT NULL , [coverage_end_date] [datetime] NOT NULL ) ON [PRIMARY] GO Here is the query: ---------------------------------------------------------------------------- SELECT emp_obenefit_id, empplan_id, obenefit_id, billing_cost_per_month, e.coverage_start_date, e.coverage_end_date FROM tBenefite WHERE e.empplan_id = 4757 AND e.emp_obenefit_id IN ( SELECT TOP 2 emp_obenefit_id from tBenefit where empplan_id = e.empplan_id AND obenefit_id = e.obenefit_id ORDER BY coverage_end_date DESC, emp_obenefit_id DESC ) ORDER BY e.obenefit_id DESC, e.coverage_end_date DESC, e.emp_obenefit_id DESC ---------------------------------------------------------------------------- Here is the result of the query: emp_obenefit_id empplan_id obenefit_id cost_per_month coverage_start_date coverage_end_date --------------- ----------- ----------- ------------------------------------ 31731 4757 84 0.0 2004-09-01 2004-12-31 26635 4757 84 0.0 2004-04-06 2004-08-31 31730 4757 83 6.2 2004-09-01 2004-12-31 31121 4757 83 0.0 2004-04-06 2004-08-31 31729 4757 82 0.0 2004-09-01 2004-12-31 31120 4757 82 0.0 2004-04-06 2004-08-31 ---------------------------------------------------------------------------- I need to construct a query that will get me one recordset for each obenefit_id where cost_per_month of previous (between 2004-04-06 and 2004-08-31) period will not be equal to current period (between 2004-09-01 and 2004-12-31), it they are equal this means that no change has been done to the costs so I do not need it The result of this query has to look like this: empplan_id obenefit_id cost_per_month_prev cost_per_month_current --------------- ----------- ----------- -------------------------------- 4757 83 0.00 6.2 Only obenefit_id = 83 must be displayed because as it is seen in the query results values for 2004-04-06 and 2004-08-31 value for cost_per_month was 0 and for 2004-09-01 and 2004-12-31 is 6.2 (value changed) Thank you for your help
VIEWS ON THIS POST

306

Posted on:

Wednesday 7th November 2012
View Replies!

Can anyone recommend good SQL Server monitoring software?

I'm looking for software that will give me a quick overview of the state all my databases in SQL Server. Also, something that would notify me, through email etc..if there were any errors in the SQL Logs. Unfortunately we've recently had a problem where one of our databases was corrupted, therefore, we are now very cautious about the stability of our databases. It would be very comforting if I could quickly glance at some type of "monitoring software" that would tell me how everything was going. Any suggestions for this type of software Thank you in advance.
VIEWS ON THIS POST

67

Posted on:

Wednesday 7th November 2012
View Replies!

Copy all tables, stored procedures and functions from one database to another one

Hi , I would like to copy all tables, stored procedures and functions from one database to another (both databases are located in the same server) in MS SQL server 2008. I would like to copy is only the schema including primary key and index but not data. Could anyone suggest how to do it
VIEWS ON THIS POST

157

Posted on:

Saturday 10th November 2012
View Replies!

How to calculate one row field against another row

My title must be confusing but I don't know how to put it. I have one column with county and another with client name. i'm trying to query how many clients are in one county. Client County df surrey sd surrey ds Hampshire gh London dj London kl Hampshire can anyone please help me out with this.
VIEWS ON THIS POST

88

Posted on:

Saturday 10th November 2012
View Replies!

Relating data to more than one table

I am setting up a website with different page formats. Some pages will actually be stories with a title and a body. Other pages will have photo galleries with a simple comment. There are other formats, but these two examples should be enough. The problem I'm having is with the database structure. Here are the two tables I'd described already: Stories StoryID int, PK Title varchar Body text PhotoGalleries GalleryID int, PK Title varchar Description varchar Now, I also want users to be able to write comments for both of these tables. My question is how do i relate the comments data to these other two tables. Here's the comments table: Comments CommentID int, PK UserID FK Details varchar Would it be better to create linking tables, one for Stories-Comments and another for PhotoGalleries-Comments My other thought was to create a master "Content" table and every record in every table would have a matching record in the Content table. My thoughts for a Contents table: Contents ContentID int, PK CreateDate datetime I hope this isn't too confusing. Thank you!
VIEWS ON THIS POST

110

Posted on:

Saturday 10th November 2012
View Replies!

Please Help select all from one table that are not in another

ok i have to tables table 1 FID username Table 2 FID filename display name i need to show all fids from table 2 that are not in table 1 for username justin. please help.
VIEWS ON THIS POST

73

Posted on:

Saturday 10th November 2012
View Replies!

Insert from one database to another

I am trying to copy data from database A and insert it into database B using the SQL Insert into statements. I got an error message saying, "An explicit value for the identity column in table 'CellPowerInventory.dbo.tblCreditors' can only be specified when a column list is used and IDENTITY_INSERT is ON'". Can somone tell me what I should do to solve this problem I am using a field which has an Identity value. Both the database has the same structure. Code: condb.Execute "Delete from CellPowerInventory.dbo.tblCreditors" condb.Execute "Insert into CellPowerInventory.dbo.tblCreditors Select * from tblCreditors"
VIEWS ON THIS POST

144

Posted on:

Saturday 10th November 2012
View Replies!

Breaking data in one column into multiple columns

I have a field in my table titled "notes", data in it is in the form of: Notes1=Testing&Notes2=Test the notes&Notes3=&Notes4=&Notes5=&Notes6=&Notes7=&Note s8=& I would like to make a view that has a column for each Notes section referenced above, so I would have: Notes1 Testing Notes2 Test the notes Notes3 Notes4 ...and so on
VIEWS ON THIS POST

238

Posted on:

Saturday 10th November 2012
View Replies!

Please can someone help me!!!

, I am really new to asp.net(student on work placement!) and I am having a bit of trouble understanding the sql side of things. I have a dataset1 that contains 2 tables joined - one for suppliers products and one for supplier products price. So it has 2 primary keys - ones(articleSupplierID) guid is the join for the other table. I want to have the possiblity that if a supplier adds a product and price to my form he has the choice to enter another price list for the same product(for different size orders etc). What happens is that he enters the info on the form presses add-> I pass the 2 primary keys into a url of a confirm page which is a datalist stating the record he just added. On the bottom of this page he has the choice to add another price for this product, if he chooses this it will redirect to the forms page with all the fields filled in except the price fields. NOW - Do I need to make a new dataset2 with 2 seperate tables(article,price) and create a new default row in the price table then, refill my first dataset1 and send it as a new row in the url\t Nothing is working that I am trying anyone any ideas for an easy solution\t Shaz
VIEWS ON THIS POST

101

Posted on:

Saturday 10th November 2012
View Replies!

Moving data from one SQL table to another

I am new to using SQL and I wonder if someone could give me a bit of assistance. I need to be able to move records from one SQL table to another, but I also need to read the previous entry in one of the columns and increment the number by one each time I move a record over. Can anyone help because I am completely lost.
VIEWS ON THIS POST

97

Posted on:

Saturday 10th November 2012
View Replies!

How to sum alias coloumn ?

below code running well until i need to order by average , how can i do this in one sql statement below as u can see i tried to write sql command as tot_average below ( in Red ) but still not luck. Need gudance here ... tks VM Code: '=ACCURASY_1rst_class============================================================================================== 'Class_Accurasy_ALL_Subclass__Agent_under_each_tl IF request("kelas")="Accuracy" AND request("sub_class")="" AND request("nama_agent")=""THEN Set rs = conn.execute("SELECT nama_agent,SUM(score_greeting) / COUNT(*) AS avg_gre, "&_ "SUM(score_closing) / COUNT(*) AS avg_clo, "&_ "SUM(score_customer_name_usage) / COUNT(*) AS avg_cnu, "&_ "SUM(score_tone_of_voice) / COUNT(*) AS avg_tov, "&_ "SUM(score_choice_of_word) / COUNT(*) AS avg_cow, "&_ "SUM(score_active_listening) / COUNT(*) AS avg_al, "&_ "SUM(score_conversation_control) / COUNT(*) AS avg_cc, "&_ "SUM(score_hold_process) / COUNT(*) AS avg_hp, "&_ "SUM(score_transfer_procedure) / COUNT(*) AS avg_tp, "&_ "SUM(score_probing_skill) / COUNT(*) AS avg_ps, "&_ "SUM(score_salesmanship_skills) / COUNT(*) AS avg_ss, "&_ "SUM(score_process_offering) / COUNT(*) AS avg_po, "&_ "SUM(score_policy_procedure) / COUNT(*) AS avg_pp, "&_ "SUM(score_risk) / COUNT(*) AS avg_rsk, "&_ "SUM(score_analysing) / COUNT(*) AS avg_ans, "&_ "SUM(score_problem_solution) / COUNT(*) AS avg_prob, "&_ "SUM(score_x_sell) / COUNT(*) AS avg_xs, "&_ "SUM(avg_gre+avg_clo+avg_cnu+avg_tov+avg_cow+avg_al+avg_cc+avg_hp+avg_tp+avg_ps+avg_ss+avg_po+avg_pp+avg_ans+avg_prob+avg_xs) AS tot_average "&_ "FROM tblObservasi WHERE YEAR(tanggal_input) ='"& request("tahon") &"' AND MONTH(tanggal_input) ='"& request("bulan") &"' "&_ "AND tl_acd='"& request("nama_tl") &"' GROUP BY nama_agent") %>
VIEWS ON THIS POST

69

Posted on:

Saturday 10th November 2012
View Replies!

DISTINCT one colum - Page 2

Originally Posted by caknight Thank you all so much Glad you found it helpful. You had two of the best helping you. Only thing - you did not mention, that it was an access database.
VIEWS ON THIS POST

116

Posted on:

Sunday 11th November 2012
View Replies!

DISTINCT one colum - Page 2

Originally Posted by caknight Thank you all so much Glad you found it helpful. You had two of the best helping you. Only thing - you did not mention, that it was an access database.
VIEWS ON THIS POST

82

Posted on:

Sunday 11th November 2012
View Replies!

Merge the two rows in one row

Emp ----- ID Name 1 x 1 P 2 y 2 Q 3 W Emp_Data Id Name-1 Name-2 1 x P 2 y Q 3 w above is my tables (Emp ),And the I want the Resulting Data in the (Emp_Data) format.is this possible Pls Help.......
VIEWS ON THIS POST

192

Posted on:

Sunday 11th November 2012
View Replies!

Two questions for anyone with a little SQL knowledge...

Just a few questions for those that a comfortable with SQL Server 2000 1. When a user logs in to my site a session is created called Session("validUser"). Is it possible to use a stored procedure to detect if this session has a value. If it does not then output something, if it does output something else instead of using ASP to accomplish this. 2. Is it possible to set a value of a variable i.e @iID to 1 if the variable is NULL A basic example of what I mean would be as below:- Code: CREATE PROCEDURE spClient @iID int AS SELECT clientID, clientFirstname, clientSurname FROM tblClients WHERE clientID = @iID So if no value was given, automatically we could set the variable to 1 stored procedure way instead of ASP
VIEWS ON THIS POST

95

Posted on:

Sunday 11th November 2012
View Replies!

Join to Database in one query

In one database I have two tables with several fields. and then another database I have one table. I need to write a view that allows me to left outer join both databases to return my values. So I can build some reprots. I need help on how to do this
VIEWS ON THIS POST

91

Posted on:

Sunday 11th November 2012
View Replies!

SQL Server Sum hours - problem

I am summing hours but when they exceed 24 total, SQL drops the 24 from the total and just give me the rest. For example: When I add 18:10 and 7:05 I get back 1:15 instead of 25:15. Excel does the same thing How do I code to get 25:15 hours as the total Here is my code: select studentID, convert(datetime, SUM(convert(numeric(25,15), #HoursAccum.HoursWorked))) from #HoursAccum group by studentID then I update it to another temp table which will be output to SSRS: update #ResultSet set TotalTriHours = CONVERT(float,(CONVERT(datetime, #TotalHours.TotalTriHours,114))) from #TotalHours where #TotalHours.studentID = #ResultSet.studentID
VIEWS ON THIS POST

128

Posted on:

Monday 19th November 2012
View Replies!

Update rows on the base of sum records

Im working on small project I stacked with sql query in sql server 2005. Ive nearly 5 thousands records with one field money data type. My question is how to update only those records which are cost less then 1k and not more than 50 k. Is there any way to write a simple query to update records on given above conditions For example: Name Cost(money data type) update(Ill update here) A 500 ---- B 1200 ---- C 999 ---- D 800 ---- Etc till 5000 rows, there should be some value in the name and cost column. If 1k records are less then 50 nos.. how many are there, weve to update those records If 1k records are more then 50. then weve take first 50 and remaining not to do anything If its near 49.5k like that, that time also not a problem. Im looking forward for your help. mdateeq2003
VIEWS ON THIS POST

255

Posted on:

Tuesday 20th November 2012
View Replies!

Query to sum the same field twice in the select statement

friends , I have table (MoneyTrans) with following structure [Id] [bigint] NOT NULL, [TransDate] [smalldatetime] NOT NULL, [TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw' [Amount] [money] NOT NULL I need to write a query to generate following output Trans Date, total deposits, total withdrawls, closing balance i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate ) I am working on this for past two days with out getting a right solution.
VIEWS ON THIS POST

123

Posted on:

Tuesday 20th November 2012
View Replies!

SQL Server Sum hours - problem - Page 2

Oh I am sorry to hear you have the flu.. I really should just leave you alone. I created the schedule table and defined the # of hours as Time. I was thinking about recreating the table and changing hours to datetime even though that doesn't make sense to me to have a date on the number of hours. I think if I defined it as float I would be able to calculate it better by just using minutes as calculation for the difference in time. I want to thank you for all your code and suggestions. It has helped me by realizing I have alternatives to getting to my end result that I need.
VIEWS ON THIS POST

140

Posted on:

Monday 26th November 2012
View Replies!