Database

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
      }
   }
}

=IF(ROUND(A1+B1-C1,5)=0,"Correct","Incorrect")

smyers wrote:

SELECT TOP 2 salary FROM tablename

smyers wrote:

Don't lie, you just made that up.