Back to Home

Clean Section Field Counts

Overview

When new checklists/sections/fields are added/updated/removed, we need to update the counts in the p11_Master_Checklist and p11_Sectionlist tables so the right numbers show in the application.

Steps

1. Run Script 1 on production to check that the correct rows get updated
2. Add Script 2 as a SAFE script to be run

Script 1

; with sectiondata as (
select checklistno, count(1) as SectionCount from smart.p11_SectionList where CurrentStatus = 1 and deleted = 0 group by checklistno
)
select * from smart.p11_Master_Checklist a
inner join sectiondata b on a.checklistno = b.checklistno
where a.SectionCount != b.SectionCount or a.SectionCount is null

; with fielddata as (
select sectionno, count(1) as fieldCount from smart.p11_fieldList where CurrentStatus = 1 and deleted = 0 group by sectionno
)
select * from smart.p11_SectionList a
inner join fielddata b on a.sectionno = b.sectionno
where a.fieldCount != b.fieldCount or a.FieldCount is null

; with sectiondata as (
select checklistno, SUM(FieldCount) as FieldCount from smart.p11_SectionList where CurrentStatus = 1 and deleted = 0 group by checklistno
)
select * from smart.p11_Master_Checklist a
inner join sectiondata b on a.checklistno = b.checklistno
where b.FieldCount != a.FieldCount or a.FieldCount is null

Script 2

; with sectiondata as (
select checklistno, count(1) as SectionCount from smart.p11_SectionList where CurrentStatus = 1 and deleted = 0 group by checklistno
)
update a set a.SectionCount = b.SectionCount from smart.p11_Master_Checklist a
inner join sectiondata b on a.checklistno = b.checklistno
where a.SectionCount != b.SectionCount or a.SectionCount is null

; with fielddata as (
select sectionno, count(1) as fieldCount from smart.p11_fieldList where CurrentStatus = 1 and deleted = 0 group by sectionno
)
update a set a.fieldCount = b.fieldCount from smart.p11_SectionList a
inner join fielddata b on a.sectionno = b.sectionno
where a.fieldCount != b.fieldCount or a.FieldCount is null

; with sectiondata as (
select checklistno, SUM(FieldCount) as FieldCount from smart.p11_SectionList where CurrentStatus = 1 and deleted = 0 group by checklistno
)
update a set a.fieldCount = b.fieldCount from smart.p11_Master_Checklist a
inner join sectiondata b on a.checklistno = b.checklistno
where b.FieldCount != a.FieldCount or a.FieldCount is null