If you look in the Formats.xml file in your Helpdesk 6 instance you will see a section 'sidSqlClosedOn' around line 110.  This is the out of the box code that is used to calculate the closed on date in the web GUI.  This can be used in reporting to get the incidemts 'Closed_on_date' however, this code is flawed.  I have created an alternate sql statement that covers scenarios that this orgional does not.  Specificly, when the ticket is marked with a status of closed when it is first created, and then comments are added later, the origonal code in unable to calculate the closed on date.  I will present my revised SQL down below.

Once we have the SQL statement to get the closed on date, you will want to use it for reporting.  So the obvious question is how do I use this SQL.  The trick to using this SQL statement is you have to pass the incident number to it, so it becomes very difficult to use it in inline SQL that is pulling a reoprt for multiple incidents.

The solution:
Create your own scalar-valued function in your SQL database for helpdesk.  You can then use that function in any of your SQL calls for reporting.

Here is the SQL code to generate a custom function 'Altiris_SD_Workitem_closed_on'
---------------------------- copy below this line ------------------------------------------------
USE [Altiris_Incidents]   -- Replace this with the name of your database
GO
/****** Object:  UserDefinedFunction [dbo].[Altiris_SD_Workitem_closed_on]    Script Date: 02/25/2010 08:43:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Don Rowland
-- Create date: 5/12/2009
-- Updated date: 2/24/2010 - to account for additional scenarios
-- Description: Get the datetime an incident was closed
-- =============================================
ALTER FUNCTION [dbo].[Altiris_SD_Workitem_closed_on]
(
 -- Add the parameters for the function here
 @Ticket_number int
)
RETURNS datetime
AS
BEGIN
 -- Declare the return variable here
 DECLARE @workitem_closed_on datetime

 -- Add the T-SQL statements to compute the return value here

-- THIS IS THE ORIGONAL SQL FROM THE FORMATS.XML.  I have commented it out to have for refence but I do not use it anymore.
 --SET @workitem_closed_on = (SELECT top 1 w1.workitem_modified_on
 --FROM workitem_detail_view w1
 --  inner join workitem_detail_view w2 on w1.workitem_number = w2.workitem_number
 --  and ((w1.workitem_version = w2.workitem_version +1
 --  and w1.workitem_status_lookup_id <> w2.workitem_status_lookup_id) or ((SELECT count(*) FROM workitem_detail_view w3
 -- WHERE w3.workitem_number=@Ticket_number)=1))
 --WHERE w1.workitem_status_lookup_id=600 and w1.workitem_number=@Ticket_number
 --ORDER BY w1.workitem_version Desc)
 
-- THIS THE SQL I CAME UP WITH.  If you want to use this in your formats.xml, be sure to replace the varible i used with the one used by the orgional code.
 SET @workitem_closed_on = (SELECT min(w1.workitem_modified_on)
 FROM workitem_detail_view w1
 WHERE w1.workitem_number=@Ticket_number and
 w1.workitem_modified_on > (CASE WHEN (SELECT COUNT(*) FROM workitem_detail_view WHERE workitem_number=@Ticket_number and
         workitem_status_lookup_id <> 600 )>=1
         THEN (SELECT MAX(workitem_modified_on)
         FROM workitem_detail_view WHERE workitem_number=@Ticket_number and
         workitem_status_lookup_id <> 600)
         ELSE 0 END))

 -- Return the result of the function
 RETURN @workitem_closed_on

END
---------------------------------Stop before ths line-----------------------------------------

Here is a sample SQL statement use for reporting SLA's over the past 30 days, based on the closed on date which is calculated using the above function:
---------------------------- copy below this line ------------------------------------------------
SELECT workitem_number,
 workitem_priority_lookup_value,
 workitem_status_lookup_value,
 workitem_due_on, 
 dbo.Altiris_SD_Workitem_closed_on(workitem_number) as workitem_closed_on,
 assigned_to_worker_name,
 owned_by_worker_name,
 workitem_category_tree_value,
 CASE WHEN dbo.Altiris_SD_Workitem_closed_on(workitem_number) > workitem_due_on THEN
 'MISSED'
 ELSE
 'MET'
 END AS SLA
FROM dbo.workitem_current_view
WHERE dbo.Altiris_SD_Workitem_closed_on(workitem_number) > CONVERT(varchar, getdate() -31, 101)
 AND dbo.Altiris_SD_Workitem_closed_on(workitem_number) < CONVERT(varchar, getdate(), 101)
ORDER BY workitem_number