Data in Slate is stored and organized into tables, and all of these tables together comprise a Slate database. These tables are all connected, making Slate a Relational Database.
When viewing a Person record in Slate, an additional tab appears when that Person has an Application. This Application record is represented as a row on the Application table. A Person may have multiple Applications, each Application existing as an individual row in the Application table.
One-to-Many
How does Slate know these two rows in the Application table belong to the same individual (i.e., the same row in the Person table)? In a Slate database, there is a one-to-many relationship between the Person table and the Application table; that is, one instance on the Person table could be related to more than one instance on the Application table, but one instance on the Application table can be related to only one instance on the Person table.
One row on the Person table (or one Person Record) can be related to more than one row on the Application table (or more than one Application Records).
Queries and Reports
The Slate Query Builder enables access for viewing data in the underlying tables and functionality for extracting and manipulating data. Currently, the Query Builder provides access to table data through a standard list of Query populations or bases. Many of these query populations present table data in a predetermined way; for example, the Prospects query population automatically excludes person records with an Opt-Out tag.
Calculations
Slate uses table triggers to calculate and store a 'rank' value on rows in tables that routinely need to be joined in to a query where only a single record is desired. For example, you may wish to view the information about each person's highest GRE score. Table rankings are used to efficiently determine the highest score, without needing to compute this each time the data is accessed. Table ranks are re-calculated for the entire record any time any of the record's objects on that table are modified.
Some tables have multiple 'rank' columns, and the behavior associated with each rank will vary based upon what we have observed to be the most commonly-desired sorts.
The ranking for all tables is performed per record, so each record is eligible to have exactly one rank 1 item, exactly one rank 2 item, etc. There are certain tables that rank within specific item types on the record, and this behavior is enumerated for the applicable tables below.
For all tables, the order of the rank criteria matters. Ranking criteria after the first item are used only in the case of a tie. For example, a hypothetical table might have this ranking order:
- Priority (high, then normal, then low)
- Updated Date (most recent to least recent)
- Created Date (most recent to least recent)
- ID (ascending)
If a record has three items where two are High Priority and the third is Normal Priority, then using the ranking criteria above, the Normal Priority item would immediately be ranked #3 since the other two items both have a higher priority. The two High Priority items would then have their Updated Dates compared. If the Updated Dates are not the same, then the item that was updated most recently would be ranked #1, and the other would be #2. If the Updated Dates are the same, then the Created Dates would be compared, etc.
You will often see that the last order by column is simply [id]. While this may seem to be 'random', it will only ever be 'random' if all preceding sorts contain exactly the same value, and it will only ever be 'random' within a group of records. Without this column, the sort would be truly random and might result in the changing of ranking upon any change to the record, resulting in an unstable behavior.
Below, we have included the plain English explanations along with the SQL that generates each rank.
Addresses have two rank columns:
- rank
- rank_overall
Rank determines the address rank based on the address type. A record can have both a rank 1 mailing address and a rank 1 permanent address. The ranking order is as follows:
- Priority (high, then normal, then low)
- Street has a value (value exists, then no value exists)
- Updated Date (Most recent to least recent)
- Created Date (Most recent to least recent)
- ID (ascending order)
Note: Addresses created via an application submission are stored with a high priority.
Rank overall, also known as Active Address, determines the overall address rank, regardless of the address type. A record can have only one address assigned a rank overall of 1. The ranking order is as follows:
- Highest priority (High, Normal, or Low)
- If addresses have the same priority, then whichever has a street address value is ranked higher
- If addresses have the same priority and have a street address value, the address that is a mailing address will be ranked higher.
- If addresses have the same priority and have a street address value and both are mailing addresses or both are not mailing addresses, then the most recently updated address is ranked higher
- If addresses have the same priority, have a street address value and are both mailing addresses or both are not mailing addresses and have the same updated date/time then the most recently created address will be ranked higher
- If all of these items are the same, then the Active Address will rank by the address ID number (whichever is first alphabetically)
Ranking Exclusions:
- Addresses with an inactive priority are not ranked.
- Addresses with an effective date ('Valid From Date') in the future are not ranked.
- Addresses with an expires date ('Valid Until Date') in the past are not ranked.
The SQL code used to perform the ranking is below:
select row_number() over (partition by [record], [type] order by [priority] desc, (case when ([street] is null) then 1 else 0 end), [updated] desc, [created] desc, [id]) as [rank],
row_number() over (partition by [record] order by [priority] desc, (case when ([street] is null) then 1 else 0 end), (case when ([type] is null) then 0 else 1 end), [updated] desc, [created] desc, [id]) as [rank_overall]
from [address] with (index([record, type, rank]))
where ([quality] < 3)
and (isnull([priority], 0) > -2)
and (coalesce([expires], getdate()) >= getdate())
and (coalesce([effective], getdate()) <= getdate())Applications have two rank columns:
- rank
- rank_submitted
Rank determines the application rank for applications on the record, regardless of the submission status. The ranking order is as follows:
- Priority (high, then normal, then low)
- Submitted Date if it exists, or Created Date for applications that are not submitted (most recent to least recent)
Rank submitted determines a rank based solely on submission date. Only submitted applications will receive this rank. The ranking order is as follows:
- Submitted Date (most recent to least recent)
Ranking Exclusions:
- Applications with an inactive priority are not ranked at all.
- Applications in a round configured as Protected from Rank are not ranked at all.
- Applications that have not been submitted receive a rank, but not a rank_submitted.
The SQL code used to perform the ranking is below:
(case when (r.[protected] = 0) and (a.[priority] > -2) then row_number() over (partition by a.[person], r.[protected] order by a.[priority] desc, coalesce(a.[submitted], a.[created]) desc) end) as [rank],
(case when (r.[protected] = 0) and (a.[priority] > -2) and (a.[submitted] is not null) then row_number() over (partition by a.[person], r.[protected], (case when (a.[submitted] is not null) then 1 end) order by a.[submitted] desc) end) as [rank_submitted]
from [application] a
inner join [lookup.round] r on (r.[id] = a.[round])Application References have three rank columns:
- rank
- rank_type
- rank_submitted
Rank determines the application reference rank for application references on the record, regardless of the submission status or type. The ranking order is as follows:
- Last Name (alphabetical order)
- First Name (alphabetical order)
- ID (ascending)
Rank type determines the application reference rank within each application reference type. A record can have multiple references where rank type is 1, but each of those will have different types. For example, if an application has three references: two teacher recommendations and a peer recommendation, the peer recommendation will be assigned 1 for the rank type. One of the teacher recommendations will be assigned 1 for the rank type, and the other will be assigned 2 for the rank type. The ranking order is as follows:
- Last Name (alphabetical order)
- First Name (alphabetical order)
- ID (ascending)
Rank submitted determines the application reference rank for submitted references, regardless of the type. The ranking order is as follows:
- Submitted Date (most recent first)
- Last Name (alphabetical order)
- First Name (alphabetical order)
- ID (ascending)
Ranking Exclusions:
- Application references that have been excluded are not ranked at all.
- Application references that have not been submitted receive a rank and a rank_type, but not a rank_submitted.
The SQL code used to perform the ranking is below:
select row_number() over (partition by [application] order by [last], [first], [id]) as [rank],
row_number() over (partition by [application], [type] order by [last], [first], [id]) as [rank_type],
(case when ([submitted] is not null) then row_number() over (partition by [application], (case when ([submitted] is null) then 0 else 1 end) order by [submitted], [last], [first], [id]) end) as [rank_submitted]
from [application.reference]
where ([exclude] = 0)Courses have two rank columns:
- rank
- rank_overall
Rank determines the course rank for each school record. If a person has multiple school records, then each school is eligible to have a rank 1 course. The ranking order is as follows:
- Semester (ascending)
- Grade Level (prompt order) - If the course has no Grade Level, it is treated as having the order 99.
- Term (prompt order) - If the course has no Term, it is treated as having the order 99.
- Type has a value (value exists, then no value exists)
- ID (ascending)
Rank overall determines the course rank regardless of the school it is associated with. A person record will only have one course with the rank overall set to 1. The ranking order is as follows:
- Semester (ascending)
- Grade Level (prompt order) - If the course has no Grade Level, it is treated as having the order 99.
- Term (prompt order) - If the course has no Term, it is treated as having the order 99.
- Type has a value (value exists, then no value exists)
- ID (ascending)
Ranking Exclusions:
- None - All courses are ranked.
The SQL code used to perform the ranking is below:
select row_number() over (partition by c.[record], c.[school] order by c.[semester], (case when (c.[grade_level] is not null) then p.[order] else 99 end), (case when (c.[term] is not null) then pt.[order] else 99 end), (case when (c.[type] is null) then 1 else 0 end), c.[id]) as [rank],
row_number() over (partition by c.[record] order by c.[semester], (case when (c.[grade_level] is not null) then p.[order] else 99 end), (case when (c.[term] is not null) then pt.[order] else 99 end), (case when (c.[type] is null) then 1 else 0 end), c.[id]) as [rank_overall]
from [course] c
left outer join [lookup.prompt] p on (p.[id] = c.[grade_level])
left outer join [lookup.prompt] pt on (pt.[id] = c.[term])Decisions have four rank columns:
- rank
- rank_reverse
- rank_confirmed
- rank_released
Rank uses the confirmed or created dates to order the decisions from most recent to least recent. The ranking order is as follows:
- Confirmed Date if it exists, or Created Date for decisions that are not confirmed (most recent to least recent)
- ID (ascending)
Rank reverse uses the same data to rank the decisions, but the order is reversed, giving preference to the oldest (first) decision. The ranking order is as follows:
- Confirmed Date if it exists, or Created Date for decisions that are not confirmed (oldest/least recent to newest/most recent)
- ID (ascending)
Rank confirmed ranks only confirmed decisions. The ranking order is as follows:
- Confirmed Date (most recent to least recent)
- ID (ascending)
Rank released ranks only released decisions. The ranking order is as follows:
- Released Date (most recent to least recent)
- ID (ascending)
Ranking Exclusions:
- Decisions with the type Correspondence/Letter are not ranked at all.
- Only decisions with a confirmed date receive a rank confirmed.
- Only decisions with a released date receive a rank released.
The SQL code used to perform the ranking is below:
select (case when (l.[innert] = 0) then row_number() over (partition by d.[application], l.[innert] order by coalesce(d.[confirmed], d.[created]) desc, d.[id]) end) as [rank],
(case when (l.[innert] = 0) then row_number() over (partition by d.[application], l.[innert] order by coalesce(d.[confirmed], d.[created]), d.[id]) end) as [rank_reverse],
(case when (l.[innert] = 0) and (d.[confirmed] is not null) then row_number() over (partition by d.[application], l.[innert], (case when (d.[confirmed] is null) then 0 else 1 end) order by d.[confirmed] desc, d.[id]) end) as [rank_confirmed],
(case when (l.[innert] = 0) and (d.[released] is not null) then row_number() over (partition by d.[application], l.[innert], (case when (d.[released] is null) then 0 else 1 end) order by d.[released] desc, d.[id]) end) as [rank_released]
from [decision] d
inner join [lookup.decision] l on (l.[id] = d.[code])Devices have one rank column:
- rank
Rank is determined based on the device type. A record can have multiple rank 1 devices, but just one per device type. For example, if a person has two email addresses and a primary phone number, one email address will be assigned rank 1, one primary phone number will be assigned rank 1, and the other email address will be assigned rank 2. The ranking order is as follows:
- Priority (high, then normal, then low)
- Updated Date (most recent to least recent)
- Created Date (most recent to least recent)
- ID (ascending)
Ranking Exclusions:
- Devices with an inactive priority are not ranked.
The SQL code used to perform the ranking is below:
select (case when ([priority] != -2) then row_number() over (partition by [record], [type], (case when ([priority] = -2) then 0 else 1 end) order by [priority] desc, [updated] desc, [created] desc, [id]) end) as [rank]
from [device]Form responses have four rank columns:
- rank_form
- rank_form_reverse
- rank_reader
- rank_reader_reverse
Rank form is assigned based on the specific form. A record can have multiple form responses with a rank form assignment of 1, but just one per form. The form response will only have a rank form assignment greater than 1 if the person submitted a registration for the same form multiple times. The ranking order is as follows:
- Submitted Date (most recent to least recent)
- ID (ascending)
Like rank form, rank form reverse is assigned based on the specific form, and a record can have multiple form responses with a rank form reverse assignment of 1, but just one per form. This rank reverses the order, where the first submitted form response is assigned 1. The ranking order is as follows:
- Submitted Date (oldest/least recent to newest/most recent)
- ID (ascending)
Rank reader is assigned to form responses for forms with the scope Reader. This rank is evaluated among all reader form submissions on the application, so an application will only have one reader form that is assigned 1 as the rank reader. The first reader form submitted for an application will be assigned 1. The ranking order is as follows:
- Submitted Date (oldest/least recent to newest/most recent)
- ID
Rank reader reverse is assigned to form responses for forms with the scope Reader. This rank is evaluated among all reader form submissions on the application, so an application will only have one reader form that is assigned 1 as the rank reader reverse. The most recent reader form submitted for an application will be assigned 1. The ranking order is as follows:
- Submitted Date (most recent to least recent)
- ID
Ranking Exclusions:
- Form responses that have not been submitted are not ranked at all.
- Only form responses for forms with the scope Reader are assigned a rank reader and a rank reader reverse.
The SQL code used to perform the ranking is below:
select row_number() over (partition by r.[record] order by r.[submitted], r.[id]) as [rank_reader],
row_number() over (partition by r.[record] order by r.[submitted] desc, r.[id] desc) as [rank_reader_reverse]
from [form.response] r
inner join [form] f on (f.[id] = r.[form])
where (f.[scope] = 'reader') and (r.[active] = 1)
select row_number() over (partition by [record], [form] order by [submitted], [id]) as [rank_form],
row_number() over (partition by [record], [form] order by [submitted] desc, [id] desc) as [rank_form_reverse]
from [form.response]
where ([active] = 1)Interests have one rank column:
- rank
The ranking order is as follows:
- Order of Importance (ascending)
- Participation Date: To (most recent to least recent). If no To Date is specified, the current date is used.
- ID (ascending)
Ranking Exclusions:
- None - all interests are ranked.
The SQL code used to perform the ranking is below:
select row_number() over (partition by [record] order by [order], coalesce([to], getdate()) desc, [id]) as [rank]
from [interest]Jobs have one rank column:
- rank
The ranking order is as follows:
- Order of Importance (ascending) - only if all Jobs have a defined Order of Importance.
- Employment Date: To (most recent to least recent). If no To Date is specified, the current date is used.
- ID (ascending)
Ranking Exclusions:
- None - all jobs are ranked.
The SQL code used to perform the ranking is below:
select row_number() over (partition by [record] order by [order], coalesce([to], getdate()) desc, [id]) as [rank]
from [job]Relations have one rank column:
- rank_category
Rank category is determined within each relation category. A record can have multiple rank category 1 relations, but just one per relation type category. Categories are configured on the relationship_type prompt. For example, if a person has a mother and a father, and both of these relationship types have the category parent, then one will be assigned 1 and the other will be assigned 2. If this person has one other relation that is a sister, and the category for sister is configured as sibling, then that relation record will be assigned 1. The ranking order is as follows:
- Relationship Type Prompt Order (ascending)
- Relationship Type Value (alphabetical)
- ID (ascending)
Note: Relations that do not have a category will all be ranked together. Using the example above, if categories were not assigned to the prompts, then the mother, father, and sister would be assigned rank categories 1, 2, and 3 based on the ranking order above, since they all share the same (blank) category.
Ranking Exclusions:
- None - all relations are ranked.
The SQL code used to perform the ranking is below:
select r.[id], row_number() over (partition by r.[record], pt.[category] order by pt.[order], pt.[value], r.[id]) as [rank_category]
from [relation] r
left outer join [lookup.prompt] pt on (pt.[id] = r.[type])Schools have two rank columns:
- rank
- rank_overall
Rank is determined based on the school's level of study. A record can have multiple rank 1 schools, but just one per level of study. For example, if a person has two high schools and an undergraduate school, one high school will be assigned rank 1, one undergraduate school will be assigned rank 1, and the other high school will be assigned rank 2. The ranking order is as follows:
- Priority (high, then normal, then low)
- Attended Date: To if it exists, otherwise Attended Date: From (most recent to least recent)
- Created Date (most recent to least recent)
- ID (ascending)
Note: School records with no level of study are ranked together, since they all have the same (blank) level of study.
Rank overall is determined across all schools, regardless of the level of study. A record can only have one school that is assigned rank overall 1. The ranking order is as follows:
- Priority (high, then normal, then low)
- Attended Date: To if it exists, otherwise Attended Date: From (most recent to least recent)
- Created Date (most recent to least recent)
- ID (ascending)
Ranking Exclusions:
- Schools with an inactive priority are not ranked.
The SQL code used to perform the ranking is below:
select row_number() over (partition by s.[record], s.[type] order by s.[priority] desc, isnull(s.[to], s.[from]) desc, s.[created] desc, s.[id]) as [rank],
row_number() over (partition by s.[record] order by s.[priority] desc, isnull(s.[to], s.[from]) desc, s.[created] desc, s.[id]) as [rank_overall]
from [school] s
where (isnull(s.[priority], 0) > -2)School reports have one rank column:
- rank
The ranking order is as follows:
- Last Name (alphabetical order)
- First Name (alphabetical order)
- ID (ascending)
Ranking Exclusions:
- None - all school reports are ranked.
The SQL code used to perform the ranking is below:
select row_number() over (partition by [application] order by [last], [first], [id]) as [rank]
from [school.report]Sports have one rank column:
- rank
Column Description
The ranking order is as follows:
- Priority (high, then normal, then low)
- Rating has a value (value exists, then no value exists)
- Rating Prompt Order (ascending)
- Updated Date (most recent to least recent)
- Created Date (most recent to least recent)
- ID (ascending)
Ranking Exclusions:
- Sports with an inactive priority are not ranked.
The SQL code used to perform the ranking is below:
select row_number() over (partition by sp.[record] order by [priority] desc, (case when (sp.[rating] is null) then 1 else 0 end), r.[order], sp.[updated] desc, sp.[created] desc, sp.[id]) as [rank]
from [sport] sp
left outer join [lookup.prompt] r on (r.[id] = sp.[rating])
where (sp.[priority] > -2)Tests have 57 rank columns, but there are three basic types:
- rank
- rank_confirmed
- rank_overall
For all test score ranks, rank is determined among test scores with the same test type. There are also subtype ranks, which further divide the scores based on the test's subtype. For example, if the person has two AP scores: one AP Biology score and one AP Chemistry score, the rank would assign just one of those scores 1 and the other would be assigned 2. Since Chemistry and Biology are two different subtypes of the AP test type, both of these would be assigned 1 as the rank_subtype.
The rank calculation considers both verified and self-reported test scores; however, it prioritizes verified scores during the calculation. There are 19 different ranks that use this type of ranking. When referring to score below, the specific score that is used depends on which rank is being calculated. The rank and rank_subtype ranks look at the total column. There are 17 subscore columns, which are also ranked, such as rank_score1 and rank_score2. For these ranks, the specified subscore is used.
The ranking order for rank and rank_subtype is as follows:
- Test Status (verified first, then self-reported)
- Score (highest to lowest)
- Sum of score 1, score 2, score 3, and score 4 (highest to lowest)
- Test Date (most recent to least recent)
- ID (ascending)
The ranking order for rank_score[1-17] is as follows:
- Test Status (verified first, then self-reported)
- Score (highest to lowest)
- Test Date (most recent to least recent)
- ID (ascending)
The rank confirmed calculation considers only verified test scores. There are 19 different ranks that use this type of ranking. When referring to score below, the specific score that is used depends on which rank is being calculated. The rank_confirmed and rank_confirmed_subtype ranks look at the total column. There are 17 subscore columns, which are also ranked, such as rank_confirmed_score1 and rank_confirmed_score2. For these ranks, the specified subscore is used.
The ranking order for rank_confirmed and rank_confirmed_subtype is as follows:
- Score (highest to lowest)
- Sum of score 1, score 2, score 3, and score 4 (highest to lowest)
- Test Date (most recent to least recent)
- ID (ascending)
The ranking order for rank_confirmed_score[1-17] is as follows:
- Score (highest to lowest)
- Test Date (most recent to least recent)
- ID (ascending)
The rank overall calculation considers both verified and self-reported test scores. Unlike Rank, described above, rank overall does not give preference to verified test scores. There are 19 different ranks that use this type of ranking. When referring to score below, the specific score that is used depends on which rank is being calculated. The rank_overall column looks at the total column. There are 17 subscore columns, which are also ranked, such as rank_overall_score1 and rank_overall_score2. For these ranks, the specified subscore is used.
The ranking order for rank_overall and rank_overall_subtype is as follows:
- Score (highest to lowest)
- Sum of score 1, score 2, score 3, and score 4 (highest to lowest)
- Test Date (most recent to least recent)
- ID (ascending)
The ranking order for rank_overall_score[1-17] is as follows:
- Score (highest to lowest)
- Test Date (most recent to least recent)
- ID (ascending)
Ranking Exclusions:
- Cancelled test scores are not ranked.
- Optional test scores are not ranked.
- Test scores with the status Discrepancy are not ranked.
- Only test scores that are verified are assigned rank_confirmed values.
- Only test scores associated with test types that have subtypes are assigned rank_subtype, rank_overall_subtype, and rank_confirmed_subtype values.
The SQL code used to perform the ranking is below:
select row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [total] desc, [score1] + coalesce([score2], 0) + coalesce([score3], 0) + coalesce([score4], 0) desc, [date] desc, [id]) as [rank],
(case when ([score1] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score1] desc, [date] desc, [id]) end) as [rank_score1],
(case when ([score2] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score2] desc, [date] desc, [id]) end) as [rank_score2],
(case when ([score3] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score3] desc, [date] desc, [id]) end) as [rank_score3],
(case when ([score4] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score4] desc, [date] desc, [id]) end) as [rank_score4],
(case when ([score5] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score5] desc, [date] desc, [id]) end) as [rank_score5],
(case when ([score6] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score6] desc, [date] desc, [id]) end) as [rank_score6],
(case when ([score7] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score7] desc, [date] desc, [id]) end) as [rank_score7],
(case when ([score8] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score8] desc, [date] desc, [id]) end) as [rank_score8],
(case when ([score9] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score9] desc, [date] desc, [id]) end) as [rank_score9],
(case when ([score10] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score10] desc, [date] desc, [id]) end) as [rank_score10],
(case when ([score11] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score11] desc, [date] desc, [id]) end) as [rank_score11],
(case when ([score12] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score12] desc, [date] desc, [id]) end) as [rank_score12],
(case when ([score13] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score13] desc, [date] desc, [id]) end) as [rank_score13],
(case when ([score14] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score14] desc, [date] desc, [id]) end) as [rank_score14],
(case when ([score15] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score15] desc, [date] desc, [id]) end) as [rank_score15],
(case when ([score16] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score16] desc, [date] desc, [id]) end) as [rank_score16],
(case when ([score17] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score17] desc, [date] desc, [id]) end) as [rank_score17]
from [test]
where ([cancelled] is null) and ([optional] = 0) and (coalesce([confirmed], -1) != 0)
select row_number() over (partition by [record], [type] order by [total] desc, [score1] + coalesce([score2], 0) + coalesce([score3], 0) + coalesce([score4], 0) desc, [date] desc, [id]) as [rank_confirmed],
(case when ([score1] is not null) then row_number() over (partition by [record], [type] order by [score1] desc, [date] desc, [id]) end) as [rank_confirmed_score1],
(case when ([score2] is not null) then row_number() over (partition by [record], [type] order by [score2] desc, [date] desc, [id]) end) as [rank_confirmed_score2],
(case when ([score3] is not null) then row_number() over (partition by [record], [type] order by [score3] desc, [date] desc, [id]) end) as [rank_confirmed_score3],
(case when ([score4] is not null) then row_number() over (partition by [record], [type] order by [score4] desc, [date] desc, [id]) end) as [rank_confirmed_score4],
(case when ([score5] is not null) then row_number() over (partition by [record], [type] order by [score5] desc, [date] desc, [id]) end) as [rank_confirmed_score5],
(case when ([score6] is not null) then row_number() over (partition by [record], [type] order by [score6] desc, [date] desc, [id]) end) as [rank_confirmed_score6],
(case when ([score7] is not null) then row_number() over (partition by [record], [type] order by [score7] desc, [date] desc, [id]) end) as [rank_confirmed_score7],
(case when ([score8] is not null) then row_number() over (partition by [record], [type] order by [score8] desc, [date] desc, [id]) end) as [rank_confirmed_score8],
(case when ([score9] is not null) then row_number() over (partition by [record], [type] order by [score9] desc, [date] desc, [id]) end) as [rank_confirmed_score9],
(case when ([score10] is not null) then row_number() over (partition by [record], [type] order by [score10] desc, [date] desc, [id]) end) as [rank_confirmed_score10],
(case when ([score11] is not null) then row_number() over (partition by [record], [type] order by [score11] desc, [date] desc, [id]) end) as [rank_confirmed_score11],
(case when ([score12] is not null) then row_number() over (partition by [record], [type] order by [score12] desc, [date] desc, [id]) end) as [rank_confirmed_score12],
(case when ([score13] is not null) then row_number() over (partition by [record], [type] order by [score13] desc, [date] desc, [id]) end) as [rank_confirmed_score13],
(case when ([score14] is not null) then row_number() over (partition by [record], [type] order by [score14] desc, [date] desc, [id]) end) as [rank_confirmed_score14],
(case when ([score15] is not null) then row_number() over (partition by [record], [type] order by [score15] desc, [date] desc, [id]) end) as [rank_confirmed_score15],
(case when ([score16] is not null) then row_number() over (partition by [record], [type] order by [score16] desc, [date] desc, [id]) end) as [rank_confirmed_score16],
(case when ([score17] is not null) then row_number() over (partition by [record], [type] order by [score17] desc, [date] desc, [id]) end) as [rank_confirmed_score17]
from [test]
where ([cancelled] is null) and ([optional] = 0) and ([confirmed] = 1)
select row_number() over (partition by [record], [type] order by [total] desc, [score1] + coalesce([score2], 0) + coalesce([score3], 0) + coalesce([score4], 0) desc, [date] desc, [id]) as [rank_overall],
(case when ([score1] is not null) then row_number() over (partition by [record], [type] order by [score1] desc, [date] desc, [id]) end) as [rank_overall_score1],
(case when ([score2] is not null) then row_number() over (partition by [record], [type] order by [score2] desc, [date] desc, [id]) end) as [rank_overall_score2],
(case when ([score3] is not null) then row_number() over (partition by [record], [type] order by [score3] desc, [date] desc, [id]) end) as [rank_overall_score3],
(case when ([score4] is not null) then row_number() over (partition by [record], [type] order by [score4] desc, [date] desc, [id]) end) as [rank_overall_score4],
(case when ([score5] is not null) then row_number() over (partition by [record], [type] order by [score5] desc, [date] desc, [id]) end) as [rank_overall_score5],
(case when ([score6] is not null) then row_number() over (partition by [record], [type] order by [score6] desc, [date] desc, [id]) end) as [rank_overall_score6],
(case when ([score7] is not null) then row_number() over (partition by [record], [type] order by [score7] desc, [date] desc, [id]) end) as [rank_overall_score7],
(case when ([score8] is not null) then row_number() over (partition by [record], [type] order by [score8] desc, [date] desc, [id]) end) as [rank_overall_score8],
(case when ([score9] is not null) then row_number() over (partition by [record], [type] order by [score9] desc, [date] desc, [id]) end) as [rank_overall_score9],
(case when ([score10] is not null) then row_number() over (partition by [record], [type] order by [score10] desc, [date] desc, [id]) end) as [rank_overall_score10],
(case when ([score11] is not null) then row_number() over (partition by [record], [type] order by [score11] desc, [date] desc, [id]) end) as [rank_overall_score11],
(case when ([score12] is not null) then row_number() over (partition by [record], [type] order by [score12] desc, [date] desc, [id]) end) as [rank_overall_score12],
(case when ([score13] is not null) then row_number() over (partition by [record], [type] order by [score13] desc, [date] desc, [id]) end) as [rank_overall_score13],
(case when ([score14] is not null) then row_number() over (partition by [record], [type] order by [score14] desc, [date] desc, [id]) end) as [rank_overall_score14],
(case when ([score15] is not null) then row_number() over (partition by [record], [type] order by [score15] desc, [date] desc, [id]) end) as [rank_overall_score15],
(case when ([score16] is not null) then row_number() over (partition by [record], [type] order by [score16] desc, [date] desc, [id]) end) as [rank_overall_score16],
(case when ([score17] is not null) then row_number() over (partition by [record], [type] order by [score17] desc, [date] desc, [id]) end) as [rank_overall_score17]
from [test]
where ([cancelled] is null) and ([optional] = 0) and (coalesce([confirmed], -1) != 0)
select row_number() over (partition by [record], [type], [subtype] order by (case when ([confirmed] = 1) then 0 else 1 end), [total] desc, [score1] + coalesce([score2], 0) + coalesce([score3], 0) + coalesce([score4], 0) desc, [date] desc, [id]) as [rank_subtype],
row_number() over (partition by [record], [type], [subtype] order by [total] desc, [score1] + coalesce([score2], 0) + coalesce([score3], 0) + coalesce([score4], 0) desc, [date] desc, [id]) as [rank_overall_subtype]
from [test]
where ([cancelled] is null) and ([optional] = 0) and (coalesce([confirmed], -1) != 0) and ([subtype] is not null)
select row_number() over (partition by [record], [type], [subtype] order by [total] desc, [score1] + coalesce([score2], 0) + coalesce([score3], 0) + coalesce([score4], 0) desc, [date] desc, [id]) as [rank_confirmed_subtype]
from [test]
where ([cancelled] is null) and ([optional] = 0) and ([confirmed] = 1) and ([subtype] is not null)