CIP Sample Queries

This page was created for administrators to be able to easily find any and all sample queries that are located within CIP Learn in one location. Making it easier for Administrators to filter through all of the query samples in one place to see all of the Queries we offer.

User Query Samples

A list for Attorneys to find cases where discovery is needed

In this example, this query returns a list of all cases where a user is listed in the relationships, the case is open and active, there is an event indicating the Discovery was requested, and there are no events indicating the discovery was sent.

select i.incident_id  
from vi_incident_status i  
join tb_event rd on rd.incident_id=i.incident_id and rd.event_type='DISCOVERY' and rd.event_sub_type='REQUESTED'  
join vius_relationship rel on rel.incident_id=i.incident_id and rel.current_rel='Y'  
where i.incident_status='O'  
and i.user_incident_status='O'  
and not exists (Select 0 from tb_event dr where dr.incident_id=i.incident_id and dr.event_type='DISCOVERY' 
and dr.event_sub_type='AUTHORIZED' and dr.event_date>rd.event_date)  
and rel.sec_user=[CurrentUser]

Cases Where Discovery was NOT Sent

select distinct i.incident_id
from vi_incident_status i
join tb_event e on e.incident_id=i.incident_id and e.event_type='DISCOVER'
join tb_image m on m.incident_id=i.incident_id
join vius_relationship a on a.incident_id=i.incident_id and a.current_rel='Y' and a.sec_user=[currentUser]
where not exists (select 0 from tb_image_log l where l.image_id=m.image_id and l.action in ('DVIASH','D'))

Cases with HEARINGS scheduled by date and division

SELECT e.incident_id
FROM tb_event e 
JOIN vi_incident_status v on v.incident_id=e.incident_id
where e.event_type='hearing'
and e.event_date>=[Date From]
and e.event_Date< [Date To]
and v.current_division=[Division]
and v.incident_status='o'

Cases without a TASA by division

SELECT i.incident_id From vi_incident_status i
JOIN tb_event e on e.incident_id=i.incident_id and e.event_type='OPEN' and e.event_date>=dateadd(DD,-540,getdate())
WHERE NOT EXISTS (Select 0 from vius_relationship f where f.incident_id=i.incident_id and f.relationship='TASA' and f.current_rel='Y')
and i.user_incident_status='O'
and i.case_Type in ('CF','CT','MM','CJ')
and i.current_division=[Division]

Lists all cases with images that will be purged based on the office's current configuration

SELECT i.incident_id
FROM vi_incident_status i
JOIN cd_case_type ct on ct.case_type=i.case_type and ct.minimun_dispo_age_for_image_purge>0
JOIN tB_image m on m.incident_id=i.incident_id and m.dt_created<dateadd(DD, convert(int,(select currentvalue 
from cd_configuration where configuration='IPIMAGEAGE')) -1,getdate())
JOIN cd_image_type it on it.image_type=m.image_type and it.delete_on_file_purge='Y'
JOIN tb_event e on e.incident_id=i.incident_id
AND e.event_type=(select currentvalue from cd_configuration where configuration='IPPGEVTP')
AND e.event_sub_type=(select currentvalue from cd_configuration where configuration='IPPGEVSTP')
AND e.event_date<getdate()
WHERE i.incident_status='C'
AND NOT EXISTS (Select 0 from tb_event x where x.incident_id=i.incident_id and
x.event_date>=dateadd(DD, ct.minimun_dispo_age_for_image_purge-1,getdate()) and x.case_status in ('O','C'))
order by m.image_idSELECT i.incident_id
FROM vi_incident_status i
JOIN cd_case_type ct on ct.case_type=i.case_type and ct.minimun_dispo_age_for_image_purge>0
JOIN tB_image m on m.incident_id=i.incident_id and m.dt_created<dateadd(DD, 
convert(int,(select currentvalue from cd_configuration where configuration='IPIMAGEAGE')) -1,getdate())
JOIN cd_image_type it on it.image_type=m.image_type and it.delete_on_file_purge='Y'
JOIN tb_event e on e.incident_id=i.incident_id
AND e.event_type=(select currentvalue from cd_configuration where configuration='IPPGEVTP')
AND e.event_sub_type=(select currentvalue from cd_configuration where configuration='IPPGEVSTP')
AND e.event_date<getdate()
WHERE i.incident_status='C'
AND NOT EXISTS (Select 0 from tb_event x where x.incident_id=i.incident_id and
x.event_date>=dateadd(DD, ct.minimun_dispo_age_for_image_purge-1,getdate()) and x.case_status in ('O','C'))

My Active Cases

This query will list the open cases for the user that is currently logged in

select v.incident_id
from vi_incident_status v
join vius_relationship a on a.incident_id=v.incident_id
where a.sec_user=[user name]
and v.user_incident_status='O'
and a.current_rel='Y'

My cases assigned in the last 14 days

select v.incident_id
from vi_incident_status v
join vius_relationship a on a.incident_id=v.incident_id and a.current_rel='Y' and a.sec_user=[user name]
and a.relationship_date >=dateadd(dd,-14,GETDATE)
where v.incident_status='O'

My cases with notes added in the last 30 days

select incident_id
from tb_note
where user_created=[currentuser]
and dt_Created>=dateadd(dd,-30,getdate())

My cases with To DO Bookmark

This query will search for all cases with records that are bookmarked with a Bookmark type of “To Do”

select v.incident_id
from vi_incident_status v
join vius_relationship a on a.incident_id=v.incident_id and a.current_rel='Y'
join tb_bookmark b on b.incident_id=v.incident_id and b.bookmark_type='TODO'
where a.sec_user=[Current User]

My cases with MY To Do Bookmark

This query is similar to the one above but will search for records that were bookmarked by the user that is currently logged in and the record is a Bookmark type of “To Do”.

select v.incident_id 
from vi_incident_status v
join vius_relationship a on a.incident_id=v.incident_id and a.current_rel='Y'
join tb_bookmark b on b.incident_id=v.incident_id and b.bookmark_type='TODO' and b.sec_user=a.sec_user
where a.sec_user=[Current User]

My cases with a hearing in the next 14 days

select v.incident_id
from vi_incident_status v
join vius_relationship a on a.incident_id=v.incident_id and a.current_rel='Y' and a.sec_user=[Current User]
join tb_event e on e.incident_id = v.incident_id and e.event_type='HEARING'
and e.event_date>=GETDATE
and e.event_date <= DATEADD)
where v.incident_status='O'

My cases with hearings today

select v.incident_id
from vi_incident_status v
join vius_relationship a on a.incident_id=v.incident_id and a.current_rel='Y' and a.sec_user=[Current User]
join tb_event e on e.incident_id = v.incident_id and e.event_type='HEARING'
and e.event_date>=convert(date,GETDATE)
and e.event_date <= DATEADD))
where v.incident_status='O'

My cases with a warning

select i.incident_id
from vi_incident_status i
join vius_relationship a on a.incident_id=i.incident_id and a.current_rel='Y' and a.sec_user=[Current user]
where (i.warning is not null or case_warning<>'*')

My cases without a filing decision

select v.incident_id
from vi_incident_status v
join vius_relationship a on a.incident_id=v.incident_id
where a.sec_user=[user name]
and v.user_incident_status='O'
and a.current_rel='Y'
and not exists (Select 0 from tb_event f where f.incident_id=v.incident_id and f.event_type='FILED')

My OPEN Cases with a HEARING scheduled in by date

SELECT I.INCIDENT_ID 
FROM TB_INCIDENT I, ST_INCIDENT_SUMMARY S, TB_RELATIONSHIP R , tb_event e
WHERE I.INCIDENT_ID = S.INCIDENT_ID AND I.INCIDENT_ID = R.INCIDENT_ID AND R.DIRECTORY_ID = [TYPE APD] 
AND R.RELATIONSHIP = 'APD' AND R.CURRENT_REL = 'Y' AND S.INCIDENT_STATUS_PD = 'O'
and e.incident_id=i.incident_id
and e.event_type='HEARING'
and e.event_date>=[BEGIN DATE]
and e.event_date < [END DATE]

My OPEN Cases with a HEARING scheduled in the next 7 days

select i.incident_id 
from vi_incident_status i
join vius_Relationship r on r.incident_id=i.incident_id
join tb_event e on e.incident_id=i.incident_id
where r.current_Rel='Y'
and r.sec_user =[secuser]
and i.incident_status='O'
and e.event_type='hearing'
and e.event_Date between getdate() and dateadd(dd,7,getdate())

My Pending Cases with Bookmarks

select distinct i.incident_id
from vi_incident_status i
join vius_Relationship r on r.incident_id=i.incident_id
join tb_bookmark b on b.incident_id=i.incident_id
where r.current_Rel='Y'
and r.sec_user =[secuser]
and i.incident_status='O'

Open cases with a Terminated or Inactive Attorney listed as current

select v.incident_id 
from vi_incident_status v 
join vius_relationship r on r.incident_id=v.incident_id 
where v.incident_status='O' 
and v.user_incident_status='O' 
and r.directory_type='ASA' 
and r.status in ('I','T')

Open Cases without Activity in the last 90 days

SELECT i.incident_id
FROM vi_incident_status i
WHERE i.incident_status='O'
AND i.user_incident_status='O'
AND NOT EXISTS (SELECT 0 FROM tb_event e WHERE i.incident_id=e.incident_id and e.dt_created>=dateadd(DD,-90,GETDATE()))

This query returns a list of cases a user accessed in the current day

SELECT incident_id
FROM st_incident_viewed
WHERE sec_user = [USER]
AND dt_created >= CAST(GETDATE() AS DATE)
AND dt_created <= GETDATE()

This query returns a list of cases a user accessed yesterday

SELECT incident_id
FROM st_incident_viewed
WHERE sec_user = [USER]
AND dt_created >= DATEADD(dd,-1, CAST(GETDATE() AS DATE))
AND dt_created < CAST(GETDATE() AS DATE) 

Event Trigger Samples

This query can be used to prevent users from adding a hearing if there is no current attorney in a case

SELECT directory_id FROM tb_relationship WHERE incident_id=[INCIDENTID] AND RELATIONSHIP IN ('TASA','FASA','RASA') AND CURRENT_REL='Y'

This query can be used to prevent users from disposing a case when there are open requests or open investigations

SELECT 0
FROM tb_incident i
WHERE i.incident_id=[INCIDENTID]
AND 
(EXISTS(SELECT 0 FROM tb_request r WHERE r.incident_id=i.incident_id and r.dt_completed IS NULL)
OR EXISTS (SELECT 0 FROM tb_investigation v WHERE v.incident_id=i.incident_id and v.investigation_outcome='*')) 

CIP Portal Forms Samples

Victim Impact Statement

Before running this script, please do a search and replace the following: change and replace the parenthesis with Greater than and less then for the following entries: (h4), (/h4), (b), and (/b)

declare @image_type varchar(10),@image_sub_type varchar(10)
select top 1 @image_type=t.image_type,@image_sub_type=st.image_sub_type from cd_image_type t
inner join cd_image_sub_type st on t.image_type=st.image_type
where t.image_type <> '*' and t.inactive='N' and st.inactive='N'
declare @sequence int = 10
declare @page int = 1
declare @formId int 
declare @r int
EXEC @r=sp_getSequence 'form_template', @formid OUTPUT
print @formid
insert into cd_Form_template (form_template_id,name,email_template_id,inactive,user_modified,user_created)
select @formId,'Victim Impact Worksheet',20,'N','cip','cip'
--page 1
insert into cd_form_field_template (form_template_id,name,label,page_no,sequence,col_size,form_field_type_id, r
equired,instruction,form_field_map_type_id,image_type,image_sub_type,send_stac_values,user_Created)
select @formid,null,'(h4)1. Victim Contact Information(/h4)',1,10,12,14,'N',null,0,'*','*','N','cip'
union select @formid,'victim_first_name','First Name:',1,20,6,1,'Y',null,1,'*','*','N','cip'
union select @formid,'victim_middle_name','Middle Name:',1,30,3,1,'N',null,2,'*','*','N','cip'
union select @formid,'victim_last_name','Last Name:',1,40,3,1,'Y',null,3,'*','*','N','cip'
union select @formid,'victim_dob','Date of Birth:',1,50,6,4,'N',null,10,'*','*','N','cip'
union select @formid,'victim_address','Address:',1,60,12,1,'Y',null,5,'*','*','N','cip'
union select @formid,'victim_city','City:',1,70,6,1,'Y',null,6,'*','*','N','cip'
union select @formid,'victim_state','State:',1,80,3,15,'Y',null,7,'*','*','N','cip'
union select @formid,'victim_zip','Zip:',1,90,3,1,'Y',null,8,'*','*','N','cip'
union select @formid,'victim_home_phone','Home:',1,100,6,10,'N',null,0,'*','*','N','cip'
union select @formid,'victim_home_cell','Cell:',1,105,6,10,'Y',null,0,'*','*','N','cip'
--union select @formid,'victim_email','Email:',1,110,12,9,'Y',null,4,'*','*','N','cip'
union select @formid,'victim_email_contact','May we contact you via email ?',1,130,12,3,'N',null,0,'*','*','N','cip'
union select @formid,'victim_employer','Employer:',1,140,12,1,'N',null,0,'*','*','N','cip'
union select @formid,'victim_employer_business_address','Business Address:',1,150,12,1,'N',null,0,'*','*','N','cip'
union select @formid,'victim_employer_business_phone','Phone:',1,160,6,10,'Y',null,0,'*','*','N','cip'
union select @formid,'victim_employer_business_fax','Fax:',1,170,6,10,'Y',null,0,'*','*','N','cip'
union select @formid,'victim_employer_city','City:',1,180,6,1,'N',null,0,'*','*','N','cip'
union select @formid,'victim_employer_state','State:',1,190,3,15,'N',null,0,'*','*','N','cip'
union select @formid,'victim_employer_zip','Zip:',1,200,3,1,'N',null,0,'*','*','N','cip'
union select @formid,null,'Please provide contact information for a friend, relative, or Victim Advocate:',1,210,12,14,'N',null,0,'*','*','N','cip'
union select @formid,'victim_friend_fullname','Full Name:',1,215,12,1,'N',null,0,'*','*','N','cip'
union select @formid,'victim_friend_relationship','Relationship:',1,220,12,1,'N',null,0,'*','*','N','cip'
union select @formid,'victim_friend_phone','Phone:',1,230,6,10,'N',null,0,'*','*','N','cip'
union select @formid,'victim_friend_cell','Cell:',1,240,6,10,'Y',null,0,'*','*','N','cip'
union select @formid,null,'(b)NOTE: Please contact our office if any of the above information changes.(/b)',1,250,12,14,'N',null,0,'*','*','N','cip'
--page 2
insert into cd_form_field_template (form_template_id,name,label,page_no,sequence,col_size,form_field_type_id, required,instruction,
form_field_map_type_id,image_type,image_sub_type,send_stac_values,user_Created)
select @formid,null,'(h4)2. Physical Injury(/h4)',2,10,12,14,'N',null,0,'*','*','N','cip'
union select @formid,null,
'If you required medical treatment for injuries sustained as a result of this incident, please complete the following section.
If you were not injured, please skip this section and proceed to section 3.',
2,20,12,14,'N',null,0,'*','*','N','cip'
union select @formid,'physical_injury_medical_release',
'The State may need to obtain copies of your medical records pertaining to this incident to assist in the prosecution of your case. 
Are you willing to sign a medical release so we may subpoena your records?',
2,30,12,3,'N',null,0,'*','*','N','cip'
union select @formid,'physical_injury_physician_hospital','Physician / Hospital:',2,40,12,1,'N',null,0,'*','*','N','cip'
union select @formid,'physical_injury_injuries','Nature of Injuries:',2,50,12,2,'N',null,0,'*','*','N','cip'
union select @formid,'physical_injury_documentation_copies','(b)Please attach copies of documentation (bills, estimates, receipts)
(/b)',2,60,12,8,'N',null,0,@image_type,@image_sub_type,'N','cip'
union select @formid,'physical_injury_future_medical_treatment',
'Do you anticipate future medical treatment as a result of this incident ?',2,70,12,3,'N',null,0,'*','*','N','cip'
union select @formid,'physical_injury_future_medical_treatment_details',
'If you answered yes, please specify below',2,80,12,2,'N',null,0,'*','*','N','cip'
union select @formid,'physical_injury_insurance_pay','Will your insurance pay for these anticipated expenses ?',2,90,12,3,'N',null,0,'*','*','N','cip'
union select @formid,'physical_injury_insurance_copay','Do you have co-pay ?',2,100,6,3,'N',null,0,'*','*','N','cip'
union select @formid,'physical_injury_insurance_copay_amount','If yes, how much ?',2,110,6,6,'N',null,0,'*','*','N','cip'
union select @formid,'physical_injury_filed_claim','Have you filed a claim through the 
(b)State’s Victim Compensation Fund?(/b)',2,120,6,3,'N',null,0,'*','*','N','cip'
union select @formid,'physical_injury_filed_claim_number','If so, do you have a claim number ?',2,130,6,1,'N',null,0,'*','*','N','cip'
union select @formid,null,
'(b)*NOTE: If you have not filed a claim or are in need of other services, please detach page 5 '+
'and contact your local victim services location or call our office and we will contact them on your behalf.(/b)',2,140,12,14,'N',null,0,'*','*','N','cip'
--page 3
insert into cd_form_field_template (form_template_id,name,label,page_no,sequence,col_size,form_field_type_id, required,
instruction,form_field_map_type_id,image_type,image_sub_type,send_stac_values,user_Created)
select @formid,null,'(h4)3. Property Damage or Loss(/h4)',3,10,12,14,'N',null,0,'*','*','N','cip'
union select @formid,null,
'Please list the property that was damaged or lost as a result of this incident and attach copies of documentation. (b)'+
'(Note: If you are 60 years of age or older and/or a disabled adult you may be entitled to reimbursement for property loss with the bureau of victim
compensation).(/b)',
3,20,12,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'Item Description',3,30,6,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'Purchase Date',3,40,2,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'Purchase Price',3,50,2,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'Replacement Price',3,60,2,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'1.',3,70,1,14,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_1',Null,3,80,5,1,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_purchase_date_1',Null,3,90,2,4,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_purchase_price_1',Null,3,100,2,6,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_replacement_price_1',Null,3,110,2,6,'N',null,0,'*','*','N','cip'
union select @formid,null,'2.',3,120,1,14,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_2',Null,3,130,5,1,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_purchase_date_2',Null,3,140,2,4,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_purchase_price_2',Null,3,150,2,6,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_replacement_price_2',Null,3,160,2,6,'N',null,0,'*','*','N','cip'
union select @formid,null,'3.',3,170,1,14,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_3',Null,3,180,5,1,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_purchase_date_3',Null,3,190,2,4,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_purchase_price_3',Null,3,200,2,6,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_replacement_price_3',Null,3,210,2,6,'N',null,0,'*','*','N','cip'
union select @formid,null,'4.',3,220,1,14,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_4',Null,3,230,5,1,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_purchase_date_4',Null,3,240,2,4,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_purchase_price_4',Null,3,250,2,6,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_replacement_price_4',Null,3,260,2,6,'N',null,0,'*','*','N','cip'
union select @formid,null,'5.',3,270,1,14,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_5',Null,3,280,5,1,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_purchase_date_5',Null,3,290,2,4,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_purchase_price_5',Null,3,300,2,6,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_item_replacement_price_5',Null,3,310,2,6,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_financial_impact','Has this incident impacted you financially ? (replacement / recovery costs, loss of wages, etc.)
',3,500,12,3,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_financial_impact_specify','If you answered yes, please specify below',3,510,12,2,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_insurance_coverage','Were the items covered by your insurance3 company ?',3,520,6,3,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_insurance_coverage_deductible','If you answered yes, how much is your deductible for your insurance claim ?'
,3,530,6,6,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_custody','Do you currently have property in the custody of the Police or Sheriff''s Department ?'
,3,540,12,3,'N',null,0,'*','*','N','cip'
union select @formid,'property_damage_custody_specify','If you answered yes, please specify',3,550,12,2,'N',null,0,'*','*','N','cip'
--page 4
insert into cd_form_field_template (form_template_id,name,label,page_no,sequence,col_size,form_field_type_id, 
required,instruction,form_field_map_type_id,image_type,image_sub_type,send_stac_values,user_Created)
select @formid,null,'(h4)4. Statement Summary(/h4)',4,10,12,14,'N',null,0,'*','*','N','cip'
union select @formid,'statement_summary_impact','Please state how this crime has affected you in terms of any hardships you have endured, 
directly or indirectly',4,20,12,2,'N',null,0,'*','*','N','cip'
union select @formid,'statement_summary_resolution','What resolution do you believe would be the most appropriate in this case ? 
(Ex: Commitment Program, Probation, Drug Treatment, etc.)',4,30,12,2,'N',null,0,'*','*','N','cip'
union select @formid,null,'Please review the information you have provided for accuracy.'+
' Please note that lying or falsification of any information on this statement is considered perjury and could result in criminal charges',4,40,12,14,'N',
null,0,'*','*','N','cip'
insert into cd_form_field_template (form_template_id,name,label,page_no,sequence,col_size,form_field_type_id, 
required,instruction,form_field_map_type_id,image_type,image_sub_type,send_stac_values,user_Created)
select @formid,'statement_summary_hearing_contact','Would you like to be contacted for every hearing, or only hearings for which your presence is important?
',4,50,12,11,'Y',null,0,'*','*','N','cip'
declare @formFieldTemplateId int = SCOPE_IDENTITY()
insert into cd_form_field_option_template(form_field_template_id,value,label,user_modified,user_created)
select @formFieldTemplateId ,'all','Every hearing','cip','cip'
union select @formFieldTemplateId ,'requiremyattendance','Only hearings which require my attendance','cip','cip'
insert into cd_form_field_template (form_template_id,name,label,page_no,sequence,col_size,form_field_type_id, required,
instruction,form_field_map_type_id,image_type,image_sub_type,send_stac_values,user_Created)
select @formid,'statement_summary_initials','Your initials',4,60,6,1,'Y',null,0,'*','*','N','cip'
union select @formid,'statement_summary_date','Date',4,60,6,4,'Y',null,0,'*','*','N','cip'
--page 5
insert into cd_form_field_template (form_template_id,name,label,page_no,sequence,col_size,form_field_type_id, required,instruction,
form_field_map_type_id,image_type,image_sub_type,send_stac_values,user_Created)
select @formid,null,'(h4)Palm Beach County Victim Services offers the following(/h4)',5,10,12,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'1. (b)State''s Victim Compensation Fund(/b) applications for crime related injuries',5,10,12,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'2. Supportive counseling during this difficult time',5,20,12,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'3. Court accompaniment and information',5,30,12,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'4. Social Services referrals',5,40,12,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'5. Crime Prevention referrals',5,50,12,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'6. Restraining Orders according to Florida Statutes',5,60,12,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'(h4)PALM BEACH COUNTY VICTIM SERVICES PROGRAM(/h4)',5,70,12,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'WEST PALM BEACH',5,80,6,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'561-555-8888',5,90,6,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'PALM BEACH GARDENS',5,100,6,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'561-555-8888',5,110,6,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'DELRAY BEACH',5,120,6,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'561-555-8888',5,130,6,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'BELLE GLADE',5,140,6,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'561-555-8888',5,150,6,14,'N',null,0,'*','*','N','cip'
union select @formid,null,'As the victim of a crime you have rights. These rights are outlined in Florida Statute 960.',5,160,12,14,'N',
null,0,'*','*','N','cip'
union select @formid,null,'For additional information, please contact our office or you may visit our website: www..state.fl.us',
5,170,12,14,'N',null,0,'*','*','N','cip' 

Notification Center Samples

Although some examples use relationships specific to State Attorney’s or Public Defender’s, they can be easily modified to work for any office.

User Notifications

Arrest cases without filing decision

This notification tells the attorney on a case when there is no filing decision on an arrest case 12 days after the arrest

select i.incident_id, 
asa.directory_id as recipient_directory_id,
subject='Arrest Case Without Filing Decision',
line01=asa.first_name+', ',
line02='Cases '+incident_no+' is open and it was not filed.',
line03='The arrest date was on '+dbo.fmtDate(arrest.event_date) +' which was '+dbo.qint(DATEDIFF(dd,arrest.event_date, getdate()))+' days ago.'
from vi_incident_status i
join tb_event arrest on arrest.incident_id=i.incident_id 
join vius_fasa asa on asa.incident_id=i.incident_id
where user_incident_status='O'
and arrest.event_type='arrest'
and not exists (Select 0 from tb_event e where e.incident_id=i.incident_id and e.event_type in ('FILED','DISPO') )
and arrest.event_Date<=dateadd(dd,-12,getdate())
and asa.sec_user<>'*'

Confirming receipt of Discovery

Since the CIP Portal records when files are downloaded and that data is returned to STAC, it is easy to know when discovery is not retrieved from the Portal. We can use the Notification module to first send an email to the recipient reminding them about the file(s) waiting to be downloaded.

A good solution is to email the recipient if there are files waiting to be downloaded and there is a hearing coming. In this instance, the system can email both the recipient and the attorney on the case about the discovery not being downloaded.

This query can be added to the Notification module, and they will notify the ASA on the case about files not downloaded.

select  recipient_directory_id=r.directory_id, i.incident_id,  
subject='Discovery files not reviewed' ,  
line01='Dear '+max(r.first_name)+','+char(10),  
line02='The State Attorney''s Office has sent you discovery material related to the case number '+max(i.incident_no)+char(10),  
line03='A notification was sent to this email address ('+max(isnull(r.email,''))+ ')  over 5 days ago.'+char(10),  
line04='There are '+dbo.qint(count(0))+' file(s) currently in this situation as of '+dbo.qdate(getdate())+'.'+char(10),  
line05='Please go to https://portal.cipi.net at any time to review your files.'+char(10),  
line06='If you unable to access this material, please contact our office at your earliest convenience.' 
From tb_incident i   
join tb_image m on m.incident_id=i.incident_id  
join tb_image_log l on l.image_id=m.image_id  
join vius_relationship r on r.incident_id=i.incident_id and r.directory_id=l.action_directory_id  
join vius_fasa asa on asa.incident_id=i.incident_id  
where l.action='SHIMAGE'  
and not exists (select 0 from tb_image_log l2 where l2.image_id=m.image_id and l2.action='SHIMAGED')  
and l.dt_created<=dateadd(dd,-5,getdate())  
group by asa.directory_id, i.incident_id, r.directory_id 

The second query notifies the recipient via email about the files not yet downloaded

select  recipient_directory_id=asa.directory_id,  
i.incident_id,  
subject='Discovery send and not reviewed' ,  
line01='Discovery material was sent to '+dbo.fmtNameById(3,r.directory_id) +' ('+max(isnull(r.email,''))+ ')  over 5 days ago.',  
line02='There are '+dbo.qint(count(0))+' files currently in this situation.'  
From tb_incident i   
join tb_image m on m.incident_id=i.incident_id  
join tb_image_log l on l.image_id=m.image_id  
join vius_relationship r on r.incident_id=i.incident_id and r.directory_id=l.action_directory_id  
join vius_fasa asa on asa.incident_id=i.incident_id  
where l.action='SHIMAGE'  
and not exists (select 0 from tb_image_log l2 where l2.image_id=m.image_id and l2.action='SHIMAGED')  
and l.dt_created<=dateadd(dd,-5,getdate())  
group by asa.directory_id, i.incident_id, r.directory_id 

Creating variables to list the files sent as Discovery

If files being shared are listed in STAC (either added to the system or a linked file), you can easily write a variable to list all the files that were sent as part of the discovery to print in a document. Unfortunately, this would not work for linked folders because STAC does not know what is inside each folder.

This query can be added to the Notification module, and they will notify the ASA on the case about files not downloaded.

This example lists all images that are flagged to be part of the discovery since the last event authorizing sending the files was added to a case.

select it.description+ case when ist.image_sub_type='*' then '' else  '-'+ist.description end image_desc 
from tb_image i 
join cd_image_type it on it.image_type=i.image_type 
join cd_image_sub_type ist on ist.image_type=i.image_type and ist.image_sub_type=i.image_sub_type 
where i.incident_id=:INCIDENTID 
and (Select top 1 action from tb_image_log l where l.image_id=i.image_id and l.action in ('DS','D','SINFO','DVIASH','N') 
order by l.image_log_id desc) <>'N' 
and isnull((Select top 1 l.dt_created from tb_image_log l where l.image_id=i.image_id and l.action in ('DS','D','SINFO','DVIASH') 
order by l.image_log_id desc),getdate())> 
isnull((select top 1 e.event_date 
From tb_event e 
join cd_document d on d.document_id=e.document_id and d.efile_type='discovery' 
where e.dt_created<dateadd(MI,-1,getdate()) 
and incident_id=:INCIDENTID 
order by e.event_date desc),'1/1/2021') 
order by it.description+'-'+ ist.description 

Daily Digest

This notification is a summary of the open cases, e-filed documents, and pending documents to be approved. In this example we are sending the notifications to active APD’s'

select  recipient_directory_id =d.directory_id,
subject = 'Daily Digest',
line01 = isnull(d.first_name+': ','')+'This is the daily summary for your cases',
line02 = dbo.qint((select count(*) from vi_incident_status i join vius_apd t on i.incident_id=t.incident_id where i.incident_status='O' and t.directory_id=d.directory_id)) +' open active cases',
line03 = dbo.qint((select count(*) from vi_incident_status i join vius_apd t on i.incident_id=t.incident_id join tb_Event e on e.incident_id=i.incident_id and e.event_type='hearing' and e.event_Date>=getdate() where i.incident_status='O' and t.directory_id=d.directory_id)) +' hearings scheduled',
line04 = dbo.qint((select count(*) from tb_document_send e where e.attorney_directory_id=d.directory_id and e.efile='Y' and e.send_status_id in (4,5) and e.dt_created>=dateadd(dd,-30,getdate()))) +' Document efiled in the last 30 days',
line05 = dbo.qint((select count(*) from tb_document_send e where e.attorney_directory_id=d.directory_id and e.efile='Y' and e.send_status_id in (2) and e.dt_created>=dateadd(dd,-300,getdate()))) +' Efile Documents awaiting to be approved '
from tb_directory d
where directory_id in (select directory_id from vi_incident_status i join vius_apd t on i.incident_id=t.incident_id where i.incident_status='O')
and d.status='A'

Filed cases without filed charges

select 
recipient_directory_id =userCreated.directory_id, 
i.incident_id, 
subject='Filed Event without Filed charges', 
line01='Case '+isnull(i.incident_no,'')+ ' has a Filed event without Filed Charges',
Line02='You''ve added this event on '+dbo.qdate(f.dt_created),
line03='The Filed date is '+dbo.qdate(f.event_date),
line04='Please update the case and file the charges properly'
from vi_incident_status i
join tb_event f on f.incident_id=i.incident_id and f.event_type='FILED'
join tb_directory userCreated on userCreated.sec_user=f.user_created
where incident_status='O'
and not exists (select 0 from tb_charge c where c.incident_id=i.incident_id and c.charge_type='FILED')

New cases assigned

New cases assigned by employee

select recipient_directory_id =r.directory_id, 
i.incident_id, 
subject=case when not exists (Select 0 from tb_relationship x where r.incident_id=i.incident_id and x.relationship=r.relationship and x.relationship_id<r.relationship_id) then 'New Case Assigned' else 'Case Reassigned' end , 
line01=dbo.fmtNameById(1,r.directory_id)+char(13)+' You have been assigned to this case on '+dbo.fmtDateTime(r.relationship_date), 
line02=' Defendant Name ='+def.Name+ ' - Case# '+i.incident_no , 
line03=isnull((select top 1 'Charge: '+description from tb_charge c where c.incident_id=i.incident_id order by c.sequence_id desc, c.count_no ),''),
line04=isnull((select top 1 'Next Hearing: '+dbo.fmtDateTime(x.event_date) +' - '+est.description
from tb_Event x 
join cd_Event_sub_type est on est.event_type=x.event_type and est.event_sub_type=x.event_sub_type
where x.incident_id=i.incident_id and x.event_type='HEARING' and x.event_date>=getdate() order by x.event_date),'')
from tb_incident i 
join vius_relationship r on r.incident_id=i.incident_id and r.relationship_date>=dateadd(dd,-3,getdate()) and r.current_rel='Y' and r.sec_user<>'*'
join vius_main_subject def on def.incident_id=i.incident_id

New client contact

Sends a new notification to attorney when there is a new event recording a client contact

select recipient_directory_id =apd.directory_id, 
i.incident_id, 
subject='Client Contact', 
line01='On  '+dbo.fmtDateTime(e.dt_createD)+ ' your client left you a message', 
line02=' Defendant Name ='+def.Name ,
line03=case when e.event_sub_type<>'*' then st.description else '' end + case when e.event_action<>'*' then ' - ' +ea.description else '' end,
line04=e.comment_pd,
line05='Message added by '+e.user_created
from tb_incident i 
join vius_apd apd on apd.incident_id=i.incident_id 
join tb_event e on e.incident_id=i.incident_id 
join cd_Event_sub_type st on st.event_type=e.event_type and st.event_sub_type=e.event_sub_type
join cd_Event_action ea on ea.event_type=e.event_type and ea.event_action=e.event_action
and e.event_type='CLNTCONT'  
and e.event_date>=dateadd(dd,-1,getdate()) 
join vius_main_subject def on def.incident_id=i.incident_id

Notify a user when they create a directory without an Email, Phone, or Address

select incident_id=0,
recipient_directory_id=userCreated.directory_id,
subject='New Directory Record without Email, Address, or Phone records',
line01=isnull('Dear '+dbo.fmtsentencecase(usercreated.first_name)+',',''),
line02='On '+dbo.fmtdatetime(d.dt_created)+' you created a directory record for '+dbo.fmtNameById(1,d.directory_id)+' - Directory ID:'+dbo.qint(d.directory_id),
line03='However, there is no email, phone number, or an address for this person, making it impossible to contact them.',
line04='Please update the record with the missing information'
from tb_directory d
join tb_directory userCreated on userCreated.sec_user=d.user_created 
where d.directory_type='OTH'and d.dt_created>=dateadd(mm,-1,getdate())and d.email is null 
and not exists (select 0 from tb_address a where a.directory_id=d.directory_id)
and not exists (select 0 from tb_phone a where a.directory_id=d.directory_id)

Notify attorneys when a relationship in one of their cases was arrested in another case

This notification can be used to replace the functionality in STAC that notifies attorneys when any of the relationships in an open case is arrested in another case. Using this option instead allows for an easier customization of this notification.

select i.incident_id,
Subject='Arrest notice',
recipient_directory_id=a.directory_id,
line01=a.first_name+', ',
line02=isnull(r.first_name,'')+isnull(' '+r.last_name,'')+' is listed as '+r.relationship+ ' on case '+i.incident_no+' and you listed as the current '+a.relationship,
line03='On '+dbo.qdate(e.event_date)+ ' '+isnull(r.first_name,'')+' was arrested on the following charge(s)',
line04=(select top 1 'Count 1:'+description from vius_charge_current c where c.incident_id=arrestcase.incident_id and count_no=1)+
isnull((select top 1 ' / Count 2:'+description from vius_charge_current c where c.incident_id=arrestcase.incident_id and count_no=2),'')+
isnull((select top 1 ' / Count 3:'+description from vius_charge_current c where c.incident_id=arrestcase.incident_id and count_no=3),''),
line05='Please see case# '+arrestcase.incident_no+' for more information'
from vi_incident_status i
join vius_fasa a on a.incident_id=i.incident_id
join vius_relationship r on r.incident_id=i.incident_id and r.current_rel='Y'
join vius_main_subject d on d.directory_id=r.directory_id and d.relationship_id<>r.relationship_id
join tb_Event e on e.incident_id=d.incident_id and e.event_type='ARREST'
join tb_incident arrestcase on arrestcase.incident_id=d.incident_id
where i.incident_status='O'
and e.dt_created>=dateadd(dd,-1,getdate())
and a.sec_user<>'*'

Notify the requestor when a request is updated

select incident_id=r.incident_id,
recipient_directory_id=r.requestor_directory_id ,
Subject='Request Updated - Case Number '+isnull(i.clerk,'')+' - Def: '+d.name,
line01='Request '+r.request_type+case when r.request_sub_type='*' then '' else '/'+r.request_sub_type end+ ' Dated: '+dbo.fmtDateTime(r.dt_requested),
line02='Current request status='+r.request_status,
line03='Request Notes: '+isnull(r.result,'NO NOTES'),
line04='Updated by: '+r.user_modified+' on '+dbo.fmtDateTime(r.dt_modified)
from tB_request  r
join vi_incident_status i on i.incident_id=r.incident_id
join vius_def d on d.incident_id=r.incident_id
where r.dt_modified>= dateadd(dd,-1,getdate()) 
and r.dt_modified<> r.dt_created
and i.incident_status='O'

Notify the User and the STAC Administrators when CMS action is complete or it fails

This notification will notify the user who sent the file for conversion and the STAC Administrators when the file is complete or if the conversion failed.

It is recommended to schedule this notification on the Frequently Job Schedule and should be flagged to not allow duplicate messages.

STAC knows who the STAC Administrator is by checking the “Receive Server Notification” flag on the User Security screen!

SELECT recipient_directory_id=d.directory_id,
    incident_id = (SELECT TOP 1 i.incident_id FROM tb_image i WHERE i.image_path = f.file_path),   
    subject= 'Media Conversion ' + IIF(sha.dt_completed IS NOT NULL, 'completed', 'failed'),               
    line01=CASE WHEN sha.dt_completed IS NOT NULL 
                THEN 'Media was successfully converted and now can be previewed on STAC' 
                ELSE 'Media failed to be converted due to error: ' + sha.error_message
            END,
    line02=CHAR(10) + 'Image(s) related to media: ' + CHAR(10) + ISNULL((SELECT (SELECT '- Case No: ' + i.incident_no + ' | Image: ' + CONVERT(VARCHAR, im.image_id) + '-' + im.image_type + IIF(im.image_sub_type='*','','/'+im.image_sub_type) + ' - Date: ' + FORMAT(im.image_date,'MM/dd/yyyy HH:mm') + CHAR(10) FROM tb_image im JOIN tb_incident i ON i.incident_id = im.incident_id WHERE im.image_path = f.file_path ORDER BY i.incident_id, im.image_id FOR XML PATH(''), type).value('(./text())[1]','varchar(max)')),'')
FROM tb_service_hub_action sha
JOIN st_file f ON f.file_id = sha.file_id
JOIN cd_sec_user u ON u.inactive = 'N' AND (u.receive_server_notification = 'Y' OR u.sec_user = sha.user_created)   
JOIN tb_directory d ON d.sec_user = u.sec_user AND d.status = 'A'   
WHERE sha.service_hub_action_type_id = 140
AND sha.file_id > 0
AND ISNULL(sha.dt_completed, sha.dt_voided) IS NOT NULL   
AND DATEDIFF(MINUTE, ISNULL(sha.dt_completed, sha.dt_voided), GETDATE()) <= 60
AND NOT EXISTS (SELECT 1 FROM tb_service_hub_action_link_parser_result lpr WHERE lpr.service_hub_action_id = sha.related_to_service_hub_action_id)

Notify the User and the STAC Administrators when Link Parser fails

This notification will notify the user who added the link and the STAC Administrators about Link Parser URLs that failed. This query will identify the URL and the Cases related to that URL.

It is recommended to schedule this notification on the Frequently Job Schedule.

STAC knows who the STAC Administrator is by checking the “Receive Server Notification” flag on the User Security screen!

SELECT recipient_directory_id=d.directory_id,
incident_id = CASE WHEN (SELECT COUNT(*) 
FROM tb_service_hub_action_link_parser_cases lpc 
WHERE lpc.link_parser_id = lp.link_parser_id) = 1 
THEN (SELECT lpc.incident_id FROM tb_service_hub_action_link_parser_cases lpc 
WHERE lpc.link_parser_id = lp.link_parser_id) 
ELSE 0 END, 
subject= 'Link Parser failed',
line01='The URL failed to be parsed due to error: ' + sha.error_message,
    line02='URL: ' + lp.url,
    line03='Case(s) related: ' + ISNULL((SELECT STUFF((SELECT ', ' + incident_no FROM tb_service_hub_action_link_parser_cases lpc JOIN tb_incident i ON i.incident_id = lpc.incident_id WHERE lpc.link_parser_id = lp.link_parser_id FOR XML PATH('')),1,1,'')),'')
FROM tb_service_hub_action sha
JOIN tb_service_hub_action_link_parser lp ON lp.service_hub_action_id = sha.service_hub_action_id   JOIN cd_sec_user u ON u.inactive = 'N' AND (u.receive_server_notification = 'Y' OR u.sec_user = lp.user_created)   JOIN tb_directory d ON d.sec_user = u.sec_user AND d.status = 'A'   WHERE sha.service_hub_action_type_id = 150 
AND sha.dt_voided IS NOT NULL
AND DATEDIFF(MINUTE, sha.dt_voided, GETDATE()) <= 60

Notify when an APD is not set to receive Discovery via the exchange

This query is very helpful to notify when APDS are not set to receive discovery via exchange. This issue impacts how the discovery is sent, and the data exchange between SAO and PDO. This query was last updated 7/2022

select recipient_directory_id =d.directory_id,
0 as incident_id,
'There are APDS that are not configured to receive discovery via the Exchange with the PD' as subject,
line01=d.first_name+',',
line02='APD '+dbo.fmtNameById(1,apd.directory_id) +' is not configured to receive discovery via exchange',
line03='This will impact discovery submission and data exchange between both offices'
from 
tb_directory APD 
join tb_directory d on d.sec_user=APD.user_modified 
join vi_sys_project p on p.dbusage=2
where APD.directory_type = 'APD'
and apd.status = 'A'
and apd.discovery_method<>'EXCHANGE'

Notify when an APD and DAT are not set to receive Discovery via the Exchange or the Portal

This query is similar to the one above, but this one notifies when APDs and DATs are not set to receive discovery via exchange or the Portal. This issue impacts how the discovery is sent

select recipient_directory_id =d.directory_id,
0 as incident_id,
dbo.fmtNameById(1,DAT.directory_id) +' (Dir.Id:'+dbo.qInt(dat.directory_id)+') is not configured to receive discovery via CIP Portal or Exchange' as subject,
line01=d.first_name+',',
line02=dbo.fmtNameById(1,DAT.directory_id) +' is not configured to receive discovery via exchange',
line03='This will impact submission of discovery an other files',
line04='You updated this record on '+dbo.fmtDateTime(dat.dt_modified)
from tb_directory DAT
join tb_directory d on d.sec_user=DAT.user_modified 
join vi_sys_project p on p.dbusage=2
where DAT.directory_type in ('APD','DAT')
and DAT.status = 'A'
and DAT.discovery_method not in ('EXCHANGE','PORTAL')

Password expiration warning

Provides a warning to the users that their login password is about to expire. Regular users of the system will be able to see this notification, and update their passwords before they are locked out.

SELECT d.directory_id AS [recipient_directory_id]
,subject = 'WARNING: Password Expires in '+ CAST(90-DATEDIFF(DAY,u.password_dt,GETDATE()) AS VARCHAR) +' days.'
,Line01 = 'Your password will expire on: '+ CAST(DATEADD(DAY,90,u.password_dt) AS VARCHAR)
,Line02 = 'If you do not update your password prior to the date above, your account will be disabled.'
,Line03 = 'Please contact Information Systems if you require assistance.'
FROM tb_directory d
JOIN cd_sec_user u on u.sec_user = d.sec_user
WHERE d.sec_user <> '*' AND u.inactive = 'N' AND u.active_directory_user = 'N' AND (90-DATEDIFF(DAY,u.password_dt,GETDATE())) BETWEEN 0 AND 7

Admin Notifications

Alert administrators when an exchange is failing

Sends a new notification to administrators if an exchange has no activity in the last 5 days

select recipient_directory_id =d.directory_id,
'Exchange error' as subject,
line01='The following exchanges have not received data for the past 5+ days'+char(13),
line02=(Select SUBSTRING( 
(
 SELECT distinct 'exchange name '+c.name+' last received data '+CONVERT(varchar(23), s.ts_log, 121)+char(13)
     FROM   
       cd_exchange c, it_exchange i,st_import_log s 
     WHERE    
       inactive='N' AND    
       c.name=i.name AND    
             s.exchange_name=c.name and 
     EXISTS(SELECT 0 FROM it_exchange i WHERE i.import_exchange_method IN(2,3,4,6,7,9,11,12,14)) and i.real_time = 'N' 
         and s.import_log_id in (select top 1 import_log_id from st_import_log where exchange_name=c.name order by import_log_id desc)
         and DATEDIFF(DAY, s.ts_log,getdate())>=5 FOR XML PATH(''), type).value('(./text())[1]','nvarchar(max)'), 0 , 9999) As line01
)
from tb_directory d
join cd_sec_user s on s.sec_user=d.sec_user and s.receive_server_notification='Y'

Alert over 75 documents e-file pending

This sends a notification to the System Administrator when there are over 75 files pending to be e-filed. This code was copied from CIP’s notification: “Admin - E-files pending”, and is intended to provide an alternative notification. Users should adjust the number of pending documents according to their organizations typical throughput. Setting this notification to Discard when pending exists to prevent duplications.

SELECT
[recipient_directory_id] = d.directory_id,
[incident_id] = 0,
[subject] = 'E-File Warning',
[line01] = 'Over 75 documents are currently sending. This could indicate a problem with the E-Filing system.'
FROM tb_directory d
JOIN cd_sec_user u ON u.sec_user=d.sec_user AND u.receive_server_notification='Y'
WHERE (SELECT COUNT(*) FROM tb_document_send t WHERE t.send_status_id in (4)) > 75

Alert 50 emails pending

Alerts system administrators in the event of a possible email issue.

SELECT 
[recipient_directory_id] = d.directory_id,
incident_id = 0,
[subject] = 'Email Warning',
[line01] = 'Over 50 emails are currently pending. This could indicate a problem with the email system.'
FROM tb_directory d
JOIN cd_sec_user u ON d.sec_user = u.sec_user AND u.receive_server_notification ='Y'
WHERE (SELECT COUNT(*) FROM tb_email ee WHERE ee.dt_sent IS NULL AND ee.dt_voided IS NULL) > 50

E-files completed and not e-serviced

This notification shows any e-file that were configured to be E-serviced but there were no records for the E-service

select recipient_directory_id =stacadmin.directory_id,
i.incident_id as incident_id,
subject='WARNING - EFILE WITHOUT ESERVICE',
line01=' There is a efile on case '+i.incident_no+' with an efile document that HAS NOT BEEN EServiced',
line02=' Document name '+dbo.q(cd.name)+' created on '+dbo.fmtDateTime(d.dt_created)+' by user '+d.user_created
from  tb_incident i 
join tb_document_send d on d.incident_id=i.incident_id
join tb_document_send_action a on a.document_send_id=d.document_send_id
join tb_directory stacadmin on stacadmin.sec_user in (Select sec_user from cd_sec_user where receive_server_notification='Y')
join cd_document cd on cd.document_id=d.document_id
where a.document_send_log_id=250
and d.send_status_id=5 and send_type_id=1
and d.dt_created>=dateadd(dd,-60,getdate())
and not exists (Select 0 from tb_document_send_action a2 where a2.document_send_id=a.document_send_id and a2.document_send_log_id in (85,80))

Lists all cases where an inactive directory is a current relationship on an open case

This is very helpful for the administrator because it will produce a list of all of the open cases that have a current relationship with an inactive directory.

select recipient_directory_id =d.directory_id,
v.incident_id,
'Open with an inactive directory related' as subject,
line01=dbo.fmtNameById(1,r.directory_id) +' is a currently related as '+r.relationship+' and it is flag as inactive in the directory',
line02='Please update the case with an active employee'
from vi_incident_status v
join vius_relationship r on r.incident_id=v.incident_id and r.status in ('I','T') and r.current_rel='Y'
join cd_relationship c on c.relationship=r.relationship and c.internal_relationship<>'*'
join tb_directory d on d.sec_user in (select s.sec_user from cd_sec_user s where s.receive_server_notification='Y' and s.inactive='N')
where v.incident_status='O' and v.user_incident_status='O'

Notify the administrator when a new record is added to a case with an inactive attorney

This is very helpful to notify the administrator when a new record is added from an exchange, and the attorney is inactive

select recipient_directory_id =d.directory_id,
i.incident_id,
'New image received on a case with an inactive APD' as subject,
line01='Image '+m.image_type+case when m.image_sub_type<>'*' then ' sub type '+m.image_sub_type else '' end +' Date '+dbo.fmtDate(m.image_date) +' was received',
line02='The APD listed in the case is '+a.name+' who is inactive in the directory'
from tb_image m
join vi_incident_status i on i.incident_id=m.incident_id
join vius_apd a on a.incident_id=m.incident_id and a.status<>'A'
join tb_directory d  on d.sec_user in (select s.sec_user from cd_sec_user s where s.receive_server_notification='Y' and s.inactive='N')
where m.user_created='SA' and m.dt_created>=dateadd(dd,-3,getdate())

Open cases without activity

This notification tells the system administrator when a case has been open for over 90 days without a new event, relationship, or note being added

select i.incident_id,
d.directory_id as recipient_directory_id,
'Open Case without activity' as subject,
line01='Case# '+dbo.q(incident_no)+' is currently open and there was no activity in the last 90 days'
from vi_incident_status i
join tb_directory d on sec_user in (Select sec_user from cd_sec_user where receive_server_notification='Y')
where i.incident_status='O'
and i.user_incident_status='O'
and not exists 
(Select 0 from tb_event e where i.incident_id=e.incident_id and e.dt_created>=dateadd(dd,-90,getdate()))
and not exists 
(Select 0 from tb_relationship e where i.incident_id=e.incident_id and e.dt_created>=dateadd(dd,-90,getdate()))
and not exists 
(Select 0 from tb_note e where i.incident_id=e.incident_id and e.dt_created>=dateadd(dd,-90,getdate()))

Pending e-file documents

This query finds any pending e-file document older than 30 days and notify the system administrator

select recipient_directory_id =d.directory_id,
0 as incident_id,
'Pending Efile over 30 days' as subject,
line01='There are e-file documents that are at least 30 days old and are still pending',
line02='',
line03=(select convert(varchar(6), count(*)) +' documents in '+max(i.description) +' status' from tb_document_send  t join it_document_send_status i on i.send_status_id=t.send_status_id where t.send_status_id in (1)  and dt_created<=dateadd(dd,-30,getdate()) group by t.send_status_id),
line04=(select convert(varchar(6), count(*)) +' documents in '+max(i.description) +' status' from tb_document_send  t join it_document_send_status i on i.send_status_id=t.send_status_id where t.send_status_id in (2)  and dt_created<=dateadd(dd,-30,getdate()) group by t.send_status_id),
line05=(select convert(varchar(6), count(*)) +' documents in '+max(i.description) +' status' from tb_document_send  t join it_document_send_status i on i.send_status_id=t.send_status_id where t.send_status_id in (4)  and dt_created<=dateadd(dd,-30,getdate()) group by t.send_status_id)
from tb_directory d
join cd_sec_user s on s.sec_user=d.sec_user and s.receive_server_notification='Y'
where exists (
select 0
from tb_document_send  t 
join it_document_send_status i on i.send_status_id=t.send_status_id 
where t.send_status_id in (1,2,4)  and dt_created<=dateadd(dd,-30,getdate()) )

Pending files shared through the CIP Portal for over 24 hours

This query notifies the system administrator to check the CIP Portal Status page.

It is recommended to run this type of notification with the Nightly job schedule, so it does not affect anything during work hours.

SELECT incident_id = 0,
       recipient_directory_id = d.directory_id,
       subject = 'Files shared through CIP Portal are pending for over 24 hours',
       line01=(SELECT CONVERT(VARCHAR, COUNT(*)) + ' files remain on Waiting to be Sent status and the oldest is from ' + CONVERT(VARCHAR, DATEDIFF(HOUR, MIN(dt_created), GETDATE())) + ' hours ago' FROM tb_service_hub_action sha WHERE sha.service_hub_action_type_id = 10 AND sha.dt_sent IS NULL AND sha.dt_voided IS NULL AND DATEDIFF(HOUR, sha.dt_created, GETDATE()) >= 24),
       line02='Please review the CIP Portal Status page under Special Activities menu for more information!'
  FROM tb_directory d
  JOIN cd_sec_user u ON u.sec_user = d.sec_user
 WHERE d.status = 'A'
   AND u.sec_user <> '*' 
   AND u.inactive = 'N' 
   AND u.receive_server_notification = 'Y'
   AND EXISTS (SELECT 1 FROM tb_service_hub_action sha WHERE sha.service_hub_action_type_id = 10 AND sha.dt_sent IS NULL AND sha.dt_voided IS NULL AND DATEDIFF(HOUR, sha.dt_created, GETDATE()) >= 24)

Victims Witnesses Officers and Defendants Notifications

These notifications are sent as a TEXT, Phone call, or email and require the CIP Portal service

Disposition letter

This notification replaces the disposition letter. Again, the system can automatically identify the cases and email witnesses and law enforcement

select e.incident_id, 
recipient_directory_id=r.directory_id,
subject='State Attorney''s Office xxth Judicial Circuit - Case Disposed'+v.incident_no,
line01='Re:	State of Florida vs. '+(select top 1 name from vius_def def where def.incident_id=e.incident_id)+CHAR(10)+
    'Court Case No: '+v.incident_no+char(10)+
    'Agency: '++(select top 1 name from vius_age age where age.incident_id=e.incident_id)+CHAR(10)+
    'Agency Case No: '+v.first_age_rpt_no+CHAR(10)+
    'Offense/Arrest Date: '+(Select top 1 dbo.qdate(x.event_Date) from tb_event x where x.incident_id=e.incident_id and x.event_type='OFFENS')+char(10),
line02='Dear '+dbo.fmtnamebyid(3,r.directory_id) +': '+char(10),
line03='I am writing to inform you of the outcome of the case.',
line04='On '+convert(varchar(20),e.event_date,101)+', the case was closed with the following disposition:'+char(10)+isnull(e.comment_sa,''),
line05='If you have any questions, please do not hesitate to contact me.'+char(10),
line06='Sincerely,',
line07=dbo.fmtNameById(3,a.directory_id),
line08='Assistant State Attorney'
from tb_event e 
join vi_incident_status v on v.incident_id=e.incident_id
join vius_relationship r on r.incident_id=v.incident_id and r.current_rel='y'
join vius_fasa a on a.incident_id=e.incident_id
where e.event_type='DISPO'
and e.event_date>=dateadd(dd,-1,getdate())
and r.witness_flag<>'*'

Email notification of testimony

Automated email notification to a relationship when an event is set on the system. This particular example notifies the outside victim advocate (OVA) when a testimony for a victim is set.

select recipient_directory_id = ova.directory_id,
incident_id = i.incident_id,
subject = i.clerk_printable + ' ' + def.name + ' - Testimony Scheduled',
line01 = dbo.fmtName(1, vic.last_name, vic.suffix, vic.first_name, vic.middle_name, vic.title, 0, 0,0,vic.offender_type, vic.directory_warning, vic.directory_warning_2) + ' has been scheduled for the following testimony.',
line02 = 'Case Number:  ' + clerk_printable,
line03 = 'Defendant:  ' + def.name,
line04 = 'Appearance:  ' + convert(varchar, e.event_date, 100),
line05 = 'ASA:  ' + asa.name
from tb_incident i, tb_event e, vius_relationship ova, tb_directory vic, vius_def def, vius_asa asa
where 
i.incident_id = e.incident_id
and i.incident_id = def.incident_id
and i.incident_id = asa.incident_id
and i.incident_id = ova.incident_id
and e.directory_id = vic.directory_id
and e.event_type = 'testimony'
and e.event_sub_type = 'VIC'
and e.dt_created >= '03/27/2019'
and ova.relationship = 'ova'
and ova.current_rel = 'Y'

Introductory letter

This notification replaces the letter that is sent to a victim when a case is opened. It automatically finds new victims on new cases, and emails them without any user interaction

select e.incident_id, 
recipient_directory_id=r.directory_id,
subject='State Attorney''s Office xxth Judicial Circuit - New Case '+v.incident_no,
line01='Case Number:'+v.incident_no,
line02='State of Florida vs.'+(select top 1 name from vius_def def where def.incident_id=e.incident_id),
line03='',
line04='Dear '+dbo.fmtnamebyid(3,r.directory_id) +': ',
line05='I am the Assistant State Attorney responsible for the prosecution of this case.  It is important for you to remain in contact with our office until this case is closed and to make sure we have your correct address and phone number.',
line06='You may be contacted by defense attorneys, public defenders, investigators seeking to discuss this case with you.  While you are free to speak about the case with anyone you choose, you are not required to do so unless you have been served with a trial or deposition subpoena.  If you wish to discuss the case with me before giving a statement, please call me.',
line07='The vast majority of all criminal cases do not require a trial, as a defendant may choose to plead guilty and proceed to sentencing.  The sentencing range will be largely determined by the sentencing guidelines as enacted by the Florida legislature.  Your input is important to us and the Judge to determining a fair resolution of this matter.  Please contact me as soon as possible to discuss the sentencing guidelines and any recommendations you may have regarding an appropriate sentence.  If I do not hear from you, I will resolve this case without your input.',
line08='If the defendant does not bond out of jail immediately, it is likely that a bond hearing will be scheduled by the defense attorney.  This is often done on short notice to the State.  If you wish to be present at this hearing, or any other hearing that may be scheduled, please notify me immediately.',
line09='If you have a request or recommendation concerning sentencing or restitution, please call me within ten (10) days of the date of this letter at 904-867-5309.  I will also be happy to answer any questions I can.',
line10='',
line11='Sincerely',
line12='',
line13=dbo.fmtNameById(3,a.directory_id),
line14='Assistant State Attorney'
from tb_event e 
join vi_incident_status v on v.incident_id=e.incident_id
join vius_relationship r on r.incident_id=v.incident_id and r.current_rel='y'
join vius_fasa a on a.incident_id=e.incident_id
where e.event_type='OPEN'
and r.dt_created>=dateadd(dd,-1,getdate())
and r.victim_flag='Y'

Notify a defendant that a new discovery file was received

Automatically email a defendant when a new Discovery file was received. This can be easily modify to notify Witnesses or Victims

select recipient_directory_id =def.directory_id, 
i.incident_id, 
subject='New Discovery File was added to case number '+i.incident_no+'; Defendant '+def.Name, 
line01='Please call you attorney if you have any questions about this file',
line02='Comments: '+im.comment,
im.image_id
from tb_incident i 
join vius_apd apd on apd.incident_id=i.incident_id 
join tb_image im on im.incident_id=i.incident_id and im.image_type='DISCOVERY'
and im.dt_created>=dateadd(dd,-1,getdate()) 
join vius_main_subject def on def.incident_id=i.incident_id and def.email is not null

Notify clients that the court is cancelled

This would be used when something like a hurricane happens, all you need to do is disable the messages telling clients about the court event and enable this message that tells them the courthouse is closed.

 select i.incident_id,
 d.directory_id as recipient_directory_id,
 subject='From the Public Defender''s Office',
 line01='Your court appointment on  '+dbo.fmtDateTime(e.event_Date)+' has been cancelled. Please, do not go to the courthouse.'
 from vi_incident_status i
 join tb_Event e on e.incident_id=i.incident_id
 join vius_def d on d.incident_id=i.incident_id
 where i.incident_status='O'
 and e.event_type='HEARING'
 and e.event_Date>dbo.GetNextBusinessDay(getdate()+1)
 and e.event_date <= dbo.GetNextBusinessDay(getdate()+2)
 and dbo.DirectoryHasCellPhone(d.directory_id)=1
 and getdate()<'10/1/25'
 order by e.event_date

Notify the recipients that discovery was sent 15 days ago and it has not been retrieved

select Subject= 'Discovery sent over 15 days and not downloaded', Recipient_directory_id=x.directory_id,incident_id=x.incident_id,
line01=isnull('Dear '+d.first_name+',',''),
line02='We have sent discovery on case '+isnull(i.ucn, i.incident_no)+ ' on '+ dbo.fmtdate(x.dt_sent)+', and it seems that you have not downloaded it yet.',
line03='Please be sure to login to PORTAL.CIPI.NET and access your account and download the discovery at your earliest convenience.',
line04='This files will be available until '+dbo.fmtdate(dateadd(dd,29,x.dt_sent))+'.',
line05='Best Regards,'from(select  distinct et.directory_id, m.incident_id, convert(date,et.dt_sent) as dt_sent from tb_service_hub_action sha join tb_email_to et on et.email_to_id=sha.email_to_id and et.directory_id>0join tb_image m on m.image_id=sha.image_id
where sha.service_hub_action_type_id=10 and sha.portal_action_guid is not null and sha.dt_sent>= dateadd(mm,-1,getdate())and convert(Date,sha.dt_sent) = convert(Date,dateadd(dd,-15,getdate()))and not exists 
(Select 0 from tb_service_hub_action_log shal 
where shal.service_hub_action_id=sha.service_hub_action_id and shal.service_hub_action_log_type_id=100)) x join vi_incident_status i on i.incident_id=x.incident_id and i.incident_status='O' join tb_directory d on d.directory_id=x.directory_id

Notify the secretary when a text message failed to be delivered

If the system has an invalid phone number or the phone number does not accept text, the message fails and the portal returns a record with the error

SELECT recipient_directory_id =r.directory_id,
       sh.incident_id,
       subject='Text message failed',
       line01='Unable to deliver a text message to our client. Please check the Communication tab',
       line02=ISNULL(sha.comment,'') + ISNULL(IIF(LEN(ISNULL(sha.comment,''))>0, ' - ', '') + sha.comment2,'')
  FROM tb_service_hub_action_log sha 
  JOIN tb_service_hub_action sh ON sh.service_hub_action_id=sha.service_hub_action_id
  JOIN vius_relationship r ON r.incident_id=sh.incident_id AND r.current_rel='Y' AND r.relationship='SECRETARY'
 WHERE sha.service_hub_action_log_type_id IN (320, 321) 
   AND sha.dt_created>=DATEADD(dd,-2,GETDATE())

Notify witnesses and victims of an upcoming hearing

The functions below will help users identify records with cell phones and calculate business days

Function	Description
dbo.fmtdatetime or dbo.fmtdate	Return a date or date/time that is easy to read
dbo.GetNextBusinessDay	Find the next business day - it excludes Saturday and Sundays and any date defined in the cd_holiday
dbo.directoryHasCellPhone	Returns 1 if a person has a cell phone number. This is based in the setting in the cd_phone_type accepts_sms field and at least one of the phone types in the address table set a cell phone
dbo.directoryHasPhone	Returns 1 if a person has a phone number. This is based on the phone type in the address table

The queries below would work in the following scenario - Notification runs on the nightly job (1 am approx.) and creates messages for victims on hearings scheduled for the following day

/* Sends a text message recipients that have a CELL phone on file */
select i.incident_id,
recipient_directory_id=d.directory_id,
subject='From the State Attorney''s office',
line01='There is a hearing schedule on case# '+i.incident_no+' on ' + dbo.fmtDateTime(e.event_date),
line02='Please contact us if you have any questions.'
from vi_incident_status i
join tb_event e on e.incident_id=i.incident_id and e.event_type='HEARING' and e.event_date>=dbo.GetNextBusinessDay(getdate()) and e.event_date< dateadd(dd,1,dbo.GetNextBusinessDay(getdate()))
join vius_relationship d on d.incident_id=i.incident_id and d.current_rel='Y' and d.victim_flag='Y'
where i.incident_status='O'
and dbo.directoryHasCellPhone(d.directory_id)=1
	
/* Sends a phone call to recipients that have no CELL phone on file */
select i.incident_id,
recipient_directory_id=d.directory_id,
subject='From the State Attorney''s office',
line01='There is a hearing schedule on case# '+dbo.qStringInsert(i.incident_no,' ')+' on ' + dbo.fmtDateTime(e.event_date),
line02='Please contact us if you have any questions.'
from vi_incident_status i
join tb_event e on e.incident_id=i.incident_id and e.event_type='HEARING' and e.event_date>=dbo.GetNextBusinessDay(getdate()) and e.event_date< dateadd(dd,1,dbo.GetNextBusinessDay(getdate()))
join vius_relationship d on d.incident_id=i.incident_id and d.current_rel='Y' and d.victim_flag='Y'
where i.incident_status='O'
and dbo.directoryHasCellPhone(d.directory_id)=0
and dbo.directoryHasPhone(d.directory_id)=1

/* Sends an email to recipients that have no phone on file */
select i.incident_id,
recipient_directory_id=d.directory_id,
subject='From the State Attorney''s office',
line01='There is a hearing schedule on case# '+i.incident_no+' on ' + dbo.fmtDateTime(e.event_date),
line02='Please contact us if you have any questions.'
from vi_incident_status i
join tb_event e on e.incident_id=i.incident_id and e.event_type='HEARING' and e.event_date>=dbo.GetNextBusinessDay(getdate()) and e.event_date< dateadd(dd,1,dbo.GetNextBusinessDay(getdate()))
join vius_relationship d on d.incident_id=i.incident_id and d.current_rel='Y' and d.victim_flag='Y'
where i.incident_status='O'
and dbo.directoryHasCellPhone(d.directory_id)=0
and dbo.directoryHasPhone(d.directory_id)=0
and d.email is not null

Notify when someone replies to a text message

The system now handles replies to a text message. Any reply is appended to the last message sent to a phone number. This query notifies the APD of a new reply

select recipient_directory_id =a.directory_id,
i.incident_id,
subject='New Text Reply',
line01=case when et.directory_id >0 then dbo.fmtNameById(1,et.directory_id) else et.email_address end+' has replied to your text with the following:',
line02=' >> ' +er.reply
from tb_email_to_reply er
join tb_email_to et on et.email_to_id=er.email_to_id
join tb_email e on e.email_id=et.email_id
join vi_incident_status i on i.incident_id=e.incident_id
join vius_apd a on a.incident_id=i.incident_id
where er.dt_created>=dateadd(dd,-2,getdate())
and len(er.reply)>1

This example is similar to the query above, and the difference is that it will notify the person who created the message instead of a set relationship in a case. This query has been modified to eliminate notifications when the agency is replying to the messages and will only notify when the recipient is replying to the messages.

select recipient_directory_id =a.directory_id,
i.incident_id,
subject='New Text Reply',
line01=case when et.directory_id >0 then dbo.fmtNameById(1,et.directory_id) else et.email_address end+' has replied to your text with the following:',
line02=' >> ' +er.reply
from tb_email_to_reply er
join tb_email_to et on et.email_to_id=er.email_to_id
join tb_email e on e.email_id=et.email_id
join vi_incident_status i on i.incident_id=e.incident_id
join tb_directory a on a.sec_user=e.user_created
where er.dt_created>=dateadd(dd,-2,getdate())
and len(er.reply)>1
and email_to_reply_source = 1 

Notify when someone replies to a text message Translated

This query notifies about a new reply from a text message. This version will print a translated version of the reply if the recipient replies in another language.

select recipient_directory_id =a.directory_id, 
i.incident_id,
subject='New Text Reply',
line01=case when et.directory_id >0 then dbo.fmtNameById(1,et.directory_id) else et.email_address end+' has replied to your text with the following:',
line02=' >> ' +isnull(er.translated_reply, er.reply)
from tb_email_to_reply er
join tb_email_to et on et.email_to_id=er.email_to_id
join tb_email e on e.email_id=et.email_id
join vi_incident_status i on i.incident_id=e.incident_id
join tb_directory a on a.sec_user=e.user_created
where er.dt_created>=dateadd(dd,-2,getdate())
and len(er.reply)>1 

Sending an image as an email attachment or via the CIP Portal

Automated email notification advising the relationship an image was added to the case. This gets sent when an Image is added to the system AND there was a relationship added in the Name field at the time the image is added.

In the query for the notification center, the entity_name=‘tb_image’ has to be hardcoded in order for this notification to work and a second column named ENTITY_ID that should have the image id of the file being sent

select recipient_directory_id =m.directory_id, 
m.incident_id,
subject='New Document from the State Attorney''s office. Case number: '+i.incident_no,
line01='Dear '+dbo.fmtsentencecase(dbo.fmtnamebyid(1,m.directory_id)),
line02='Please find attached a '+isnull(it.document, it.description),
line03='dated '+dbo.fmtDatetime(m.image_date),
entity_name='tb_image',
entity_id=image_id 
from tb_image m
join vi_incident_status i on i.incident_id=m.incident_id
join tb_directory d on d.directory_id=m.directory_id
join cd_image_type it on it.image_type=m.image_type
where m.dt_created>= dateadd(dd,-1,getdate())
and d.email is not null

Send text message to the defendant notifying of an upcoming appointment

This query selects cases with an appointment event in the next 2 days. The result of this query is used to send a text message to the defendant to remind them of the upcoming appointment

select i.incident_id,
d.directory_id as recipient_directory_id,
subject='Client Appointment', 
line01='On  '+dbo.fmtDateTime(e.event_Date),
e.event_id as entity_id,
'tb_Event' as  entity_name
from vi_incident_status i
join tb_Event e on e.incident_id=i.incident_id
join vius_def d on d.incident_id=i.incident_id
where i.incident_status='O'
and e.event_type='APPT'
and e.event_sub_type in ('CLIENT','CLIENTPHON')
and e.event_Date>=dateadd(dd,2,getdate())
and e.event_date <= dateadd(dd, case when datepart(DW,getdate()) in (5,6) then 5 else 3 end, getdate())

Notify when the request is not completed and the Request Status is COMPLETED or CANCELED (STAC Conference 2024)

This query selects pending completion requests that are not completed, and the request status is Completed or Canceled. It will notify 3 groups of people:

• Last Modified User: It will send to the user that did the last modification on the request. It assumes that was the person who updated the status.
• Assign To: It will send to the user who is assigned to complete the request.
• STAC Administrators: It will send to the STAC Administrators after 5 days from the modified date. The STAC Administrator is based on the Receive Server Notification parameter under the User screen.
  

It is recommended to run this type of notification with the Nightly job schedule, so it does not affect anything during work hours.

SELECT x.incident_id,
       x.recipient_directory_id,
       'Missing to complete the request: ' + x.request_type + IIF(x.request_sub_type <> '*', '/' + x.request_sub_type, '') + ' - ' + +dbo.fmtDateTime(x.dt_requested) as subject,
       'Changing the status (' + x.request_status + ') does not complete the request! ' + CHAR(13) + 'Please, complete the request by clicking on the Complete Request button!' as line01 
  FROM (SELECT r.incident_id as incident_id,
               d.directory_id AS recipient_directory_id,
               r.request_type,
               r.request_sub_type,
               r.dt_requested,
               r.request_status
          FROM tb_request r
          JOIN tb_directory d ON d.sec_user = r.user_modified AND d.status = 'A' AND d.sec_user <> '*'
         WHERE r.request_status IN ('COMPLETED','CANCELED')
           AND ISNULL(r.approval_status,'approved') = 'approved'
           AND r.dt_completed IS NULL
         UNION
        SELECT r.incident_id as incident_id,
               d.directory_id AS recipient_directory_id,
               r.request_type,
               r.request_sub_type,
               r.dt_requested,
               r.request_status
          FROM tb_request r
          JOIN tb_directory d ON d.directory_id = r.assign_to_directory_id AND d.status = 'A' AND d.sec_user <> '*'
         WHERE r.request_status IN ('COMPLETED','CANCELED')
           AND ISNULL(r.approval_status,'approved') = 'approved'
           AND r.dt_completed IS NULL
         UNION
        SELECT r.incident_id as incident_id,
               d.directory_id AS recipient_directory_id,
               r.request_type,
               r.request_sub_type,
               r.dt_requested,
               r.request_status
          FROM tb_request r
          JOIN (SELECT d.directory_id
                  FROM cd_sec_user u
                  JOIN tb_directory d ON d.sec_user = u.sec_user AND d.status = 'A'
                 WHERE u.receive_server_notification = 'Y'
                   AND u.inactive = 'N') d ON 1=1
         WHERE r.request_status IN ('COMPLETED','CANCELED')
           AND ISNULL(r.approval_status,'approved') = 'approved'
           AND r.dt_completed IS NULL
           AND DATEDIFF(dd, r.dt_modified, GETDATE()) > 5) x

Notify attorney on the case when a Discovery is not answered and an Hearing is coming in the next 7 days (STAC Conference 2024)

This query selects cases where exists a DISCOVERY event type with PENDING event action and HEARING event type that the event day is greater than today and less than 7 days.

It is recommended to run this type of notification with the Nightly job schedule, so it does not affect anything during work hours.

SELECT e.incident_id as incident_id,
       r.directory_id AS recipient_directory_id,
       'Discovery is not answered' as subject,
       'Hearing is coming in the next 7 days or less' as line01 
  FROM tb_event e
  JOIN tb_relationship r ON r.incident_id = e.incident_id AND r.relationship = 'ASA' AND r.current_rel = 'Y'
 WHERE e.event_type = 'DISCOVERY'
   AND e.event_action = 'PENDING'
   AND EXISTS (SELECT 1 
                 FROM tb_event e2 
                WHERE e2.incident_id = e.incident_id 
                  AND e2.event_type = 'HEARING'
                  AND DATEDIFF(dd, GETDATE(), e2.event_date) BETWEEN 1 AND 7)
   AND EXISTS (SELECT 1 FROM tb_directory d JOIN cd_sec_user u ON u.sec_user = d.sec_user WHERE d.directory_id = r.directory_id AND d.status = 'A' AND u.inactive = 'N')

Notify attorney on the case when the case is filed and Discovery is not answered (STAC Conference 2024)

This query selects cases where exists a DISCOVERY event type without event action and FILED event type on the case. The date modified/created must be less than 30 days, as a safeguard for old cases.

It is recommended to run this type of notification with the Nightly job schedule, so it does not affect anything during work hours.

SELECT e.incident_id as incident_id,
       r.directory_id AS recipient_directory_id,
       'Case Filed and Discovery is not answered' as subject,
       'Please, review the case and update the Discovery event' as line01 
  FROM tb_event e
  JOIN tb_relationship r ON r.incident_id = e.incident_id AND r.relationship = 'ASA' AND r.current_rel = 'Y'
 WHERE e.event_type = 'DISCOVERY'
   AND e.event_action = '*'
   AND DATEDIFF(dd,ISNULL(e.dt_modified,e.dt_created),GETDATE()) < 30
   AND EXISTS (SELECT 1 
                 FROM tb_event e2 
                WHERE e2.incident_id = e.incident_id 
                  AND e2.event_type = 'FILED')
   AND EXISTS (SELECT 1 FROM tb_directory d JOIN cd_sec_user u ON u.sec_user = d.sec_user WHERE d.directory_id = r.directory_id AND d.status = 'A' AND u.inactive = 'N')

Notification that adds records such as Events, Requests, and Relationships to a case

These notifications will add an Event/Relationship/Request to a case, but it will not send a message/notification to any recipient.

The notifications that add a record require the CIP Portal service

Adding an Agency to the case if the officer is related to an Agency

This Notification will check for cases with an Officer and will then check the officer’s directory to see if there is an Agency that is related to the officer. If the Officer is related to an Agency, the Agency will be added to the Relationship tab of the case. If the officer’s directory is not related to an Agency, then nothing will happen and STAC will move onto the next case.

It is recommended to run this type of notification with the Nightly job schedule, so it does not affect anything during work hours.

select r.incident_id as incident_id,
r.related_to_dir_id as recipient_directory_id,
subject='Adding agency from officer record',
line01='New agency '+dbo.fmtNameById(1, r.related_to_dir_id)
from vius_relationship r 
where r.relationship='OFC'
and r.current_rel='Y'
and r.related_to_dir_id<>r.directory_id
and not exists (select 0 from vius_relationship age where age.incident_id=r.incident_id and age.relationship='AGE' and age.directory_id=r.related_to_dir_id)
and r.dt_created>=dateadd(dd,-360,getdate()) 

Data Quality Control Samples

There are a few ways the administrator can improve the data quality and user satisfaction of the application.

The queries below can be used to help the STAC Administrator control the quality of the data.

These queries will show the Admin the codes, and then will show them how many times they were used in the last 12 months and the last 24 months, the total amount of times the code was used, etc. The admin can quickly and easily review the codes and see which ones have not been used and go and inactivate the codes that are no longer needed.

Cases

The queries below can be used to pull the records from the associated code table and shows the amount of times the codes were used over the last 12 months, the last 24 months, and the total times used ever.

Case type usage

select case_type,  dt_created, dt_modified, 
recs =(Select count(*) from tb_incident e where e.case_type=c.case_type),
recslast12mts =(Select count(*) from tb_incident e where e.case_type=c.case_type and e.dt_created>=dateadd(yy,-1,getdate())),
recslast24mts =(Select count(*) from tb_incident e where e.case_type=c.case_type and e.dt_created>=dateadd(yy,-2,getdate()))
from cd_case_type c
where case_type<>'*'
order by recs desc

Case warning usage

select case_warning,  dt_created, dt_modified, 
recs =(Select count(*) from tb_incident e where e.case_warning=c.case_warning),
recslast12mts =(Select count(*) from tb_incident e where e.case_warning=c.case_warning and e.dt_created>=dateadd(yy,-1,getdate())),
recslast24mts =(Select count(*) from tb_incident e where e.case_warning=c.case_warning and e.dt_created>=dateadd(yy,-2,getdate()))
from cd_case_warning c
where case_warning<>'*'
order by recs desc

Database

The queries below can be used to find/pull records from the Database.

List of users currently logged into STAC

This query lists the users who have recently logged in or searched for a case. The last column displays the most recent action.

select c.sec_user, c.name, lh.dt_login, lh.dt_logoff , iv.dt_viewed as dt_last_viewed_case, case when dt_viewed  > dt_login then dt_viewed else dt_login end as dt_most_recent_activity
from cd_sec_user c
join (Select * 
           from st_login_history h 
         where h.dt_login>=convert(date,getdate()) 
         and h.login_history_id in  (select max(h2.login_history_id) from st_login_history h2 group by h2.user_name)) 
         as lh on lh.user_name=c.sec_user and lh.dt_logoff is null
left outer join (select max(v.dt_created) as dt_viewed, v.sec_user
                       from st_incident_viewed v 
                    where v.dt_created>=convert(date,getdate()) 
                    group by v.sec_user) iv on iv.sec_user=c.sec_user
where c.inactive='N'
order by case when dt_viewed  > dt_login then dt_viewed else dt_login end desc

This is how we can find where a certain code is used in STAC

declare @sql varchar(max)=''
declare @col varchar(max)='relationship'
declare @value varchar(max)='FASA'
select @sql=@sql+char(13)+'if exists (select * from '+t.name+' where '+ c.name + '='+dbo.q(@value)+') BEGIN
select '+dbo.q(t.name)+'
select * from '+t.name+' where '+ c.name + '='+dbo.q(@value)+ ' END'
from sys.tables t
join sys.columns c on c.object_id=t.object_id
where c.name=@col

exec (@sql)
--print @sql

Departments

The query below can be used to pull the records from the Departments code table and shows the amount of times the codes were used over the last 12 months, the last 24 months, and the total times used ever.

Department usage

select department,  dt_created, dt_modified,
recs =(Select count(*) from tb_incident e where e.department=c.department),
recslast12mts =(Select count(*) from tb_incident e where e.department=c.department and e.dt_created>=dateadd(yy,-1,getdate())),
recslast24mts =(Select count(*) from tb_incident e where e.department=c.department and e.dt_created>=dateadd(yy,-2,getdate()))
from cd_department c
where department<>'*'
order by recs desc

Directory

The queries below can be used to pull records from the Directory to help improve data quality.

Relationships that DO NOT match expected directory type

This query looks for relationships on a case that do not match the directory type.

For example: An ASA on the case should have a directory type of ASA (in most situations). There is a configuration table for relationships that can be programmed to allow only specific directory types to be added to the relationship type on the case. Circuits may have implemented this process or procedure more recently and need to locate the cases that have the relationship types not matched to the directory type.

select r.relationship, d.directory_type, count(*)
from tb_relationship r
join tb_directory d on d.directory_id=r.directory_id
join cd_relationship cr on cr.relationship=r.relationship and cr.directory_type<>d.directory_type
group by r.relationship, d.directory_type
order by count(*) desc

This is very similar to the query above. The query below is more detailed version and looks for relationships on a case that do not match the directory type and includes the Case and Directory information in the results.

select i.incident_no, r.relationship, r.relationship_date, d.directory_id, d.last_name, d.first_name, d.directory_type,  c.directory_type as [ExpectedDirectoryType]
from vi_incident_status i
join tb_relationship r on r.incident_id=i.incident_id
join tb_directory d on d.directory_id=r.directory_id
join cd_relationship c on c.relationship=r.relationship and c.directory_type<>d.directory_type

Directory not related to a case and not an alias

This query is good for directory clean up. This query will locate records that are directory type ‘OTH’ AND that have NOT been related to a case AND they also do NOT have an alias record. The administrators can then investigate and decide if they want to remove these records.

SELECT *
FROM tb_directory d1
WHERE directory_type='OTH'
AND NOT EXISTS (SELECT 1 FROM tb_relationship WHERE tb_relationship.directory_id = d1.directory_id) --Directory
AND NOT EXISTS (SELECT 1 FROM tb_directory d2 WHERE d2.alias_of_directory_id = d1.directory_id AND d2.alias_of_directory_id <> d1.directory_id)
AND d1.directory_id = d1.alias_of_directory_id
ORDER BY directory_id

Directory type Other with a Badge or Bar Number

This query is another good directory clean up. This query looks for a Directory type of ‘OTH’ that has a badge or bar number, this will allow agencies to properly update the directory type to attorney or law enforcement. Directories with badge and/or bar numbers should never be entered as Directory Type “Other”.

select *
from tb_directory 
where directory_type='OTH'
and (bar_no is not null or badge_no is not null)

Directory without SSN, DL, DOB, address

This query is great for looking for missing data, especially when it comes to victims and witnesses. This query will look for a specific directory type ‘OTH’ and find the records that are missing a SSN, DL, DOB and/or address. The circuit can then investigate and update the records.

select *
from tb_directory 
where directory_type='OTH'
and soc_sec_no is null
and drive_license is null
and date_of_birth is null
and not exists (Select 0 from tb_address x where x.directory_id=tb_directory.directory_id)
and not exists (Select 0 from tb_phone x where x.directory_id=tb_directory.directory_id)

This query is NOT recommended for directory types such as ASA, DAT, Judge, etc.

Disposition

The queries below can be used to pull the records from the associated code table and shows the amount of times the Events and Charge codes were used over the last 12 months, the last 24 months, and the total times used ever.

Disposition action usage

select disposition_action, dt_created, dt_modified, 
Events_recs =(Select count(*) from tb_event e where e.disposition_action=c.disposition_action ),
Events_recslast12mts =(Select count(*) from tb_event e where e.disposition_action=c.disposition_action  and e.dt_created>=dateadd(yy,-1,getdate())),
Events_recslast24mts =(Select count(*) from tb_event e where e.disposition_action=c.disposition_action and e.dt_created>=dateadd(yy,-2,getdate())),
Charges_recs =(Select count(*) from tb_charge e where e.disposition_action=c.disposition_action ),
Charges_recslast12mts =(Select count(*) from tb_charge e where e.disposition_action=c.disposition_action  and e.dt_created>=dateadd(yy,-1,getdate())),
Charges_recslast24mts =(Select count(*) from tb_charge e where e.disposition_action=c.disposition_action and e.dt_created>=dateadd(yy,-2,getdate()))
from cd_disposition_action_charge c
where disposition_action<>'*'
order by disposition_action desc

Disposition reason usage

select disposition_reason, dt_created, dt_modified,
Events_recs =(Select count(*) from tb_event e where e.disposition_reason=c.disposition_reason ),
Events_recslast12mts =(Select count(*) from tb_event e where e.disposition_reason=c.disposition_reason  and e.dt_created>=dateadd(yy,-1,getdate())),
Events_recslast24mts =(Select count(*) from tb_event e where e.disposition_reason=c.disposition_reason and e.dt_created>=dateadd(yy,-2,getdate())),
Charges_recs =(Select count(*) from tb_charge e where e.disposition_reason=c.disposition_reason ),
Charges_recslast12mts =(Select count(*) from tb_charge e where e.disposition_reason=c.disposition_reason  and e.dt_created>=dateadd(yy,-1,getdate())),
Charges_recslast24mts =(Select count(*) from tb_charge e where e.disposition_reason=c.disposition_reason and e.dt_created>=dateadd(yy,-2,getdate()))
from cd_disposition_reason_charge c
where disposition_reason<>'*'
order by disposition_reason desc

Events

The 2 queries below can be used to pull the records from the associated code tables and shows the amount of times the codes were used over the last 12 months, the last 24 months, and the total times used ever.

Event type usage

select event_type,  dt_created, dt_modified,
recs =(Select count(*) from tb_event e where e.event_Type=c.event_type ),
recslast12mts =(Select count(*) from tb_event e where e.event_Type=c.event_type and e.dt_created>=dateadd(yy,-1,getdate())),
recslast24mts =(Select count(*) from tb_event e where e.event_Type=c.event_type and e.dt_created>=dateadd(yy,-2,getdate()))
from cd_event_type c
where event_type<>'*'
order by recs desc

Event sub type usage

select event_type, event_sub_type,  dt_created, dt_modified,
recs =(Select count(*) from tb_event e where e.event_Type=c.event_type and e.event_sub_type=c.event_sub_type),
recslast12mts =(Select count(*) from tb_event e where e.event_Type=c.event_type and e.event_sub_type=c.event_sub_type and e.dt_created>=dateadd(yy,-1,getdate())),
recslast24mts =(Select count(*) from tb_event e where e.event_Type=c.event_type and e.event_sub_type=c.event_sub_type and e.dt_created>=dateadd(yy,-2,getdate()))
from cd_event_sub_type c
where event_type<>'*'
order by recs desc

List of all the Event Type and Sub Type combinations and how many times that code is used in STAC

This query also lists whether the record is Active or Inactive.

select event_type, event_sub_type, inactive,
records =(Select count() from tb_event e where e.event_Type=c.event_type and e.event_sub_type=c.event_sub_type),
last_used =(Select max(e.dt_created) from tb_event e where e.event_Type=c.event_type and e.event_sub_type=c.event_sub_type)
from cd_event_sub_type c
where event_type<>''
order by records desc ,event_type, event_sub_type

Similar to the Query above but this one only looks at the Event Type (this query is looking at Active and Inactive records)

select event_type,
records =(Select count() from tb_event e where e.event_Type=c.event_type ),
last_used =(Select max(e.dt_created) from tb_event e where e.event_Type=c.event_type)
from cd_event_type c
where event_type<>''
order by records desc ,event_type

Images

The 2 queries below can be used to pull the records from the associated code tables and shows the amount of times the codes were used over the last 12 months, the last 24 months, and the total times used ever.

Image type usage

select image_type,  dt_created, dt_modified,
recs =(Select count(*) from tb_image e where e.image_Type=c.image_type ),
recslast12mts =(Select count(*) from tb_image e where e.image_Type=c.image_type and e.dt_created>=dateadd(yy,-1,getdate())),
recslast24mts =(Select count(*) from tb_image e where e.image_Type=c.image_type and e.dt_created>=dateadd(yy,-2,getdate()))
from cd_image_type c
where image_type<>'*'
order by recs desc

Image sub type usage

select image_type, image_sub_type,  dt_created, dt_modified,
recs =(Select count(*) from tb_image e where e.image_Type=c.image_type and e.image_sub_type=c.image_sub_type),
recslast12mts =(Select count(*) from tb_image e where e.image_Type=c.image_type and e.image_sub_type=c.image_sub_type and e.dt_created>=dateadd(yy,-1,getdate())),
recslast24mts =(Select count(*) from tb_image e where e.image_Type=c.image_type and e.image_sub_type=c.image_sub_type and e.dt_created>=dateadd(yy,-2,getdate()))
from cd_image_sub_type c
where image_type<>'*'
order by recs desc

Notes

The query below can be used to pull the records from the Note Type code table and shows the amount of times the codes were used over the last 12 months, the last 24 months, and the total times used ever.

Note type usage

select note_type,  dt_created, dt_modified,
recs =(Select count(*) from tb_note e where e.note_type=c.note_type),
recslast12mts =(Select count(*) from tb_note e where e.note_type=c.note_type and e.dt_created>=dateadd(yy,-1,getdate())),
recslast24mts =(Select count(*) from tb_note e where e.note_type=c.note_type and e.dt_created>=dateadd(yy,-2,getdate()))
from cd_note_type c
where note_type<>'*'
order by recs desc

Relationship

The query below can be used to pull the records from the Relationships code table and shows the amount of times the codes were used over the last 12 months, the last 24 months, and the total times used ever.

Relationship type usage

select relationship,  dt_created, dt_modified,
recs =(Select count(*) from tb_relationship e where e.relationship=c.relationship),
recslast12mts =(Select count(*) from tb_relationship e where e.relationship=c.relationship and e.dt_created>=dateadd(yy,-1,getdate())),
recslast24mts =(Select count(*) from tb_relationship e where e.relationship=c.relationship and e.dt_created>=dateadd(yy,-2,getdate()))
from cd_relationship c
where relationship<>'*'
order by recs desc

Statutes

The query below can be used to pull the records from the Statutes code table and shows the amount of times the codes were used over the last 12 months, the last 24 months, and the total times used ever.

Statutes Usage

select sec_stat_no , dt_created, dt_modified,
recs =(Select count(*) from tb_charge e where e.sec_stat_no=c.sec_stat_no),
recslast12mts =(Select count(*) from tb_charge e where e.sec_stat_no=c.sec_stat_no and e.dt_created>=dateadd(yy,-1,getdate())),
recslast24mts =(Select count(*) from tb_charge e where e.sec_stat_no=c.sec_stat_no and e.dt_created>=dateadd(yy,-2,getdate()))
from cd_statute c
where sec_stat_no<>'*'
order by recs desc 

Summary Tab Query Samples

Arrest

Includes offense and arrest dates, Agency report number, Booking number, Agency, and Arresting officer

select 
(select top 1 dbo.fmtdate(e.event_date) from tb_event e where e.incident_id =i.incident_id and e.event_type='OFFENS' order by e.event_date ) as 'offense date',
(select top 1 dbo.fmtdate(e.event_date) + case when e.event_sub_type <>'*' then ' - '+ce.description else '' end from tb_event e join cd_Event_sub_type ce on ce.event_type=e.event_type and ce.event_sub_type=e.event_sub_type where e.incident_id =i.incident_id and e.event_type='arrest' order by e.event_date ) as 'arrest date',
i.first_age_rpt_no as 'AGE RPT#',
i.first_booking_no as 'BOOKING#',
(select top 1 r.name from vius_relationship r where r.incident_id=i.incident_id and r.relationship='AGE' order by r.relationship_date) as 'AGENCY',
(select top 1 r.name from vius_relationship r where r.incident_id=i.incident_id and r.relationship='AO' order by r.relationship_date) as 'ARRESTING OFF.'
from vi_incident_Status i 
where i.incident_id=  [INCIDENTID] 

Case

Includes Case number, UCN, Status, Division, and the Defendant’s name, DOB, race and gender

select  i.incident_no as Case#, UCN,   (i.incident_status + '/' + i.user_incident_status) as [Status],  
i.current_division as Division,  
i.clerk as Clerk#,  
m.name as Defendant, 
dbo.fmtdate(m.date_of_birth) as DOB,  
m.race,  m.sex, m.directory_id, i.incident_id
from vi_incident_status i 
join  vius_main_subject m on (i.incident_id=m.incident_id)    
where i.incident_id =  [INCIDENTID] 

Charges

Lists current charges

select  TOP 5 
c.count_no as 'Count#',   
c.description+' (FS '+s.statute_no+')' as 'Charge',   
case when c.charge_level='*' then '' else c.charge_level end+ case when c.degree='*' then '' else ' / '+c.degree end as 'Level/Degree', 
c.charge_type as 'Charge Type',
Victim=(select top 1 r.name from vius_relationship r join tb_charge_relationship cr on cr.relationship_id=r.relationship_id where cr.charge_id=c.charge_id and r.victim_flag='Y')
from vius_charge_current c   
join cd_statute s on s.sec_stat_no=c.sec_stat_no  
where c.incident_id=[INCIDENTID]   
order by c.count_no 

Below is just a more sophisticated version of the same select statement above that replaces the description of the 5th charge with the words ““PLEASE SEE THE CHARGE TAB FOR ALL CHARGES”

select top 5 
c.count_no as 'Count#',   
case when ROW_NUMBER() over (order by c.count_no)=5 then '**** PLEASE SEE THE CHARGE TAB FOR ALL CHARGES' else  c.description+' (FS '+s.statute_no+')' end as 'Charge',   
case when c.charge_level='*' then '' else c.charge_level end+ case when c.degree='*' then '' else ' / '+c.degree end as 'Level/Degree', 
c.charge_type as 'Charge Type',
Victim=(select top 1 r.name from vius_relationship r join tb_charge_relationship cr on cr.relationship_id=r.relationship_id where cr.charge_id=c.charge_id and r.victim_flag='Y')
from vius_charge_current c   
join cd_statute s on s.sec_stat_no=c.sec_stat_no  
where c.incident_id=[INCIDENTID]   
order by c.count_no 

Defendant’s other cases

SELECT i.incident_no , 
r.relationship ,  
i.incident_status +'/' +i.user_incident_status as status,
i.incident_id      
FROM  tb_relationship r  
JOIN vi_incident_status i ON i.incident_id =r.incident_id   
WHERE r.directory_id in        
    (SELECT d2.directory_id           
      FROM tb_directory d2
      WHERE d2.alias_of_directory_id IN
              ( SELECT d3.alias_of_directory_id                
                FROM tb_directory d3               
               WHERE d3.directory_id IN 
                    (SELECT directory_id                         
                        FROM  vius_main_subject m                         
                     WHERE m.incident_id= [INCIDENTID] ) ) )  
AND	r.incident_id <>[INCIDENTID] 

Defendant’s known aliases

SELECT d2.directory_name as Name,
d2.directory_id, 
d2.alias_of_directory_id, 
d2.directory_detail as Details
FROM vi_directory_detail d2        
WHERE d2.alias_of_directory_id in                  
    ( SELECT d3.alias_of_directory_id                
        FROM tb_directory d3               
        WHERE d3.directory_id in (SELECT v.directory_id FROM vius_main_subject v WHERE v.incident_id= [INCIDENTID]  ))
AND d2.directory_id NOT IN (SELECT v.directory_id FROM vius_main_subject v WHERE v.incident_id= [INCIDENTID]  )

Disposition and Sentence

These next 3 queries should be added together in a page to display disposition and sentence information

1. Charges and Dispositions

select   c.count_no as 'Count#',   
case when li_sec_stat_no='*' then  c.description+' (FS '+s.statute_no+')' else 	'LI: '+li_description+' (FS '+sl.statute_no+')' end as Charge,
case when c.charge_level='*' then '' else c.charge_level end+ 
case when c.degree='*' then '' else ' / '+c.degree end as 'Level/Degree',
Victim=(select top 1 r.name from vius_relationship r join tb_charge_relationship cr on cr.relationship_id=r.relationship_id where cr.charge_id=c.charge_id and r.victim_flag='Y'),
dbo.fmtdate(dt_disposition) as 'Disposition Date',
disposition=da.description+ case when c.disposition_reason<>'*' then ' - '+dr.description else '' end
from vius_charge_current c   
join cd_statute s on s.sec_stat_no=c.sec_stat_no  
join cd_disposition_action_charge da on da.disposition_action=c.disposition_action
join cd_disposition_reason_charge dr on dr.disposition_reason=c.disposition_reason
join cd_statute sl on sl.sec_stat_no=c.li_sec_stat_no
where c.incident_id=[INCIDENTID]  
and c.incident_id>0_
and c.disposition_action<>'*' 
order by c.count_no 

2. Sentence

select  t.description +' '+   
case when s.amount>0 then dbo.fmtcurrency(s.amount, case when s.amount_type in ('Us$') then 1 else 3 end) else '' end  +  
  case when s.amount_type not in ('*','Us$') then ' '+a.description else '' end + 
  case when s.adjudicated='*' then '' else ' (' + d.description+')' end as sentence ,
date=dbo.fmtdate(e.event_date)     
from tb_sentence s   
join cd_amount_type a on a.amount_type=s.amount_type  
join cd_sentence_type t on t.sentence_type=s.sentence_type  
join cd_adjudicated d on d.adjudicated=s.adjudicated
left  outer join tb_event e on e.event_id=s.event_id    
where s.incident_id = [INCIDENTID] 
and s.incident_id> 0 

3. Sentence related to charge

select   c.count_no as 'Count#',   
case when li_sec_stat_no='*' then  c.description+' (FS '+s.statute_no+')' else 'LI: '+li_description+' (FS '+sl.statute_no+')' end as Charge , 
disposition=da.description+ case when c.disposition_reason<>'*' then ' - '+dr.description else '' end ,
 t.description +' '+   
case when sen.amount>0 then dbo.fmtcurrency(sen.amount, case when sen.amount_type in ('Us$') then 1 else 3 end) else '' end  +  
  case when sen.amount_type not in ('*','Us$') then ' '+a.description else '' end + 
  case when sen.adjudicated='*' then '' else ' (' + d.description+')' end as sentence
from vius_charge_current c   
join cd_statute s on s.sec_stat_no=c.sec_stat_no  
join cd_disposition_action_charge da on da.disposition_action=c.disposition_action
join cd_disposition_reason_charge dr on dr.disposition_reason=c.disposition_reason
join cd_statute sl on sl.sec_stat_no=c.li_sec_stat_no
join tb_charge_sentence cs on cs.charge_id=c.charge_id
join tb_sentence sen on sen.sentence_id=cs.sentence_id
join cd_amount_type a on a.amount_type=sen.amount_type  
join cd_sentence_type t on t.sentence_type=sen.sentence_type  
join cd_adjudicated d on d.adjudicated=sen.adjudicated    
where c.incident_id=[INCIDENTID]   
and c.incident_id>0
order by c.count_no , sen.sentence_id

Emails from a case

This is a list of all emails generated from a case

select dbo.fmtdatetime(dt_created) as dt_Created, 
email_action_description,
email_status,
dbo.fmtdatetime(email_status_dt) as Statuts_date, 
recipient_name+' ('+recipient_email+')' as recipiend,
subject,
body
from vi_email_to 
where message_type='MAIL' 
and incident_id= [INCIDENTID]  
and incident_id>0 
order by dt_created desc

Next Court Dates

Lists all future hearings

select dbo.fmtdatetime(e.event_date)+' (in '+convert(varchar(3),DATEDIFF(DD,GETDATE(),e.event_date))+' days)' as Date,
e.event_type as type, 
ce.description as 'Sub Type' 
From tb_event e
join cd_event_sub_type ce on e.event_type=ce.event_type and ce.event_sub_type=e.event_sub_type
where e.event_type in ('hearing','Trial')
and e.event_date>=GETDATE()
and e.incident_id= [INCIDENTID] 
order by e.event_date

Other cases where parties of this case are also related

This is a list of any previous cases where a relationship in the current case is listed on both cases

select r.name, 
v.incident_id,  
v.incident_no as 'Case#',  
(v.incident_status + '/' + v.user_incident_status) as [Status], 
r.relationship, 
dbo.fmtdate(r.relationship_Date) as 'Related Date'
From vi_incident_status v
join vius_relationship r on r.incident_id=v.incident_id
where directory_id in (
select r2.directory_id  from 
vi_incident_status v2
join vius_relationship r2 on r2.incident_id=v2.incident_id
where r2.directory_type='OTH'
and r2.relationship<>'DEF'
and v2.incident_id= [INCIDENTID] )
and v.incident_id<> [INCIDENTID]  
order by r.name,v.incident_no

Parties

Includes the Judge, ASA, APD or DAT, and agencies

select TOP 10 
c.relationship as relationship, 
r.name, 
d.directory_detail as detail, 
d.directory_id  
from vius_relationship r  
join cd_relationship c on c.relationship=r.relationship  
join vi_directory_detail d on d.directory_id=r.directory_id  
where r.incident_id =  [INCIDENTID]   
and c.internal_relationship in ('JDG','ASA','APD','DAT','AGE','SEC') 
and r.current_rel='Y'
order by c.sort_order 

Victims and restitution info

List of Victims including restitution details due to each victim

select r.name, r.relationship , 
rd.rest_amount as [Amount], 
rd.rest_damages_reason as [Damages], 
rd.rest_funeral_reason as [Funeral], 
rd.rest_loss_income_reason as [Loss of Income], 
rd.rest_med_exp_reason as [Medical Exp.], 
rd.rest_other_reason as [Other Reason],
rd.rest_otptrehab_reason as [Rehab], 
rd.rest_tobedetermined_reason as [To be Determined]
from vius_relationship r
join cd_relationship cr on cr.relationship=r.relationship
left outer join tb_relationship_detail  rd on rd.relationship_id=r.relationship_id
where r.incident_id= [INCIDENTID] 
and  r.victim_flag='Y' 
order by cr.sort_order, r.name 

Write Your Own Variables Samples

List of Charges (Original and Filed)

 SELECT DISTINCT CONVERT(VARCHAR(3),COUNT_NO) + CHAR(9)+'ORIGINAL: '+ISNULL((SELECT TOP 1 O.DESCRIPTION FROM TB_CHARGE O WHERE O.INCIDENT_ID = C.INCIDENT_ID AND O.COUNT_NO = C.COUNT_NO AND O.CHARGE_TYPE = 'ORIGINAL' ORDER BY SEQUENCE_ID),'')+ 
 CHAR(13)+CHAR(9)+'FILED: ' +ISNULL((SELECT TOP 1 O.DESCRIPTION FROM TB_CHARGE O WHERE O.INCIDENT_ID = C.INCIDENT_ID AND O.COUNT_NO = C.COUNT_NO AND O.CHARGE_TYPE = 'FILED' ORDER BY SEQUENCE_ID),'DN')  
 FROM TB_CHARGE C  
 WHERE INCIDENT_ID = :incident_id
 AND C.SEQUENCE_ID=0 

List of current charges on case

SELECT CONVERT(VARCHAR(3),COUNT_NO) +') '+ DESCRIPTION+' (L:'+CHARGE_LEVEL+' - D:' +DEGREE+ ')'
FROM VIUS_CHARGE_CURRENT
WHERE INCIDENT_ID = :INCIDENTID
ORDER BY COUNT_NO

List of Discovery grouped by Date sent, Image Type, and Image Sub Type and how the files were sent

SELECT
isnull(dbo.fmtdate(max(disc.actionDate)),'')+ ' - '+
isnull(dbo.fmtSentenceCase(max(t.description)),'')+' - '+
isnull(dbo.fmtsentencecase(max(s.description)),'')+' - '+
' via '+max(disc.method)+' - '+convert(varchar(4), count(*))+ ' File(s) '
FROM
TB_IMAGE IMG
join cd_image_type t on t.image_type=img.image_type
join cd_image_sub_type s on s.image_type=img.image_type and s.image_sub_type=img.image_sub_type
join (select convert(date,imlog.dt_created) as actionDate, case when imlog.action='D' then 'Exchange' else 'CIP Portal' end as method, image_id from tb_image_log imlog where imlog.action in ('D','DVIASH') ) disc on disc.image_id=img.image_id
WHERE
IMG.INCIDENT_ID = :incidentid
and exists (select imlog.dt_created from tb_image_log imlog where imlog.image_id = img.image_id )
and img.discovery = 'Y'
group by
convert(date,disc.actionDate),
t.image_type,
s.image_sub_type,
disc.method
order by
convert(date,disc.actionDate) desc,
max(t.description),
max(s.description),
disc.method

This variable lists a previous defense attorney

SELECT TOP  1 FIRST_NAME+' '+LAST_NAME 
 FROM VIUS_RELATIONSHIP 
WHERE INCIDENT_ID = :INCIDENTID 
  AND RELATIONSHIP ='DAT' 
  AND CURRENT_REL = 'N' 
ORDER BY RELATIONSHIP_DATE DESC

This variable list all known cases for a given defendant

SELECT INCIDENT_NO 
FROM TB_INCIDENT I, TB_RELATIONSHIP R
WHERE I.INCIDENT_ID = R.INCIDENT_ID
AND R.DIRECTORY_ID IN (SELECT DIRECTORY_ID 
FROM TB_RELATIONSHIP  
WHERE INCIDENT_ID = :INCIDENTID 
AND RELATIONSHIP = 'DEF')
AND R.RELATIONSHIP = 'DEF' 
ORDER BY INCIDENT_NO DESC

This variable lists all relationships scheduled for a deposition replacing the names of the victims and minors with X’s

select dbo.fmtDateTime(dep.dt_scheduled)+' - '+ case when  r.relationship not in ('VIC','MVIC') then  r.name else replicate('X',20) +' ('+r.relationship+')'  end
from vi_incident_status i 
join vius_relationship r on r.incident_id=i.incident_id and r.current_rel='Y' and r.witness_flag <>'*'
join tb_deposition dep on dep.incident_id =i.incident_id and dep.dt_scheduled>=getdate() and dep.dt_canceled is null and r.directory_id=dep.directory_id
where i.incident_id=[incidentid]
order by dep.dt_scheduled

This variable list all the defendant’s Name, Sex, Race, DOB, and SSN for a single or multiple codefendants. It could be used on the Information

select dbo.fmtNameById(1,d.directory_id)+char(13)+
    'RACE/SEX: '+d.race+'/'+d.sex+char(13)+
    'DOB: '+dbo.fmtDate(d.date_of_birth)+char(13)+
    'SSN: '+dbo.fmtSSN(d.soc_sec_no)
FROM tb_incident i
JOIN vius_main_subject d on d.incident_id=i.incident_id
WHERE i.case_id IN (
    SELECT x.case_id 
    FROM tb_incident x 
    WHERE x.incident_id=:incidentId)
ORDER BY i.defendant_letter

This variable lists charges (count#, description, Statute#, degree, level, and maximum sentence) of a current set of charges

SELECT LTRIM(STR(C.COUNT_NO))+') '+CHAR(9)+C.DESCRIPTION+' '+STATUTE_NO+' ('+C.DEGREE+' '+S.FDLE_LEVEL+')'+CASE WHEN MAXIMUM_SENTENCE IS NULL THEN '' ELSE ' - '+MAXIMUM_SENTENCE END 
FROM VIUS_CHARGE_CURRENT C, CD_STATUTE S
WHERE C.SEC_STAT_NO = S.SEC_STAT_NO
AND C.INCIDENT_ID = :INCIDENTID
ORDER BY SEQUENCE_ID

This variable lists the first 5 charges on a case and it prints a disclaimer indicating there are more charges on file. In most documents, there is not a point of printing dozens of charges, especially if this is just a letter.

SELECT TOP 6 CASE WHEN count_no<6 THEN dbo.qint(count_no)+') '+description ELSE 'More Charges on File' END
FROM VIUS_CHARGE_CURRENT
WHERE incident_id=:incidentid
ORDER BY 1

This variable lists the name and current addresses in a Relationship Document

SELECT a.name+
CASE WHEN datediff(dd,  a.date_of_birth, getdate ()) < 18 THEN char(13) + 'C/O PARENT OR GUARDIAN'  else '' end +
CASE WHEN b.address_1 is null then '' else char(13)+ b.address_1 end  +
CASE WHEN b.address_2 is null then '' else char(13)+ b.address_2 end  +
CASE WHEN b.city is null then '' else char(13)  + b.city + ', ' end +
CASE WHEN b.state is null then '' else b.state + '  ' end +
CASE WHEN b.zip is null then '' else b.zip + char(13) + char(13) end
FROM vius_relationship a 
LEFT OUTER JOIN tb_address b on a.directory_id = b.directory_id 
WHERE B.main_address = 'Y'
and a.incident_id=:incidentid
and a.directory_id = :directoryID

The variable below also lists the name and current addresses in a Relationship Document.

Here we are using a function called dbo.fmtDate that converts date fields to a formated date. There another function called dbo.fmtDateTime that returns the date and time.

SELECT dbo.fmtDate(note_date) + ' - '+ CONVERT(VARCHAR(8000),TEXT) 
FROM TB_NOTE 
WHERE INCIDENT_ID = :INCIDENTID 
ORDER BY NOTE_DATE

This variable lists the previous address of a given directory id

SELECT TOP 1 address_1+' ,'+city+', '+state+' '+zip
FROM tb_address
WHERE directory_id :directoryid
AND main_address = 'N'
ORDER address_id DESC

To return the previous address of a defendant for example the following query could be used

SELECT TOP 1 address_1+' ,'+city+', '+state+' '+zip
FROM tb_address a
JOIN vius_relationship r on r.directory_id=a.directory_id
WHERE r.incident_id=:incidentId
AND a.main_address = 'N'
and r.relationship='DEF'
ORDER a.address_id DESC

This variable list witness name, type, phone number, and next leave

SELECT  witness_flag+ ' - '+ relationship  + ' - '+
case when title = '*' then '' else title+ ' ' end + 
case when first_name is null then '' else first_name+' ' end +
case when middle_name is null then '' else middle_name+' ' end +
case when last_name is null then '' else last_name end+
case when suffix is null  then '' else ' '+suffix end +
case when badge_no is null then '' else ', #'+badge_no end +
isnull((select top 1 ' - ('+area_code_1+') '+ left(phone_no_1,3)+'-'+right(phone_no_1,4) from tb_address a where a.directory_id in( r.directory_id, r.related_to_dir_id)  and main_address ='Y' order by address_id desc ),'')+
      isnull((select top 1 ' - '+leave_reason+' ' + convert(varchar(20),begin_date,101)+case when end_Date is null then '' else ' '+convert(varchar(20),end_Date,101) end +case when comment is null then '' else ' '+comment end +
      isnull((select top 1 ' - ' +convert(varchar(8000),text) from tb_directory_note a where a.directory_id = r.directory_id order by directory_note_id desc), '')
      From tb_directory_leave_reason a where a.directory_id = r.directory_id and begin_date > getdate() order by begin_date),'')
From vius_relationship r
where incident_id = :incidentId
and witness_flag <>'*'
order by witness_flag, relationship, last_name

This variable outputs the whole signature block using one variable instead of using name, Bar no, address, etc

SELECT TOP 1 DBO.fmtNameById(1,a.directory_id)+char(13)+
'ASSISTANT STATE ATTORNEY'+char(13)+
ISNULL('FLORIDA BAR NO:'+a.bar_no+char(13),'')+
ISNULL(ad.address_1+char(13),'')+
ISNULL(ad.address_2+char(13),'')+
ISNULL(ad.city+', FL,','')+ISNULL(ad.zip+char(13),'')+
ISNULL('PHONE:'+dbo.fmtPhoneNumber(p.area_code+p.phone_no),'')
FROM vius_asa a JOIN tb_address ad ON ad.directory_id=a.directory_id LEFT JOIN tb_phone p ON p.directory_id=a.directory_id and p.main_phone=‘Y’ WHERE a.incident_id=:incidentid

This variable returns the STAC URL. It can be handy to be used in emails generated from the request or assignment module

SELECT iis_url+'/pages/incident.aspx?searchby=CASEID&searchvalue='+dbo.qint(incident_id) 
FROM tb_incident,  
vi_sys_project 
WHERE incident_id=:incidentid

This variable will create a list of the charges ordered by Sequence and count number:

SELECT  
LTRIM(STR(C.COUNT_NO))+') '+CHAR(9)+C.DESCRIPTION+' '+STATUTE_NO+' ('+C.DEGREE+' '+S.FDLE_LEVEL+')'+CASE WHEN MAXIMUM_SENTENCE IS NULL THEN '' ELSE ' - '+MAXIMUM_SENTENCE END  
FROM TB_CHARGE C
JOIN CD_STATUTE S ON S.SEC_STAT_NO = C.SEC_STAT_NO
WHERE C.SEQUENCE_ID IN (SELECT MAX(C2.SEQUENCE_ID) FROM TB_CHARGE C2 WHERE C2.INCIDENT_ID = C.INCIDENT_ID) 
AND C.INCIDENT_ID =:INCIDENTID 
ORDER BY SEQUENCE_ID, COUNT_NO

Custom Report Samples

Document Usage

This report lists how many times a document was executed. This is a great report to help with data quality control and see which documents are being used.

select 
sortkey1=dc.description, 
sortkey1_Value=dc.description, 
sortkey1_caption='Document Category',
sortkey2=d.name, 
sortkey2_value=d.name, 
sortkey2_caption='Document', 
field1=d.name,
field1_caption='Document', 
field2=d.document_type,
field2_caption='Type', 
field3=(select count(0) from cd_document_variable dv where dv.document_id=d.document_id),
field3_caption='Variables',
field4=(select count(0) from tb_document_send ds where ds.document_id=d.document_id and ds.dt_created>=dateadd(yy,-1,getdate())),
field4_caption='Runs 1 year',
field5=(select count(0) from tb_document_send ds where ds.document_id=d.document_id and ds.dt_created>=dateadd(dd,-30,getdate())),
field5_caption='Runs 30 days'
from cd_document d
join cd_document_document_category ddc on ddc.document_id=d.document_id
join cd_document_category dc on dc.document_category=ddc.document_category
where dc.document_category<>'*'
and d.document_id>0
and dc.inactive='N'
and d.inactive='N'
and dc.description not like '%ALL%DOCUMENT%'

List of activity by users

This report will list how many Events, Notes, Images each user has added

select    sortkey1=s.sec_user,   
sortkey1_value=s.sec_user,   
sortkey1_caption='User',   
field1=s.sec_user,    
field1_caption='User',   
field2= (select count(*) from tb_incident x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date]) ,   
field2_caption='Cases'  ,   
field3= (select count(*) from tb_note x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date]) ,   
field3_caption='Notes' ,   
field4= (select count(*) from tb_image x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date]) ,   
field4_caption='Images' ,   
field5= (select count(*) from tb_document_send x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date]) ,   
field5_caption='Documents' ,   
field6= (select count(*) from tb_relationship x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date]) ,   
field6_caption='Relationships',   
field7= (select count(*) from tb_event x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date]) ,   
field7_caption='Events'  
from cd_sec_user s
where exists (select 0 from tb_Event x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date])
or exists (select 0 from tb_relationship x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date])
or exists (select 0 from tb_document_send x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date])
or exists (select 0 from tb_incident x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date])
or exists (select 0 from tb_image x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date])
or exists (select 0 from tb_note x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date])
order by s.sec_user

The admin will need to add the argument begin_date and end_date to the report by going to Add > Add argument to any report

List of Pending Image reviews by user

This report lists all the users that have images/files that are pending in the Image review and shows which files are pending and what cases they are related to.

 select  
 sortkey1=r.name,
 sortkey1_value=r.name,
 sortkey1_caption='Employee',
 sortkey2=i.incident_no,
 sortkey2_value=i.incident_no,
 sortkey2_caption='Case#',
 field1=i.incident_no,  
 field1_caption='Case #',
 field2=dbo.fmtdatetime(m.image_date) ,
 field2_caption='Image Dt Created',
 field2_sum_count='COUNT',
 field3=m.image_type,
 field3_caption='Image Type',
 field4=m.image_sub_type,
 field4_caption='Sub Type'
 from vi_incident_status i
 join vius_relationship r on r.current_rel='Y' and i.incident_id = r.incident_id
 join tb_image m on m.incident_id=i.incident_id
 join tb_image_log l on l.action_directory_id=r.directory_id and action='RVW' and action_dt_completed is null	

List of Pending Notifications by user

This report creates a list of the pending notification for each user that has any pending notifications.

select  
sortkey1=r.name,
sortkey1_value=r.name,
sortkey1_caption='Employee',
sortkey2=i.incident_no,
sortkey2_value=i.incident_no,
sortkey2_caption='Case#',
field1=i.incident_no,  
field1_caption='Case #',
field2=dbo.fmtdate(n.dt_created) +' ('+dbo.qint(datediff(dd, n.dt_created, getdate()))+' days)',
field2_caption='Dt Created',
field2_sum_count='COUNT',
field3=n.subject,
field3_caption='Subject',
field4=n.message,
field4_caption='Message'
from vi_incident_status i
join vius_relationship r on r.current_rel='Y' and i.incident_id = r.incident_id
join tb_notification n on n.incident_id=i.incident_id and n.recipient_directory_id=r.directory_id and n.dt_reviewed is null

Login attempt from non-users

This report creates a list of all the failed attempted login from users that are not recognized by STAC. This report will show a list of the username attempting to login, the date and time the attempt was made, and the Ip address of the attempted login location.

select 
sortkey1= s.user_name, 
sortkey1_value=  s.user_name, 
sortkey1_caption='User Name',
sortkey2= s.dt_created , 
sortkey2_value= s.dt_created , 
sortkey2_caption='Date',
field1=s.user_name ,
field1_caption='User Name',
field2='',
field2_caption='',
field3=dbo.fmtdateTime(s.dt_created),
field3_caption='Date',
field4=s.ip_address,
field4_caption='IP Address'
From st_login s 
where s.user_name not in (Select sec_user from cd_sec_user) 
and s.dt_created>dateadd(dd,-180,getdate())

Number of cases opened per County and Case Type and ASA

This report lists the number of cases opened currently per County, Case type, and ASA.

select    
sortkey1=i.county+space(1)+cc.description,   
sortkey1_caption='County',   
sortkey2=i.case_type,   
sortkey2_caption='Case Type',   
sortkey3=max(rel.name),   
sortkey3_caption='ASA name',   
field1=max(rel.name),    
field1_caption='ASA',   
field2= (select count(*) from vi_incident_status i2 join vius_relationship a2 on a2.incident_id=i2.incident_id and a2.current_rel='Y'  and a2.status='A' and i2.case_Type=i.case_Type and a2.directory_id=rel.directory_id and i2.user_incident_status = 'O' and i2.incident_status = 'O'),      
field2_caption='CASES',   
field2_sum_count='SUM',   
field2_align=1  
from vi_incident_status i   
join vius_relationship rel on rel.current_rel='Y' and i.incident_id = rel.incident_id and rel.status ='A'  and rel.relationship='ASA'  
join cd_county cc on cc.county=i.county   
where i.incident_status = 'O' and i.user_incident_status = 'O'   
group by i.county+space(1)+cc.description, i.case_Type, rel.directory_id

Number of cases opened per year, and average number of days opened

This report lists the number of cases opened per year, case type, and location. It also lists the average number of days a case is opened. This example lists cases created in the last 5 years.

select 
sortkey1=i.case_year,
sortkey2=ct.description,
sortkey3=cl.description,
field1=case_year, 
field1_caption='YEAR',
field2=ct.description, 
field2_caption='CASE TYPE',
Field3=cl.description, 
field3_caption='LOCATION',
field4=count(*), 
field4_caption='TOTAL CASES',
field4_align=1,
field5=avg(DATEDIFF(dd,op.event_date, dp.event_Date)) ,
field5_caption='AVERAGE OPEN DAYS',
field5_align=1
from vi_incident_status i
join tb_event op on op.incident_id=i.incident_id and op.event_type='OPEN'
join tB_event dp on dp.incident_id=i.incident_id and dp.event_Type='DISPO' and dp.event_id in (Select min(d2.event_id) from tb_event d2 where d2.incident_id=i.incident_id and d2.event_type='dispo')
join cd_Case_type ct on ct.case_type=i.case_type
join cd_location cl on cl.location=i.location
where i.incident_status='C'
and i.case_year>year(getdate())-5
group by case_year, ct.description, cl.description
order by case_year, ct.description, cl.description

Quantity of Cases per FDLE type

This report lists the number of cases opened per County and FDLE type. Administrators should confirm that their Case Type codes are correct for their statement.

select sortkey1= x.county, 
sortkey1_value= x.county, 
sortkey1_caption='County',
field1=x.county ,
field1_caption='county',
field2=sum(x.fdle_felony),
field2_caption='Qty FDLE Felony',
field3=sum(x.fdle_misdemeanor),
field3_caption='Qty FDLE Misdemeanor',
field4=count(*),
field4_caption='total'
from (select county=i.county + '-' + c.description,
event_date=dbo.qdatetime(dbo.fmtdate(e.event_date)),
fdle_felony=(case i.case_type when 'cf' then 1 else 0 end),
fdle_misdemeanor=(case i.case_type when 'cf' then 0 else 1 end)
from tb_incident i
join tb_event e
on (i.incident_id = e.incident_id)
join cd_county c
on (c.county = i.county)
where i.case_type in ('cf', 'mm', 'mo', 'co', 'tc')
and e.event_type = 'open'
and cast(e.event_date as date) >= [begin_date] 
and cast(e.event_date as date) < [end_date]) x
group by x.county
order by x.county

User Activity by Date

This report will show how to add arguments to a custom report. This report will list the user activity by the date specified and the date is the argument.

select    sortkey1=s.sec_user,   
sortkey1_value=s.sec_user,   
sortkey1_caption='User',   
field1=s.sec_user,    
field1_caption='User',   
field2= (select count(*) from tb_incident x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date]) ,   
field2_caption='Cases'  ,   
field3= (select count(*) from tb_note x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date]) ,   
field3_caption='Notes' ,   
field4= (select count(*) from tb_image x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date]) ,   
field4_caption='Images' ,   
field5= (select count(*) from tb_document_send x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date]) ,   
field5_caption='Documents' ,   
field6= (select count(*) from tb_relationship x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date]) ,   
field6_caption='Relationships',   
field7= (select count(*) from tb_event x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date]) ,   
field7_caption='Events'  
from cd_sec_user s
where exists (select 0 from tb_Event x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date])
or exists (select 0 from tb_relationship x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date])
or exists (select 0 from tb_document_send x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date])
or exists (select 0 from tb_incident x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date])
or exists (select 0 from tb_image x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date])
or exists (select 0 from tb_note x where x.user_created=s.sec_user and x.dt_created>=[begin_date] and x.dt_created< [end_date])
order by s.sec_user

Since this report has arguments there is an extra step for the administrator. By default, the arguments will be identified and added to the Argument as a STRING. However, the STRING is not the best argument and the administrator should edit the argument to better match what the user will need to search.

We can edit the arguments by, Select the report > Select the argument below > Edit > Edit Argument.

Users in Multiple Groups

This report creates a page for each user that is assigned to multiple security groups. This allows the administrator to make sure all the users are in the appropriate security group, specifically the users that are assigned to multiple groups.

select 
sortkey1=  u.sec_user , 
sortkey1_value=  u.sec_user, 
sortkey1_caption='User Name',
sortkey2= g.name , 
sortkey2_value= g.name , 
sortkey2_caption='Group',
field1=u.sec_user ,
field1_caption='User Name',
field2=g.name,
field2_caption='Group'
From cd_sec_user u
join cd_sec_group_user gu on gu.sec_user=u.sec_user
join cd_sec_group g on g.sec_group_id=gu.sec_group_id
where u.sec_user in (select x.sec_user from cd_sec_group_user x group by  x.sec_user having count(*)>1)