Converting a date range table to a grid (SQL)
Posted on 2011-10-27
I'm having some difficulty getting this to work. I have a table that keeps track of date ranges (for various activities):
tblTrabalho (FK_ID_intEmpresa smallint, FK_ID_intFuncionario int, datHoraEntradaTrabalho smalldatetime, datHoraSaidaTrabalho smalldatetime, FK_ID_intTipoTrabalho uniqueidentifier)
First is code for company. For testing purposes, you can assume the filter to be 41. Second is employee number. Can be assumed to be 1451. Then it's the start and end date, followed by a foreign key to a table that defines the type. This can pretty much be displayed as it is. I'll worry about linking stuff later. This generates something like:
41 1451 '01-01-2011' '01-01-2011' '1abc.....'
41 1451 '03-02-2011' '08-02-2011' '1abc.....'
41 1451 '09-02-2011' '02-03-2011' 'ab1c.....'
...etc... Now, what I want is to translate that, into a year grid. Like:
Year Jan Feb Mar .... Dec
1 1abc ab1c