Hello All,
I have following table structure with more than 10 lakh records and i want to find out MAX Rainfall by every day with group by AWS_Id and AddeedOn as Date. I have tried but gives me error like
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
CREATE TABLE [dbo].[AWS_Responce2](
[Id] [int] IDENTITY(1,1) NOT NULL,
[AWS_Id] [nvarchar](50) NULL,
[Temperature] [decimal](10, 4) NULL,
[Humidity] [decimal](10, 4) NULL,
[Rainfall] [decimal](10, 4) NULL,
[WindDirection] [decimal](10, 4) NULL,
[WindSpeed] [decimal](10, 4) NULL,
[Radiation] [decimal](10, 4) NULL,
[Luminiousity] [decimal](10, 4) NULL,
[SensorFault] [nvarchar](50) NULL,
[IsActive] [bit] NULL,
[IsDeleted] [bit] NULL,
[AddedOn] [datetime] NULL,
[AddedBy] [int] NULL,
[ModifiedOn] [datetime] NULL,
[ModifiedBy] [int] NULL,
CONSTRAINT [PK_AWS_Responce2] PRIMARY KEY CLUSTERED
(
[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
SET ANSI_PADDING ON
GO
/****** Object: Index [PK_AWSAddedOn] Script Date: 11/29/2021 9:20:59 PM ******/
CREATE NONCLUSTERED INDEX [PK_AWSAddedOn] ON [dbo].[AWS_Responce2]
(
[AWS_Id] ASC,
[AddedOn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
I have tried following query
WITH CTE AS
(
SELECT Rainfall,AddedOn,AWS_Id
FROM AWS_Responce2
WHERE cast(AddedOn as date) between DATEADD(DD, -1, '2021/01/01') AND '2021/11/10' AND IsDeleted = 0 AND AWS_ID = '29420467921183302'
)
SELECT
Max(Rainfall) as Rainfall,
CAST(AddedOn as date) as AddedOn,AWS_Id
FROM CTE
GROUP BY cast(AddedOn as date),AWS_Id
ORDER BY cast(AddedOn as date) DESC;
Please help me.