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

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

josephanzel
1#
josephanzel Published in 2018-02-12 09:39:24Z

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
Pawan Kumar
2#
Pawan Kumar Reply to 2018-02-12 10:05:58Z

Please try this - Please let us know if you require any modification.

SELECT a.PersId, MIN(salesDate) MinSalesDate , CASE WHEN  MIN(salesDate) = MAX(salesDate) 
THEN NULL ELSE MAX(salesDate)  END MaxSalesDate , a.city FROM Table1 a
FULL JOIN Table2 b ON a.PersId = b.PersId
AND SalesDate > '2017-01-17 09:00:00' and SalesDate < '2017-01-23 09:00:00'
WHERE a.PersId IS NOT NULL
GROUP BY a.PersId,a.city,CAST(salesDate AS DATE)
ORDER BY a.PersId

OUTPUT

PersId      MinSalesDate            MaxSalesDate            city
----------- ----------------------- ----------------------- ----------
1           2017-01-22 08:00:40.000 2017-01-22 20:00:00.000 Tirana
2           2017-01-22 09:00:00.000 NULL                    Tirana
3           NULL                    NULL                    Tirana
1           2017-01-21 09:00:00.000 2017-01-21 18:55:00.000 Tirana

(4 rows affected)

I have updated your query also. Please see.

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' And '2017-01-23 09:00:00'
        ) temp
        WHERE RNKMIN = 1 or RNKMAX = 1
        GROUP BY PersId,CAST(salesDate AS DATE)
    ) T2
    on T1.PersId = T2.PersId

OUTPUT from your query

Id          PersId      MIN_salesDate           MAX_salesDate           City
----------- ----------- ----------------------- ----------------------- ----------
6           1           2017-01-21 09:00:00.000 2017-01-21 09:00:00.000 Tirana
4           1           2017-01-22 20:00:00.000 2017-01-22 20:00:00.000 Tirana
2           2           2017-01-22 09:00:00.000 2017-01-22 09:00:00.000 Tirana
NULL        3           NULL                    NULL                    Tirana

(4 rows affected)
Eva Lai
3#
Eva Lai Reply to 2018-02-12 10:21:53Z

Try this

DECLARE @Table1 TABLE(
PersId INT,
name NVARCHAR(50),
city NVARCHAR(50)
)

DECLARE @Table2 TABLE(
Id INT,
PersId INT,
salesDate NVARCHAR(50)
)

INSERT INTO @Table1 VALUES(1, 'John', 'Tirana')
INSERT INTO @Table1 VALUES(2, 'Leri', 'Tirana')
INSERT INTO @Table1 VALUES(3, 'Adam', 'Tirana')

INSERT INTO @Table2 VALUES(1, 1, '2017-01-22 08:00:40')
INSERT INTO @Table2 VALUES(2, 2, '2017-01-22 09:00:00')
INSERT INTO @Table2 VALUES(3, 1, '2017-01-22 10:00:00')
INSERT INTO @Table2 VALUES(4, 1, '2017-01-22 20:00:00')
INSERT INTO @Table2 VALUES(5, 3, '2017-01-15 09:00:00')
INSERT INTO @Table2 VALUES(6, 1, '2017-01-21 09:00:00')
INSERT INTO @Table2 VALUES(7, 1, '2017-01-21 10:00:00')
INSERT INTO @Table2 VALUES(8, 1, '2017-01-21 18:55:00')



SELECT T1.PersId, GroupTable.MinSalesDate, GroupTable.MaxSalesDate, T1.city
FROM @Table1 AS T1
LEFT JOIN
(SELECT max(salesDate) as 'MaxSalesDate', CASE WHEN min(salesDate) <> max(salesDate) THEN min(salesDate) ELSE NULL END AS 'MinSalesDate', PersId
FROM @Table2
WHERE salesDate > '2017-01-17 09:00:00' and SalesDate < '2017-01-23 09:00:00'
GROUP BY DATEPART(day, salesDate) , PersId) AS GroupTable
ON T1.PersId = GroupTable.PersId

Result:

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO