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.firstname.lastname@example.org
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