T-SQL最大日期和两个日期之间的最小日期

First, thanks for your time and your help!

首先，感谢您的时间和帮助！

I have two tables:

我有两张桌子：

Table 1

表格1

PersId     name       lastName   city
---------------------------------------
1          John       Smith      Tirana
2          Leri       Nice       Tirana
3          Adam       fortsan    Tirana

Table 2

表2

Id       PersId      salesDate
--------------------------------------------
1         1          2017-01-22 08:00:40 000
2         2          2017-01-22 09:00:00 000
3         1          2017-01-22 10:00:00 000
4         1          2017-01-22 20:00:00 000
5         3          2017-01-15 09:00:00 000
6         1          2017-01-21 09:00:00 000
7         1          2017-01-21 10:00:00 000
8         1          2017-01-21 18:55:00 000

I would like to see the first recent sales between two dates according to each city for each day I want to bring it empty if I do not have a sale

我希望看到根据每个城市的两个日期之间的第一次近期销售，如果我没有销售的话，我想把它带空

 SalesDate > '2017-01-17 09:00:00 000' 
 and SalesDate < '2017-01-23 09:00:00 000'

Table 2, id = 5 because the record is not in the specified date range

表2，id = 5，因为记录不在指定的日期范围内

If I wanted my results to look like

如果我希望我的结果看起来像

Id      PersId     MinSalesDate                 MaxSalesDate             City 
-----------------------------------------------------------------------------
1         1        2017-01-22 08:00:40 000      2017-01-22 20:00:00 000 Tirana
2         2        2017-01-22 09:00:00 000      null                   Tirana
3         3        null                         null                   Tirana
4         1        2017-01-21 09:00:00 000      2017-01-21 18:55:00 000   Tirana

2 个解决方案

#1

You dont identify how to get ID in the result. You appear to just want Row_Number(). I will leave that out, but this should get you started. You may have to work out conversion issues in the data range check, and I havent checked the query for syntax errors, I will leave that to you.

你不知道如何在结果中获得ID。您似乎只想要Row_Number（）。我会把它留下来，但这应该让你开始。您可能必须在数据范围检查中解决转换问题，并且我没有检查查询的语法错误，我会留给您。

Select T1.PersId, City
     , Min(T2.salesDate) MinSalesDate
     , Max(T2.salesDate) MaxSalesDate
   From Table1 T1
   Left Join Table2 T2 
     On T1.PersId = T2.PersId 
    And T2.salesDate Between '2017-01-17 09:00:00 000' And < '2017-01-23 09:00:00 000'
   Group BY T1.PersId, T2.City

#2

Try the following using row_number to get min and max sale dates:

使用row_number尝试以下操作以获取最小和最长销售日期：

        SELECT 
            T2.Id, T1.PersId, T2.MIN_salesDate, T2.MAX_salesDate, T1.City
        FROM Table1 T1
        LEFT JOIN
        (
            SELECT MIN(Id) as Id, PersId, MIN(salesDate) as MIN_salesDate, MAX(salesDate) as MAX_salesDate 
            FROM
            (
                SELECT 
                     *
                    ,ROW_NUMBER() OVER (PARTITION BY PersId ORDER BY salesDate ASC) as RNKMIN
                    ,ROW_NUMBER() OVER (PARTITION BY PersId ORDER BY salesDate DESC) as RNKMAX 
                FROM Table2 T2 
                WHERE salesDate Between '2017-01-17 09:00:00 000' And '2017-01-23 09:00:00 000'
            ) temp
            WHERE RNKMIN = 1 or RNKMAX = 1
            GROUP BY PersId
        ) T2
        on T1.PersId = T2.PersId