nopCommerce Report for Top 10 Gross Sales Days

Declare @current_utc_offset int

IF ServerProperty('EngineEdition') >= 5

Begin

set @current_utc_offset = (select convert(int, left(current_utc_offset,3)) AS current_utc_offset from sys.time_zone_info Where Name = 'US Eastern Standard Time')

End

PRINT @current_utc_offset

SELECT TOP 10 SUM([OrderTotal]) as totalSales, Count([Id]) AS Orders, dateadd(hour, @current_utc_offset, dateadd(hour, datediff(hour, 0, convert(date, [CreatedOnUtc])), 0))

FROM [dbo].[Order]

GROUP BY dateadd(hour, @current_utc_offset, dateadd(hour, datediff(hour, 0, convert(date, [CreatedOnUtc])), 0))

ORDER BY SUM([OrderTotal]) desc