Have a better UPDATE statement than what I wrote?

[serge]

/*
This is a long post. You can paste the whole message
in the SQL Query Analyzer.
I have a scenario where there are records
with values pointing to wrong records and I need to fix them
using an Update statement.

I have a sample code to reproduce my problem.
To simplify the scenario I am trying to use Order related
tables to explain a little better the tables i have to work with.
Please don't bother looking at the wrong relationship and how
the tables are designed. That's not my current problem. My
job is to correct the wrong data either using code or manually.

Here are the tables I have created:

TBLORDERS where two fields I am interested in are:
ORDERTYPENO linking to TBLORDERTYPE
LASTSTATUSNO linking to TBLSTATUS

TBLORDERTYPE where one field I am interested in is
ORDERPROCESSINGNO

TBLORDERPROCESSING

Each order has a link to OrderTypeNo and each
OrderTypeNo has a link to OrderProcessingNo.
TBLORDERSTATUSES where one field I am
interested in is
STATUSNO

TBLSTATUS where one field I am interested in is
ORDERPROCESSINGNO
I have the sample code here:
*/

--DROP DATABASE TestDB

CREATE DATABASE TestDB
GO

USE TestDB

CREATE TABLE TBLORDER
(
IDNO INT PRIMARY KEY NOT NULL,
ORDERNUMBER VARCHAR(50),
ORDERTYPENO INT,
LASTSTATUSNO INT
)

INSERT INTO TBLORDER (IDNO, ORDERNUMBER, ORDERTYPENO, LASTSTATUSNO)
SELECT 1, 'ORDERTEST1', 1, 3 UNION ALL
SELECT 2, 'ORDERTEST2', 1, 3 UNION ALL
SELECT 3, 'ORDERTEST3', 2, 16 UNION ALL
SELECT 4, 'ORDERTEST4', 2, 16 UNION ALL
SELECT 5, 'ORDERTEST5', 2, 16 UNION ALL
SELECT 6, 'ORDERTEST6', 2, 16 UNION ALL
SELECT 7, 'ORDERTEST7', 4, 5 UNION ALL
SELECT 8, 'ORDERTEST8', 4, 5 UNION ALL
SELECT 9, 'ORDERTEST9', 6, 22 UNION ALL
SELECT 10, 'ORDERTEST10', 6, 22 UNION ALL
SELECT 11, 'ORDERTEST11', 7, 20
CREATE TABLE TBLORDERSTATUSES
(
IDNO INT PRIMARY KEY NOT NULL,
ORDERNO INT,
STATUSNO INT
)

INSERT INTO TBLORDERSTATUSES (IDNO, ORDERNO, STATUSNO)
SELECT 1, 1, 1 UNION ALL
SELECT 2, 1, 2 UNION ALL
SELECT 3, 1, 3 UNION ALL
SELECT 4, 1, 4 UNION ALL
SELECT 5, 2, 1 UNION ALL
SELECT 6, 2, 2 UNION ALL
SELECT 7, 2, 3 UNION ALL
SELECT 8, 2, 4 UNION ALL
SELECT 9, 3, 15 UNION ALL
SELECT 10, 3, 16 UNION ALL
SELECT 11, 3, 17 UNION ALL
SELECT 12, 4, 15 UNION ALL
SELECT 13, 4, 16 UNION ALL
SELECT 14, 4, 17 UNION ALL
SELECT 15, 5, 15 UNION ALL
SELECT 16, 5, 16 UNION ALL
SELECT 17, 5, 17 UNION ALL
SELECT 18, 6, 15 UNION ALL
SELECT 19, 6, 16 UNION ALL
SELECT 20, 6, 17 UNION ALL
SELECT 21, 7, 5 UNION ALL
SELECT 22, 7, 6 UNION ALL
SELECT 23, 8, 5 UNION ALL
SELECT 24, 8, 6 UNION ALL
SELECT 25, 9, 22 UNION ALL
SELECT 26, 9, 23 UNION ALL
SELECT 27, 9, 24 UNION ALL
SELECT 28, 9, 25 UNION ALL
SELECT 29, 10, 22 UNION ALL
SELECT 30, 10, 23 UNION ALL
SELECT 31, 10, 24 UNION ALL
SELECT 32, 10, 25 UNION ALL
SELECT 33, 11, 18 UNION ALL
SELECT 34, 11, 19 UNION ALL
SELECT 35, 11, 20 UNION ALL
SELECT 36, 11, 21
CREATE TABLE TBLORDERTYPE
(
IDNO INT PRIMARY KEY NOT NULL,
ORDERTYPE VARCHAR(50),
ORDERPROCESSINGNO INT
)

INSERT INTO TBLORDERTYPE (IDNO, ORDERTYPE, ORDERPROCESSINGNO)
SELECT 1, 'CATEGORY 100', 1 UNION ALL
SELECT 2, 'CATEGORY 200', 5 UNION ALL
SELECT 3, 'CATEGORY 300', 3 UNION ALL
SELECT 4, 'CATEGORY 400', 2 UNION ALL
SELECT 5, 'CATEGORY 500', 4 UNION ALL
SELECT 6, 'CATEGORY 600', 9 UNION ALL
SELECT 7, 'CATEGORY 700', 8 UNION ALL
SELECT 8, 'CATEGORY 800', 7 UNION ALL
SELECT 9, 'CATEGORY 900', 6
CREATE TABLE TBLORDERPROCESSING
(
IDNO INT PRIMARY KEY NOT NULL,
ORDERPROCESSING VARCHAR(50)
)

INSERT INTO TBLORDERPROCESSING (IDNO, ORDERPROCESSING)
SELECT 1, 'ORDER PROCESSING A1' UNION ALL
SELECT 2, 'ORDER PROCESSING A9' UNION ALL
SELECT 3, 'ORDER PROCESSING Z5' UNION ALL
SELECT 4, 'ORDER PROCESSING 76' UNION ALL
SELECT 5, 'ORDER PROCESSING 98' UNION ALL
SELECT 6, 'ORDER PROCESSING AB' UNION ALL
SELECT 7, 'ORDER PROCESSING 11' UNION ALL
SELECT 8, 'ORDER PROCESSING T7' UNION ALL
SELECT 9, 'ORDER PROCESSING ZX'
CREATE TABLE TBLSTATUS
(
IDNO INT PRIMARY KEY NOT NULL,
STATUS VARCHAR(50),
ORDERPROCESSINGNO INT
)

INSERT INTO TBLSTATUS (IDNO, STATUS, ORDERPROCESSINGNO)
SELECT 1, 'ABC', 1 UNION ALL
SELECT 2, 'DEF', 1 UNION ALL
SELECT 3, 'GHI', 1 UNION ALL
SELECT 4, 'JKL', 1 UNION ALL
SELECT 5, 'MNO', 2 UNION ALL
SELECT 6, 'PQR', 2 UNION ALL
SELECT 7, 'STU', 3 UNION ALL
SELECT 8, 'VWX', 3 UNION ALL
SELECT 9, 'YZ', 3 UNION ALL
SELECT 10, '123', 3 UNION ALL
SELECT 11, '456', 3 UNION ALL
SELECT 12, '789', 3 UNION ALL
SELECT 13, '0AA', 3 UNION ALL
SELECT 14, '0BB', 3 UNION ALL
SELECT 15, '0CC', 5 UNION ALL
SELECT 16, '0DD', 5 UNION ALL
SELECT 17, '0EE', 5 UNION ALL
SELECT 18, '0FF', 8 UNION ALL
SELECT 19, '0GG', 8 UNION ALL
SELECT 20, '0HH', 8 UNION ALL
SELECT 21, '0II', 8 UNION ALL
SELECT 22, '0JJ', 9 UNION ALL
SELECT 23, '0KK', 9 UNION ALL
SELECT 24, '0LL', 9 UNION ALL
SELECT 25, '0MM', 9

/*
If you run the above, the data is CORRECT and the way
it normally should be.

Basically, each Order is linked to an OrderTypeNo. Each
OrderTypeNo is linked to an OrderProcessingNo.
Each Order has MANY OrderStatuses. Each
OrderProcessingNo has MANY Statuses.
So both TBLORDERTYPE and TBLSTATUS is pointing
to TBLORDERPROCESSING. I will mess up an Order
record for example to point to a wrong OrderType and
leave its LASTSTATUSNO and all its CHILD
TBLORDERSTATUSES STATUS records point to
the CORRECT ORDERPROCESSINGNO.
*/

UPDATE TBLORDER
SET ORDERTYPENO = 3
WHERE IDNO = 5 OR IDNO = 10

/*
So now both Order IDNO 5 & 10 are basically messed
up as they are pointing to ORDERTYPENO 3 (i.e.
ORDERPROCESSINGNO 3) whereas their
LASTSTATUSNO and all its TBLORDERSTATUS
STATUS records are pointing to .......
ORDERPROCESSINGNO 5 & 9

Now I will mess up both TBLORDER and
TBLORDERDETAILS in order for my code
NOT to fix it since this I will have to deal and
decide what to correct manually.
*/

UPDATE TBLORDER
SET ORDERTYPENO = 2, LASTSTATUSNO = 15
WHERE IDNO = 8

SELECT * FROM TBLORDER
GO

CREATE VIEW VIEW1
-- This VIEW1 returns all TBLORDER records that have the problem
AS
SELECT TBLORDER.IDNO, TBLORDER.ORDERTYPENO, TBLORDER.LASTSTATUSNO
FROM TBLORDER
INNER JOIN TBLORDERTYPE ON TBLORDER.ORDERTYPENO = TBLORDERTYPE.IDNO
INNER JOIN TBLSTATUS ON TBLORDER.LASTSTATUSNO = TBLSTATUS.IDNO
AND TBLORDERTYPE.ORDERPROCESSINGNO <> TBLSTATUS.ORDERPROCESSINGNO
GO

CREATE VIEW VIEW2
-- This VIEW2 does a GROUP BY of all TBLORDER.IDNO &
TBLSTATUS.ORDERPROCESSINGNO
AS
SELECT TOP 100 PERCENT TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNO
FROM TBLORDERSTATUSES INNER JOIN
TBLORDER ON TBLORDERSTATUSES.ORDERNO = TBLORDER.IDNO
INNER JOIN
TBLSTATUS ON TBLORDERSTATUSES.STATUSNO =
TBLSTATUS.IDNO INNER JOIN
VIEW1 ON TBLORDER.IDNO = VIEW1.IDNO
GROUP BY TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNO
ORDER BY TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNO
GO

CREATE VIEW VIEW3
-- This VIEW3 checks to see if TBLORDERSTATUS records have more than one
ORDERPROCESSINGNO
AS
SELECT IDNO
FROM VIEW2
GROUP BY IDNO
HAVING (COUNT(*) > 1)
GO

CREATE TABLE TMPORDERS
(
IDNO INT,
OLDORDERTYPENO INT,
NEWORDERTYPENO INT,
LASTSTATUSNO INT
)

INSERT INTO TMPORDERS (IDNO, OLDORDERTYPENO, LASTSTATUSNO)
SELECT TBLORDER.IDNO, TBLORDER.ORDERTYPENO, TBLORDER.LASTSTATUSNO
FROM TBLORDER
INNER JOIN TBLORDERTYPE ON TBLORDER.ORDERTYPENO = TBLORDERTYPE.IDNO
INNER JOIN TBLSTATUS ON TBLORDER.LASTSTATUSNO = TBLSTATUS.IDNO
AND TBLORDERTYPE.ORDERPROCESSINGNO <> TBLSTATUS.ORDERPROCESSINGNO
LEFT JOIN VIEW3 ON TBLORDER.IDNO = VIEW3.IDNO AND VIEW3.IDNO IS NULL

SELECT * FROM TMPORDERS

UPDATE TMPORDERS
SET NEWORDERTYPENO = TBLORDERTYPE.IDNO
FROM TBLORDERTYPE
INNER JOIN TBLORDERPROCESSING ON TBLORDERTYPE.ORDERPROCESSINGNO =
TBLORDERPROCESSING.IDNO
INNER JOIN TBLSTATUS ON TBLORDERPROCESSING.IDNO =
TBLSTATUS.ORDERPROCESSINGNO
WHERE TBLSTATUS.IDNO = TMPORDERS.LASTSTATUSNO

UPDATE TBLORDER
SET ORDERTYPENO = NEWORDERTYPENO
FROM TMPORDERS
WHERE TMPORDERS.IDNO = TBLORDER.IDNO

SELECT * FROM TBLORDER

/*
Is there a better to write my Update statement? As you can see that
I am using 3 views, 1 temp table and 2 update statements to
fix my problem.
I am not even sure if i'll need to add more update statements
to handle other corrections. If that is the case I am trying to
see if my code can be simplified in order for it to be easily
modifiable to handle other scenarios.
Thank you for your time.
*/

[Razvan Socol]

Hello, Serge

1. To complete your DDL, you should also add the foreign keys and
unique constraints:

ALTER TABLE TBLORDERTYPE ADD FOREIGN KEY (ORDERPROCESSINGNO)
REFERENCES TBLORDERPROCESSING (IDNO)
ALTER TABLE TBLSTATUS ADD FOREIGN KEY (ORDERPROCESSINGNO)
REFERENCES TBLORDERPROCESSING (IDNO)
ALTER TABLE TBLORDER ADD FOREIGN KEY (ORDERTYPENO)
REFERENCES TBLORDERTYPE (IDNO)
ALTER TABLE TBLORDER ADD FOREIGN KEY (LASTSTATUSNO)
REFERENCES TBLSTATUS (IDNO)
ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (ORDERNO)
REFERENCES TBLORDER (IDNO)
ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (STATUSNO)
REFERENCES TBLSTATUS (IDNO)

ALTER TABLE TBLSTATUS ADD UNIQUE (STATUS)
ALTER TABLE TBLORDERTYPE ADD UNIQUE (ORDERTYPE)
ALTER TABLE TBLORDERPROCESSING ADD UNIQUE (ORDERPROCESSING)
ALTER TABLE TBLORDER ADD UNIQUE (ORDERNUMBER)
ALTER TABLE TBLORDERSTATUSES ADD UNIQUE (ORDERNO, STATUSNO)

2. Your "INSERT INTO TMPORDERS [...]" does not perform as you expect,
because the condition "AND VIEW3.IDNO IS NULL" is in the "LEFT JOIN"
clause, not in the WHERE clause (and therefore it's ignored). To
exclude from the INSERT any rows that are in VIEW3 you need to move the
condition "VIEW3.IDNO IS NULL" to the WHERE clause.

3. This corrected "INSERT INTO TMPORDERS [...]" can be rewritten
(without using views and with a slight performance improvement) as:

INSERT INTO TMPORDERS (IDNO, OLDORDERTYPENO, LASTSTATUSNO)
SELECT O1.IDNO, O1.ORDERTYPENO, O1.LASTSTATUSNO
FROM TBLORDER O1
INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
AND NOT EXISTS (
SELECT O2.IDNO
FROM TBLORDER O2
INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
WHERE O2.IDNO IN (
SELECT O3.IDNO
FROM TBLORDER O3
INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
)
GROUP BY O2.IDNO
HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
)

4. The "UPDATE TMPORDERS" statement, can be rewritten (by eliminating
the join with the TBLORDERPROCESSING) as:

UPDATE TMPORDERS SET NEWORDERTYPENO = T.IDNO
FROM TBLORDERTYPE T INNER JOIN TBLSTATUS S
ON T.ORDERPROCESSINGNO = S.ORDERPROCESSINGNO
WHERE S.IDNO = TMPORDERS.LASTSTATUSNO

5. The whole story can be written in a single UPDATE statement, like
this:

UPDATE TBLORDER SET ORDERTYPENO = NEWORDERTYPENO
FROM TBLORDER O INNER JOIN (
SELECT O1.IDNO, (
SELECT T4.IDNO FROM TBLORDERTYPE T4
INNER JOIN TBLSTATUS S4
ON T4.ORDERPROCESSINGNO = S4.ORDERPROCESSINGNO
WHERE S4.IDNO = O1.LASTSTATUSNO
) AS NEWORDERTYPENO
FROM TBLORDER O1
INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
AND NOT EXISTS (
SELECT O2.IDNO
FROM TBLORDER O2
INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
WHERE O2.IDNO IN (
SELECT O3.IDNO
FROM TBLORDER O3
INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
)
GROUP BY O2.IDNO
HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
)
) X ON O.IDNO=X.IDNO

I have to admit that this is a rather complex UPDATE statement and,
while it's performance is better than your solution, maintainability
may be less. So you may prefer using some views to improve readability
(for example VIEW1, which can be used in two places in the above
statement). However, there are some interesting points that you can
learn from this complex statement:
a) avoiding the use of temporary tables, when subqueries can be used
b) the use of table aliases, to improve readability
c) the use of "COUNT(DISTINCT something)" instead of two "GROUP BY"-s;
d) the use of "NOT EXISTS" subqueries, instead of "LEFT JOIN ... WHERE
.... IS NULL";
e) the use of "WHERE ... IN" subqueries, instead of joins (when there
is no column used from the joined subquery);
f) the use of subqueries in the SELECT clause, instead of joins (when
there is only one column used from the joined subquery).
I think that these usages of subqueries (points d,e,f) improve
readability (and may, in rare cases, even improve performance), but
that's for you to decide, in each particular case.

Razvan

[serge]

Hello Razvan,

Thank you for your detailed explanation.

It will take me a little bit of time to go through the points
and understand them.

Thanks again.

1. To complete your DDL, you should also add the foreign keys and
unique constraints:

ALTER TABLE TBLORDERTYPE ADD FOREIGN KEY (ORDERPROCESSINGNO)
REFERENCES TBLORDERPROCESSING (IDNO)
ALTER TABLE TBLSTATUS ADD FOREIGN KEY (ORDERPROCESSINGNO)
REFERENCES TBLORDERPROCESSING (IDNO)
ALTER TABLE TBLORDER ADD FOREIGN KEY (ORDERTYPENO)
REFERENCES TBLORDERTYPE (IDNO)
ALTER TABLE TBLORDER ADD FOREIGN KEY (LASTSTATUSNO)
REFERENCES TBLSTATUS (IDNO)
ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (ORDERNO)
REFERENCES TBLORDER (IDNO)
ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (STATUSNO)
REFERENCES TBLSTATUS (IDNO)

ALTER TABLE TBLSTATUS ADD UNIQUE (STATUS)
ALTER TABLE TBLORDERTYPE ADD UNIQUE (ORDERTYPE)
ALTER TABLE TBLORDERPROCESSING ADD UNIQUE (ORDERPROCESSING)
ALTER TABLE TBLORDER ADD UNIQUE (ORDERNUMBER)
ALTER TABLE TBLORDERSTATUSES ADD UNIQUE (ORDERNO, STATUSNO)

2. Your "INSERT INTO TMPORDERS [...]" does not perform as you expect,
because the condition "AND VIEW3.IDNO IS NULL" is in the "LEFT JOIN"
clause, not in the WHERE clause (and therefore it's ignored). To
exclude from the INSERT any rows that are in VIEW3 you need to move the
condition "VIEW3.IDNO IS NULL" to the WHERE clause.

3. This corrected "INSERT INTO TMPORDERS [...]" can be rewritten
(without using views and with a slight performance improvement) as:

INSERT INTO TMPORDERS (IDNO, OLDORDERTYPENO, LASTSTATUSNO)
SELECT O1.IDNO, O1.ORDERTYPENO, O1.LASTSTATUSNO
FROM TBLORDER O1
INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
AND NOT EXISTS (
SELECT O2.IDNO
FROM TBLORDER O2
INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
WHERE O2.IDNO IN (
SELECT O3.IDNO
FROM TBLORDER O3
INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
)
GROUP BY O2.IDNO
HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
)

4. The "UPDATE TMPORDERS" statement, can be rewritten (by eliminating
the join with the TBLORDERPROCESSING) as:

UPDATE TMPORDERS SET NEWORDERTYPENO = T.IDNO
FROM TBLORDERTYPE T INNER JOIN TBLSTATUS S
ON T.ORDERPROCESSINGNO = S.ORDERPROCESSINGNO
WHERE S.IDNO = TMPORDERS.LASTSTATUSNO

5. The whole story can be written in a single UPDATE statement, like
this:

UPDATE TBLORDER SET ORDERTYPENO = NEWORDERTYPENO
FROM TBLORDER O INNER JOIN (
SELECT O1.IDNO, (
SELECT T4.IDNO FROM TBLORDERTYPE T4
INNER JOIN TBLSTATUS S4
ON T4.ORDERPROCESSINGNO = S4.ORDERPROCESSINGNO
WHERE S4.IDNO = O1.LASTSTATUSNO
) AS NEWORDERTYPENO
FROM TBLORDER O1
INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
AND NOT EXISTS (
SELECT O2.IDNO
FROM TBLORDER O2
INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
WHERE O2.IDNO IN (
SELECT O3.IDNO
FROM TBLORDER O3
INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
)
GROUP BY O2.IDNO
HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
)
) X ON O.IDNO=X.IDNO

I have to admit that this is a rather complex UPDATE statement and,
while it's performance is better than your solution, maintainability
may be less. So you may prefer using some views to improve readability
(for example VIEW1, which can be used in two places in the above
statement). However, there are some interesting points that you can
learn from this complex statement:
a) avoiding the use of temporary tables, when subqueries can be used
b) the use of table aliases, to improve readability
c) the use of "COUNT(DISTINCT something)" instead of two "GROUP BY"-s;
d) the use of "NOT EXISTS" subqueries, instead of "LEFT JOIN ... WHERE
... IS NULL";
e) the use of "WHERE ... IN" subqueries, instead of joins (when there
is no column used from the joined subquery);
f) the use of subqueries in the SELECT clause, instead of joins (when
there is only one column used from the joined subquery).
I think that these usages of subqueries (points d,e,f) improve
readability (and may, in rare cases, even improve performance), but
that's for you to decide, in each particular case.

Razvan