March 26, 2023 at 4:20 am
I have created a table as per below query
CREATE TABLE [dbo].[mydbtemp](
[Datetime] [datetime] NULL,
[Tagvalue] [int] NULL
) ON [PRIMARY]
GO
Inserted Values Like
insert into MYDBtemp values ('2023-02-03 03:00:00.000', '1')
insert into MYDBtemp values ('2023-02-03 04:00:00.000', '1')
insert into MYDBtemp values ('2023-02-03 05:00:00.000', '2')
insert into MYDBtemp values ('2023-02-03 06:00:00.000', '2')
insert into MYDBtemp values ('2023-02-03 07:00:00.000', '2')
insert into MYDBtemp values ('2023-02-03 08:00:00.000', '1')The Result which is i ma looking for is The Query should return.
'2023-02-03 05:00:00.000' For which i have written the query. but it does not give me the result as expected
SELECT timestamp FROM my_table WHERE tag_value = 1 AND timestamp > ( SELECT MAX(timestamp) FROM my_table WHERE tag_value = 0 AND timestamp BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00' ) AND timestamp BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00'
March 26, 2023 at 11:16 am
Please be more specific than 'it doesn't work'.
We don't have access to your data, nor do we understand the underlying logic behind your query.
If you can provide some sample data, in the form of CREATE TABLE/INSERT statements, along with desired results based on that data, perhaps someone will be able to help you by providing a coded solution.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 26, 2023 at 12:52 pm
I already shared the screenshot of the table and query for the result i am expecting
March 26, 2023 at 1:08 pm
I already shared the screenshot of the table and query for the result i am expecting
If you believe this to be a valid and comprehensive response to my request, good luck and I am out.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 26, 2023 at 1:11 pm
The Result which i ma getting is not as i required it shows the recent value after running the above-mentioned query whereas the result should be as shown in the attached image highlighted in blue.
March 26, 2023 at 1:13 pm
following up on Phil answer - go and read these 2 links.
https://www.sqlservercentral.com/articles/how-to-post-performance-problems-1
and as an IRL example of what Phil is asking for - you Can't build a car if the vender does not supply you with the parts required and with the plans to do it.
March 26, 2023 at 2:25 pm
The table you are selecting from (my_table) is not the table you have created (mydbtemp). Also the column names are different.
I think you meant to put this as your query:
SELECT [Datetime]
FROM [mydbtemp]
WHERE [Tagvalue] = 1
AND [Datetime] > (SELECT MAX([Datetime])
FROM [mydbtemp]
WHERE [Tagvalue] = 0
AND [Datetime] BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00')
AND [Datetime] BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00'
;
In the data you provided there is no [Tagvalue] = 0
So your subquery:
SELECT MAX([Datetime])
FROM [mydbtemp]
WHERE [Tagvalue] = 0
AND [Datetime] BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00'
returns NULL which is why the query doesn't return a result.
March 26, 2023 at 3:15 pm
SELECT [Datetime]
FROM [mydbtemp]
WHERE [Tagvalue] = 1
AND [Datetime] > (SELECT MAX([Datetime])
FROM [mydbtemp]
WHERE [Tagvalue] = 0
AND [Datetime] BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00')
AND [Datetime] BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00'
This is the updated query Please check and revert
March 26, 2023 at 6:58 pm
SELECT [Datetime]
FROM [mydbtemp]
WHERE [Tagvalue] = 1
AND [Datetime] > (SELECT MAX([Datetime])
FROM [mydbtemp]
WHERE [Tagvalue] = 0
AND [Datetime] BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00')
AND [Datetime] BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00'This is the updated query Please check and revert
The subquery still has WHERE [Tagvalue] = 0
Your data has no row WHERE [Tagvalue] = 0
March 27, 2023 at 1:47 pm
SELECT [Datetime]
FROM [mydbtemp]
WHERE [Tagvalue] = 1
AND [Datetime] > (SELECT MAX([Datetime])
FROM [mydbtemp]
WHERE [Tagvalue] = 2
AND [Datetime] BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00')
AND [Datetime] BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00'
March 27, 2023 at 2:30 pm
Your subquesry:
SELECT MAX([Datetime])
FROM [mydbtemp]
WHERE [Tagvalue] = 2
AND [Datetime] BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00'
returns '2023-02-03 07:00:00.000'
So your query is equivalent to this:
SELECT [Datetime]
FROM [mydbtemp]
WHERE [Tagvalue] = 1
AND [Datetime] > '2023-02-03 07:00:00.000'
AND [Datetime] BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00'
So row ('2023-02-03 08:00:00.000', '1') is the only one that satisfies those constraints.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply