I need to write a query in SQL Server.
In SQL Server, I have a table named x. This table has several columns, such as FrameLossCount, ElementID1, Date, and others. Now, I want to add two additional columns named LastDateStatus and Intensification. Neither of these columns should allow null values.
The LastDateStatus column should only contain the values “Normal” and “Alarm”. It should be populated as follows: For each ElementID1, if the FrameLossCount exceeds 50,000 on the latest recorded date (determined by time, not the order of rows in the table), the column should be populated with “Alarm”. Otherwise, it should be populated with “Normal”.
The Intensification column should check the FrameLossCount for each ElementID1 on the latest recorded date. If the FrameLossCount exceeds 50,000 on that date, it should also check the FrameLossCount for the preceding two days. If the FrameLossCount exceeded 50,000 on all three consecutive days (including the latest date), the Intensification column should be populated with “Critical”. Otherwise, it should be populated with “Normal”.

SELECT DISTINCT ElementID1
FROM x t1
WHERE EXISTS (
    SELECT 1
    FROM x t2
    WHERE t2.ElementID1 = t1.ElementID1
      AND t2.Date = CAST(GETDATE() AS DATE)
)
AND EXISTS (
    SELECT 1
    FROM x t3
    WHERE t3.ElementID1 = t1.ElementID1
      AND t3.Date = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
)
AND EXISTS (
    SELECT 1
    FROM x t4
    WHERE t4.ElementID1 = t1.ElementID1
      AND t4.Date = DATEADD(DAY, -2, CAST(GETDATE() AS DATE))
);

BUT IT COULD NOT DEAL WITH THE PROBLEMS THAT COULD OCCUR IN A BIG DATABASE LIKE MISISNG SOME EXCEL FILES WHICH SEED THIS DATABASE. ( LIKE MISSING OUT THE SECOND DAY OF THAT 3 CONSECUTIVE DAYS WHICH MUST BE ANALAYSED )