[RESOLVED] How to improve this SQL

[MrNorth]

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.

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

[digitalShaman]

I think you should INNER JOIN a SELECT FROM U160BEHORIGHETSREVISION which is GROUPed BY U160_MANAGER,U160_REVISIONSID,U160_REVISION_J where you sum the 5 fields using a CASE.
i maybe have overlooked something as your statement is hard to read as you found out yourself but i think something like this will work:

SELECT * 
FROM VN220ANSTALLD VN 
INNER JOIN U160BEHORIGHETSREVISION U160 ON VN.N220_ANSTID = U160.U160_MANAGER
INNER JOIN (
	SELECT U160_MANAGER,U160_REVISIONSID,U160_REVISION_J
		,SUM(CASE WHEN U160_TOTALSTATUS=1 THEN 1 ELSE 0 END) AS NOT_SENT_COUNT
		,SUM(CASE WHEN U160_MANAGER_FD IS NOT NULL AND U160_MANAGER_FD != '' THEN 1 ELSE 0 END) AS CHANGED_MANAGER_COUNT
		,SUM(CASE WHEN U160_TOTALSTATUS = 2 AND (U160_MANAGER_STATUS != 3 AND U160_MANAGER_STATUS != 4) THEN 1 ELSE 0 END) AS MANAGER_NOT_DONE_COUNT
		,SUM(CASE WHEN U160_TOTALSTATUS = 3 THEN 1 ELSE 0 END) AS REVISION_DONE_COUNT	
		,SUM(CASE WHEN U160_TOTALSTATUS = 2 AND (U160_MANAGER_STATUS = 3 OR U160_MANAGER_STATUS = 4) THEN 1 ELSE 0 END) AS MANAGER_DONE_COUNT
	FROM U160BEHORIGHETSREVISION GROUP BY U160_MANAGER,U160_REVISIONSID,U160.U160_REVISION_J
) T1 ON U160.U160_MANAGER=T1.U160_MANAGER AND U160.U160_REVISIONSID=T1.U160_REVISIONSID AND U160.U160_REVISION_J=T1.U160_REVISION_J
WHERE U160.U160_REVISIONSID = '2006 DEC'
AND U160_REVISION_J = 1

i hope its understandable what i mean

[MrNorth]

Hi!

Yes I agree that code was sure hard to read after I was finished with it. Your code works like a charm, just a few typos and it looks much more clear than before without too much repetitive coding.

I nod my head to some clever SQL and learned some new tricks with joins, sums, case and group by. Thanks!

/S