I am getting the max date for a specific field(shopNO). Would it be faster if I add another condition just to search for daily requests in the where clause?
SELECT
MAX([RequestDate]) AS[Latest Date]
FROM[Confirm]
WHERE [ShopNo] = '111111'
Yes, it should help a lot if you add a date check. Greater-than doesn't really work here, you need gt_eq.
The appropriate code would be something like this:
WHERE [ShopNo] = '111111' and RequestDate >= CONVERT(DATE,GETDATE())