asked on

SQL: creating a yearly calendar from dates

I have a table with date ranges, which I can cross with a calendar table. The query would be something like will change later with variables):

SELECT CalendarDate cd,
		strLegenda lg
FROM tblTrabalho
INNER JOIN Calendar
ON CalendarDate BETWEEN
	CASE WHEN YEAR(datHoraEntradaTrabalho)<2011 THEN '20110101 00:00' ELSE datHoraEntradaTrabalho+' 00:00' END AND
	CASE WHEN YEAR(datHoraSaidaTrabalho)>2011 THEN '20111231 23:59' ELSE datHoraSaidaTrabalho+' 23:59' END
INNER JOIN tblTiposTrabalho
ON ID_intTipoTrabalho=FK_ID_intTipoTrabalho
WHERE FK_ID_intEmpresa=41
	AND FK_ID_intFuncionario=1453
	AND (YEAR(datHoraEntradaTrabalho)=2011
		or YEAR(datHoraSaidaTrabalho)=2011)

Open in new window

This will return the following values (using a small example):

2011-10-20 00:00:00.000	BAT
2011-10-21 00:00:00.000	BAT
2011-10-22 00:00:00.000	BAT
2011-10-23 00:00:00.000	BAT
2011-11-02 00:00:00.000	MH

Open in new window

What I want is to turn this into a year calendar. Something like:

2011	Jan	Feb	[...]	Oct	Nov	Dec
Day1
Day2					MH
Day3
[...]
Day20				BAT
Day21				BAT
[...]
Day31

Open in new window

I posted a similar question recently, but it turns out the answer wasn't quite correct.

sachitjain

Not the best but some way to achieve what you expect

declare @results table ([2011] varchar(10), Jan varchar(250), Feb varchar(250),
                                    Mar varchar(250), Apr varchar(250), May varchar(250), Jun varchar(250),
                                    Jul varchar(250), Aug varchar(250), Sep varchar(250), Oct varchar(250),
                                    Nov varchar(250), [Dec] varchar(250))
insert into @results values (1, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (2, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (3, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (4, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (5, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (6, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (7, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (8, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (9, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (10, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (11, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (12, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (13, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (14, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (15, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (16, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (17, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (18, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (19, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (20, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (21, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (22, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (23, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (24, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (25, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (26, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (27, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (28, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (29, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (30, '', '', '', '', '', '', '', '', '', '', '', '')
insert into @results values (31, '', '', '', '', '', '', '', '', '', '', '', '')

update x set x.Jan = (case y.Mon when 1 then (case x.Jan when '' then y.lg else x.Jan+','+y.lg end) else x.Jan end),
            x.Feb = (case y.Mon when 2 then (case x.Feb when '' then y.lg else x.Jan+','+y.lg end) else x.Feb end),
            x.Mar = (case y.Mon when 3 then (case x.Mar when '' then y.lg else x.Mar+','+y.lg end) else x.Mar end),
            x.Apr = (case y.Mon when 4 then (case x.Apr when '' then y.lg else x.Apr+','+y.lg end) else x.Apr end),
            x.May = (case y.Mon when 5 then (case x.May when '' then y.lg else x.May+','+y.lg end) else x.May end),
            x.Jun = (case y.Mon when 6 then (case x.Jun when '' then y.lg else x.Jun+','+y.lg end) else x.Jun end),
            x.Jul = (case y.Mon when 7 then (case x.Jul when '' then y.lg else x.Jul+','+y.lg end) else x.Jul end),
            x.Aug = (case y.Mon when 8 then (case x.Aug when '' then y.lg else x.Aug+','+y.lg end) else x.Aug end),
            x.Sep = (case y.Mon when 9 then (case x.Sep when '' then y.lg else x.Sep+','+y.lg end) else x.Sep end),
            x.Oct = (case y.Mon when 10 then (case x.Oct when '' then y.lg else x.Oct+','+y.lg end) else x.Oct end),
            x.Nov = (case y.Mon when 11 then (case x.Nov when '' then y.lg else x.Nov+','+y.lg end) else x.Nov end),
            x.[Dec] = (case y.Mon when 12 then (case x.[Dec] when '' then y.lg else x.[Dec]+','+y.lg end) else x.[Dec] end)
from @results x left join (SELECT day(CalendarDate) DayOfMon, month(CalendarDate) Mon,
                                                      strLegenda lg
                                          FROM tblTrabalho
                                          INNER JOIN Calendar
                                          ON CalendarDate BETWEEN
                                                CASE WHEN YEAR(datHoraEntradaTrabalho)<2011 THEN '20110101 00:00' ELSE datHoraEntradaTrabalho+' 00:00' END AND
                                                CASE WHEN YEAR(datHoraSaidaTrabalho)>2011 THEN '20111231 23:59' ELSE datHoraSaidaTrabalho+' 23:59' END
                                          INNER JOIN tblTiposTrabalho
                                          ON ID_intTipoTrabalho=FK_ID_intTipoTrabalho
                                          WHERE FK_ID_intEmpresa=41
                                                AND FK_ID_intFuncionario=1453
                                                AND (YEAR(datHoraEntradaTrabalho)=2011
                                                      or YEAR(datHoraSaidaTrabalho)=2011)) y
on x.[2011] = y.DayofMon

select * from @results

sachitjain

Instead of last line select * from @results

use the line
select Day + cast([2011] as varchar(10)), Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, [Dec] from @results

Cluskitt

ASKER

Well, that doesn't quite work. It seemed it did, at first, but if you insert, for example, a value for 1st March, any values for day 1 will be ignored from then on. So, 1st November won't have anything in it.
Also, no need to add values. For each day, there is a single value.

ASKER CERTIFIED SOLUTION

sachitjain

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Cluskitt

ASKER

While I feel that this is probably not the most effective way to achieve this, it does what is necessary and it's fast enough. Thanks for your help. :)

Anthony Perkins

>>While I feel that this is probably not the most effective way to achieve this<<
You are right. A far better way is to build a permanent well indexed calendar table and use that instead of building it on the fly every time you need it.

Sorte.

Cluskitt

ASKER

I have a permanent calendar table. I just don't know how to use it to create a grid.
Unless you mean to create the @results table permanently. That would involve continuously clearing the table and repopulating, or duplicating info from other tables. Doesn't seem very effective either, at least in terms of space.

Anthony Perkins

>>Doesn't seem very effective either, at least in terms of space. <<
Fair enough. I guess we will have to agree to disagree on that one.

Cluskitt

ASKER

The problem is that we have about 20000 employees in total, which we would have to load up with 2 records for each. Also, each year a new record would need to be added to most of them. And this isn't called so often as to justify storing everything. The query, as it is, is fast enough. I just thought there would be a more efficient way with pivots or something like that, but I could never get the hang of them.

sachitjain

I agree with acperkins the way I suggested is not so efficient but problem is that if try pivot query then we would get only days that have corresponding values under months' columns but the other days won't come so output could be like
2011      Jan      Feb      [...]      Oct      Nov      Dec
Day2                              MH
Day20                        BAT
Day21                        BAT
Day30 MH

So if that is acceptable then something could be tried out through pivot queries too.

Cluskitt

ASKER

I have a numbers table. Basically, a table called Numbers with a field called Number with numbers from 0 to 999999. You can use a:
SELECT Number FROM Numbers WHERE Number<32
and left join from there.