Retrieve date that sequence value was used?

I have an OOB system that has hundreds of tables. Each table has a ROWSTAMP column (integer) that gets populated by a trigger — each time a record is created or updated. All the tables/triggers use the same db sequence. Each time a record is updated anywhere in the db, that one sequence is used to get the next available integer. (I suppose I could consider the sequence to be a sort of global sequence).

IBM: Usage of ROWSTAMP column in Maximo tables

It’s unfortunate that those ROWSTAMP columns are integers, and not dates. What I really need in the tables are CHANGEDATE columns. Dates would be useful for data entry troubleshooting, reporting, and analysis in general. But I can’t really justify customizing hundreds of tables and adding hundreds of triggers in the OOB system. The customized tables wouldn’t be supported by IBM and it wouldn’t be worth the effort/added complexity.

I feel like those ROWSTAMP columns are so close to being useful to me. I can’t help but wonder:

Is there a way to retrieve the date that those ROWSTAMP sequence values were generated?

For example, and I doubt this is possible, could I add a DATE column to the sequence, and then join from my table to the sequence to get the date? Or could I use some sort of logging mechanism on the sequence to put rows (with dates) in a table whenever the next sequence value is used?

Julian Date Converter

Why are you selling this site?

Need to work on different projects
How is it monetized?
PropellerAds and revenuehits

Does this site come with any social media accounts?

How much time does this site take to run?
no need to work. Its getting organic traffic

What challenges are there with running this site?
Keep updating the contents

Plsql query to split data based on startdate and end date

I want to split data based on start ,end date ,date range configured (yearly/monthly/weekly/quarterly)

For example if the

Startdate is 2015/10/02 and the

Enddate is 2015/12/22

and my date range is Monthly (M)

then my required output is:

Output ------------- Newstartdate newEnddate 2015/10/02.   2015/10/31 2015/11/01.   2015/11/30 2015/12/01.   2015/12/30 

So I was looking for a generic PL/SQL query to split data what ever date range can be(Y/Q/M/W/D) based on the start and end dates and the range specification.

Choosing a specific future date using Microsoft Dynamics CRM Advanced Search

Is it possible to use the advanced filter to select a specific future date, dynamically? For example if I want to return all data with a "date" field populated for 30 days from today, but no data from before or after that? From what I am seeing the only filter option similar is "Next X Days" but it’s inclusive of all days to that point.

Monthly summary of active users based on a start/end date roster

I have a table that dictates who was on a project, from a startdate to an enddate. I’d like some help writing a query that will return the number of "active" users at the end of every month, for the past 12 months.

CREATE TABLE `roster` (   `id` int(11) NOT NULL,   `user_id` int(11) DEFAULT NULL,   `project_id` int(11) DEFAULT NULL,   `start_date` datetime NOT NULL,   `end_date` datetime NOT NULL,   `closed_date` datetime DEFAULT NULL,   PRIMARY KEY (`id`) ); 
id  user_id project_id  start_date  end_date    closed_date 1   1   1   2019-05-27 00:00:00 2021-02-01 00:00:00 NULL 2   2   2   2020-05-27 00:00:00 2021-02-01 00:00:00 2020-02-05 00:00:00 3   3   3   2020-05-27 00:00:00 2024-02-01 00:00:00 2020-02-05 00:00:00 

And the result would be something that shows how many distinct users had an active project within each month (only since the start of this year).

So for the dataset above, we can see that all 3 projects were "active" in the month January 2021, because the end date is in the future.

2020-01-31 | 3 

The last bit of complexity is that sometimes a project can be closed before the EndDate and I’d like to exclude any users who’s endDate is in the future but the project has actually closed.

For example in the dataset above, the third project has a end date of Feb 2024, however the project closed in Feb 2021. So technically the person was active in 2021 Jan and Feb, but not March.

p.s Hoping to get an answer in Postgres (I’m using Redshift)