SELECT STATEMENT – CREATE XML FILE

This is a SELECT statement in SQL that is used to retrieve data from multiple tables in a database. The statement begins by selecting a number of columns from the tables specified in the FROM clause.

The first column selected is “ct.name”, which is the name of the call type from the [CallType] table (aliased as “ct”). This column is given the alias “NatureOfCall”.

The second column selected is the concatenation of the first and last names of the user, which are stored in the [Users] table (aliased as “u”). The full name is given the alias “Employee”.

The third and fourth columns selected are the date and time of the call, respectively. These values are stored in the “CreateDatetime” column of the [Call] table (aliased as “c”). The “convert” function is used to convert the “CreateDatetime” value to a date and a time, and the “switchoffset” function is used to adjust the time to the Central Standard Time zone. The “datepart” function is used to extract the time zone offset from the “CreateDatetime” value. The date and time are given the aliases “DateOfCall” and “TimeOfCall”, respectively.

The fifth column selected is the address of the call, which is composed of several columns from the [Address] table (aliased as “a”). The “HouseNumber”, “prefixdirectional”, “streetname”, “streettype”, “qualifier”, “xprefixdirectional”, “xstreetname”, “xstreettype”, and “city” columns are concatenated together using the “+” operator. The resulting address is given the alias “AddressOfCall”.

The final column selected is the call number, which is stored in the “CallNumber” column of the [Call] table (aliased as “c”). This column is given the alias “CADNumber”.

The SELECT statement also includes an INNER JOIN clause, which specifies the relationships between the tables being queried. In this case, the [Call].[Call] table is joined with the [Address] table using the “CurrentAddressID” column, the [CallType] table is joined with the [Call] table using the “CallTypeID” column, and the [Users] table is joined with the [Call] table using the “CreateUser” column.

The SELECT statement also includes a WHERE clause, which specifies a range of dates for the “CreateDatetime” column using the BETWEEN operator. The DATEADD function is used to subtract one day from the current date, and the resulting date is used as the lower bound of the range. The current date is used as the upper bound of the range.

Finally, the SELECT statement includes an ORDER BY clause, which specifies that the results should be sorted by the “CreateDatetime” column in ascending order.

The FOR XML PATH option at the end of the statement specifies that the results should be returned as an XML document, with the “IncidentData” elements representing each row of the results and the “root” element representing the top level of the XML document.

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *