Date Manipulation
Returns the Month of when a record was created.
DatePart(Day,[Created Date], getdate()) |
Assessing Relationships
Returns the of name of the organisation that a person works for.
[Contact].[Organisation] |
Categorising Data
Returns whether a person is an adult or child.
iif([Age] > 17, 'Adult', 'Child') |
Date and Time Differences
Calculates the difference between when a Task record was created and the current date, only when the Task status is not completed. Otherwise it returns null.
iif([Task Status] <> 'Completed', Datediff(day,[Created Date], getdate()), null) |
Maximum Age
Finds the maximum field value for given a set of data.
Max([Age]) |
Text Manipulation
Returns the person's name from an email address.
LEFT([Business Email],charindex('@',[Business Email]) -1) |
Conditional Count
Returns a count of the number of textbooks that are only e-books.
Count([Textbooks] where [E-book] = true) |
Sum with Conditions
Returns sum of the cost of textbooks that are only e-books
sum(([Textbooks] where [E-book]=true).[Purchase Price]) |
Variables
Using a variable in a calculation
Let a = [Start Date] SELECT iif(a>getdate(),'Future','Past') |