CODEWITHSUNDEEP
sqlsql-server
Manjay_TBAG
Manjay_TBAG

Reputation: 2245

SQL for calculating sum of sales at particular time every day

Order Table.Reprpesents Order Date and Created is time at which record was inserted

Price   OrderDate   Created
240.00  2021-12-26  2021-12-26 05:17:38.710
13.00   2021-12-26  2021-12-26 04:17:28.810
4.00    2021-12-26  2021-12-26 03:07:50.507
7.00    2021-12-26  2021-12-26 02:07:50.507
119.00  2021-12-26  2021-12-26 01:07:50.507


16      2021-12-25  2021-12-26 23:56:06.810
108.00  2021-12-25  2021-12-26 12:56:02.140
162.00  2021-12-25  2021-12-26 09:50:54.667
40.00   2021-12-25  2021-12-26 05:17:38.710
23.00   2021-12-25  2021-12-26 04:17:28.810
40.00   2021-12-25  2021-12-26 03:07:50.507
7.00    2021-12-25  2021-12-26 02:07:50.507
100.00  2021-12-25  2021-12-26 01:07:50.507

The laltest record in this table is at(Created value) - 05:17:38.710

Select Max(Created) From dbo.TenantOrder -

Output - 2021-12-26 05:17:38.710

At this time on 26th i.e. today's date the sales was

Select SUM(Price) From dbo.TenantOrder Where OrderDate = (Select Max(OrderDate) From dbo.TenantOrder)

At the same time I want to know the sales on 25th

Select SUM(Price) From dbo.TenantOrder Where OrderDate = (Select (Max(OrderDate) -1) From dbo.TenantOrder) and Created <= (Select Max(Created) -1 From dbo.TenantOrder)

I want this kind of record for every day i.e. group by order date and at the max created time. So like at 5PM what was the sales every day

Upvotes: 0

Views: 152

Answers (2)

AliNajafZadeh
AliNajafZadeh

Reputation: 1328

Try following code:

select sum(Price) as SuumPrice
from dbo.TenantOrder
group by OrderDate

and for particular time:

select sum(price) as SuumPrice
from (
    select *
    from dbo.TenantOrder
    where convert(varchar(5),Created ,108) = N'12:10'
) as Dt
group by Dt.OrderDate

Upvotes: 0

Charlieface
Charlieface

Reputation: 72087

It seems you want grouped totals for every day, but only up to the time which marks the same time as the latest row in the table.

In SQL Server you can use the following

DECLARE @time time = (
    SELECT TOP (1)
      CAST(o.OrderDate AS time)
    FROM dbo.TenantOrder o
    ORDER BY o.OrderDate DESC);

SELECT
  date = CAST(o.OrderDate AS date),
  total = SUM(o.price)
FROM TenantOrder o
WHERE CAST(o.OrderDate AS time) <= @time
GROUP BY
  CAST(o.OrderDate AS date);

db<>fiddle

Upvotes: 2

Related Questions