Adhikar Patil

Adhikar Patil

  • 1.5k
  • 460
  • 80.7k

I want per day max value from million of records in SQL

Nov 29 2021 4:35 PM

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.


Answers (1)