I have table with below data. I want to add one more column 'Resolution Time' in SQL view with all column which has in table currently.

Resolution Time=DATEDIFF(DAY,[INC CreatedOn],[ResolvedOn]) this needs to be calculated based on distinct ticket number.

Expected Results with ResolutionTime column

I tried adding Datediff logic but it calculates for every row.

NOTE : I need to get the average of Resolution Time based on unique incident ticket number.

So here, avg(resolution time) = (3 9 4 6)/4

Adding few more information here which might help to build query

Incident table has following column

| IncidentID | TicketNumber | Inc CreatedOn | ResolvedOn |

Payment table

| IncidentID | PaymentID | Amount | Payment CreatedOn |

One IncidentID can have multiple Payments so its (Incident)One-To-Many(Payment) which are joined on Incident.IncidentID=Payment.IncidentID

I need to create single SQL view which should also have resolutiontime so that average of resolution time can be derived for reporting

Any help would be much appreciated! Thanks

CodePudding user response：

Your desired output is not at all clear. I will show you have you could return the values you posted. And then also how you might get the average value you stated you want.

First we need some sample data to work with.

declare @Something table
(
    IncidentID int
    , TicketNumber varchar(20)
    , PaymentID varchar(10)
    , Amount int
    , IncCreatedOn date
    , PaymentCreatedOn date
    , ResolvedOn date
)
insert @Something
select 1, 'INC-01', '1a', '100', '05-Jan-22', '05-Jan-22', '08-Jan-22' union all
select 1, 'INC-01', '2a', '200', '05-Jan-22', '06-Jan-22', '08-Jan-22' union all
select 1, 'INC-01', '3a', '400', '05-Jan-22', '07-Jan-22', '08-Jan-22' union all
select 2, 'INC-02', '4a', '300', '01-Feb-22', '04-Feb-22', '10-Feb-22' union all
select 2, 'INC-02', '5a', '500', '01-Feb-22', '07-Feb-22', '10-Feb-22' union all
select 3, 'INC-03', '6a', '200', '01-Mar-22', '02-Mar-22', '05-Mar-22' union all
select 4, 'INC-04', '7a', '800', '04-Mar-22', '05-Mar-22', '10-Mar-22' union all
select 4, 'INC-04', '8a', '900', '04-Mar-22', '08-Mar-22', '10-Mar-22' 
;

This query will return the data you said you want.

select *
    , ResolutionTimeinDays = case when ROW_NUMBER()over(partition by TicketNumber order by PaymentID) = 1 then datediff(day, IncCreatedOn, ResolvedOn) end
from @Something s
order by s.IncidentID
    , s.PaymentID

You can leverage the above query easily enough to get your average.

with MyCte as
(
    select *
        , ResolutionTimeinDays = case when ROW_NUMBER()over(partition by TicketNumber order by PaymentID) = 1 then datediff(day, IncCreatedOn, ResolvedOn) end
    from @Something s
)

select AVG(ResolutionTimeinDays * 1.0) --multiplied here by 1.0 or the average function will be returned as an integer
from MyCte
where ResolutionTimeinDays is not null

CodePudding user response：

It's difficult to tell what your view code is.

If you do not want that value for each list, you could (a) make another GroupBy table to see which record on the view needs to have the ResolutionTimeinDaysfield then run a case statement or (b) have another column that states which record is the main record, and only update that value on the view

I'd suggest (a) since (b) is updating more of the view. You need to figure out how to programatically say that row 1 will get the ResolutionTimeinDays value rather than row 2. To do this you make another group by innerquery, and lower aggregate function on the date. Then see how to uniquely group it back to your result.

The inner groupby to find the specific row that you want to add the value to:

select incidentid, min(Paymentcreatedon) from tab group by incidentid

Then join it to your table on both incidentid and min(Paymentcreatedon). But make sure it is a unique way to join it.