CODEWITHSUNDEEP
sqlsql-server
ayman
ayman

Reputation: 29

Getting hourly sales for each brand sql

I have tow brands brand1 and brand2 i am looking for getting hourly sales for each brand.

I have tried to do it using the below query but it returns the below output :

Query

WITH MyGroupCte
AS (SELECT
  OrderDate,
  Brands.BrandID,
  Brands.BrandName,
  SUM((OrderDetails.Quantity * OrderDetails.UnitPrice)) + Orders.DeliveryCharge - ((SUM((OrderDetails.Quantity * OrderDetails.UnitPrice))) * Orders.Discount / 100) AS Amount,
  COUNT(Orders.OrderID) AS Orders
FROM dbo.Orders
INNER JOIN dbo.Outlets
  ON dbo.Orders.OutletID = dbo.Outlets.OutletID
INNER JOIN dbo.OrderDetails
  ON dbo.OrderDetails.OrderID = dbo.Orders.OrderID
INNER JOIN dbo.Brands
  ON dbo.Brands.BrandID = dbo.Outlets.BrandID
WHERE dbo.Orders.OrderStatus = 'Closed'
AND dbo.Orders.OrderDate BETWEEN '06/May/2017 14:00:00.000' AND '06/May/2017 16:00:00.000'

GROUP BY Orders.OrderID,
         dbo.Orders.DeliveryCharge,
         Orders.Discount,
         Brands.BrandID,
         Brands.BrandName,
         Orders.OrderDate)
SELECT
  BrandName AS Brand,
  OrderDate as [Date-Time],
  COUNT(Orders) AS Orders,
  SUM(Amount) AS Amount
FROM MyGroupCte
GROUP BY OrderDate,
         BrandName
ORDER BY Amount DESC

Output 

Brand   Date-Time               Orders   Amount
brand1  2017-05-06 14:04:52.000   1      530.00
brand1  2017-05-06 14:05:06.000   1      448.00
brand2  2017-05-06 15:27:21.000   1      347.00
brand2  2017-05-06 15:52:25.000   1      254.00
brand2  2017-05-06 16:06:25.000   1      100.00

Instead of that i am looking for getting the output in belowformat

Brand   Date-Time               Orders   Amount
brand1  2017-05-06 14:00:00.000   2      978.00
brand2  2017-05-06 15:00:00.000   2      601.00
brand2  2017-05-06 16:00:00.000   2      100.00

Upvotes: 2

Views: 55

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use DATEADD(hour, DATEDIFF(hour, 0, OrderDate), 0) to truncate datetime to the hour and use it for grouping.

SELECT 
  BrandName AS Brand,
  DATEADD(hour, DATEDIFF(hour, 0, OrderDate), 0) as [Date-Time],
  COUNT(Orders) AS Orders,
  SUM(Amount) AS Amount
FROM MyGroupCte
GROUP BY BrandName, DATEADD(hour, DATEDIFF(hour, 0, OrderDate), 0)

Upvotes: 3

Related Questions