title: Calculate Avg time and Won %
body: <p>Below is my database table in the SQL server</p>
  <pre><code>Status      Start_Date          End_Date           Category
  Won         2020-03-11 17:01    2020-11-17 11:36   A
  Won         2020-03-11 17:00    2020-11-13 19:07   B
  Won         2020-03-11 17:00    2020-11-12 13:40   A
  Lost        2020-03-11 16:59    2020-11-12 13:06   B
  Lost        2020-03-11 12:20    2020-11-12 12:58   C
  Initiated   2020-02-11 19:31                       C
  Initiated   2020-02-11 17:05                       B
  Won         2020-02-11 12:25    2020-11-10 12:55   A
  Lost        2020-02-11 12:23    2020-11-09 16:35   A
  Lost        2020-02-11 12:22    2020-11-09 16:32   A
  </code></pre>
  <p>I want to get 2 outputs, one is overall stats and the 2nd is category-wise stats.</p>
  <p>For overall I want won% and Avg time in Days, hours in a single query.</p>
  <p>the output should be like this</p>
  <pre><code>Won%    AvgTime
  4%      2 Days 13 Hours 
  </code></pre>
  <p>and if the avg time is under 24 hours it should not display the day, only hours should be displayed.</p>
  <p>and for category wise, the output should be like this in a single query</p>
  <pre><code>Category     Won%     AvgTime
  A            4%       2 Days 13 hours
  B            1%       7 hours
  C            0%       
  </code></pre>
  <p>Thanks in advance.</p>
  <p>I have tried the following query but got confused and stopped as I don't know if I am going the right direction</p>
  <pre><code>select (
          select count(*)
          from jap.dbo.JAP_Master_Table j2
          where Status like '%won%'
      )
      , avg(DATEDIFF(day,Start_date,End_Date))
  from jap.dbo.JAP_Master_Table j1
  </code></pre>
  <p><strong>Update</strong></p>
  <p>i have tried further for overall results query and I get the output which is somewhat close to what I want with this query</p>
  <pre><code>select round(cast((select count(*) from jap.dbo.JAP_Master_Table j2 where Status like '%won%') as float)/ cast((count(*)) as float),2) as [Won%]
  ,avg(DATEDIFF(day,EntryDate,CAMStatusUpdatedon)) as AverageTime from jap.dbo.JAP_Master_Table j1
  </code></pre>
  <p>and the output is</p>
  <pre><code>Won%    AverageTime
  0.02    11
  </code></pre>
  <p>but the query is not efficient and the output is only giving me average time in minutes, not in the format I wanted. I am trying but if someone can provide me with something more efficient that would be much appreciated.</p>