dims.sql

declare @data table ( dateid date, timeid time(0), value decimal(19,5) );
declare @DB2_DATE table ( dateid date );
declare @DB2_TIME table ( timeid time(0) );

/*  Build DB2 dim tables */
insert into @DB2_DATE values ('2013-03-01')
insert into @DB2_DATE values ('2013-03-02')
insert into @DB2_DATE values ('2013-03-03')
insert into @DB2_DATE values ('2013-03-04')
insert into @DB2_DATE values ('2013-03-05')
insert into @DB2_TIME values ('11:30:00')
insert into @DB2_TIME values ('11:30:01')
insert into @DB2_TIME values ('11:30:02')
insert into @DB2_TIME values ('11:30:03')
insert into @DB2_TIME values ('11:30:04')

/*	Insert using explicit values down to the milli	(x.999999999 is the max precision) */
/*	But the first will have rounding issues.  It will be bumped up to the next value	*/
insert into @data values ( '01/01/2013 23:59:59.999999999', '2013-01-18 11:30:02.999999999', 42.00 );
insert into @data values ( '01/01/2013 23:59:59.999999999', '2013-01-18 11:30:02', 42.00 ); /* correct */

/*	Insert random data	*/
insert into @data values ( '2013-03-01', '11:30:02', 10.00 );
insert into @data values ( '2013-03-01', '11:30:02', 20.00 );
insert into @data values ( '2013-03-02', '11:30:02', 30.00 );
insert into @data values ( '2013-04-01', '11:30:03', 40.00 );
insert into @data values ( '2013-04-01', '11:30:03', 50.00 );


/*	30 by date	*/
select * from @data d
	join @DB2_DATE dt on (dt.dateid = d.dateid and dt.dateid = '2013-03-02')

/*	60 by date	*/
select * from @data d
	join @DB2_DATE dt on (dt.dateid = d.dateid)
where dt.dateid between '2013-03-01' and '2020-01-01'

/*	NULL - No warehouse datedim defined	for April	*/
select * from @data d
	join @DB2_DATE dt on (dt.dateid = d.dateid and dt.dateid = '2013-04-01')


/*	102 by time	 */
select * from @data d
	join @DB2_TIME dt on (dt.timeid = d.timeid)
where dt.timeid = '11:30:02'