Hi
CR XI
SQL2008
Win 7
I am not sure how to start analysing my data for this query! I have a set of rent arrears monitoring data, grouped by tenancy number. The arrears are managed via a workflow of actions codes where for example if the arrears are X then action code 1 is used. When the arrears increase (or decrease) by say £100, the next action code is fired in the workflow. Each time this is fired either automatically or manually, the system generates a sequence number for the transaction. Each action also has created date However due to possibly bad coding, from time to time the sequence numbers go out of sync.
When a staff member comes to escalate the workflow and move the tenancy on to the next arrears action, it does this automatically based on the action date and sequence number i.e. it looks at the latest action date AND the sequence number. However if the action date is later than the previous action date but the sequence number is lower than the previous seq no., this breaks the workflow. This can be overcome manually by running the escalation but the problem is locating the accounts as they do not appear on the escalation reports.
I want to identify all the tenancies where there is a sequence number out of sync. The data is grouped by tenancy number, and sorted in action create date order as this is as it is used by the escalation process. I wrote a simple formula to identify the accounts
if {hrateaac.act_seq_no} < previous({hrateaac.act_seq_no}) then 0 else 1 with a view to adding a summary at group level to show only those sum >=1
Of course this will not allow me to then sum the result and sort on it, or carry out any further analysis. Additionally of course it does not reset so if the seq number of the previous tenancy follows my formula rule, then it is included in the results when within the tenancy group all the sequence numbers are correct.
Please help me identify a simple way to identify these accounts. I apologise if the explanation is too wordy...
Many thanks, Ade
CR XI
SQL2008
Win 7
I am not sure how to start analysing my data for this query! I have a set of rent arrears monitoring data, grouped by tenancy number. The arrears are managed via a workflow of actions codes where for example if the arrears are X then action code 1 is used. When the arrears increase (or decrease) by say £100, the next action code is fired in the workflow. Each time this is fired either automatically or manually, the system generates a sequence number for the transaction. Each action also has created date However due to possibly bad coding, from time to time the sequence numbers go out of sync.
When a staff member comes to escalate the workflow and move the tenancy on to the next arrears action, it does this automatically based on the action date and sequence number i.e. it looks at the latest action date AND the sequence number. However if the action date is later than the previous action date but the sequence number is lower than the previous seq no., this breaks the workflow. This can be overcome manually by running the escalation but the problem is locating the accounts as they do not appear on the escalation reports.
I want to identify all the tenancies where there is a sequence number out of sync. The data is grouped by tenancy number, and sorted in action create date order as this is as it is used by the escalation process. I wrote a simple formula to identify the accounts
if {hrateaac.act_seq_no} < previous({hrateaac.act_seq_no}) then 0 else 1 with a view to adding a summary at group level to show only those sum >=1
Of course this will not allow me to then sum the result and sort on it, or carry out any further analysis. Additionally of course it does not reset so if the seq number of the previous tenancy follows my formula rule, then it is included in the results when within the tenancy group all the sequence numbers are correct.
Please help me identify a simple way to identify these accounts. I apologise if the explanation is too wordy...
Many thanks, Ade