locked
Sql Query how to turn into a rollup with a Sum RRS feed

  • Question

  • User-373571878 posted

    Hello,  I have this query with sample output below it.  How can I change this query to become a rollup with a sum of 'Clocked In Period':

    SELECT i.employeeID,  CONVERT(varchar,i.CreationDate,1) as CreateDate, i.Time AS [In Punch], o.Time AS [Out Punch],
           CONVERT(varchar(12), DATEADD(minute, DATEDIFF(minute, i.Time, o.Time), 0), 114) as 'Clocked In Period'
    FROM timesheet i
    INNER JOIN  timesheet o ON i.employeeID = o.employeeID 
    INNER JOIN  employee e ON e.id = i.employeeID
    WHERE (o.IsClockOut = 1) AND (i.IsClockIn = 1) AND (o.Time =  (SELECT MIN([Time]) 
     FROM timesheet
     WHERE employeeId = i.employeeID AND Time > i.Time and IsClockOut = 1))
     order by i.Time desc

    Query sample results.    Thanks

    employeeID	CreateDate	In Punch	         Out Punch    	     Clocked In Period
    10001xxxxx	04/13/18	2018-04-14 03:35:00.000	2018-04-14 04:10:00.000	00:35:00:000
    10001xxxxx	04/13/18	2018-04-14 03:20:00.000	2018-04-14 03:30:00.000	00:10:00:000
    10001xxxxx	04/13/18	2018-04-14 02:55:00.000	2018-04-14 03:00:00.000	00:05:00:000
    00:50:00:000 for example 10000zzzzz 04/06/18 2018-04-06 08:30:00.000 2018-04-06 10:45:00.000 02:15:00:000 10000zzzzz 03/27/18 2018-03-27 08:15:00.000 2018-04-06 10:45:00.000 02:30:00:000 10000yyyyy 03/26/18 2018-03-26 16:10:00.000 2018-03-26 16:20:00.000 00:10:00:000 10000yyyyy 03/15/18 2018-03-15 09:10:00.000 2018-03-26 16:20:00.000 07:10:00:000 10000yyyyy 03/15/18 2018-03-13 10:30:00.000 2018-03-13 11:30:00.000 01:00:00:000 10000yyyyy 03/15/18 2018-03-13 10:30:00.000 2018-03-13 11:30:00.000 01:00:00:000

    Tuesday, April 17, 2018 3:51 PM

All replies

  • User347430248 posted

    Hi StrangerMike,

    I don't have a multiple tables and data to make a test.

    So I try to create a table which looks like your output.

    On which, You can use query like below.

    SELECT emp_id,create_date,in_punch,out_punch,
    SUM (clocked_in_period) AS [sum of data]
    FROM Table_1
    GROUP BY 
    grouping sets((emp_id,create_date,in_punch,out_punch),())
    

    or

    SELECT emp_id,create_date,in_punch,out_punch,
    SUM (clocked_in_period) AS [sum of data]
    FROM Table_1
    GROUP BY ROLLUP((emp_id,create_date,in_punch,out_punch))
    ;

    You can try to implement the logic in your actual query.

    Regards

    Deepak

    Thursday, April 19, 2018 8:25 AM
  • Sunday, April 22, 2018 4:19 AM