Home T-SQL max date and min date between two date for each day
Reply: 0

T-SQL max date and min date between two date for each day

user2979
1#
user2979 Published in May 21, 2018, 6:44 pm

First, thanks for your time and your help!

I have two tables:

Table 1

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

Table 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

If I wanted my results to look like

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

vb.....

It works for 1 day, but it does not work every day for 2 days

 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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.314612 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO