Выгрузить статистику по часам

@Aeroshka · Регистрация: 21.03.2015

Author24 — интернет-сервис помощи студентам

Здравствуйте!

Подскажите, как можно реализовать такое:

id,date,1,2,3,4,...,23
1,2019-05-01,4,3,2,1,...4

Задача звучит так: Выгрузить продажи продукта по часам(count за час) и за 2 месяца

Пока логику вижу такую : Создаем временную таблицу с датами, и с помощью left join приписываем столбцы:

SQL

DECLARE @HOUR INT = 0;
DECLARE @startDate DATE = '2019-05-01';
DECLARE @endDate DATE = '2019-07-01';
/*--*/
DROP TABLE IF EXISTS #repdate
CREATE TABLE #repdate (
repdate DATE)
INSERT INTO #repdate(repdate) SELECT DISTINCT CAST (SaleDate AS DATE) FROM SaleTable WITH(nolock) WHERE SaleDate BETWEEN @startDate AND @endDate GROUP BY CAST (SaleDate AS DATE) ORDER BY CAST (SaleDate AS DATE)
SELECT COUNT rd.reportdate,(ob.SaleId), COUNT (ob1.SaleId),... COUNT (ob23.SaleId)
FROM #repdate rd LEFT JOIN SaleTable ob WITH(nolock) CAST (ob.SaleDate AS DATE) BETWEEN rd.repdate AND DATEADD(DAY,1,Getdate(-1)) AND DATEPART (HOUR, ob.SaleDate = @HOUR)

Но насколько это правильно в целом я очень не уверен.

Может через какую функцию\процедуру?

Спасибо!

Добавлено через 7 минут
Таблица

SQL

SaleTable (
....,
SaleId CHAR(50),
....,
SaleDate datetimesmall(7),
.... )

@qwertehok · 12.07.2019, 20:49

Сообщение от Aeroshka

SaleId CHAR(50)

char? как же по ним потом искать?

Сообщение от Aeroshka

Может через какую функцию\процедуру?

сделать 23 Outer apply

@invm · 12.07.2019, 22:15

T-SQL

declare @SaleTable table (SaleId char(50), SaleDate datetime);
 
insert into @SaleTable
values
 ('1', '20190712 10:00'), ('1', '20190712 13:00'), ('1', '20190712 19:00'),
 ('1', '20190713 17:00'), ('1', '20190713 09:00'), ('1', '20190713 21:00'),
 ('2', '20190712 11:00'), ('2', '20190712 12:00'), ('2', '20190712 19:00');
 
with s(SaleID, SaleDate, SaleHour) as
(
 select SaleId, cast(SaleDate as date), datepart(hour, SaleDate) from @SaleTable
)
select
 SaleId, SaleDate,
 sum(case when SaleHour = 0 then 1 else 0 end) as [0],
 sum(case when SaleHour = 1 then 1 else 0 end) as [1],
 sum(case when SaleHour = 2 then 1 else 0 end) as [2],
 sum(case when SaleHour = 3 then 1 else 0 end) as [3],
 sum(case when SaleHour = 4 then 1 else 0 end) as [4],
 sum(case when SaleHour = 5 then 1 else 0 end) as [5],
 sum(case when SaleHour = 6 then 1 else 0 end) as [6],
 sum(case when SaleHour = 7 then 1 else 0 end) as [7],
 sum(case when SaleHour = 8 then 1 else 0 end) as [8],
 sum(case when SaleHour = 9 then 1 else 0 end) as [9],
 sum(case when SaleHour = 10 then 1 else 0 end) as [10],
 sum(case when SaleHour = 11 then 1 else 0 end) as [11],
 sum(case when SaleHour = 12 then 1 else 0 end) as [12],
 sum(case when SaleHour = 13 then 1 else 0 end) as [13],
 sum(case when SaleHour = 14 then 1 else 0 end) as [14],
 sum(case when SaleHour = 15 then 1 else 0 end) as [15],
 sum(case when SaleHour = 16 then 1 else 0 end) as [16],
 sum(case when SaleHour = 17 then 1 else 0 end) as [17],
 sum(case when SaleHour = 18 then 1 else 0 end) as [18],
 sum(case when SaleHour = 19 then 1 else 0 end) as [19],
 sum(case when SaleHour = 20 then 1 else 0 end) as [20],
 sum(case when SaleHour = 21 then 1 else 0 end) as [21],
 sum(case when SaleHour = 22 then 1 else 0 end) as [22],
 sum(case when SaleHour = 23 then 1 else 0 end) as [23]
from
 s
group by
 SaleId, SaleDate
order by
 SaleId, SaleDate;

@qwertehok 5396 / 4324 / 1060 Регистрация: 29.08.2013 Сообщений: 27,135 Записей в блоге: 3
	12.07.2019, 20:49	2
	Сообщение от Aeroshka SaleId CHAR(50) char? как же по ним потом искать? Сообщение от Aeroshka Может через какую функцию\процедуру? сделать 23 Outer apply 0

	12.07.2019, 22:15