Solved

sql query case statement

Posted on 2014-12-30
3
268 Views
Last Modified: 2014-12-30
i have a query and want to do the case statement in the where clause but I get the error message. Basically I want say if the type is SBW then compare to 1 otherwise comapre to whatever is in the dataabse

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)

			
        )

    ) 

Open in new window

0
Comment
Question by:erikTsomik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40524693
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 (
                              SELECT COUNT(1)
                              FROM sessionMap SM WITH (NOLOCK)
                              WHERE SM.sessionKey = S.sessionKey
                              ) >= IsNull(SU.btwSeatsOverride, CASE WHEN SMM.type = 'SBW' THEN 1 ELSE S.Seats END)
                     )
                  ) OR (S.productKey =1))
        )
    )
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40524695
>Basically I want say if the type is SBW then compare to 1 otherwise comapre to whatever is in the dataabse
Define 'to whatever is in the database' better.
0
 
LVL 19

Author Closing Comment

by:erikTsomik
ID: 40524986
Great. Thank you
0

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question