尝试安排SQL查询以正确使用DATEDIFF() [英] Trying to arrange SQL query to properly use DATEDIFF()

问题描述

我正在创建一个查询，其中显示成员具有的不同状态以及它们处于每种状态的时间.

I'm creating a query where I'm showing the different statuses a member has had, and how long they were in each status.

这是我到目前为止写的:

This is what I've written so far:

    select memberid, 
    case when statusname = "Pending" then cast(statusdate as date) end as StatusDate1,
    case when statusname ="In Progress" then cast(statusdate as date) end as StatusDate2, 
    case when statusname = "Approved" then cast(statusdate as date) end as StatusDate3
    into #test
    from Table A
    group by memberid, case when statusname = "Pending" then cast(statusdate as date) end, 
    case when statusname ="In Progress" then cast(statusdate as date) end,
    case when statusname = "Approved" then cast(statusdate as date) end 

这将产生以下结果:

     Member Id     StatusDate1  StatusDate2 StatusDate3 
     10          2017-01-06      null         null             
     10            null       2017-02-20      null
     10            null          null         2017-03-02

然后我尝试使用DATEDIFF来获取状态之间的时间长度:

I then tried using a DATEDIFF to get the length of time between statuses:

    select *, DATEDIFF(dd, StatusDate2, StatusDate1) as Length1
    from #test

但是这没有用，因为它给了我一个空值.我在想问题是状态日期都必须在一行上.我尝试做一个UNION，其中第一个查询仅包含StatusDate1，下一个UNION具有StatusDate2，但仍然没有将它们放在一行上.

But this didn't work as it just gave me a null. I'm thinking the issue is that the status dates all need to be on one line. I tried doing a UNION where the first query only had StatusDate 1, the next UNION had StatusDate2, but it still didn't put them on one line.

关于如何获取状态日期之间的天数的任何建议?

Any suggestions on how to get the number of days between status dates?

推荐答案

通过聚集 memberid :

SELECT
    memberid,
    MAX(CASE WHEN statusname = 'Pending'
             THEN CAST(statusdate AS DATE) END) AS StatusDate1,
    MAX(CASE WHEN statusname = 'In Progress'
             THEN CAST(statusdate AS DATE) END) AS StatusDate2, 
    MAX(CASE WHEN statusname = 'Approved'
             THEN CAST(statusdate AS DATE) END) AS StatusDate3
INTO #test
FROM TableA
GROUP BY memberid

这篇关于尝试安排SQL查询以正确使用DATEDIFF()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！