SQL for Calclate sum of Sales At particular time every day-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Try following code:

selec 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