Sqlserver 每日訂單半小時資料統計

標籤：where   lock   訂單   var   nvarchar   char   sqlserver   art   sele   

SELECT dttime, SUM(a.price) ‘銷售額‘,count(1) ‘訂單數‘FROM (SELECT CASE                WHEN DATEPART(mi, o.create_at) < 30                THEN                    CONVERT(DATETIME,CONVERT(NVARCHAR(50), create_at, 23) +‘ ‘+CONVERT(VARCHAR(50),DATEPART(hh, create_at))+‘:00:00‘)                ELSE                    CONVERT(DATETIME,CONVERT(NVARCHAR(50), create_at, 23) +‘ ‘+CONVERT(VARCHAR(50),DATEPART(hh, create_at))+‘:30:00‘)                END ‘dttime‘,*                            FROM dbo.[order](NOLOCK) o         WHERE create_at >= ‘2018-07-11‘ and create_at<‘2018-07-12‘) aGROUP BY dttime

 

Sqlserver 每日訂單半小時資料統計