按ID和日期分组,但每个组的日期?
问题描述:
我有这个疑问:两列的按ID和日期分组,但每个组的日期?
declare @values table
(
Id int,
Dept varchar(1),
CounterL int,
CounterU int,
InsertDate datetime
)
insert into @values
select 1, 'L', 5, null, '2017-10-28 4:00:00.000'
union
select 1, 'L', 8, null, '2017-10-28 4:00:00.000'
union
select 1, 'U', null, 30, '2017-10-28 3:00:00.000'
union
select 1, 'U', null, 40, '2017-10-28 3:00:00.000'
select id, sum(counterl), sum(counteru) from @values
where (datepart(hh, insertdate) = 4 or datepart(hh, insertdate) = 3)
group by id, cast(InsertDate as date)
下收益的总和,但我希望能够包括这些分组的日期。
的例子会是这个样子:
id ColumnL, ColumnU, Date ValueU ValueL
1 13 70 2017-10-28 '2017-10-28 3:00:00.000' '2017-10-28 4:00:00.000'
总会有一天两个小时,无论是HR 3或4
感谢。
答
这不够吗?
select id, sum(counterl), sum(counteru), cast(InsertDate as date) as dte
from @values v
where datepart(hour, insertdate) in (3, 4)
group by id, cast(InsertDate as date);
我的意思是,你还可以添加时间:
select id, sum(counterl), sum(counteru), cast(InsertDate as date) as dte,
dateadd(hour, 3, cast(InsertDate as date)),
dateadd(hour, 4, cast(InsertDate as date))
from @values v
where datepart(hour, insertdate) in (3, 4)
group by id, cast(InsertDate as date);
,但似乎没有必要。
请注意,我用一个in
替换了or
表达式。而且,我已经写出了hour
,所以代码更易于阅读。
编辑:
基于您的评论,你想有条件聚集:
select id, sum(counterl), sum(counteru), cast(InsertDate as date) as dte,
min(case when dept = 'L' then InsertDate end) as l_insertdate,
min(case when dept = 'U' then InsertDate end) as u_insertdate
from @values v
where datepart(hour, insertdate) in (3, 4)
group by id, cast(InsertDate as date);
答
SELECT DISTINCT Id,
SUM(CounterL) OVER(PARTITION BY ID, CAST(InsertDate AS DATE)) AS [ColumnL],
SUM(CounterU) OVER(PARTITION BY ID, CAST(InsertDate AS DATE)) As [ColumnU],
CAST(InsertDate AS DATE) [Date],
DATEADD(HOUR, 3-DATEPART(HOUR, InsertDate), InsertDate) AS [ValueU],
DATEADD(HOUR, 4-DATEPART(HOUR, InsertDate), InsertDate) AS [ValueL]
FROM @values
WHERE DATEPART(HH, INSERTDATE) IN (3,4)
我有,但我需要知道valueU(70)具有“2017年10月28日3:00:00.000',而L值(13)为'2017-10-28 4:00:00.000'。我在问题中包含了所需的结果集 – rbhat