在TWO日期之间查找中位数SQL Server 2008 [英] Finding Median between TWO dates SQL Server 2008

问题描述

我正在寻找一种方式来采取一系列开始和结束日期（LOTS AND LOTS of dates）的MEDIAN。但是，它将针对各种发票编号。

I am looking of a way to take the MEDIAN of a bunch of start and end dates (LOTS AND LOTS of dates). However, it would be specific to various "invoice numbers." See sample data below.

 invoice_no    invoice start date      invoice end date
 4006            11/14/2001               12/15/2004
 20071           11/29/2001               02/01/2003
 19893           11/30/2001               12/02/2001
 19894           11/30/2001               12/04/2001
 004             10/22/2002               10/31/2002
 004             12/02/2002               10/31/2002
 004             01/19/2002               10/31/2002
 004             05/10/2002               10/31/2002

查找开始和结束日期之间的中位数。

Find median between start and end date.

对于只显示一次的发票，中位数只是该特定invoice_no的开始和结束日期之间的任何值。但是，许多案例将是显示发票004的方式。它会在不同的日期重复多次 - 但在这里仍然是一样的概念。需要找到两个日期之间的中间值，但仍需要根据该发票编号显示。

For an invoice that only displays once, the median would just be whatever is between start and end date for that specific invoice_no. However, MANY cases will be the way that invoice '004' are shown. It will repeat many times for different dates - but still the same concept here. Need to find the median between two dates, but still need it to display based on that invoice number.

尽可能多地过滤数据。我意识到我也可以做地位，拒绝，也应该帮助保持很多无休止的日期。此外，我只想在几个月之间过滤，所以我也添加了BETWEEN DATETIME。

In an effort to filter data as much as possible. I realized I can also do WHERE STATUS <> 'REJECTED' and it should also help keep a lot of uncessary dates out. Also, I only wanted to filter between a few months so I added the BETWEEN DATETIME in as well.

代码到目前为止（但不工作...这样的逻辑似乎工作，如果有一个日期列，但现在我们正在使用两个日期，所以我'不确定）：

Code so far (but not working... this sort of logic seems to work if there was 1 date column, but now we're working with two dates so I'm not sure):

 WITH
          tmp AS
    (
        SELECT invoice_no,
        invoice_start_date, invoice_end_date, check_date, status_code,
        cast(count(*) OVER (PARTITION BY invoice_no) as float) AS total,
        row_number() OVER (PARTITION BY invoice_no ORDER BY 
        invoice_start_date, invoice_end_date, check_date) AS rn

推荐答案

设置的开始日期和结束日期，然后将它们放在一列中：

If you mean the set of start dates and end dates, then put them in one column:

WITH t AS (
       SELECT invoice_no, invoice_start_date, invoice_end_date, check_date, status_code,
       FROM INVOICE_HEADER INNER JOIN
            INVOICE_HEADER_CUSTOM
            ON INVOICE_HEADER.invoice_id = INVOICE_HEADER_CUSTOM.invoice_id
       WHERE status_code <> 'REJECTED' AND 
             Check_Date BETWEEN CONVERT(DATETIME, '2014-12-01 00:00:00', 102) AND
             CONVERT(DATETIME, '2014-12-31 00:00:00', 102)
     ), 
     t2 as (
      select d, row_number() over (order by d) as seqnum,
             count(*) over () as cnt
      from (select invoice_start_date as d from t
            union all
            select invoice_end_date as d from t
           ) t
     )
select dateadd(day, datediff(hour, min(d), max(d)) / 2.0, min(d))
from t2
where 2 * seqnum in (cnt, cnt + 1, cnt + 2);

这篇关于在TWO日期之间查找中位数SQL Server 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！