Page Comparison

Number of days since receivable created (NOTE: getdate() is today)

datediff(day,`receivable.receivable date`, getdate())

Permit Sequence

NOTE: using a sequence, one can then filter on this column where it = 1, which means you return the the most recent permit for each combination of Site and Permit Category.

row_number() over (partition by `regulated entity.number`, `permit.category` order by `permit.version` DESC)

Site Permit Seq excl Draft

NOTE: as above, but excluding any draft permits

row_number() over (partition by `regulated entity.number`, `permit.category` order by IIF(`permit.status` = 'Draft',1,0), `permit.version` DESC)

Type + Stat

CASE WHEN `PERMIT.Type` = 'Stormwater Construction' AND `PERMIT.status` IN ('Closed', 'Expired', 'Terminated') THEN 'SWC Inactive' WHEN `PERMIT.Type` = 'Stormwater Industrial' AND `PERMIT.Status` IN ('Closed', 'Expired', 'Terminated') THEN 'SWInd Inactive' WHEN `PERMIT.Type` = 'Ready Mix' THEN 'WW ReadyMix' WHEN `PERMIT.Category` = 'BOW Individual Wastewater Permit' AND `PERMIT.Type` <> 'Industrial Pretreatment' THEN 'WW CMIF' ELSE '' END

Permit process speed

DATEDIFF(d,`submission.received date`,`permit.issue date`)

Inspection Priority

case when `entity.county` IN ('Rice', 'Sedgwick') then 'High'
when `entity.county` = 'lyon' and `permit.effective date` < getdate() - 365 THEN 'Medium'
ELSE 'Low'
END

Days in effect

iif(`permit.status` = 'in effect', datediff(day,`permit.effective date`, getdate()), null)

Permit number

CONCAT(LEFT(`permit.permit number`, 3), 'DLB')

Days from submit until permit

DATEDIFF(d,`submission.received date`,`permit.issue date`)

Tank Seq

row_number() over (partition by `entity.number`, `Tanks.BER Tank Number` order by `permit.effective date` desc)

Eval Seq

NOTE: When column = 1, returns the most recent evaluation for each permit.

row_number() OVER (PARTITION BY `permit.permit number` ORDER BY `evaluation.start date` DESC)

Receivable + 30 days

dateadd(d,30,`receivable.receivable date`)

Construction Permit?

IIF(`permit.category` like '%construction%', 'Yes', 'No')

Paid too early?

DATEDIFF(d,`payable.received date`, `payment.payment date`)