SQL Server 2019 : Subquery Join won't execute

Question

I have to design a report which will be a list of a bunch of overlapping data from the same table.

I have to start with the Project table because for the report @ProjectId is the only thing you get as an anchor.

In the RessourceBookings table are all bookings and some are overlapping like

Project A books resource B 24.01., 10 a.m. to 6 p.m.
Project C books resource B 24.01., 11 a.m. to 1 p.m
Project D books resource B 24.01., 1 p.m. to 4 p.m

These are the one I have to filter for and list all those booking conflicts for Project A.

This is the code I came up with at the moment. I didn't shorten it because I want to show that there are a few dependencies from some tables, which makes it a bit complicated (at least for me). If there are other parts besides the main problem which could be optimised, I would be happy to learn.

DECLARE @ProjectId INT = 8501

SELECT 
    p.Id AS 'Project-ID'
    ,p.Name AS 'Project-Name'
    ,p.Info AS 'Project-Info'
    ,pc.Name AS 'Project-Color'
    ,upr.DocumentName AS 'Project-Responsible'
    ,r.ResourceName AS 'Ressource-Name'
    ,r.Info AS 'Ressource-Info'
    ,CASE WHEN ugrr.ResourceRoleId IN (4,7)
        THEN 0
        ELSE 1
        END AS 'Ressource-Availability'
    ,r.Quantity AS 'Booking-Quantity'
    ,rb.Start AS 'Booking-Start'
    ,rb.[End] AS 'Booking-End'
    ,pat.Name AS 'Booking-Action'
    ,rb.IsOption AS 'Booking-State'
    ,rb.OptionRequestAccepted AS 'Booking-Response'
    ,q2.ProjectId AS 'Conflict-Project-ID'
    ,q2.Name AS 'Conflict-Project-Name'
    ,q2.DocumentName AS 'Conflict-Project-Responsible'
    ,q2.Start AS 'Conflict-Start'
    ,q2.[End] AS 'Conflict-End'
    ,q2.IsOption AS 'Conflict-State'
FROM 
    Projects p
LEFT JOIN 
    ProjectColors pc ON pc.Id = p.ProjectColorId
LEFT JOIN 
    Users upr ON upr.Id = p.ResponsibleUserId
LEFT JOIN 
    ResourceBookings rb ON rb.ProjectId = p.Id
LEFT JOIN 
    Resources r ON r.Id = rb.ResourceId
LEFT JOIN 
    ProjectActions pa ON pa.Id = rb.ProjectActionId
LEFT JOIN 
    ProjectActionTypes pat ON pat.Id = pa.ProjectActionTypeId
LEFT JOIN 
    UserGroupResourceRoles ugrr ON ugrr.ResourceId = r.Id
LEFT JOIN 
    (SELECT 
         p2.ProjectId
         ,p2.Name
         ,upr2.DocumentName
         ,rb2.Start
         ,rb2.[End]
         ,rb2.IsOption
     FROM 
         ResourceBookings rb2
     LEFT JOIN 
         Projects p2 ON p2.Id = rb2.ProjectId
     LEFT JOIN 
         Users upr2 ON upr2.Id = p2.ResponsibleUserId
     WHERE 
         rb2.ResourceId = r.Id
         AND (rb2.Start BETWEEN DATEADD(s, +1, rb.Start) AND DATEADD(s, -1, rb.[End])
              OR rb2.[End] BETWEEN DATEADD(s, +1, rb.Start) AND DATEADD(s, -1, rb.[End])
              OR DATEADD(s,+1,rb.Start) BETWEEN rb2.Start AND rb2.[End])) q2
WHERE 
    p.Id = @ProjectId
    AND rb.Start >= SysDateTime()
    AND ugrr.UserGroupId = 11
    AND (SELECT Count(rb2.Id) - 1
         FROM ResourceBookings rb2
         WHERE rb2.ResourceId = r.Id
           AND (rb2.Start BETWEEN DATEADD(s, +1, rb.Start) AND DATEADD(s, -1, rb.[End])
                OR rb2.[End] BETWEEN DATEADD(s, +1, rb.Start) AND DATEADD(s, -1, rb.[End])
                OR DATEADD(s, +1, rb.Start) BETWEEN rb2.Start AND rb2.[End])) > 0

My goal is something like:

Project-ID	Project-Name	[...]	Conflict-Project-ID	Conflict-Project-Name	[...]
1	Project A	[...]	2	Project B	[...]
1	Project A	[...]	3	Project C	[...]
1	Project A	[...]	4	Project D	[...]

Without the whole q2 parts, it works well to list all the information for Project A.

The errors are:

Wrong syntax near the WHERE-keyword.. Native error: 156. SQLSTATE: 42000. Severity 15. Msg State 1. Line 76.

Wrong syntax near ">".. Native error: 102. SQLSTATE: 42000. Severity 15. MsgState 1. Line 87.

Without the subquery join, it works well but I think I need that there. I've googled the last two days but can't find any solution. In my eyes it seems correct.

I can't change the tables or their content. I have to deal with the setup. So temporary tables are not an option (which maybe could help).

(But please no single-word technical-term-answers. I'm not a developer, I have just to work with these kind of things. I'm willing to learn but it's a bit hard without a professional background)

Edit: For clarifications, here is a picture of the table ResourceBookings (top) and bottom the "goal" of the query if @ProjectId = 8525

the error is from the last subquery. you cannot use aggregate functions in where clause, instead use having count(q2.id)>1. for this you need to add rb2.id in your left join subquery ------ also you are missing a join condition in the last left join, it simply ends with q2 and starts the where clause starts. Even if you fix the where issue, the join will throw error — Moulitharan M, Jan 24 '22 at 15:15
Side points: it seems your `LEFT JOIN`s should probably be `INNER`. You might find it better to use window functions rather than that subquery. [`BETWEEN` is a bad idea to use on dates.](https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries) Use `[]` to quote column names, not `''` — Charlieface, Jan 24 '22 at 15:45
@Charlieface Thanks for the reply! `INNER JOIN` seems to be right there, I will keep this im mind. I can't follow what you mean with `You might find it better to use window functions rather than that subquery.` What is the difference in using `[]` then `''`? — IrishWolf, Jan 24 '22 at 16:04
You can use a windowed count to achieve the same thing as the subquery: `COUNT(*) OVER (PARTITION BY rb.ResourceId)` or something like that. Using `''` to quote column names is bad, it can cause unintended results in some cases, see for example https://dba.stackexchange.com/questions/194224/impact-of-quotes-in-t-sql-for-column-name — Charlieface, Jan 24 '22 at 16:24

Moulitharan M · Answer 1 · 2022-01-24T16:35:28.267

Try this

DECLARE @ProjectId INT = 8501

SELECT p.Id AS 'Project-ID'
    ,p.Name AS 'Project-Name'
    ,p.Info AS 'Project-Info'
    ,pc.Name AS 'Project-Color'
    ,upr.DocumentName AS 'Project-Responsible'
    ,r.ResourceName AS 'Ressource-Name'
    ,r.Info AS 'Ressource-Info'
    ,CASE WHEN ugrr.ResourceRoleId IN (4,7)
        THEN 0
        ELSE 1
        END AS 'Ressource-Availability'
    ,r.Quantity AS 'Booking-Quantity'
    ,rb.Start AS 'Booking-Start'
    ,rb.[End] AS 'Booking-End'
    ,pat.Name AS 'Booking-Action'
    ,rb.IsOption AS 'Booking-State'
    ,rb.OptionRequestAccepted AS 'Booking-Response'
    ,q2.ProjectId AS 'Conflict-Project-ID'
    ,q2.Name AS 'Conflict-Project-Name'
    ,q2.DocumentName AS 'Conflict-Project-Responsible'
    ,q2.Start AS 'Conflict-Start'
    ,q2.[End] AS 'Conflict-End'
    ,q2.IsOption AS 'Conflict-State'
    

FROM Projects p
LEFT JOIN ProjectColors pc ON pc.Id = p.ProjectColorId
LEFT JOIN Users upr ON upr.Id = p.ResponsibleUserId
LEFT JOIN ResourceBookings rb ON rb.ProjectId = p.Id
LEFT JOIN Resources r ON r.Id = rb.ResourceId
LEFT JOIN ProjectActions pa ON pa.Id = rb.ProjectActionId
LEFT JOIN ProjectActionTypes pat ON pat.Id = pa.ProjectActionTypeId
LEFT JOIN UserGroupResourceRoles ugrr ON ugrr.ResourceId = r.Id
LEFT JOIN (SELECT p2.Id as id2
                ,p2.Name
                ,upr2.DocumentName
                ,rb2.Start
                ,rb2.[End]
                ,rb2.IsOption
                ,rb2.id
                ,rb2.ProjectId as rb_pid
                ,count(rb2.id) as id_count
            FROM ResourceBookings rb2
            LEFT JOIN Projects p2 ON p2.Id = rb2.ProjectId
            LEFT JOIN Users upr2 ON upr2.Id = p2.ResponsibleUserId
            LEFT JOIN Resources r ON r.Id = rb2.ResourceId
            WHERE rb2.ResourceId = r.Id
                AND (
                    rb2.Start BETWEEN DateAdd(s,+1,rb2.Start) AND DateAdd(s,-1,rb2.[End])
                    OR rb2.[End] BETWEEN DateAdd(s,+1,rb2.Start) AND DateAdd(s,-1,rb2.[End])
                    OR DateAdd(s,+1,rb2.Start) BETWEEN rb2.Start AND rb2.[End]
                )
                group by p2.Id
                ,p2.Name
                ,upr2.DocumentName
                ,rb2.Start
                ,rb2.[End]
                ,rb2.IsOption
                ,rb2.id
                ,rb2.ProjectId 
            ) q2
on q2.rb_pid = p.Id

WHERE p.Id = @ProjectId
    AND rb.Start >= SysDateTime()
    AND ugrr.UserGroupId = 11
    AND q2.id_count > 1

score 1 · Answer 2 · edited Feb 02 '22 at 21:59

Well tried far longer and it seems I was overcomplicating things with the subquery.

Without it and with a few more Joins it works like a charm.

For clarifying why I "ignored" some of the tips I got:

INNER wasn't the right choice. It would mess with the results badly (probably because the tables aren't great designed and have many overlapping headers without correlation...)
[] instead of '' will probably the better choice after reading a bit more about it. Will fix that in the future in my queries.
The window functions seems powerful and less resource-hungry and I will look into it but at this point I didn't fully understand them well enough.

I think I will try to optimise it again after reading ad understanding more about the named tips but for now this works for me:

DECLARE @ProjectId INT = 6290

SELECT
    p.Id AS 'Project-ID'
    ,p.Name AS 'Project-Name'
    ,p.Info AS 'Project-Info'
    ,pc.Name AS 'Project-Color'
    ,upr.DocumentName AS 'Project-Responsible'
    ,r.ResourceName AS 'Resource-Name'
    ,r.Info AS 'Resource-Info'
    ,CASE WHEN ugrr.ResourceRoleId IN (4,7)
        THEN 0
        ELSE 1
        END AS 'Resource-Availability'
    ,rb.Quantity AS 'Booking-Quantity'
    ,rb.Start AS 'Booking-Start'
    ,rb.[End] AS 'Booking-End'
    ,pat.Name AS 'Booking-Action'
    ,rb.IsOption AS 'Booking-State'
    ,rb.OptionRequestAccepted AS 'Booking-Response'
    ,rb2.ProjectId AS 'Conflict-Project-ID'
    ,p2.Name AS 'Conflict-Project-Name'
    ,pc2.Name AS 'Conflict-Project-Color'
    ,upr2.DocumentName AS 'Conflict-Project-Responsible'
    ,rb2.Start AS 'Conflict-Start'
    ,rb2.[End] AS 'Conflict-End'
    ,pat2.Name AS 'Conflict-Booking-Action'
    ,rb2.IsOption AS 'Conflict-Booking-State' 
    ,rb2.Quantity AS 'Conflict-Booking-Quantity'

FROM Projects p
LEFT JOIN ProjectColors pc ON pc.Id = p.ProjectColorId
LEFT JOIN Users upr ON upr.Id = p.ResponsibleUserId
LEFT JOIN ResourceBookings rb ON rb.ProjectId = p.Id
LEFT JOIN Resources r ON r.Id = rb.ResourceId
LEFT JOIN ProjectActions pa ON pa.Id = rb.ProjectActionId
LEFT JOIN ProjectActionTypes pat ON pat.Id = pa.ProjectActionTypeId
LEFT JOIN UserGroupResourceRoles ugrr ON ugrr.ResourceId = r.Id
/* Doubles for Conflicts */
LEFT JOIN ResourceBookings rb2 ON rb2.ResourceId = rb.ResourceId
LEFT JOIN Projects p2 ON p2.Id = rb2.ProjectId
LEFT JOIN ProjectColors pc2 ON pc2.Id = p2.ProjectColorId
LEFT JOIN ProjectActions pa2 ON pa2.Id = rb2.ProjectActionId
LEFT JOIN ProjectActionTypes pat2 ON pat2.Id = pa2.ProjectActionTypeId
LEFT JOIN Users upr2 ON upr2.Id = p2.ResponsibleUserId

WHERE
    p.Id = @ProjectId
    AND rb.Start >= SysDateTime()
    AND ugrr.UserGroupId = 11
    AND rb.Id <> rb2.Id
    AND (
            (
                rb2.Start >= rb.Start
                AND rb2.Start <= rb.[End]
            )
        OR
            (
                rb2.[End] >= rb.Start
                AND rb2.[End] <= rb.[End]
            )
        OR
            (
                rb.Start >= rb2.Start
                AND rb.Start <= rb2.[End]
            )
        )

SQL Server 2019 : Subquery Join won't execute

2 Answers2