CODEWITHSUNDEEP
sqlsql-servert-sql
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3301

Grouping by id and date, but with date of each group?

I have this query:

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)

The following returns the sum of both columns, but I would like to be able to include the date of each of these groupings.

The example would look something like this:

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'

There will always be two hours for the day, either HR 3 or 4.

Thanks.

Upvotes: 0

Views: 316

Answers (2)

IamMOI
IamMOI

Reputation: 1

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)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Isn't this sufficient?

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);

I mean, you can also add the hour:

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);

But that seems unnecessary.

Notice that I replaced the or expressions with a single in. And, I've spelled out hour so the code is easier to read.

EDIT:

Based on your comment, you want conditional aggregation:

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);

Upvotes: 1

Related Questions