SELECT
distinct U.userKey
, U.firstName
, U.lastName
, S.sessionKey
, CAST(CAST(MONTH(SU.sessionStart) AS VARCHAR) + '/' + CAST(DAY(SU.sessionStart) AS VARCHAR) + '/' + CAST(YEAR(SU.sessionStart) AS VARCHAR) AS DATETIME) AS sessionDt
, SU.sessionStart
, SU.sessionEnd
, SU.sessionUnitKey
, L.locationKey
, L.name AS locationName
, LPT.productTypeCode
,LPT.title
, CASE WHEN SU.unit IS NULL
THEN
LPT.description
ELSE
'Class ' + SU.unit
END AS sessionType
FROM users U WITH (NOLOCK)
INNER JOIN sessionUnit SU WITH (NOLOCK) ON U.userKey = SU.instructorKey
INNER JOIN session S WITH (NOLOCK) ON SU.sessionKey = S.sessionKey
LEFT OUTER JOIN sessionMap SMM WITH (NOLOCK) on SMM.sessionKey = S.sessionKey
INNER JOIN product P WITH (NOLOCK) ON S.productKey = P.productKey
INNER JOIN lkup_productType LPT WITH (NOLOCK) ON P.productTypeKey = LPT.productTypeKey
INNER JOIN location L WITH (NOLOCK) ON S.locationKey = L.locationKey
WHERE SU.sessionStart BETWEEN '12/1/2014' AND '12/31/2014'
AND (
S.status = 'reserved'
OR (
S.status = 'enabled'
AND (
S.productKey != 1
AND (
CASE WHEN SMM.type = 'SBW'
THEN
(
SELECT COUNT(1)
FROM sessionMap SM WITH (NOLOCK)
WHERE SM.sessionKey = S.sessionKey
) > = IsNull(SU.btwSeatsOverride, 1)
ELSE
(
SELECT COUNT(1)
FROM sessionMap SM WITH (NOLOCK)
WHERE SM.sessionKey = S.sessionKey
) > = IsNull(SU.btwSeatsOverride, S.Seats)
END
)) OR (S.productKey =1)
)
)
ASKER
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
Define 'to whatever is in the database' better.