[solved] Is a Common Table Expression possible?

[PatrickWolf]

Can you make Common Table expressions in Lansweeper?
This can be done in a view and that is what Lansweeper make of the reports.

Or does anybody have an workaround for this?

Thanks for Tips.

This is to check if a user has the correct OU through all domains production is leading.

WITH CTE_Production
AS (
SELECT
tblADusers.Username+tblADGroups.Name as ID,
tblADusers.Username,
tblADusers.Userdomain,
tblADGroups.Name
FROM tblADusers
INNER JOIN tblADMembership On tblADusers.ADObjectID = tblADMembership.ChildAdObjectID
INNER JOIN tblADGroups On tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID
WHERE
tblADusers.Userdomain = 'Production'
),
CTE_Test
AS (
SELECT
tblADusers.Username+tblADGroups.Name as ID,
tblADusers.Username,
tblADusers.Userdomain,
tblADGroups.Name
FROM tblADusers
INNER JOIN tblADMembership On tblADusers.ADObjectID = tblADMembership.ChildAdObjectID
INNER JOIN tblADGroups On tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID
WHERE
tblADusers.Userdomain = 'Test'
),
CTE_Acceptance
AS (
SELECT
tblADusers.Username+tblADGroups.Name as ID,
tblADusers.Username,
tblADusers.Userdomain,
tblADGroups.Name
FROM tblADusers
INNER JOIN tblADMembership On tblADusers.ADObjectID = tblADMembership.ChildAdObjectID
INNER JOIN tblADGroups On tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID
WHERE
tblADusers.Userdomain = 'Acceptance'
)
SELECT
CASE
WHEN CTE_Production.ID = CTE_Test.ID and CTE_Production.ID = CTE_Acceptance.ID THEN 'In Production and Test and Acceptance'
WHEN CTE_Production.ID = CTE_Test.ID and CTE_Acceptance.ID IS NULL THEN 'In Production and Test not in Acceptance'
WHEN CTE_Production.ID = CTE_Acceptance.ID and CTE_Test.ID IS NULL THEN 'In Production and Acceptance not in Test'
WHEN CTE_Production.ID IS NOT NULL and CTE_Acceptance.ID IS NULL and CTE_Test.ID IS NULL THEN 'In Production not in Test and not in Acceptance'
WHEN CTE_Test.ID = CTE_Acceptance.ID and CTE_Production.ID IS NULL THEN 'In Test and Acceptance not in Production'
WHEN CTE_Test.ID IS NOT NULL and CTE_Production.ID IS NULL and CTE_Acceptance.ID IS NULL THEN 'In Test not in Production and not in Acceptance'
WHEN CTE_Acceptance.ID IS NOT NULL and CTE_Production.ID IS NULL and CTE_Test.ID IS NULL THEN 'In Acceptance not in Production and not in Test'
END as [check],
CASE
WHEN CTE_Production.ID = CTE_Test.ID and CTE_Production.ID = CTE_Acceptance.ID THEN '#026625'
WHEN CTE_Production.ID = CTE_Test.ID and CTE_Acceptance.ID IS NULL THEN 'FFDF00'
WHEN CTE_Production.ID = CTE_Acceptance.ID and CTE_Test.ID IS NULL THEN '#000080'
WHEN CTE_Production.ID IS NOT NULL and CTE_Acceptance.ID IS NULL and CTE_Test.ID IS NULL THEN '#CC0033'
WHEN CTE_Test.ID = CTE_Acceptance.ID and CTE_Production.ID IS NULL THEN '#CC00##'
WHEN CTE_Test.ID IS NOT NULL and CTE_Production.ID IS NULL and CTE_Acceptance.ID IS NULL THEN 'FFDF00'
WHEN CTE_Acceptance.ID IS NOT NULL and CTE_Production.ID IS NULL and CTE_Test.ID IS NULL THEN '#000080'
END as [Backgroundcolor],
CTE_Production.ID as [IDPRD],
CTE_Production.Username as [UserNamePRD],
CTE_Production.Userdomain as [DomainPRD],
CTE_Production.Name as [OUPRD],
CTE_Test.ID as [IDTST],
CTE_Test.Username as [UserNameTST],
CTE_Test.Userdomain as [DomainTST],
CTE_Test.Name as [OUTST],
CTE_Acceptance.ID as [IDACC],
CTE_Acceptance.Username as [UserNameACC],
CTE_Acceptance.Userdomain as [DomainACC],
CTE_Acceptance.Name as [OUACC]
FROM CTE_Production
FULL OUTER JOIN CTE_Test on CTE_Production.ID = CTE_Test.ID
FULL OUTER JOIN CTE_Acceptance on CTE_Production.ID = CTE_Acceptance.ID
WHERE
CTE_Production.ID = CTE_Test.ID and CTE_Acceptance.ID IS NULL or
CTE_Production.ID = CTE_Acceptance.ID and CTE_Test.ID IS NULL or
CTE_Production.ID IS NOT NULL and CTE_Acceptance.ID IS NULL and CTE_Test.ID IS NULL or
CTE_Test.ID = CTE_Acceptance.ID and CTE_Production.ID IS NULL or
CTE_Test.ID IS NOT NULL and CTE_Production.ID IS NULL and CTE_Acceptance.ID IS NULL or
CTE_Acceptance.ID IS NOT NULL and CTE_Production.ID IS NULL and CTE_Test.ID IS NULL
ORDER BY
[check],CTE_Production.Username, CTE_Production.Userdomain

[PatrickWolf]

I solved this by doing subqueries.

This is then end result:

Select Top 1000000 Case
When CTE_Production.ID = CTE_Test.ID And CTE_Production.ID = CTE_Acceptance.ID
Then 'In Production and Test and Acceptance'
When CTE_Production.ID = CTE_Test.ID And CTE_Acceptance.ID Is Null Then
'In Production and Test not in Acceptance'
When CTE_Production.ID = CTE_Acceptance.ID And CTE_Test.ID Is Null Then
'In Production and Acceptance not in Test'
When CTE_Production.ID Is Not Null And CTE_Acceptance.ID Is Null And
CTE_Test.ID Is Null Then
'In Production not in Test not in Acceptance'
When CTE_Test.ID = CTE_Acceptance.ID And CTE_Production.ID Is Null Then
'In Test and Acceptance not in Production'
When CTE_Test.ID Is Not Null And CTE_Production.ID Is Null And
CTE_Acceptance.ID Is Null Then
'In Test not in Production not in Acceptance'
When CTE_Acceptance.ID Is Not Null And CTE_Production.ID Is Null And
CTE_Test.ID Is Null Then
'In Acceptance not in Production not in Test'
End As [check],
CTE_Production.ID As IDPRD,
CTE_Production.Username As UserNamePRD,
CTE_Production.Userdomain As DomainPRD,
CTE_Production.Name As OUPRD,
CTE_Test.ID As IDTST,
CTE_Test.Username As UserNameTST,
CTE_Test.Userdomain As DomainTST,
CTE_Test.Name As OUTST,
CTE_Acceptance.ID As IDACC,
CTE_Acceptance.Username As UserNameACC,
CTE_Acceptance.Userdomain As DomainACC,
CTE_Acceptance.Name As OUACC
From (Select tblADusers.Username + tblADGroups.Name As ID,
tblADusers.Username,
tblADusers.Userdomain,
tblADGroups.Name
From tblADusers
Inner Join tblADMembership On tblADusers.ADObjectID =
tblADMembership.ChildAdObjectID
Inner Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADusers.Userdomain = 'Production') CTE_Production
Full Outer Join (Select tblADusers.Username + tblADGroups.Name As ID,
tblADusers.Username,
tblADusers.Userdomain,
tblADGroups.Name
From tblADusers
Inner Join tblADMembership On tblADusers.ADObjectID =
tblADMembership.ChildAdObjectID
Inner Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADusers.Userdomain = 'test') CTE_Test On CTE_Production.ID =
CTE_Test.ID
Full Outer Join (Select tblADusers.Username + tblADGroups.Name As ID,
tblADusers.Username,
tblADusers.Userdomain,
tblADGroups.Name
From tblADusers
Inner Join tblADMembership On tblADusers.ADObjectID =
tblADMembership.ChildAdObjectID
Inner Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADusers.Userdomain = 'Acceptance') CTE_Acceptance On
CTE_Production.ID = CTE_Acceptance.ID
Where (CTE_Production.ID = CTE_Test.ID And CTE_Acceptance.ID Is Null) Or
(CTE_Production.ID = CTE_Acceptance.ID And CTE_Test.ID Is Null) Or
(CTE_Production.ID Is Not Null And CTE_Test.ID Is Null And
CTE_Acceptance.ID Is Null) Or
(CTE_Production.ID Is Null And CTE_Test.ID = CTE_Acceptance.ID) Or
(CTE_Production.ID Is Null And CTE_Test.ID Is Not Null And
CTE_Acceptance.ID Is Null) Or
(CTE_Production.ID Is Null And CTE_Test.ID Is Null And
CTE_Acceptance.ID Is Not Null)
Order By [check],
UserNamePRD,
DomainPRD