I have a database table that has a structure similar to the one shown below:
CREATE TABLE dated_records ( recdate DATE NOT NULL col1 DOUBLE NOT NULL, col2 DOUBLE NOT NULL, col3 DOUBLE NOT NULL, col4 DOUBLE NOT NULL, col5 DOUBLE NOT NULL, col6 DOUBLE NOT NULL, col7 DOUBLE NOT NULL, col8 DOUBLE NOT NULL );
I want to write an SQL statement that will allow me to return a record containing the changes between two set dates for the specified columns - for example. col1, col2 and col3
for example, if I wanted to see how much the value in col1, col2 and col3 changed during the interval between two dates. The dumb way to do this is to select the rows (separately) for each date, and then split the fields outside of the db server -
SQL1 = "SELECT col1, col2 col3 FROM dated_records WHERE recdate='2001-01-01'"; SQL1 = "SELECT col1, col2 col3 FROM dated_records WHERE recdate='2001-02-01'";
however, I am sure there is a way to a more reasonable way to make the difference using pure SQL. I guess this will be due to the use of self-join (and possibly a nested subquery), but I can complicate the situation - I decided it was better to ask SQL experts here to find out how they would solve this problem in the most efficient way.
Ideally, SQL should be DB agnostic, but if it should be bound to a specific db, then it should be PostgreSQL.
source share