-2

I have to get the sub total of time spent in SQL any ideas?

This is just a simple example of my query.

`SELECT 
   A.INDX, B.CLIENTNAME, C.PROJECTNAME, D.NAME, 
   CONVERT(CHAR(10), A.CDATE, 101) [DATE],
   CONVERT(TIME(0), A.START_TIME, 108) [START],
   CONVERT(TIME(0), A.END_TIME, 108) [END],
   CONVERT(TIME(0), (A.END_TIME - A.START_TIME ), 108) [HOURS_WORKED]
FROM 
   LOGSHEET A, CLIENTS B, PROJECTS C, DEVELOPERS D
WHERE B.CLIENTNO = 1
  AND C.PROJECTID = 11
  AND D.USERID = 1`

The total should be under hours worked

Dropped my logsheet table

`BEGIN CREATE TABLE [dbo].[LOGSHEET](
[INDX] [int] IDENTITY(1,1) NOT NULL,
[CLIENTNO] [int] NULL,
[PROJECTID] [int] NULL,
[USERID] [int] NULL,
[CDATE] [datetime] NULL,
[START_TIME] [datetime] NULL,
[END_TIME] [datetime] NULL) ON [PRIMARY]END GO`

basically what I Need

name | start time | End time | Hours_worked |

AJ......| 07:00:00 | 07:15:00 | 00:15:00 |

AJ .....| 07:00:00 | 07:15:00 | 00:15:00 |

            Total:   |00:30:00|
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
A.J
  • 5
  • 5
  • 1
    You're not joining your tables at all ..... do you really want a cartesian product between all those tables? – marc_s Jun 28 '12 at 12:40
  • 1
    I'd agree - stop using old-style joins (please read: https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/08/bad-habits-to-kick-using-old-style-joins.aspx). Also what if start_time -> end_time crosses midnight? – Aaron Bertrand Jun 28 '12 at 12:41
  • sorry for the not joining tables this is a very joung project thats just rough planning still and Aaron times wont go past midnight as this is a small office app only for trading hours like 07:00-16:30 – A.J Jun 28 '12 at 12:46

2 Answers2

0
DECLARE @start_time DATETIME, @end_time DATETIME;

SELECT @start_time = '20120621 22:05', @end_time = '20120622 06:47';

SELECT CONVERT(TIME(0), DATEADD(MINUTE, DATEDIFF(MINUTE, @start_time, @end_time), 0));

Result:

08:42:00

So if you had data in a table, it would be quite similar:

SELECT CONVERT(TIME(0), DATEADD(MINUTE, 
  DATEDIFF(MINUTE, A.start_time, A.end_time), 0)) AS [HOURS_WORKED]
FROM ...

You should stay away from things like adding and subtracting date/time types, and use the built-in functions like DATEADD. This shorthand works with datetime/smalldatetime but can be problematic with the new types.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • shot thanks will rather do that NOW and next time but now the question is still how can I add the total time in the hours worked field? – A.J Jun 28 '12 at 12:56
  • You'll need to provide sample data and desired results. I can't tell from your fake query what "total time" means. Please see http://dba.blogoverflow.com/2012/06/help-us-help-you/ – Aaron Bertrand Jun 28 '12 at 12:56
  • straight forward how do I get SUM from [HOURS_WORKED]? it says invalid column name. – A.J Jun 28 '12 at 13:23
  • You can't reference an alias in a subquery. And HOURS_WORKED is TIME - you can't SUM time. If you show sample data and desired results, as I've already suggested, we can help you write a query instead of piecing together all the problems with the query you have now. Please don't post a link to some question on some other site. That won't help future readers with context if that link breaks. – Aaron Bertrand Jun 28 '12 at 13:25
  • can you please give me advice on how to get (TIMEDIFF) to int that I can be able to sum those times"in minutes" and then convert it back to time format – A.J Jun 29 '12 at 09:58
0

Ok well I figured out an solution to my problem by just adding a field "MINUTES" and running the query from c#

if (e.CommandName == "Stop") {
    int row = int.Parse(e.CommandArgument.ToString());
    string indx = GridView1.Rows[row].Cells[1].Text; 
    string s = null;
    s = "UPDATE LOGSHEET ";
    s += "SET MINUTES = (DATEPART(HOUR,TIME_SPENT)*60)+(DATEPART(MINUTE,TIME_SPENT)) ";
    s += "WHERE INDX = @p0 ";


    String[] pr = new String[1];
    pr[0] = indx; 

    cQ.execSql(s, pr);

  }
}

witch gives me the minutes as int and then just run

protected string GetTotal() {
  string userid = Session["id"].ToString();
  string s = null;


  s = "SELECT SUM(MINUTES) ";
  s += "FROM LOGSHEET ";
  s += "WHERE USERID = @p0 ";

  String[] pr = new String[1];
  pr[0] = userid;

  return cQ.GenQuery(s, pr);
}

followed by 

label5.text = GetTotal();

and KABOOM done sum of all time spent in minutes problem solved

this is a C# program but I needed the SQL queries to solve it

THANKS to the people that Helped!!

A.J
  • 5
  • 5