Hi!

I just wrote up some SQL that I am not really happy about. One of the problems is that I ahve to query a really nasty non-normalized table with lots of duplicate records.

The thing I am not really fond of are the sub selects that are used to calculate occurences in the "big" table, based on certain criteria.


Also, would it be possible to convert something like this to hibernate/entity framework without having to refactor the table into multiple normalized tables?

The below query joins two queries, one big table with some kind of transactional data for managers, an another with user information.

Code:
SELECT DISTINCT U160.U160_MANAGER,
(VN.N220_FORNAMN + ' ' + VN.N220_EFTERNAMN) AS N220_NAMN, 
VN.N220_EMAIL,
(SELECT COUNT(U160_MANAGER) FROM U160BEHORIGHETSREVISION
WHERE U160_TOTALSTATUS = 1 AND U160_MANAGER = U160.U160_MANAGER
AND U160_REVISIONSID = U160.U160_REVISIONSID and
U160.U160_REVISION_J = U160.U160_REVISION_J) AS NOT_SENT_COUNT,
(SELECT COUNT(U160_MANAGER) FROM U160BEHORIGHETSREVISION
WHERE (U160_MANAGER_FD IS NOT NULL AND U160_MANAGER_FD != '')  AND U160_MANAGER = U160.U160_MANAGER
AND U160_REVISIONSID = U160.U160_REVISIONSID and
U160.U160_REVISION_J = U160.U160_REVISION_J) AS CHANGED_MANAGER_COUNT,
(SELECT COUNT(U160_MANAGER) FROM U160BEHORIGHETSREVISION
WHERE (U160_TOTALSTATUS = 2 AND (U160_MANAGER_STATUS != 3 AND U160_MANAGER_STATUS != 4)) AND U160_MANAGER = U160.U160_MANAGER
AND U160_REVISIONSID = U160.U160_REVISIONSID and
U160.U160_REVISION_J = U160.U160_REVISION_J) AS MANAGER_NOT_DONE_COUNT,
(SELECT COUNT(U160_MANAGER) FROM U160BEHORIGHETSREVISION
WHERE U160_TOTALSTATUS = 3  AND U160_MANAGER = U160.U160_MANAGER
AND U160_REVISIONSID = U160.U160_REVISIONSID and
U160.U160_REVISION_J = U160.U160_REVISION_J) AS REVISION_DONE_COUNT,
(SELECT COUNT(U160_MANAGER) FROM U160BEHORIGHETSREVISION
WHERE (U160_TOTALSTATUS = 2 AND (U160_MANAGER_STATUS = 3 OR U160_MANAGER_STATUS = 4)) AND U160_MANAGER = U160.U160_MANAGER
AND U160_REVISIONSID = U160.U160_REVISIONSID and
U160.U160_REVISION_J = U160.U160_REVISION_J) AS MANAGER_DONE_COUNT
FROM VN220ANSTALLD VN INNER JOIN U160BEHORIGHETSREVISION U160
ON VN.N220_ANSTID = U160.U160_MANAGER
WHERE U160.U160_REVISIONSID = '2006 DEC'
AND U160_REVISION_J = 1
/S