CODEWITHSUNDEEP
mysqlsqlsql-server
jackfrost5234
jackfrost5234

Reputation: 229

Date diff between 2 max dates, per day over a given time period

I'm trying to write a sql query to return the difference between a closing time and the final sale of the day for a given location, per day.

The DB schema is something like:

Store_Closing_table(storeCloseTime, storeId) store_sale_table(saleTime, storeID)

I've written the following(pseudo code):

select max(storeCloseTime), 
max(saleTime), datediff(mi, max(saletime), 
max(storeCloseTime)) as timeDifference, storeID 
from store_closing_table a
inner join store_sale_table b
on a.storeid = b.storeid
group by storeid, convert(date, saletime), convert(date, storeCloseTime)

Which returns the time difference between the final sale of the day and the store's closing time, but only for the most recent date(i.e the max date).

I'm not sure how to get the max sale date per day and the max store closing time per day(there are situations where the store 'closes' more than once in a day) and then get the datediff between the two.

Any suggestions would be appreciated.

EDIT: I modified the query to include group by clauses, which gets me closer, but I'm getting the difference between each store sale and each store closing time.

I.e. store has 3 sales over 3 days. I'm getting close 1 - sale a, close 1 - sale b, close 1 - sale c, close 2 - sale a, close 2 -sale b, close 2 - sale c, close 3 - sale a, close 3 - sale b, close 3 - sale c.

Any ideas?

Upvotes: 0

Views: 299

Answers (1)

Ahmed Saeed
Ahmed Saeed

Reputation: 851

please try this.

WITH cte
AS ( 
       select StoreID,
              CONVERT(DATE, storeCloseTime) AS [Date], 
              max(storeCloseTime) as storeCloseTime,
              max(saleTime) as SaleTime,
         FROM Store_closing_table AS a
         JOIN Store_sale_table AS b ON a.storeid = b.storeid
        GROUP BY StoreID, CONVERT(DATE, storeCloseTime)
    )
       SELECT StoreID, [Date], StoreCloseTime, SaleTime
              datediff(mi, saletime, storeCloseTime) as timeDifference

Upvotes: 0

Related Questions