How to get MAX datetime

Transact-SQL
,
1

Hello,

I am getting the MAX datetime as follows. I wonder if there is a better way in terms of better performance etc.

SELECT cf.referenceId, cc.shopNo, cc.safeNo, cc.cashierNo, cc.ConfirmCancelDateTime, cf.productCode, cf.productDescription, cf.quantity, cf.unitPrice, cf.totalPrice, cc.status 
FROM [GameConfirmResponses] cf 
INNER JOIN [ConfirmCancels] cc ON cf.referenceId = cc.referenceId 
WHERE cf.purchaseStatusDate>='2019-09-19' AND cf.purchaseStatusDate<'2019-09-20'
and cc.ConfirmCancelDateTime=(
SELECT MAX(ConfirmCancelDateTime) 
FROM [ConfirmCancels])

Thank you.

2

hi

for performance ..

SELECT  Max(confirmcanceldatetime) 
FROM [ConfirmCancels])
compute this seperately from the query .. and then use in query 
lets say for example is '2019-01-09 12:09:00'....    


SELECT     cf.referenceid, 
           cc.shopno, 
           cc.safeno, 
           cc.cashierno, 
           cc.confirmcanceldatetime, 
           cf.productcode, 
           cf.productdescription, 
           cf.quantity, 
           cf.unitprice, 
           cf.totalprice, 
           cc.status 
FROM       [GameConfirmResponses] cf 
INNER JOIN [ConfirmCancels] cc 
ON         cf.referenceid = cc.referenceid 
WHERE      cf.purchasestatusdate>='2019-09-19' 
AND        cf.purchasestatusdate<'2019-09-20' 
AND        cc.confirmcanceldatetime='2019-01-09 12:09:00'
3

Two seperate queries will not fit into my situation. Does my query seem OK?

4

Query seems Ok ... yes

some things you can do ...
one is create indexes

second is filter the data first before max ...
SELECT *
FROM (SELECT cf.referenceid,
cc.shopno,
cc.safeno,
cc.cashierno,
cc.confirmcanceldatetime,
cf.productcode,
cf.productdescription,
cf.quantity,
cf.unitprice,
cf.totalprice,
cc.status
FROM [gameconfirmresponses] cf
INNER JOIN [confirmcancels] cc
ON cf.referenceid = cc.referenceid
WHERE cf.purchasestatusdate >= '2019-09-19'
AND cf.purchasestatusdate < '2019-09-20') temp
WHERE confirmcanceldatetime = (SELECT Max(confirmcanceldatetime)
FROM [confirmcancels])

5

There is a problem in my original query. I need to get the MAX confirmcanceldatetime for every referenceid in the confirmcancels table. My query just gets the MAX confirmcanceldatetime , NOT checking the referenceid.

6

here you will have to join with referenceid :slight_smile: :slight_smile:

(SELECT Max (confirmcanceldatetime)
FROM [confirmcancels]
WHERE confirmcancels.referenceid = gameconfirmresponses.referenceid)

7

The multi-part identifier "gameconfirmresponses.referenceid" could not be bound.

8

try this .. !!

(SELECT Max (confirmcanceldatetime)
FROM [confirmcancels]
WHERE confirmcancels.referenceid = cf.referenceid)

9

I think this way works;

(SELECT Max (confirmcanceldatetime)
FROM [confirmcancels] c
WHERE c.referenceid = temp.referenceid)
10

ok if it works great !!!

:slight_smile:

:+1:

11

For performance:

SELECT cf.referenceId, cc.shopNo, cc.safeNo, cc.cashierNo, 
    cc.ConfirmCancelDateTime, cf.productCode, cf.productDescription, 
    cf.quantity, cf.unitPrice, cf.totalPrice, cc.status 
FROM [GameConfirmResponses] cf 
INNER JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY referenceId 
        ORDER BY ConfirmCancelDateTime DESC) AS row_num
    FROM [ConfirmCancels]
) AS cc ON cf.referenceId = cc.referenceId AND cc.row_num = 1
WHERE cf.purchaseStatusDate>='20190919' AND cf.purchaseStatusDate<'20190920'
12

I will try and let you know, thank you.

13

How to change the query if I want to get the MIN?

14

here i changed ... removed DESC ...this shoud give you min

SELECT *, ROW_NUMBER() OVER(PARTITION BY referenceId 
        ORDER BY ConfirmCancelDateTime DESC) AS row_num

to
SELECT *, ROW_NUMBER() OVER(PARTITION BY referenceId
ORDER BY ConfirmCancelDateTime ) AS row_num

SELECT cf.referenceid, 
       cc.shopno, 
       cc.safeno, 
       cc.cashierno, 
       cc.confirmcanceldatetime, 
       cf.productcode, 
       cf.productdescription, 
       cf.quantity, 
       cf.unitprice, 
       cf.totalprice, 
       cc.status 
FROM   [gameconfirmresponses] cf 
       INNER JOIN (SELECT *, 
                          Row_number() 
                            OVER( 
                              partition BY referenceid 
                              ORDER BY confirmcanceldatetime ) AS row_num 
                   FROM   [confirmcancels]) AS cc 
               ON cf.referenceid = cc.referenceid 
                  AND cc.row_num = 1 
WHERE  cf.purchasestatusdate >= '20190919' 
       AND cf.purchasestatusdate < '20190920'
15

Thank you.

16

Now I needed to JOIN another table in order to get some values. Here is the new query. It returns the desired output. Anything to make it better? What is your opinion?

SELECT cf.referenceid, ISNULL(cc.shopno,gr.shopNo) AS shopno, ISNULL(cc.safeno,gr.safeNo) AS safeno, ISNULL(cc.cashierno,gr.cashierNo ) AS cashierno, cc.confirmcanceldatetime, cf.productcode, cf.productdescription, cf.quantity, cf.unitprice, cf.totalprice, ISNULL(cc.status, 0) As status
FROM[gameconfirmresponses] cf LEFT JOIN(SELECT *, Row_number() OVER(partition BY referenceid ORDER BY confirmcanceldatetime) AS row_num 
FROM[confirmcancels]) AS cc ON cf.referenceid = cc.referenceid AND cc.row_num = 1
JOIN [GameRequests] AS gr ON gr.referenceId = cf.referenceId
 WHERE  cf.purchasestatusdate >= '20190928' and cf.purchasestatusdate < '20190929'