Hello,
I need to build a view which will be used as a fact table in an Analysis cube. I'm having alot of difficulty trying to extract data from the detail table I'm pulling information from.
This is a sample of my detail table clrAlarmTest:
alarmID clrDT clrUserID
---------------- ------------------------------------------------------ ---------
mdtsm1678162 2003-01-03 07:35:06.000 clr_t1
mdtsm2922530 2003-01-16 00:52:42.000 restore
mdtsm1717737 2003-01-29 22:45:05.000 restore
vatsm23276519 2003-01-27 00:57:19.000 bfn5dvd
njtsm62347185 2003-01-02 14:51:48.000 b1zh4kv
njtsm111245985 2003-01-22 04:23:17.000 bdsd8q9
njtsm122388649 2003-01-17 04:11:44.000 restore
patsm62708524 2003-01-13 11:22:45.000 restore
patsm151527999 2003-01-29 07:48:49.000 restore
njtsm43155995 2003-01-03 06:18:31.000 b5ry6vs
In this table the same alarmID and clrDT may exist more than once.
What I need is a view that shows each distinct alarmID (once and only once) with its earliest clrDT (MIN) the corresponding clrUser and a column that states whether the alarm was auto cleared(A) or manually cleared(M). An alarm is auto cleared if the clrUserID is in ('restore', 'clr_t1') else it is manually cleared (clrUserID should begin with a b or is null).
Here is the kicker. Once the MIN clrDt is found if the clrUser falls into the autoclear category. Throw that record into the view. If there are two records with the exact same time just pick one of them. I don't want two records with the same alarm to appear. If the first instance of an alarmID falls into the manually cleared category (clrUserID NOT IN ('restore', 'clr_t1')) continue to check the remaining records with the same alarmID to see if any fall into the autoclear category. An autoclear overrides a manual clear no matter what comes first. Therefore, if no autoclears are found for the alarmID then throw the first manual clear record to the view. If an autoclear is found throw the first autoclear record to the view.
I tried all day yesterday to do this with self joins on the table but keep coming up with duplicates. I begun trying to write it with a formula but have not had much success.
The table is large about 1.475 million records and will grow to about 16 million by years end. I don't know if that would have any bearing on suggestions of how to do this.
Any help would be appreciated.
Thanks,
FB
I need to build a view which will be used as a fact table in an Analysis cube. I'm having alot of difficulty trying to extract data from the detail table I'm pulling information from.
This is a sample of my detail table clrAlarmTest:
alarmID clrDT clrUserID
---------------- ------------------------------------------------------ ---------
mdtsm1678162 2003-01-03 07:35:06.000 clr_t1
mdtsm2922530 2003-01-16 00:52:42.000 restore
mdtsm1717737 2003-01-29 22:45:05.000 restore
vatsm23276519 2003-01-27 00:57:19.000 bfn5dvd
njtsm62347185 2003-01-02 14:51:48.000 b1zh4kv
njtsm111245985 2003-01-22 04:23:17.000 bdsd8q9
njtsm122388649 2003-01-17 04:11:44.000 restore
patsm62708524 2003-01-13 11:22:45.000 restore
patsm151527999 2003-01-29 07:48:49.000 restore
njtsm43155995 2003-01-03 06:18:31.000 b5ry6vs
In this table the same alarmID and clrDT may exist more than once.
What I need is a view that shows each distinct alarmID (once and only once) with its earliest clrDT (MIN) the corresponding clrUser and a column that states whether the alarm was auto cleared(A) or manually cleared(M). An alarm is auto cleared if the clrUserID is in ('restore', 'clr_t1') else it is manually cleared (clrUserID should begin with a b or is null).
Here is the kicker. Once the MIN clrDt is found if the clrUser falls into the autoclear category. Throw that record into the view. If there are two records with the exact same time just pick one of them. I don't want two records with the same alarm to appear. If the first instance of an alarmID falls into the manually cleared category (clrUserID NOT IN ('restore', 'clr_t1')) continue to check the remaining records with the same alarmID to see if any fall into the autoclear category. An autoclear overrides a manual clear no matter what comes first. Therefore, if no autoclears are found for the alarmID then throw the first manual clear record to the view. If an autoclear is found throw the first autoclear record to the view.
I tried all day yesterday to do this with self joins on the table but keep coming up with duplicates. I begun trying to write it with a formula but have not had much success.
The table is large about 1.475 million records and will grow to about 16 million by years end. I don't know if that would have any bearing on suggestions of how to do this.
Any help would be appreciated.
Thanks,
FB
