0

i'm trying to query a table that contains employee records of presence. It records the employee id and datetime of the chipping as follows:

id  datetime
3   2015-07-01 06:58:00.000
3   2015-07-01 12:01:00.000
3   2015-07-01 12:57:00.000
3   2015-07-01 19:17:00.000
3   2015-07-02 06:55:00.000
3   2015-07-02 14:05:00.000
15  2015-07-01 07:50:00.000
15  2015-07-01 12:01:00.000
15  2015-07-01 12:50:00.000
15  2015-07-01 18:04:00.000

i was trying to produce a query that gets

id date       entrance   exit       2entrance   2exit
3  2015-07-01 06:58      12:01    12:57       19:17
3  2015-07-02 06:55      14:05    00:00       00:00
15 2015-07-01 07:50      12:01    12:50       18:04

but the final result i'm looking is

id d1, d2, d3.. d31
3  11  7   12   6
15 9   0   6    12

(this is the number of worked hours, day by day, for a full month)

to get this table i was thinking to use excel with previous results, but i'd apreciate if someone pointed me in the right direction.

Any help will be appreciated.

Acácio Costa
  • 1
  • 2
  • You are trying to pivot the data, and a dynamic pivot at that. SQL result sets have a fixed number of columns, so to get a variable number of columns you have to construct the query as a string. You are probably better off doing this in Excel. – Gordon Linoff Aug 25 '15 at 11:22

2 Answers2

0

may be a bit into another direction but you could take the date strings, split them up (as they have a fixed format - yyyy-mm-dd_hh:mm:ss.mss). Then take what you need.

My first idea would be to write a small java service which takes .txt (in wich you saved it earlier automatically), cuts up the strings and for each strings give you the output in the format you need.

Or were you looking for something which did not involve programming?

Cribber
  • 2,513
  • 2
  • 21
  • 60
  • i was hoping that wouldn't involve programming. It's not one of my strengths. I can only go to basic sql queries. Thanks – Acácio Costa Aug 25 '15 at 09:47
  • this post might be helpful, especially the answer by Nathan Bedford if you dont want to programm: http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x – Cribber Aug 25 '15 at 09:52
  • also this might be interesting for you: another take on the same problem, different solution: http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows – Cribber Aug 25 '15 at 09:54
  • thanks for your answer. I just couldn't yet translate it to rows instead of columns. I'll keep trying – Acácio Costa Aug 25 '15 at 10:43
  • ah okay ^^ well here are a few stackoverflow posts with which you should be able to do it: http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server ||| http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql ||| http://stackoverflow.com/questions/20111418/sql-server-transpose-rows-to-columns – Cribber Aug 25 '15 at 13:19
0

Try:

create table #tmp (id int,log datetime) insert into #tmp select 3 , '2015-07-01 06:58:00.000' insert into #tmp select 3 , '2015-07-01 12:01:00.000' insert into #tmp select 3 , '2015-07-01 12:57:00.000' insert into #tmp select 3 , '2015-07-01 19:17:00.000' insert into #tmp select 3 , '2015-07-02 06:55:00.000' insert into #tmp select 3 , '2015-07-02 14:05:00.000' insert into #tmp select 15 , '2015-07-01 07:50:00.000' insert into #tmp select 15 , '2015-07-01 12:01:00.000' insert into #tmp select 15 , '2015-07-01 12:50:00.000' insert into #tmp select 15 , '2015-07-01 18:04:00.000' insert into #tmp select 3 , '2015-07-03 07:01:00.000' insert into #tmp select 3 , '2015-07-03 14:06:00.000' insert into #tmp select 3 , '2015-07-03 15:06:00.000' insert into #tmp select 3 , '2015-07-03 18:06:00.000' select id, [1] as Day1 ,[2] as Day2, [3] as Day3 from ( select Result.id, Result.Day, sum(Result.Diff*Result.Signal)/60 as Hours from ( select Result1.id, Result1.Day, DATEDIFF(minute, Result1.Time, Result2.Time) as Diff, ROW_NUMBER() over( order by Result1.id) % 2 as Signal from ( select id, datepart(day, log) as Day, log as Time, ROW_NUMBER() over( order by id) as RowID from #tmp ) as Result1 left join ( select id, datepart(day, log) as Day, log as Time, ROW_NUMBER() over( order by id) as RowID from #tmp ) as Result2
on Result1.RowID = Result2.RowID -1 and Result1.Day = Result2.Day ) as Result group by Result.id, Result.Day ) as PivotSource pivot ( sum(Hours) for Day in ( [1] ,[2], [3] ) ) as PivotTable

You can extend the PIVOT with the days you may need, and ofc use your table insted of #tmp. Regards,

Teve
  • 56
  • 2