Solved

sql query case statement

Posted on 2014-12-30
3
240 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
3 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher 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 65

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now