Database

SELECT L.location_name AS Client,
   TotalOpenAtStart,
   TotalOpenAtEnd,
   (TotalOpenAtEnd - TotalOpenAtEnd) AS TotalClosed,
   AvgDaysOpen
FROM location l
LEFT OUTER JOIN (
   SELECT j.location_id,
      SUM(CASE WHEN j.status_type_id IN (1,2,6,7) THEN 1 ELSE 0 END) AS TotalOpenAtStart,
      SUM(CASE WHEN j.status_type_id IN (3,4,5) THEN 1 ELSE 0 END) AS TotalOpenAtEnd,
      AVG(DATEDIFF(d, j.report_date, GETDATE())) AS AvgDaysOpen
   FROM job_ticket j
   WHERE j.report_date BETWEEN @startdate AND @enddate)
   GROUP BY j.location_id
) x
ON x.location_id = l.location_id
ORDER BY L.location_name

SELECT
    Opened, 
    [Total Open at Start], 
    [Total Open at End],
    [Total Open at End] - 
        (SELECT 
            COUNT(*) 
         FROM 
            dbo.job_ticket 
         WHERE 
            location_id = Source.location_id AND
            status_type_id like '[3,4,5]' AND 
            last_updated between @startdate and @enddate) as [Total Closed], 
    Client, 
    [Average days open]
FROM
    (SELECT 
        l.location_id,
        l.location_name AS 'Client', 
        COUNT(*) AS 'Opened',
        SUM(CASE 
                WHEN status_type_id LIKE '[1,2,6,7]' THEN 1 
                ELSE 0 
            END) AS 'Total Open at Start', 
        AVG(datediff(d,report_date,getdate())) AS [Average days open]
     FROM 
        job_ticket j 
     INNER JOIN
        location l 
        ON (l.location_id = j.location_id)
     WHERE 
        report_date BETWEEN @startdate AND @enddate
     GROUP BY
        l.location_id,
        l.location_name) AS Source

SELECT
    Opened, 
    [Total Open at Start], 
    (SELECT 
        COUNT(*) 
     FROM 
        dbo.job_ticket 
     WHERE 
        location_id = Source.location_id AND
        status_type_id like '[3,4,5]' AND 
        last_updated between @startdate and @enddate) AS [Total Open at End],
    (SELECT 
        COUNT(*) 
     FROM 
        dbo.job_ticket 
     WHERE 
        location_id = Source.location_id AND
        status_type_id like '[3,4,5]' AND 
        last_updated between @startdate and @enddate) - [Total Open at Start] AS [Total Closed], 
    Client, 
    [Average days open]
FROM
    (SELECT 
        l.location_id,
        l.location_name AS 'Client', 
        COUNT(*) AS 'Opened',
        SUM(CASE 
                WHEN status_type_id LIKE '[1,2,6,7]' THEN 1 
                ELSE 0 
            END) AS 'Total Open at Start', 
        AVG(datediff(d,report_date,getdate())) AS [Average days open]
     FROM 
        job_ticket j 
     INNER JOIN
        location l 
        ON (l.location_id = j.location_id)
     WHERE 
        report_date BETWEEN @startdate AND @enddate
     GROUP BY
        l.location_id,
        l.location_name) AS Source

chovdry wrote:

When a input sqlparameter value contain special character(eg.') the SP is not able to execute

using ( SqlConnection conn = new SqlConnection( connectString ) )
{
   using ( SqlCommand cmd = new SqlCommand( conn ) )
   {
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = "SELECT top 1000 * from vw_SearchMetadata where ";
 
      if ( title != "" )
      {
         cmd.CommandText += "title like @title ";
         cmd.Parameters.Add( new SqlParameter( "@title", title ) );
      }
 
      // more parameters here - follow pattern above for title
 
      using ( SqlDataReader rdr = cmd.ExecuteReader() )
      {
          // Process results
      }
   }
}