I have tried a couple of where clause statements for date yesterday. No matter what I put in there it provides results for the entire table.
Here is the query;
USE [nms_rt]
GO
SELECT [Timestamp] AS 'DateTime'
,[Name] AS 'Location Name'
,[AtcsLine] AS 'Line'
,[AtcsGroup] AS 'Group'
,[Error] AS 'Errors'
,COUNT([Error]) over (partition by [Name] order by [Timestamp]) as 'Errors Total'
FROM [nms_rt].[dbo].[fail_flush] WITH (NOLOCK)
WHERE Timestamp = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
and [Error] LIKE 'Delivery Flushed'
OR [Error] LIKE '%offline%'
OR [Error] LIKE '%online%'
GROUP BY [Name], [Timestamp], [AtcsLine], [AtcsGroup], [Error]
The time field is a smalldatetime data type.
What am I missing?
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
October 28, 2022 at 4:15 pm
Parentheses.
You probably want
and ([Error] LIKE 'Delivery Flushed'
OR [Error] LIKE '%offline%'
OR [Error] LIKE '%online%')
First of all you need parentheses around the OR conditions. Second since timestamp is a datetime you need to have >= yesterday 'date' and < today 'date'. Try something like this
SELECT [Timestamp] AS 'DateTime'
,[Name] AS 'Location Name'
,[AtcsLine] AS 'Line'
,[AtcsGroup] AS 'Group'
,[Error] AS 'Errors'
,COUNT([Error]) over (partition by [Name] order by [Timestamp]) as 'Errors Total'
FROM [nms_rt].[dbo].[fail_flush] WITH (NOLOCK)
WHERE Timestamp >= CAST(DATEADD(DAY, -1, GETDATE()) AS DATE) and Timestamp < CAST(GETDATE() AS DATE)
and ([Error] LIKE 'Delivery Flushed'
OR [Error] LIKE '%offline%'
OR [Error] LIKE '%online%'
)
GROUP BY [Name], [Timestamp], [AtcsLine], [AtcsGroup], [Error]
October 28, 2022 at 5:50 pm
Thank you! that is what I needed. I forgot to add the () around the where clause after the datetime clause.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply