pull data every 3 hrs

Question

Post reply

pull data every 3 hrs

Bruin

SSCrazy

Points: 2058

July 19, 2022 at 12:02 pm

Go to Answer

#4065252

I'm trying to pull data every 3hrs between 2 dates. I need to just get one record for each machine every 3rd hour between two dates.

I have this query which reports back midnight the day before thru today, but I'm looking for a single

CREATE TABLE [dbo].[mach_data](
[dtstamp] [datetime] NOT NULL,
[equipment_id] [varchar](6) NOT NULL,
[temperature] [int] NULL,
[status] [int] NULL,
 CONSTRAINT [PK_mact_data_dtstamp] PRIMARY KEY CLUSTERED 
(
[dtstamp] ASC,
[equipment_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

record every 3 hours during that stretch

not all records.

example output: has fields dtstamp,equipment_id,temperature

7-18-2022 00:00 ext3 2500

7-18-2022 03:00 ext3 1500

7-18-2022 06:00 ext3 2000

SELECT    *
FROM      mach_data
WHERE     [dtstamp] BETWEEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()-1), 0) + '00:00'
                 AND DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) + '00:00'

Thanks,

ScottPletcher

SSC Guru

Points: 101095
More actions
July 19, 2022 at 8:52 pm
Answer
#4065591
Ok, fair enough
```
SELECT 
    dtstamp, equipment_id, temperature
FROM (
    SELECT *, 
        ROW_NUMBER() OVER(PARTITION BY equipment_id, DATEDIFF(HOUR, 0, dtstamp) / 3 * 3 ORDER BY dtstamp) AS row_num
    FROM dbo.mach_data
    WHERE dtstamp >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) -1, 0) AND
        dtstamp < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
) AS derived
WHERE row_num = 1
ORDER BY dtstamp
```
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

So what row, in that 3 hour window, do you want? the "first" row, that being the one with the lowest time that "bucket"? The last row, a arbitrary row, something else? If there is no row in that bucket do you want a row still, or not?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Bruin SSCrazy Points: 2058 More actions · Answer 2

I want the first row, or if nothing found put out a message in table no record recorded.

thx.

Thom A SSC Guru Points: 99130 More actions · Answer 3

Some sample data (for the table you've provided DDL for), and expected results (for your different scenarios) will really help here. I wouldn't want to guess the wrong thing.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ScottPletcher SSC Guru Points: 101095 More actions · Answer 4

Maybe?

SELECT 
    dtstamp, equipment_id, temperature
FROM (
    SELECT *, 
        ROW_NUMBER() OVER(PARTITION BY DATEDIFF(HOUR, 0, dtstamp) / 3 * 3 ORDER BY dtstamp) AS row_num
    FROM dbo.mach_data
    WHERE dtstamp >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) -1, 0) AND
        dtstamp < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
) AS derived
WHERE row_num = 1
ORDER BY dtstamp

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Bruin SSCrazy Points: 2058 More actions · Answer 5

Scott,

That worked but how can I pull for all equipment_id's using above query. This pulled back just one ONE piece off equipment.

Thanks for response..

ScottPletcher SSC Guru Points: 101095 More actions · Answer 6

You stated nothing about equipment_id in your original post. How am I supposed to include logic for your equipment_ids when you tell us nothing about them?!

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

kaj SSCommitted Points: 1605 More actions · Answer 7

ScottPletcher wrote:

You stated nothing about equipment_id in your original post. How am I supposed to include logic for your equipment_ids when you tell us nothing about them?!

Easy now! 🙂

He did say " I need to just get one record for each machine every 3rd hour between two dates." I think equipment_id equals a machine. 🙂

And I belive your query just needs to include the equipment_id in the PARTITION BY, doesn't it?

This reply was modified 2 years, 4 months ago by kaj. Reason: typo: you --> your
This reply was modified 2 years, 4 months ago by kaj.

Bruin SSCrazy Points: 2058 More actions · Answer 8

Bruin

SSCrazy

Points: 2058

July 20, 2022 at 10:32 am

#4065853

Thanks Scott just what was needed.