SQL with PBI backend

Hi.. I just want to know is there any command or script (in SQL) to pull out only data with changes from previous day reports? Pls reply or email me to Gomathy.jogo@gmail.com

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • Jonathan Krangel
    • Director - Head of Customer Success @ Mode
    • Jonathan_Krangel
    • 10 mths ago
    • Answer
    • Reported - view

    Hi Gomathy - thanks for writing in.

    Generally, there is no built-in functionality that I know of in most mainstream SQL databases that can automatically generate the changes to a table over a given time period. That said, there area number of techniques that you can use to accomplish this goal. The approach you take depends on a number of factors that are unique to your situation, including your specific database type, how the data is stored and how the table in question is updated. A few options that may make sense:

    1) Your data may include one or more timestamps (e.g., an `updated_at` and a `created_at`). If it does, you can return only newly added rows (where `updated_at=created_at` and `created_at` is within the last day) and updated rows (where `updated_at > created_at`).

    2) You can set up a nightly job that takes all of the rows in your table (let's call it `table-a` and appends them to a second table (let's call this table `table-a-history`) with an added column containing the current date. Therefore, `table-a-history` will contain a running snapshot of `table-a` as it looked on each day. 


    3) Following the second suggestion, you can use `table-a-history` to calculate the rows that are different from day-to-day and create a "diff" table.

    I hope these suggestions help! Best of luck!

    Reply Like
Like Follow
  • Status Answered
  • 10 mths agoLast active
  • 1Replies
  • 366Views
  • 2 Following