-
Notifications
You must be signed in to change notification settings - Fork 0
/
dw.sql
28 lines (28 loc) · 1002 Bytes
/
dw.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
/* DW */
/* create fact table*/
CREATE OR REPLACE TABLE FACT_CORRELATIONS_DAILY (
FACT_CORRELATIONS_DAILYID INT PRIMARY KEY IDENTITY
,DD_CALENDARDATE DATE /* grain */
,CT_FASTINGHOURS INT
,CT_INTENSITYMINUTES INT
,CT_SLEEPSCORE INT
,CT_STRESS INT );
/* insert the structure from FastingData table along with FastingHours */
INSERT INTO FACT_CORRELATIONS_DAILY (DD_CALENDARDATE,CT_FASTINGHOURS)
SELECT CALENDARDATE, FASTINGHOURS
FROM ODS.FASTINGDATA;
/* append the IntensityMinutes for each day */
UPDATE DW.FACT_CORRELATIONS_DAILY F
SET F.CT_INTENSITYMINUTES = I.INTENSITYMINUTES
FROM ODS.INTENSITYMINUTESDATA I
WHERE F.DD_CALENDARDATE = I.CALENDARDATE;
/* append the SleepHours for each day */
UPDATE DW.FACT_CORRELATIONS_DAILY F
SET F.CT_SLEEPSCORE = SL.SLEEPSCORE
FROM ODS.SLEEPDATA SL
WHERE F.DD_CALENDARDATE = SL.CALENDARDATE;
/* append the AvgStress for each day */
UPDATE DW.FACT_CORRELATIONS_DAILY F
SET F.CT_STRESS = ST.STRESS
FROM ODS.STRESSDATA ST
WHERE F.DD_CALENDARDATE = ST.CALENDARDATE;