I have to agree with the others - this should be re-written to eliminate all of the sub-queries. Not only will that help in performance, but the query will be much easier to read and manage.

This really isn't that hard to do - first, I restructured the query so I could see it better. Then, I changed to query join from an implicit inner join to an explicit inner join - then added in the other tables as outer joins to get the columns you are looking for.

Select a.EmployeeGroupID

,a.EmployeeID

,rtrim(p.PER_FORNAVN) + ' ' + p.PER_EFTERNAVN As FNAME

,p.PER_TIMER As PTIME

,p.PER_FASTE As Active

,p.PER_FRATRAADT As RESIGNED

,convert(char, p.OnLeaveStart, 23) As LSDATE

,convert(char, p.OnLeaveEnd, 23) As LEDATE

,(Select s.SubGroupType

From dbo.Subgroup s

Where s.Subgroup_ID In (Select e.Subgroup_ID

From dbo.EmployeeGroupMapToEmployee e

Where e.EmployeeID = a.EmployeeID

And e.EmployeeGroupID = a.EmployeeGroupID))

-- ,s.SubGroupType As Substat -- this should replace the above sub-queries when you un-comment the tables below

From dbo.EmployeeGroupMapToEmployee a

Inner Join dbo.EmployeeGroup f On a.EmployeeGroupID = f.Id

Left Join dbo.Personal p On p.Per_Personal_ID = a.EmployeeID

--**** you should be able to use the following to replace the above subqueries

--Left Join dbo.EmployeeGroupMapToEmployee e On e.EmployeeID = a.EmployeeID

--Left Join dbo.SubGroup s On s.SubGroup_ID = e.SubGroup_ID

Where f.Enable = 0

Or f.Enable Is Null

Or a.EmployeeGroupID In (Select g.EmployeeGroupID

From dbo.DutyRosterShift g

Where g.DutyRosterId = 3

Group By

g.EmployeeGroupID)

Order By

EmployeeGroupID

,Substat

,FNAME;

Now that we have this, eliminating NULLS is much easier - because now we can just wrap the columns with either an ISNULL or COALESCE.

In the above, we now have the Personal table outer joined into the query - which eliminates all of those sub-queries. I would validate whether or not there will always be a record in Personal for every Employee - if so, change the join to an Inner Join.

A couple of other notes: the last sub-queries in the select can be replaced by adding the two tables as joins instead. Test both ways and validate the results to make sure - and, if there are issues post back and we can work those out.

Also, when using convert - I always recommend that you specify the length of the characters. For example: convert(char, somecolumn, style) should be changed to: convert(char(nn), somecolumn, style). In your case, I wouldn't recommend using convert at all - since these appear to be datetime columns and you really should return them to the caller as datetimes. However, that would require changes on the client side at this point - which might cause problems.