CODEWITHSUNDEEP
sqlpostgresql
Patrick260284
Patrick260284

Reputation: 89

PostgreSQL result of first query used in second query

Sample Data:

|lastmodified|Number1|Number2|password_mod|
|2020-01-25  |250000 |100000 |true        |
|2020-01-26  |250000 |200000 |false       |
|2020-02-01  |300000 |200000 |false       |
|2020-02-10  |350000 |250000 |false       |
|2020-02-12  |400000 |250000 |false       |

I need to run a query with the following criteria:

lastmodified within a month AND password_mod = false
Show Number 1 & 2 for all entries

So far it was easy but now comes the part where I am stuck:

additionally it must show the last Number 1 & 2 that is lower than the current entry

The result should look like this:

|lastmodified|Number1|Number2|password_mod|Old_Number1|Old_Number2|
|2020-02-01  |300000 |200000 |false       |250000     |100000     |
|2020-02-10  |350000 |250000 |false       |300000     |200000     |
|2020-02-12  |400000 |250000 |false       |350000     |200000     |

My main problem here is that the old_numbers can be in records that don´t match my criteria (dates within a month and password_mod = false). But these two criterias are mandatory.

I´ve made two queries that do both steps. Now I need a good way to combine them.

Query1:

SELECT
a.lastmodifiedat AS Aenderungs_Datum,
a.lastmodifiedby AS Aenderungs_User,
a.ntusername AS Betroffener_User,
a.anweisungshoehekv AS AnweisungshoeheKV,
a.anweisungshoehepflege AS AnweisungshoehePflege
FROM
t_benutzer_aud a 
WHERE
(passwort_mod = 'false'
AND (a.lastmodifiedat between '2020-02-01' AND '2020-02-29'))

Query2:

SELECT
b.ntusername AS Betroffener_User,
b.lastmodifiedat AS Aenderungs_Datum,
b.anweisungshoehekv AS AnweisungshoeheKV_alt,
b.anweisungshoehepflege AS Anweisungshoehepflege_alt
FROM
t_benutzer_aud b
WHERE 
b.lastmodifiedat < '2020-02-01'
AND
passwort_mod = 'false'
AND b.ntusername = USERNAME from first query
ORDER BY
aenderungs_datum DESC LIMIT 1

Upvotes: 0

Views: 1403

Answers (1)

richyen
richyen

Reputation: 10018

One intuitive/user-readable way to write the query would be with a CTE:

WITH userlist AS (
  SELECT
    a.lastmodifiedat AS Aenderungs_Datum,
    a.lastmodifiedby AS Aenderungs_User,
    a.ntusername AS Betroffener_User,
    a.anweisungshoehekv AS AnweisungshoeheKV,
    a.anweisungshoehepflege AS AnweisungshoehePflege
  FROM
    t_benutzer_aud a 
  WHERE
    (passwort_mod = 'false'
    AND (a.lastmodifiedat between '2020-02-01' AND '2020-02-29'))
) SELECT
    b.ntusername AS Betroffener_User,
    b.lastmodifiedat AS Aenderungs_Datum,
    b.anweisungshoehekv AS AnweisungshoeheKV_alt,
    b.anweisungshoehepflege AS Anweisungshoehepflege_alt
  FROM
    t_benutzer_aud b
  WHERE 
    b.lastmodifiedat < '2020-02-01'
  AND passwort_mod = 'false'
  AND b.ntusername = userlist.Betroffener_User
  ORDER BY aenderungs_datum DESC LIMIT 1;

Disclosure: I work for EnterpriseDB (EDB)

Upvotes: 1

Related Questions