pull records at 15 min intervals

[LadyDee]

Hello,



in SSMS:

My database has 3 columns . I can get the timestamp column OK, but I get stuck when trying to retrieve the rest of the record
This code works to get only the timestamp:

select min([timestamp]) as T2
from dbo.NM_Data where timestamp between '2020-01-01 00:00:00' and '2023-10-02 00:00:00'
group by DATEpart(year, [timestamp]),
DATEpart(month, [timestamp]),
DATEpart(day, [timestamp]),
DATEpart(hour, [timestamp]),
(DATEpart(minute, [timestamp]) / 15)
order by T2;

I tried a few things that didn't work - I get aggregate errors.
How do I get col2 & col 3 into the query too??

col 1                   col2        col3
10/15/2022 9:15:10 PM    8          A
10/15/2022 9:30:10 PM    6          A
10/15/2022 9:45:10 PM    3          C
10/15/2022 10:00:10 PM   9          B

Thanks

[madpiet]

Do you mean GROUP them into 15 minute intervals?

SELECT ...
FROM (SELECT [Date] As SaleDate
	,SaleTime
	,mins = DATEDIFF(minute,'1899-12-30',[SaleTime])
	,bin = DATEDIFF(minute,'1899-12-30',[SaleTime])/15
FROM SquareSales) ss
WHERE bin = 2;

(basically, the time stamp from the same date at midnight.)

[NoellaG]

What do you want to do with the rest of the columns? All columns in a groups query need to be part of the group by instruction or be included in a grouping function (min;max; sum; avg; ...). If you want a mix of grouping results and individual records: look at the windowing functions using over(partion by ...)

[madpiet]

Hello,

in SSMS:

My database has 3 columns . I can get the timestamp column OK, but I get stuck when trying to retrieve the rest of the record
This code works to get only the timestamp:

select min([timestamp]) as T2
from dbo.NM_Data where timestamp between '2020-01-01 00:00:00' and '2023-10-02 00:00:00'
group by DATEpart(year, [timestamp]),
DATEpart(month, [timestamp]),
DATEpart(day, [timestamp]),
DATEpart(hour, [timestamp]),
(DATEpart(minute, [timestamp]) / 15)
order by T2;

I tried a few things that didn't work - I get aggregate errors.
How do I get col2 & col 3 into the query too??

col 1                   col2        col3
10/15/2022 9:15:10 PM    8          A
10/15/2022 9:30:10 PM    6          A
10/15/2022 9:45:10 PM    3          C
10/15/2022 10:00:10 PM   9          B

Thanks

Could you post a CREATE TABLE script and an INSERT script so that we have some data? And then post the expected output? That would make this much easier to answer. But like Noella said already, you may want a windowing function to do it, because you don't have to aggregate the data. Otherwise, you'd have to create a totals query, and then join that totals query back to the original table and include the other columns that weren't part of the aggregation.

[LadyDee]

Do you mean GROUP them into 15 minute intervals?

SELECT ...
FROM (SELECT [Date] As SaleDate
    ,SaleTime
    ,mins = DATEDIFF(minute,'1899-12-30',[SaleTime])
    ,bin = DATEDIFF(minute,'1899-12-30',[SaleTime])/15
FROM SquareSales) ss
WHERE bin = 2;

(basically, the time stamp from the same date at midnight.)

Yes, I have a timestamp for every minute (huge file). I only want to report on every fifteen minute interval - on every quarter of an hour when minute is: 0,15,30,45.
The timestamp is pulling correctly on every quarter of an hour, but the remaining fields of the record (col2 & col3) gets an error when I place them in the select.
Thanks

[LadyDee]

Hi. This is the expected output:

col 1                   col2        col3
10/15/2022 9:15:10 PM    8          A
10/15/2022 9:30:10 PM    6          A
10/15/2022 9:45:10 PM    3          C
10/15/2022 10:00:10 PM   9          B

This is what i am getting now with the code I showed initially:

col 1    
10/15/2022 9:15:10 PM  
10/15/2022 9:30:10 PM   
10/15/2022 9:45:10 PM    
10/15/2022 10:00:10 PM

I need col2 & col3 to be included in the query.
If I add them to the select I get a group by error.

[LadyDee]

Hi NoellaG,
I don't know what you mean by windowing functions.
Is it a command? Can you give me a link or an example?

Thanks

[LadyDee]

Do you mean GROUP them into 15 minute intervals?

SELECT ...
FROM (SELECT [Date] As SaleDate
    ,SaleTime
    ,mins = DATEDIFF(minute,'1899-12-30',[SaleTime])
    ,bin = DATEDIFF(minute,'1899-12-30',[SaleTime])/15
FROM SquareSales) ss
WHERE bin = 2;

(basically, the time stamp from the same date at midnight.)

Hi madpiet,
This code look close but how do I add the remainder of the record - col2 & col3?
I need to pull it when minute is 0,15,30,45. like your code says bin =15, but why then does it say WHERE bin = 2?
This is the intended output. Currently only col1 is coming out through the query I initially posted:
I have hundreds of thousands rows of data:

col 1                   col2        col3
10/15/2022 9:15:10 PM    8          A
10/15/2022 9:30:10 PM    6          A
10/15/2022 9:45:10 PM    3          C
10/15/2022 10:00:10 PM   9          B

[LadyDee]

Do you want test data?

[madpiet]

Do you want test data?

The CREATE TABLE and INSERT statements to give us some data to work with would help a lot. (Don't need all the data, only enough to reproduce the problem).

[LadyDee]

The CREATE TABLE and INSERT statements to give us some data to work with would help a lot. (Don't need all the data, only enough to reproduce the problem).

I have it prepared in an excel file. I hope this helps. zip is in Attachments

[madpiet]

What's the expected output? And what are the column names?

[LadyDee]

the intended output is

Timestamp                 value       code
10/15/2022 9:15:10 PM        8          A
10/15/2022 9:30:10 PM        6          A
10/15/2022 9:45:10 PM        3          C
10/15/2022 10:00:10 PM       9          B

Thank you

[Minty]

Given your test data in a table called Test_1 With Fields Called [Timestamp], NValue, TxtCode, the following does what you want.

SELECT 
    [Timestamp],  NValue, TxtCode
    FROM
    (
    SELECT 
    ROW_NUMBER() OVER(Partition By Interval_15  Order BY [TimeStamp] ) as RowNum,*

    FROM (
        SELECT 
        DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, '2020-01-01', [TIMESTAMP]) / CAST(15 AS DECIMAL)) * 15, '2020-01-01') AS Interval_15,* 
        From 
        Test_1
        ) as q1

    ) as q2
  Where RowNum = 1

Results

Timestamp               NValue          TxtCode
2021-01-11 11:30:09.000    0.9014763960    A
2021-01-11 11:45:09.000    0.8264825220    B
2021-01-11 12:00:09.000    0.0000000000    C
2021-01-11 12:15:09.000    0.0000000000    A

The Row_Number is a T_SQL window function. If you just run the centre query (q1) on it's own, then the second one (q2) with the first one it will become obvious how they work.

You could probably mash it all into one massive Select statement but it's easier to see what is happening with the sub queries.

[NoellaG]

About SQL windowing functions: the included zip file contains my basic SQL training - you'll find examples of windowing functions on page 33