Versions Compared

Old Version 3

changes.mady.by.user Former user

Saved on

compared with

New Version Current

changes.mady.by.user Richard Kennedy (Unlicensed)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Microsoft Query is a tool that is used to create SQL (Structured Query Language) statements that will extract and graphically display virtually any data that you want from the Synergetic database. Crystal Reports allows you to produce similar results but allows a lot more manipulation of the data from a graphical perspective.

Outlined below are some intermediate to advanced SQL statements that you can use with Microsoft Query. For more detailed information, refer to:

...

Standard Synergetic fields used for filtering data

The following table lists fields useful for filtering data:

Field

Value

Additional information

DeceasedFlag

0

Only retrieve records of people not deceased.
0 = False.

ValidAddressFlag

1

1 = True

CurrentSemesterOnlyFlag

1

1 = True

FileYear

2014 


FileSemester

1, 2, ...n

 


StudentStatus

<> 'LFT'

See luStudentStatus in the System Maintenance manual.

StudentContactType

'SC1', 'SC2' or 'SCT'

See luContact in the System Maintenance manual.

StudentContactNormalMailFlag

1

1 = True


Example 1 - Use SUBSTRING with a character string

This example shows how to return only a portion of a character string.

From the vStudentClasses view, this query returns records filtered based on a portion of the class code.

Query

SELECT
  vSCvStudentClasses.StudentID, vSCvStudentClasses.StudentSurname, vSCvStudentClasses.StudentPreferred,
  vSCvStudentClasses.StudentCampus, vSCvStudentClasses.StudentHouse, vSCvStudentClasses.ClassCampus, vSCvStudentClasses.ClassCode,
  vSCvStudentClasses.ClassDescription
FROM vStudentClasses vStudentClasses vSC 
WHERE (substring(vSCvStudentClasses.ClassCode,1,2)='MA04')
ORDER BY
  vSCvStudentClasses.StudentSurname,
  vSCvStudentClasses.StudentPreferred

The WHERE substring statement selects MA 04 because there is a character match starting from position one which includes two characters of the class code.Image Removed

Image Added

The following example shows you how to display the third and fourth characters of the string constant abcdef. The result set is cd.
SELECT x = SUBSTRING('abcdef', 3, 2)

Substring function

Returns part of a character, binary, text or image expression.

Syntax

SUBSTRING(expression, start, length)

Arguments

  • expression is a character string, binary string, text, image, a column, or an expression that includes a column. Do not use expressions that include aggregate functions.
  • start is an integer that specifies where the substring begins.
  • length is an integer that specifies the length of the substring (the number of characters to return).

Return types

Returns character data if expression is one of the supported character data types. Returns binary data if expression is one of the supported binary data types.
The returned string is the same type as the given expression with the exceptions shown in the table.

Given expression

Return type

text

varchar

image

varbinary

ntext

nvarchar

Remarks

Offsets (start and length) using the ntext, char, or varchar data types must be specified in number of characters. Offsets using the text, image, binary, or varbinary data types must be specified in number of bytes.

Note: Compatibility levels can affect return values.

Example 2 - Mail names and addresses for current parents and staff

Query

SELECT
  MailNameJoint, AddressFull
FROM Community
  LEFT JOIN Addresses ON (
    Community.AddressID = Addresses.AddressID)
WHERE
  EXISTS (SELECT * from Constituencies
          WHERE Community.ID = Constituencies.ID
            AND ConstitCode = '@PC')
  AND EXISTS (SELECT * from Constituencies
              WHERE Community.ID = Constituencies.ID AND ConstitCode = '@STF')
  AND DeceasedFlag = 0
  AND ValidAddressFlag = 1
  AND SpouseFlag = 0

Points to note:

  • [NOT] EXISTS specifies the search for an expression, based on the expression's inclusion or exclusion from a list. The search expression can be a constant or a column name, and the list can be a set of constants or, more commonly, a sub query. Enclose the list of values in parentheses.


Example 3 - All parents who are also staff members (primary or spouse)

Query

SELECT DISTINCT
  StudentContactMailName, StudentContactAddressFull
FROM vStudentContactAddress
  LEFT JOIN Community ON (
    StudentContactID = Community.ID)
WHERE EXISTS (SELECT * FROM Constituencies
              WHERE vStudentContactAddress.StudentContactID = Constituencies.ID
                AND ConstitCode = '@STF')
  OR EXISTS (SELECT * from Constituencies
             WHERE SpouseID = Constituencies.ID
               AND ConstitCode = '@STF')

Points to note:

  • DISTINCT specifies that only unique rows can appear in the returned data set. Null values are considered equal for the purposes of this keyword.
  • LEFT JOIN specifies that all rows from the left table that are not meeting the join condition are included in the results set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.
  • INNER JOIN specifies that all records that meet the join condition on both sides are included in the results set.


Example 4 - Selecting records in Microsoft Query on the basis of date fields
Select enquiries in the last 7 days:

Query

SELECT
  vEnquiryContactAddress.EnquiryID, vEnquiryContactAddress.EnquiryDate
FROM vEnquiryContactAddress
WHERE
  {fn TIMESTAMPDIFF(SQL_TSI_DAY, {fn CURDATE()}, vEnquiryContactAddress.EnquiryDate)} > -6

Points to note:

  • The –6 means 'select all enquiries for today plus the last 7 days'.  Change the –6 to 0 for TODAY only. You can change to any other value to select enquiries from that many days ago. You MUST enter the number of days with the minus sign (means PRIOR to today).

...

The following query can be used in Microsoft Query to find all future students who have a sibling who is currently enrolled, using the definition above.

Sample query

Query

SELECT * FROM vFutureContactAddress
WHERE FutureContactID IN
  (SELECT StudentContactID from vStudentContactAddress
   WHERE StudentContactType = 'SC1'
   AND CurrentSemesterOnlyFlag = 1
   AND vStudentContactAddress.ID <> vFutureContactAddress.ID)
AND FutureContactType = 'SC1'


Example 6 - Select enrolment changes in the last seven days
You can also use the following statement to select records changed during the previous day(s). This statement will only work in the current year – that is, you cannot select records changed before the current year. The query uses the SQL DATEPART function to calculate the date range required.

Query

SELECT vFCA.FutureSurname, vFCA.FuturePreferred, vFCA.FutureGender, vFCA.FutureContactType, vFCA.FutureContactDescription, vFCA.FutureContactMailName, vFCA.FutureContactMailSalutation, vFCA.FutureContactAddressFull, vFCA.FutureApplicationDate, vFCA.FutureEnrolYear, vFCA.FutureStatus, vFCA.FutureCampus, vFCA.FutureYearLevel
FROM SynergyOne.dbo.vFutureContactAddress vFCA
WHERE
(DATEPART(Year,FutureStatusChangeDate) >= DATEPART(Year,GETDATE())) AND (DATEPART(DayofYear,FutureStatusChangeDate) >= (DATEPART(DayofYear,GETDATE() -6))