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,
July 19, 2022 at 1:08 pm
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
July 19, 2022 at 1:31 pm
I want the first row, or if nothing found put out a message in table no record recorded.
thx.
July 19, 2022 at 1:36 pm
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
July 19, 2022 at 4:52 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
July 19, 2022 at 5:51 pm
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..
July 19, 2022 at 8:23 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
July 19, 2022 at 8:49 pm
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?
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) A socialist is someone who will give you the shirt off *someone else's* back.
July 20, 2022 at 10:32 am
Thanks Scott just what was needed.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply