Versions Compared

Old Version 13

changes.mady.by.user Chris Hyzer (upenn.edu)

Saved on

compared with

New Version Current

changes.mady.by.user Chris Hyzer (upenn.edu)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...


See when emails were last sent

Sample queries

Returns people from a group 23 days out of group and 27 days out of group.  Could give an email notification to users if 30 day grace period, 7 days before out of group, and again at 3 days out of group

Code Block
SELECT distinct gpmglw.GROUP_NAME, gpmglw.SUBJECT_ID FROM grouper_pit_mship_group_lw_v gpmglw, grouper_time gt
where gpmglw.GROUP_NAME = 'test:testGroup' and gpmglw.FIELD_NAME = 'members' and gpmglw.SUBJECT_SOURCE = 'pennperson' and gpmglw.the_end_time is not null
and ((select max(gpmglw2.THE_END_TIME) / 1000000 from grouper_pit_mship_group_lw_v gpmglw2 
     where gpmglw2.FIELD_ID = gpmglw.FIELD_ID and gpmglw2.group_id = gpmglw.group_id and gpmglw2.member_id = gpmglw.member_id ) 
     between (gt.utc_millis_since_1970/1000 - (23*24*60*60)) and (gt.utc_millis_since_1970/1000 - (24*24*60*60))
     or
     (select max(gpmglw2.THE_END_TIME) / 1000000 from grouper_pit_mship_group_lw_v gpmglw2 
     where gpmglw2.FIELD_ID = gpmglw.FIELD_ID and gpmglw2.group_id = gpmglw.group_id and gpmglw2.member_id = gpmglw.member_id ) 
     between (gt.utc_millis_since_1970/1000 - (27*24*60*60)) and (gt.utc_millis_since_1970/1000 - (28*24*60*60)))
and not exists (select 1 from grouper_pit_mship_group_lw_v gpmglw2 
     where gpmglw2.FIELD_ID = gpmglw.FIELD_ID and gpmglw2.group_id = gpmglw.group_id and gpmglw2.member_id = gpmglw.member_id and gpmglw2.THE_ACTIVE = 'T' );