How to Check previous data loaded records vs current day in a table

Question

Post reply

How to Check previous data loaded records vs current day in a table

Zond Sita

SSC Veteran

Points: 275
More actions
February 3, 2023 at 11:51 pm

#4145328

Hello Good Morning,
I have three Tables, Assume Table1, Table2, Table3
these three tables get loads from some ETL process.
each of table has a date of load column as Table1.Insert_Date, Table2.Claim_Insert, Table3.System_dt
I would like to check these three tables particular columns (Insert_Dat,
SELECT CASE WHEN MAX(INSERT_DATE) = GETDATE() THEN '' ELSE 'TABLE 1 HAS NO LATEST DATA' END AS TableHasIssues FROM TABLE1
UNION ALL
SELECT CASE WHEN MAX(Claim_Insert) = GETDATE() THEN '' ELSE 'TABLE 2 HAS NO LATEST DATA' END FROM TABLE2
UNION ALL
SELECT CASE WHEN MAX(Claim_Insert) = GETDATE() THEN '' ELSE 'TABLE 3 HAS NO LATEST DATA' END FROM TABLE3
Here I would like to check if there is recordscount that is more or less than 25% of previous day then how can i check this? please
Thanks in advance
ASiti

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

I would use LAG([Recordcount],1) OVER (PARTITION BY TableName ORDER BY SomeDate)

pietlinden SSC Guru Points: 63503 More actions · Answer 2

I would use LAG([Recordcount],1) OVER (PARTITION BY TableName ORDER BY SomeDate)

Zond Sita SSC Veteran Points: 275 More actions · Answer 3

Thank you @pietlinden,, Can you pleaseprovide complete query?

Thank you

ASiti

Zond Sita SSC Veteran Points: 275 More actions · Answer 4

just to clarify

for the question about population, below query returns no of records per day. so here I would like to check the current day counts to previous day record counts if the different is +/- 25% then I want to alert (find out if so). such 'Table needs manual check' something. but query I am looking for is +/- more than 25% find out.

Hope this helps

select Convert(date, Insert_date), Count(*) as Counts from Table1 order by 1 desc;

02/03/2023 170

02/02/2023 90

02/01/0223 160

so if i run query today, 170 today laoded and previous day 90 in this case more than 25% to yesterday then find out it. something like this. hope this helps.

Thank you

Asiti

pietlinden SSC Guru Points: 63503 More actions · Answer 5

Something like this maybe?

use tempdb;
go

CREATE TABLE Reading ( readingDate date, quantity int);
GO
INSERT INTO Reading VALUES ('02/03/2023', 170),('02/02/2023', 90),
('02/01/0223', 160);

SELECT readingDate, 
quantity, 
prevQty = LAG(quantity) OVER (ORDER BY readingDate),
delta = quantity - COALESCE(LAG(quantity) OVER (ORDER BY readingDate),0)
FROM Reading;

This reply was modified 1 year, 10 months ago by pietlinden.