• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Converting a date range table to a grid (SQL)

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
2                                ab1c
3                   1abc
4                   1abc
5                   1abc
6                   1abc
7                   1abc
8                   1abc
9                   ab1c
10                 ab1c
11                 ab1c
12                 ab1c
...etc
0
Cluskitt
Asked:
Cluskitt
  • 6
  • 4
1 Solution
 
supengmmerCommented:
with  temp(COLUMN_DAY, COLUMN_MONTH, COLUMN_STR)
as
(select    day(begin_date)  as COLUMN_DAY,      month(begin_date)  as  COLUMN_MONTH,
               strvalue  as  COLUMN_STR
 from   T
union all
 select   day( dateadd(day,1,begin_date) ),   month( dateadd(day,1,begin_date) ),  COLUMN_STR
  from     temp
 where   dateadd(day,1,begin_date) <= end_date
 )
select  case COLUMN_MONTH when 1 then COLUMN_STR end as JAN,
            case COLUMN_MONTH when 2 then COLUMN_STR end as FEB,
            case COLUMN_MONTH when 3 then COLUMN_STR end as MAR,
            ...
            case COLUMN_MONTH when 12 then COLUMN_STR end as DEC
  from   temp
 group  by COLUMN_DAY
 order   by COLUMN_DAY


best regards:)
0
 
JohnP_RealiniCommented:
I set up this test table named TEST (based on your example)

id     id2    desde                              hasta                               fk
41      145      2011-01-01 00:00:00.0      2011-01-05 00:00:00.0      abc1
41      145      2011-02-08 00:00:00.0      2011-02-09 00:00:00.0      abc1
41      145      2011-02-09 00:00:00.0      2011-03-03 00:00:00.0      acb2
41      145      2011-03-03 00:00:00.0      2011-04-04 00:00:00.0      acd3

and then built this query

select year(desde), case
 when month(desde) = 1 then fk
 else null
 end Jan,
 case when month(desde) = 2 then fk
 else null
 end Feb,
 case when month(desde) = 3 then fk
 else null
 end Mar from test

Open in new window


And this was the result

Year         Jan    Feb  Mar  
2011      abc1                  
2011            abc1            
2011            acb2            
2011                  acd3      

I guess that is what you wanted... right?
0
 
supengmmerCommented:
sorry ,Modify:

with  temp(COLUMN_DAY, COLUMN_MONTH, COLUMN_STR, END_DATE)
as
(select    day(begin_date)  as COLUMN_DAY,      month(begin_date)  as  COLUMN_MONTH,
               strvalue  as  COLUMN_STR,  end_date
 from   T
union all
 select   day( dateadd(day,1,begin_date) ),   month( dateadd(day,1,begin_date) ),  COLUMN_STR
  from     temp
 where   dateadd(day,1,begin_date) <= END_DATE
 )
select  COLUMN_DAY as DAY
            case COLUMN_MONTH when 1 then COLUMN_STR end as JAN,
            case COLUMN_MONTH when 2 then COLUMN_STR end as FEB,
            case COLUMN_MONTH when 3 then COLUMN_STR end as MAR,
            ...
            case COLUMN_MONTH when 12 then COLUMN_STR end as DEC
  from   temp
 group  by COLUMN_DAY
 order   by COLUMN_DAY


best regards:)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CluskittAuthor Commented:
I will check both options in the morning.
0
 
CluskittAuthor Commented:
Oh, JohnP_Realini, that isn't quite what I want. I want days as rows, not year. The Year in my example was just a column name (caption). Basically, a grid with days for rows and months for columns.
Also, if it helps you, I have a Calendar table (http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx) . Has different holidays, but that shouldn't be a problem.
If it helps, I also have a numbers table.
0
 
CluskittAuthor Commented:
Ok, I tried adapting your query and it didn't work:
WITH temp(COLUMN_DAY, COLUMN_MONTH, COLUMN_STR, END_DATE)
AS
(SELECT DAY(datHoraEntradaTrabalho) AS COLUMN_DAY, MONTH(datHoraEntradaTrabalho) AS COLUMN_MONTH,
		FK_ID_intTipoTrabalho AS COLUMN_STR, datHoraSaidaTrabalho AS END_DATE
FROM TblTrabalho
UNION ALL
SELECT DAY(DATEADD(DAY,1,COLUMN_DAY)), MONTH(DATEADD(DAY,1,COLUMN_DAY)), COLUMN_STR, END_DATE
FROM temp
WHERE DATEADD(DAY,1,COLUMN_DAY) <= END_DATE)

SELECT COLUMN_DAY AS Dia,
		CASE COLUMN_MONTH WHEN 1 THEN COLUMN_STR END AS JAN,
		CASE COLUMN_MONTH WHEN 2 THEN COLUMN_STR END AS FEV,
		CASE COLUMN_MONTH WHEN 3 THEN COLUMN_STR END AS MAR,
		CASE COLUMN_MONTH WHEN 4 THEN COLUMN_STR END AS ABR,
		CASE COLUMN_MONTH WHEN 5 THEN COLUMN_STR END AS MAI,
		CASE COLUMN_MONTH WHEN 6 THEN COLUMN_STR END AS JUN,
		CASE COLUMN_MONTH WHEN 7 THEN COLUMN_STR END AS JUL,
		CASE COLUMN_MONTH WHEN 8 THEN COLUMN_STR END AS AGO,
		CASE COLUMN_MONTH WHEN 9 THEN COLUMN_STR END AS 'SET',
		CASE COLUMN_MONTH WHEN 10 THEN COLUMN_STR END AS 'OUT',
		CASE COLUMN_MONTH WHEN 11 THEN COLUMN_STR END AS NOV,
		CASE COLUMN_MONTH WHEN 12 THEN COLUMN_STR END AS DEZ
FROM temp
GROUP BY COLUMN_DAY,
		CASE COLUMN_MONTH WHEN 1 THEN COLUMN_STR END,
		CASE COLUMN_MONTH WHEN 2 THEN COLUMN_STR END,
		CASE COLUMN_MONTH WHEN 3 THEN COLUMN_STR END,
		CASE COLUMN_MONTH WHEN 4 THEN COLUMN_STR END,
		CASE COLUMN_MONTH WHEN 5 THEN COLUMN_STR END,
		CASE COLUMN_MONTH WHEN 6 THEN COLUMN_STR END,
		CASE COLUMN_MONTH WHEN 7 THEN COLUMN_STR END,
		CASE COLUMN_MONTH WHEN 8 THEN COLUMN_STR END,
		CASE COLUMN_MONTH WHEN 9 THEN COLUMN_STR END,
		CASE COLUMN_MONTH WHEN 10 THEN COLUMN_STR END,
		CASE COLUMN_MONTH WHEN 11 THEN COLUMN_STR END,
		CASE COLUMN_MONTH WHEN 12 THEN COLUMN_STR END
ORDER BY COLUMN_DAY

Open in new window

When I don't include the cases in the group by clause, it complains that column_month and column_str aren't in an aggregate function or group by clause. When I insert them in the group by it complains "The statement terminated. The maximum recursion 100 has been exhausted before statement completion.".
So, any further ideas?
0
 
supengmmerCommented:
Yesterday I was busy ,therefor did't test it. This following can work, test it on SQL Server2005.
the table:
 
create table TblTrabalho
(
  datHoraEntradaTrabalho Datetime ,
  datHoraSaidaTrabalho   Datetime ,
  FK_ID_intTipoTrabalho  varchar(5)
 );
insert into TblTrabalho values( '01-01-2011', '01-01-2011' , '1abc');
insert into TblTrabalho values( '02-03-2011', '02-08-2011' , '1abc');
insert into TblTrabalho values( '02-09-2011', '03-02-2011' , 'ab1c');

Open in new window

tablethe SQL:
 
WITH temp(COLUMN_DAY, COLUMN_MONTH, COLUMN_STR, BEGIN_DATE, END_DATE)
AS
(SELECT DAY(datHoraEntradaTrabalho)   AS COLUMN_DAY, 
        MONTH(datHoraEntradaTrabalho) AS COLUMN_MONTH,
		FK_ID_intTipoTrabalho         AS COLUMN_STR, 
        datHoraEntradaTrabalho        AS BEGIN_DATE, 
        datHoraSaidaTrabalho          AS END_DATE
   FROM TblTrabalho
UNION ALL
 SELECT DAY(DATEADD(DAY,1,BEGIN_DATE)), 
        MONTH(DATEADD(DAY,1,BEGIN_DATE)), 
        COLUMN_STR, 
        DATEADD(DAY,1,BEGIN_DATE), 
        END_DATE
   FROM temp   
  WHERE DATEADD(DAY,1,BEGIN_DATE) <= END_DATE  
)
select  COLUMN_DAY,
		MAX(CASE COLUMN_MONTH WHEN 1 THEN COLUMN_STR ELSE '' END) AS JAN,
		MAX(CASE COLUMN_MONTH WHEN 2 THEN COLUMN_STR ELSE '' END) AS FEV,
		MAX(CASE COLUMN_MONTH WHEN 3 THEN COLUMN_STR ELSE '' END) AS MAR,
		MAX(CASE COLUMN_MONTH WHEN 4 THEN COLUMN_STR ELSE '' END) AS ABR,
	    MAX(CASE COLUMN_MONTH WHEN 5 THEN COLUMN_STR ELSE '' END) AS MAI,
	    MAX(CASE COLUMN_MONTH WHEN 6 THEN COLUMN_STR ELSE '' END) AS JUN,
	    MAX(CASE COLUMN_MONTH WHEN 7 THEN COLUMN_STR ELSE '' END) AS JUL,
	    MAX(CASE COLUMN_MONTH WHEN 8 THEN COLUMN_STR ELSE '' END) AS AGO,
	    MAX(CASE COLUMN_MONTH WHEN 9 THEN COLUMN_STR ELSE '' END) AS SEP,
	    MAX(CASE COLUMN_MONTH WHEN 10 THEN COLUMN_STR ELSE '' END) AS OCT,
	    MAX(CASE COLUMN_MONTH WHEN 11 THEN COLUMN_STR ELSE '' END) AS NOV,
	    MAX(CASE COLUMN_MONTH WHEN 12 THEN COLUMN_STR ELSE '' END) AS DEZ
   from temp
  group by COLUMN_DAY
  order by COLUMN_DAY

Open in new window

the result:
 result
..etc

I wish it can help you.Thuus,award me more points..^_^
0
 
CluskittAuthor Commented:
Awesome, this is exactly what I was looking for. Thanks!!
0
 
CluskittAuthor Commented:
Just a small thing. I've adapted to this:
WITH temp(COLUMN_DAY, COLUMN_MONTH, COLUMN_STR, BEGIN_DATE, END_DATE)
AS
(SELECT DAY(datHoraEntradaTrabalho)   AS COLUMN_DAY, 
        MONTH(datHoraEntradaTrabalho) AS COLUMN_MONTH,
		strLegenda			          AS COLUMN_STR, 
        CASE WHEN YEAR(datHoraEntradaTrabalho)<2011 THEN '20110101' ELSE datHoraEntradaTrabalho END        AS BEGIN_DATE, 
        CASE WHEN YEAR(datHoraSaidaTrabalho)<2011 THEN '20110101' ELSE datHoraSaidaTrabalho END          AS END_DATE
   FROM TblTrabalho INNER JOIN tblTiposTrabalho ON ID_intTipoTrabalho=FK_ID_intTipoTrabalho 
   WHERE YEAR(datHoraSaidaTrabalho)=2011 OR YEAR(datHoraSaidaTrabalho)=2011 
        AND FK_ID_intFuncionario=1452 AND FK_ID_intEmpresa=41
UNION ALL
 SELECT DAY(DATEADD(DAY,1,BEGIN_DATE)), 
        MONTH(DATEADD(DAY,1,BEGIN_DATE)), 
        COLUMN_STR, 
        DATEADD(DAY,1,BEGIN_DATE), 
        END_DATE
   FROM temp   
  WHERE DATEADD(DAY,1,BEGIN_DATE) <= END_DATE
)
select  COLUMN_DAY,
		MAX(CASE COLUMN_MONTH WHEN 1 THEN COLUMN_STR ELSE '' END) AS JAN,
		MAX(CASE COLUMN_MONTH WHEN 2 THEN COLUMN_STR ELSE '' END) AS FEV,
		MAX(CASE COLUMN_MONTH WHEN 3 THEN COLUMN_STR ELSE '' END) AS MAR,
		MAX(CASE COLUMN_MONTH WHEN 4 THEN COLUMN_STR ELSE '' END) AS ABR,
	    MAX(CASE COLUMN_MONTH WHEN 5 THEN COLUMN_STR ELSE '' END) AS MAI,
	    MAX(CASE COLUMN_MONTH WHEN 6 THEN COLUMN_STR ELSE '' END) AS JUN,
	    MAX(CASE COLUMN_MONTH WHEN 7 THEN COLUMN_STR ELSE '' END) AS JUL,
	    MAX(CASE COLUMN_MONTH WHEN 8 THEN COLUMN_STR ELSE '' END) AS AGO,
	    MAX(CASE COLUMN_MONTH WHEN 9 THEN COLUMN_STR ELSE '' END) AS SEP,
	    MAX(CASE COLUMN_MONTH WHEN 10 THEN COLUMN_STR ELSE '' END) AS OCT,
	    MAX(CASE COLUMN_MONTH WHEN 11 THEN COLUMN_STR ELSE '' END) AS NOV,
	    MAX(CASE COLUMN_MONTH WHEN 12 THEN COLUMN_STR ELSE '' END) AS DEZ
   from temp
  group by COLUMN_DAY
  order by COLUMN_DAY

Open in new window

because I have to filter by year. Also, it has to be for just one person. However, two things happen:
1- The results are the same for all employees
2- When there isn't any record on a particular year, nothing is returned (it should return an empty grid, that is, all days, just no values inside it).
0
 
supengmmerCommented:
Sorry, I don't understand the 1st question.
For the second, you can create a table for left join such as :
 
create table EmptyGrid
(
COLUMN_DAY int,
JAN varchar(3),
FEV varchar(3),
MAR varchar(3),
ABR varchar(3),
MAI varchar(3),
JUN varchar(3),
JUL varchar(3),
AGO varchar(3),
SEP varchar(3),
OCT varchar(3),
NOV varchar(3),
DEZ varchar(3)
);
insert into EmptyGrid values(1,'','','','','','','','','','','','');

with x(COLUMN_DAY,JAN,FEV,MAR,ABR,MAI,JUN,JUL,AGO,SEP,OCT,NOV,DEZ) as
(select COLUMN_DAY,JAN,FEV,MAR,ABR,MAI,JUN,JUL,AGO,SEP,OCT,NOV,DEZ
from EmptyGrid  
union all
select COLUMN_DAY+1,JAN,FEV,MAR,ABR,MAI,JUN,JUL,AGO,SEP,OCT,NOV,DEZ
from x
where COLUMN_DAY+1 between 2 and 31 )
insert into EmptyGrid select * from x  where COLUMN_DAY >= 2

Open in new window


then SQL mofidy to:
 
WITH temp(COLUMN_DAY, COLUMN_MONTH, COLUMN_STR, BEGIN_DATE, END_DATE)
AS
(SELECT DAY(datHoraEntradaTrabalho)   AS COLUMN_DAY, 
        MONTH(datHoraEntradaTrabalho) AS COLUMN_MONTH,
		FK_ID_intTipoTrabalho         AS COLUMN_STR, 
        datHoraEntradaTrabalho        AS BEGIN_DATE, 
        datHoraSaidaTrabalho          AS END_DATE
   FROM TblTrabalho
UNION ALL
 SELECT DAY(DATEADD(DAY,1,BEGIN_DATE)), 
        MONTH(DATEADD(DAY,1,BEGIN_DATE)), 
        COLUMN_STR, 
        DATEADD(DAY,1,BEGIN_DATE), 
        END_DATE
   FROM temp   
  WHERE DATEADD(DAY,1,BEGIN_DATE) <= END_DATE  
)
select E.COLUMN_DAY,isnull(G.JAN,'') AS JAN,isnull(G.FEV,'') AS FEV,isnull(G.MAR,'') AS MAR,isnull(G.ABR,'') AS ABR,
                    isnull(G.MAI,'') AS MAI,isnull(G.JUN,'') AS JUN,isnull(G.JUL,'') AS JUL,isnull(G.AGO,'') AS AGO,
                    isnull(G.SEP,'') AS SEP,isnull(G.OCT,'') AS OCT,isnull(G.NOV,'') AS NOV,isnull(G.DEZ,'') AS DEZ
from  EmptyGrid E left join 
(select  COLUMN_DAY,
		MAX(CASE COLUMN_MONTH WHEN 1 THEN COLUMN_STR ELSE '' END) AS JAN,
		MAX(CASE COLUMN_MONTH WHEN 2 THEN COLUMN_STR ELSE '' END) AS FEV,
		MAX(CASE COLUMN_MONTH WHEN 3 THEN COLUMN_STR ELSE '' END) AS MAR,
		MAX(CASE COLUMN_MONTH WHEN 4 THEN COLUMN_STR ELSE '' END) AS ABR,
	    MAX(CASE COLUMN_MONTH WHEN 5 THEN COLUMN_STR ELSE '' END) AS MAI,
	    MAX(CASE COLUMN_MONTH WHEN 6 THEN COLUMN_STR ELSE '' END) AS JUN,
	    MAX(CASE COLUMN_MONTH WHEN 7 THEN COLUMN_STR ELSE '' END) AS JUL,
	    MAX(CASE COLUMN_MONTH WHEN 8 THEN COLUMN_STR ELSE '' END) AS AGO,
	    MAX(CASE COLUMN_MONTH WHEN 9 THEN COLUMN_STR ELSE '' END) AS SEP,
	    MAX(CASE COLUMN_MONTH WHEN 10 THEN COLUMN_STR ELSE '' END) AS OCT,
	    MAX(CASE COLUMN_MONTH WHEN 11 THEN COLUMN_STR ELSE '' END) AS NOV,
	    MAX(CASE COLUMN_MONTH WHEN 12 THEN COLUMN_STR ELSE '' END) AS DEZ
   from temp 
  group by COLUMN_DAY
) G on G.COLUMN_DAY = E.COLUMN_DAY  
order by E.COLUMN_DAY

Open in new window


Now when there isn't any record on a particular year, nothing is returned. Maybe you have a better diea to solve it.
 a
Best regards.
0
 
CluskittAuthor Commented:
The first part, what I meant was that the results returned were the same for all employees. It should be filtered by employee. That is, one has the dates I posted above, another, for example, has the same but one month later. And when I run the query, both results appear all the time.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now