计算两列sql之间的差异

in my SQL Query i am Calculating column values by using CASE WHEN THEN ELSE END Condition. Now i want to find difference between computed column and normal column

在我的SQL查询中,我正在使用CASE WHEN THEN ELSE END Condition计算列值。现在我想找到计算列和普通列之间的区别

My Query

SELECT  T.Ticket [Ticket],
        TT.TicketType [Ticket Type],
        T.Dependency [Dependency],
        CASE 
                WHEN (
                        SELECT  TOP 1 f1.UpdatedOn
                        FROM    TicketTypeFollowUp AS f1
                        WHERE   f1.UpdatedOn < T.UpdatedOn
                                AND f1.Ticket = 61423
                        ORDER   BY f1.UpdatedOn DESC
                    ) IS NULL
                THEN Ticket.TicketRaisedOn
                ELSE (
                        SELECT  TOP 1 f1.UpdatedOn
                        FROM    TicketTypeFollowUp AS f1
                        WHERE   f1.UpdatedOn < T.UpdatedOn
                                AND f1.Ticket = 61423
                        ORDER   BY f1.UpdatedOn DESC
                    )
                END [Start Date],
        T.UpdatedOn [End Date],
        L.EmpName [Updated By]
FROM    dbo.TicketTypeFollowUp T
        LEFT JOIN dbo.Ticket
                ON T.Ticket = Ticket.Code
        LEFT JOIN dbo.TicketType TT
                ON T.TicketType = TT.Code
        LEFT JOIN LoginUser L
                ON T.UpdatedBy = L.Code
WHERE Ticket = 61423

Query Result

Ticket  Type    Dependency  Start Date              End Date                Updated By
61423   FLM     AGS         2013-01-22 15:50:08.757 2013-01-22 18:35:50.893 Kedar S

I want one more column which displays End Time - Start Time I have used CASE hence i am unable to compute difference.

我想要一个显示结束时间的列 - 开始时间我使用CASE因此我无法计算差异。

Database is SQL SERVER 2008

数据库是SQL SERVER 2008

2 个解决方案

#1

You can use your current query as a CTE or a derived table. Here is an example using a CTE:

您可以将当前查询用作CTE或派生表。以下是使用CTE的示例:

;WITH CTE AS
(
    -- your current query here
)
SELECT *, DATEDIFF(MINUTE,[Start Date],[End Date]) [End Time - Start Time]
FROM CTE

#2

with lastUpdate(ticket, lastUpdate) as (
        select  ticket,
                max(updateOn)
        from    TicketTypeFollowUp
        group by ticket)
SELECT  T.Ticket [Ticket],
        TT.TicketType [Ticket Type],
        T.Dependency [Dependency],
        coalesce(lastUpdate, ticketRaisedOn) [Start Date],
        T.UpdatedOn [End Date],
        datediff(mi, coalesce(lastUpdate, ticketRaisedOn), T.UpdatedOn) durationMins,
        L.EmpName [Updated By]
FROM    dbo.TicketTypeFollowUp T
        LEFT JOIN dbo.Ticket
                ON T.Ticket = Ticket.Code
        LEFT JOIN dbo.TicketType TT
                ON T.TicketType = TT.Code
        LEFT JOIN LoginUser L
                ON T.UpdatedBy = L.Code
        left join lastUpdate lu on t.ticket = lu.ticket
WHERE Ticket = 61423