An adult approached me at the store while I was writing a program on the computer demanding I, "Ask it a question". I tried to explain it didn't work like that when he shoved me out of the way and began typing. Seconds later came the demand, "What does Syntax Error mean?". For a 14 year old kid it was poetic but I open the manual to the description of the error message and showed the person. As you might expect he dropped the book and walked away.

The SDM Database
The Service Delivery Management (SDM) database was a database used internally by TD Bank to manage service requests from all areas of the bank, globally. Although capable of handling a host of requests types, the database was used primarily for support requests, trouble tickets and change management requests such as software system upgrade & promotion requests or hardware replacements.

The database consisted of some 300+ data tables. There was documentation for the each of the tables but TD bank had implemented specialized custom tables for which there was no documentation. Being a global system, the database also employed a custom time zone calculation function.

All change and support activities went through this database. I was the SME for this system until its eventual replacement with ServiceNow.

This Query
I’ve selected this query as an example because of its physical size (easier for you to read on a web page) and use of the SQL language in a highly normalized scenario.

The query was designed to produce a report for use in the Monthly Operation Management meetings showing all current support incidents dating back 2 years from the current date. The report was designed to be hosted on the SQL server to that the report could be used directly and easily from ASP.Net code, Excel data queries, or SSMS Query Analyzer from which Excel or flat file extract reports could easily be generated.

The “Top 100” clause is there because this is my prototyping & testing query. Since this query would return record counts excess of 100,000 records, I restricted the result set for my testing work.

SQL developers will notice that the column lines of code start with a comma rather than ending with one. SQL requires comma separation between columns in the code but it doesn’t care where the comma occurs. The end of one line or the beginning of the next is all the same to SQL. Having the comma at the beginning of line allows me to remark out (Coding slang for disable) a column in the query without deleting the code for that column altogether and without having to add or remove commas to other lines which can occasionally be a source of easily avoidable bugs.

The query also contains use of a very simple Case/When/Then clause to convert a column value to a “yes” or “No” value. Time difference calculations are also used to show how long an incident was open for. The RPT_GetTzTime function which was used to resolve time zone differences can also be seen here.

As you move down the query you’ll notice simple inner and outer joins but also a compound query joining a table with a query result.

My query coding style is focused on use of white space to make things visually easier to find in very large or very complex queries and stored procedures.

This is a relatively basic query whose biggest challenge was unravelling the table relationships amongst the 300+ tables.

Select top 100
        Cr.Persid
        ,COR2.resource_name as [Affected Service]
        ,Incidents.[assigned_group]	as [Assigned Group]
        ,Incidents.[assignee_first_name]+' '+Incidents.[assignee_last_name] as [Assignee Full Name]
        ,Incidents.[assignee_UserID] as [assignee_userid]
        ,dbo.RPT_GetTzTime(Incidents.[Close_date_time], 'ET') as [Close Date]
        ,[configuration_item] as [Configuration Item]
        ,CRC.[description] as [Configuration Item Class]
        ,COR.[resource_description] as [Configuration Family]
        ,Res_Owner_LInk.resource_name as [CI_Owner]
        ,Incidents.[incident_area] as [Incident Area]
        ,Incidents.[ref_num] as [Number]
        ,dbo.RPT_GetTzTime(Incidents.[open_date_time], 'ET') as [Open Date]
        ,Incidents.[priority] as [Priority]
        ,Incidents.[reported_by_first_name] +' '+Incidents.[reported_by_last_name] as [Reported By]
        ,Incidents.[reported_by_userid] as [Reported By UserID]
        ,Incidents.[reported_method] as [Reported Method]
        ,Incidents.[resolution_code] as [Resolution Code]
        ,Incidents.[resolution_method] as [Resolution Method]
        ,dbo.RPT_GetTzTime(Incidents.[resolve_date_time], 'ET') as [Resolve Date]
        ,dbo.RPT_GetTzTime(Incidents.[detection_date_time], 'ET')as [Service Outage Detection]
        ,dbo.RPT_GetTzTime(Incidents.[outage_end_date_time], 'ET') as [Service Outage Repair/Restoration]
        ,dbo.RPT_GetTzTime(Incidents.[outage_start_date_time], 'ET') as [Service Outage Start]
        ,CASE Breach.sla_viol_status WHEN 1 THEN 'Yes' ELSE 'No' END AS slo_breached
        ,Incidents.[status]	as [Status]
        ,Incidents.[resolution_summary]	as [Summary]
        ,Incidents.[symptom] as [Symptom]
        ,Cast(DateDiff(Hour, 0, [close_date_time]-[open_date_time]) as varchar) as [Time Open (Hours)]
      	,CR.[Time_Spent_Sum]/60/60 as [Total Activity (Hours)]
      	,Incidents.[vendor] as [Vendor]
      	,caused_by_chg.chg_ref_num AS caused_by_change	
      	,chg.chg_ref_num AS related_change	
      	,crProb.ref_num AS related_problem	
      	,cr.outage_detail_what AS resolution_summary	
      	,AssignedGrp.last_name as [Reported_By_Group]
      	,COORDLOB.last_name as [Coordinator_Group]
      
 from [dbo].[OAMRep_Incident_Details_View] as Incidents
      Left Join call_req as CR
      		on Incidents.ref_num = CR.Ref_Num
      Left Join (
      			SELECT [sla_viol_status],[mapped_cr]
      			FROM RPT_MDB.[dbo].[attached_sla]

      			) as Breach
      		on Cr.PersID = Breach.[mapped_cr]
      Left Join ca_owned_resource as COR
      		on CR.affected_rc = COR.own_resource_uuid
      Left Join ca_owned_resource as COR2
      		on CR.z_affected_service = COR2.own_resource_uuid
      Left Join ca_resource_family as CAF
		    on CAF.ID = COR.resource_family
      Left Join ca_resource_class as CRC
		    on CRC.ID = COR.resource_class
      Left Join [RPT_MDB].[dbo].[zlrel_inc_ci] as CI_Owner_Link
	  	    on cr.persid = CI_Owner_Link.inc
      Left Join [RPT_MDB].[dbo].[ca_owned_resource] as Res_Owner_LInk
      		on CI_Owner_Link.znr_fac = Res_Owner_LInk.own_resource_uuid
      LEFT OUTER JOIN [RPT_MDB].[dbo].chg AS caused_by_chg WITH (NOLOCK)
      		ON cr.caused_by_chg = caused_by_chg.id
      LEFT OUTER JOIN [RPT_MDB].[dbo].chg AS chg WITH (NOLOCK)
      		ON cr.change = chg.id
      Left Join [RPT_MDB].[dbo].[ca_contact] as AssignedGrp
      		on CR.z_reported_by_group = AssignedGrp.contact_uuid
      Left Join [RPT_MDB].[dbo].[ca_contact] as COORDLOB
      		on CR.Z_Owner_Group = COORDLOB.contact_uuid
      LEFT OUTER JOIN call_req AS crProb WITH (NOLOCK)
    		ON cr.problem = crProb.persid
WHERE Incidents.[open_date_time] > DATEADD(year,-2,GETDATE())
Order by CR.Ref_Num ASC

© John J Goodwin, 2013
You can contact me at http://ca.linkedin.com/in/developergoodwin/