[prev in list] [next in list] [prev in thread] [next in thread] 

List:       odtug-warehouse-l
Subject:    RE: DW Design?
From:       "Graziano, Kent" <Kent_Graziano () dpsk12 ! org>
Date:       2004-07-23 19:26:32
Message-ID: F001.005E95C1.20040723121826 () fatcity ! com
[Download RAW message or body]

Very nice! An additional thought would be to make the fact table a
materialized view that gets refreshed nightly if you needed a
just-in-time age calculation - then the reports would run faster...

Kent 

-----Original Message-----
epierce@surewest.net
Sent: Tuesday, July 20, 2004 2:58 PM
To: Multiple recipients of list ODTUG-WAREHOUSE-L

correct, but probably more typically there is an "anchor date" (e.g.,
per
survey year) that is used for a particular study/survey period that
would
be used instead of sysdate.

fwiw, here is what I use to calculate age-groups by decade:

---
       DECODE(
       substr(
               TRUNC( MONTHS_BETWEEN( TO_DATE( '20031001', 'YYYYMMDD' ),
                                      TO_DATE( C.STU_BIRTH_DT,
'YYYYMMDD' )
                                    ) /12
                    )
               ,1,1
             ),
               '*', 'UNKNOWN' , /* fall through, should not be seen */
               '0', '00 - 09' ,
               '1', '10 - 19' ,
               '2', '20 - 29' ,
               '3', '30 - 39' ,
               '4', '40 - 49' ,
               '5', '50 - 59' ,
               '6', '60 - 69' ,
               '7', '70 - 79' ,
               '8', '80 - 89' ,
               '9', '90 - 99'
             ) "AGROUP_01OCT2003"
---

Ashar Saeed said:
> you can calculate age at runtime of the report by
>
> AGE := TRUNC(MONTHS_BETWEEN(SYSDATE,:BIRTH_DATE)/12);
> or user this statement in sql statement
>
>>>> amin_adatia@hotmail.com 07/20/04 03:28PM >>>
> Does not the Age of the child depend on when you look at the record?

---
[previous post:]

> Main data entry tables:
...
> FAMILY_DETAIL (Contains information about the children of "head of the
> family" e.g Name, Gender, DOB, Class, School etc )
...
> The Data warehouse should answer the following questions :
...
> 2.        What are the ages of school going children in a town /city
...

-- 
ODTUG Now! 2004, Let Us Show you How! June 20-24, 2004 at the Westin
Kierland
Resort, Scottsdale, Arizona.  Visit http://www.odtug.com for details.-- 
Author: 
  INET: epierce@surewest.net

Fat City Hosting, San Diego, California -- http://www.fatcity.com
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ODTUG-WAREHOUSE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
ODTUG Now! 2004, Let Us Show you How! June 20-24, 2004 at the Westin Kierland
Resort, Scottsdale, Arizona.  Visit http://www.odtug.com for details.-- 
Author: Graziano, Kent
  INET: Kent_Graziano@dpsk12.org

Fat City Hosting, San Diego, California -- http://www.fatcity.com
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ODTUG-WAREHOUSE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
[prev in list] [next in list] [prev in thread] [next in thread]