I used Apex Refactor to format the SQL.
SELECT
[Number_of_Req] = COUNT([Req].[Number_of_Req])
FROM
[Req]
INNER JOIN [Date1] ON [Req].[Date1ID] = [Date1].[ID]
INNER JOIN [Date2] ON [Req].[Date2ID] = [Date2].[ID]
INNER JOIN [Time1] ON [Req].[Time1ID] = [Time1].[ID]
INNER JOIN [Time2] ON [Req].[Time2ID] = [Time2].[ID]
LEFT JOIN [Date3] ON [Req].[Date3ID] = [Date3].[ID]
INNER JOIN [Date4] ON [Req].[Date4ID] = [Date4].[ID]
LEFT JOIN [Time3] ON [Req].[Time3ID] = [Time3].[ID]
INNER JOIN [Time4] ON [Req].[Time4ID] = [Time4].[ID]
WHERE
(
[Date1].[ADate] < (SELECT MAX([Date2].[BDate]) FROM [Date2])
OR
(
[Date1].[ADate] = (SELECT MAX([Date2].[BDate]) FROM [Date2])
AND [Time1].[ATime] <= (SELECT MAX([Time2].[BTime]) FROM [Time2] )
)
)
AND
(
(CASE
WHEN [Date3].[CDate] IS NULL THEN
[Date4].[DDate]
ELSE
[Date3].[CDate]
END > (SELECT MIN([Date4].[DDate]) FROM [Date4] )
)
OR
(
CASE
WHEN [Date3].[CDate] IS NULL THEN
[Date4].[DDate]
ELSE
[Date3].[CDate]
END = (SELECT MIN([Date4].[DDate]) FROM [Date4] )
AND
(
CASE
WHEN [Time3].[CTime] IS NULL THEN
[Time4].[DTime]
ELSE
[Time3].[CTime]
END >= (SELECT MIN([Time4].[DTime]) FROM [Time4] )
OR
(
[Time3].[CTime] IS NULL
AND [Time4].[DTime] IS NULL
)
)
)
);
You could also use a CTE
WITH MaxDate2 AS (
SELECT MAX([BDate]) AS MaxBDate FROM [Date2]
),
MaxTime2 AS (
SELECT MAX([BTime]) AS MaxBTime FROM [Time2]
),
MinDate4 AS (
SELECT MIN([DDate]) AS MinDDate FROM [Date4]
),
MinTime4 AS (
SELECT MIN([DTime]) AS MinDTime FROM [Time4]
)
SELECT
COUNT([Req].[Number_of_Req]) AS [Number_of_Req]
FROM
[Req]
INNER JOIN [Date1] ON [Req].[Date1ID] = [Date1].[ID]
INNER JOIN [Date2] ON [Req].[Date2ID] = [Date2].[ID]
INNER JOIN [Time1] ON [Req].[Time1ID] = [Time1].[ID]
INNER JOIN [Time2] ON [Req].[Time2ID] = [Time2].[ID]
LEFT JOIN [Date3] ON [Req].[Date3ID] = [Date3].[ID]
INNER JOIN [Date4] ON [Req].[Date4ID] = [Date4].[ID]
LEFT JOIN [Time3] ON [Req].[Time3ID] = [Time3].[ID]
INNER JOIN [Time4] ON [Req].[Time4ID] = [Time4].[ID]
WHERE
(
[Date1].[ADate] < (SELECT MaxBDate FROM MaxDate2)
OR
(
[Date1].[ADate] = (SELECT MaxBDate FROM MaxDate2)
AND [Time1].[ATime] <= (SELECT MaxBTime FROM MaxTime2)
)
)
AND
(
(
ISNULL([Date3].[CDate], [Date4].[DDate]) > (SELECT MinDDate FROM MinDate4)
)
OR
(
ISNULL([Date3].[CDate], [Date4].[DDate]) = (SELECT MinDDate FROM MinDate4)
AND
(
ISNULL([Time3].[CTime], [Time4].[DTime]) >= (SELECT MinDTime FROM MinTime4)
OR
(
[Time3].[CTime] IS NULL
AND [Time4].[DTime] IS NULL
)
)
)
);
Can you accept my reply as a solution if it works for you?
