How to have different restrictions to calculate max(Date) and min(Date) in one SELECT statement-CodePudding

I need a query that will return the earliest and latest hour of the transaction for a specific day.

The issue is that I often get the earliest transaction before 5 AM, where I want to include them only if they are later than 5 AM. But with the latest transaction, I want to include every transaction, also ones that happened earlier than 5 AM (due to some shops being open overnight).

Below is what my script looks like. Is there any possibility of giving different restrictions to how I calculate max(s.Date) and min(s.Date)? I thought of creating two select statements but not sure how to connect them within one FROM.

from (
      select l.Name,
             s.ShopID, 
             Day,
             Time,
             s.Date,
             max(s.Date) over (partition by s.Day) as max_date ,
             min(s.Date) over (partition by s.Day) as min_date
      from [Shops].[Transaction].[Transactions] s
      INNER JOIN [Shops].[Location].[Locations] l ON s.ShopID= l.ShopID
      WHERE s.ShopID IN (1, 2, 3, 4, 5) AND Day > 20210131 AND Time <> 4
    ) t

CodePudding user response：

You can implement this in several ways. The easiest in my opinion is to set the condition directly in the aggregate. Like:

from (
      select l.Name,
             s.ShopID, 
             Day,
             Time,
             s.Date,
             max(s.Date) over (partition by s.Day) as max_date ,
             min(IIF(DATEPART(HOUR, s.Date) > 5, s.date, NULL)) over (partition by s.Day) as min_date
      from [Shops].[Transaction].[Transactions] s
      INNER JOIN [Shops].[Location].[Locations] l ON s.ShopID= l.ShopID
      WHERE s.ShopID IN (1, 2, 3, 4, 5) AND Day > 20210131 AND Time <> 4
    ) t

UPDATE: A little clarification. Only strings with a date hour greater than 5 are included in the aggregation here. The value for strings with an hour less than 5 is set to NULL