Design question

I have 2 tables like this:
First table has rows indicating is someone comes in (InOut = 1 or goes
out (InOut = 0)
InOut
SNIDDate InOut
3'2007-01-01'0
4'2007-01-15'0
3'2007-03-28'1
5'2007-01-01'0
4'2007-04-15'1
5'2007-02-12'1
Second table indicates a category
Category
SNIDDate Cat
3'2007-01-01'0
4'2007-01-15'4
3'2007-04-16'2
5'2007-01-01'2
4'2007-03-15'1
5'2007-01-18'3
I need to have a list of each week, month, year, ... with the number of
people present and their category:
Something like:
DateCatTotal
Jan 2007031
Jan 2007416
Feb 2007...
I can do the calculation each day and save the number to the
datawarehouse. But isn't it also possible to save the two tables to
the datawarehouse and have it do the calculations on the fly?
How can I do the second option? Is it with a slowly changing dimension?
Thanks in advance,
Stijn Verrept.
No, I would actually use T-SQL to calculate. It shouldnt take too long..
Something like (I would replace 0 with -a in InOut column):
select tbl2.category, tbl1.date, sum(tbl1.InOut) as NumberOfPeople
from
tbl1
inner join tbl2 on tbl1.date = tbl2.date and tbl1.SNID = tbl2.SNID
group by
tbl2.category, tbl1.date
MC
PS. You can optimize by using only recent changes or something...
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:yc6dnTCj_5dGYbDbnZ2dnUVZ8qSnnZ2d@.scarlet.biz. ..
>I have 2 tables like this:
> First table has rows indicating is someone comes in (InOut = 1 or goes
> out (InOut = 0)
> InOut
> SNID Date InOut
> 3 '2007-01-01' 0
> 4 '2007-01-15' 0
> 3 '2007-03-28' 1
> 5 '2007-01-01' 0
> 4 '2007-04-15' 1
> 5 '2007-02-12' 1
> Second table indicates a category
> Category
> SNID Date Cat
> 3 '2007-01-01' 0
> 4 '2007-01-15' 4
> 3 '2007-04-16' 2
> 5 '2007-01-01' 2
> 4 '2007-03-15' 1
> 5 '2007-01-18' 3
> I need to have a list of each week, month, year, ... with the number of
> people present and their category:
> Something like:
> Date Cat Total
> Jan 2007 0 31
> Jan 2007 4 16
> Feb 2007 ...
> I can do the calculation each day and save the number to the
> datawarehouse. But isn't it also possible to save the two tables to
> the datawarehouse and have it do the calculations on the fly?
> How can I do the second option? Is it with a slowly changing dimension?
> --
> Thanks in advance,
> Stijn Verrept.