300 300 - 1 month ago 6
SQL Question

How to add select in a group by SQL query?

Table: I have a database table mytable1 in SQL Server 2012. Table definition is

Column_name Type Length Nullable
ts datetime 8 no
s_no int 4 no
calls int 4 yes


DDL: And created it using

CREATE TABLE mytable1(
ts DATETIME NOT NULL,
s_no INT NOT NULL,
calls INT
);


Populate data

INSERT INTO mytable1 (ts, s_no, calls)
VALUES
('2016-10-14 10:04:01.000', 3, 56),
('2016-10-14 10:04:01.000', 4, 145),
('2016-10-14 10:09:00.000', 3, 143),
('2016-10-14 10:09:00.000', 4, 329),
('2016-10-14 10:14:01.000', 3, 0),
('2016-10-14 10:14:01.000', 4, 49),
('2016-10-14 10:19:00.000', 3, 6),
('2016-10-14 10:19:00.000', 4, 16),
('2016-10-14 10:24:01.000', 3, 22),
('2016-10-14 10:24:01.000', 4, 28),
('2016-10-14 10:29:00.000', 3, 4),
('2016-10-14 10:29:00.000', 4, 7),
('2016-10-14 10:34:00.000', 3, 14),
('2016-10-14 10:34:00.000', 4, 9),
('2016-10-14 10:38:59.000', 3, 39),
('2016-10-14 10:38:59.000', 4, 391),
('2016-10-14 10:44:01.000', 3, 3),
('2016-10-14 10:44:01.000', 4, 31),
('2016-10-14 10:49:01.000', 3, 116),
('2016-10-14 10:49:01.000', 4, 52),
('2016-10-14 10:54:00.000', 3, 75),
('2016-10-14 10:54:00.000', 4, 8),
('2016-10-14 10:59:00.000', 3, 16),
('2016-10-14 10:59:00.000', 4, 8),
('2016-10-14 11:04:01.000', 3, 23),
('2016-10-14 11:04:01.000', 4, 13);


I am trying to divide timestamp into 30 minute window and then select hisghest timestamp from each 30 minute window. Also I want to select all the different s_no values for the highest of 30 minute timestamp.

Existing Query: I have a query that does this

select m.s_no, m.bucket_window, max(m.ts) ts
from (
select m.*, datepart(hour, m.ts)*2 + floor(datepart(minute, m.ts)/30) bucket_window
from mytable1 m
where m.ts >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
) m
group by m.s_no, m.bucket_window;


Result: It gives result as

s_no bucket_window ts
3 20 2016-10-14 10:29:00.000
4 20 2016-10-14 10:29:00.000
3 21 2016-10-14 10:59:00.000
4 21 2016-10-14 10:59:00.000
3 22 2016-10-14 11:04:01.000
4 22 2016-10-14 11:04:01.000


Now I want to improve this query and add column calls in the above result. This column should have value from calls column of the mytable1 table where the combination of s_no and ts from above result matches with combination of s_no and ts from mytable1 table.

Expected Result: So result I want now is

s_no bucket_window ts calls
3 20 2016-10-14 10:29:00.000 4
4 20 2016-10-14 10:29:00.000 7
3 21 2016-10-14 10:59:00.000 16
4 21 2016-10-14 10:59:00.000 8
3 22 2016-10-14 11:04:01.000 23
4 22 2016-10-14 11:04:01.000 13


I tried using join but can't put the query together with correct syntax:

What I tried:

select m.s_no, m.bucket_window, max(m.ts) ts, i.calls
from (
select m.*, datepart(hour, m.ts)*2 + floor(datepart(minute, m.ts)/30) bucket_window
from mytable1 m
where m.ts >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
) m
LEFT JOIN mytable1 i
ON max(m.ts) = i.ts--OR (m.ts) = i.ts
group by m.s_no, m.bucket_window, i.calls


Please suggest me how I can modify this existing query to get the expected result.

As this existing query is being used in production for long time and is used as sub query in other dynamically generated queries, I don't want to change it entirely to get expected result.

Answer

Try this one

SELECT M1.*,
(SELECT calls From mytable1 M2 where M2.ts=M1.ts and M2.s_no=M1.s_no) as calls
FROM
(
    select m.s_no, m.bucket_window, max(m.ts) ts
    from (
        select m.*, datepart(hour, m.ts)*2 + floor(datepart(minute, m.ts)/30) bucket_window
        from mytable1 m
        where m.ts >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
    ) m
    group by m.s_no, m.bucket_window
) M1