How to Check previous data loaded records vs current day

Question

Hello Good Morniong

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

Answer 1

I'm not sure that I understand your question.

First of all, the query you post makes little sense. Since the value of getdate() changes every millisecond (more or less), the likelihood that it would match the max(insertdate) is small. Did you mean that you want to test by the date only, disregarding time? In that case, you would need to write:

convert(date, MAX(insert_date) = convert(date, getdate())

As for the question about 25%, the business rules are not clear to me. For many of the questions where people are asking for how to craft a query, we recommend that you post the CREATE TABLE statement for your table(s), together with INSERT statements with sample data and the expected result given the sample. That helps to clarify, and also make it easy to copy and paste into a query window to develop a tested solution.

Answer 2

You could try working from this query:

WITH cnts AS (
   SELECT convert(date, insert_date) AS date, COUNT(*) AS cnt
   FROM   tbl
   GROUP  BY convert(date, insert_date)
), prevvalue AS (
   SELECT date, cnt, LAG(cnt) OVER(ORDER BY date) AS prevcnt
   FROM   cnts
)
SELECT date, cnt, prevcnt
FROM   prevvalue
WHERE  cnt > 1.25 * prevvnt OR
       cnt < 0.75 * prevcnt

By the way, I took the liberty to convert your post to a comment. Answers are for answers to the original question. As the original poster, you do not use Answers, with the exception that you find the solution yourself.

Answer 3

Hi @bizzi Manna

You can try this query.

;with T1 as(
  select convert(date, insert_date) as date, count(*) as num
  from Table1 group by convert(date, insert_date)
),T2 as(
  select A.date as curr,A.num as currnum,B.date as pre,B.num as prenum
  from T1 as A left outer join T1 as B on A.date = dateadd(day,1,B.date))
select * from T2 where currnum > 1.25 * prenum or currnum < 0.75 * prenum;

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.