Results 1 to 2 of 2

Thread: Case Statements

  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Case Statements

    I have a SELECT statement that includes a few CASE Statements, but my problem is that in my second CASE I need to refer to the Column created by the first CASE.

    How can I use the first CASE in the second like below


    Code:
    SELECT ASV.db_serv_job_no AS [JobNo], 
    ASV.prop_seql AS [UPRN], 
    QASV.status AS [Status], 
    QASVS.status AS [SubStatus], 
    ASV.booking_date AS [Latest/NextApptDate], 
    ASV.[1st_na] AS [Appt1],
    ASV.[1st_na] AS [NA1],
    'Appt1Outcome' =
    	CASE 
    		WHEN ASV.[1st_na] = ASV.[1st_na] THEN 'NA1'
    		WHEN ASV.[completion_date] = ASV.[1st_na] THEN 'CompVisit1'
    		ELSE 'UnknAppt1'
    	END,
    ASV.[2nd_na] AS [Appt2],
    ASV.[2nd_na] AS [NA2], 
    'Appt2Outcome' =
    	CASE 
    		WHEN [Appt1Outcome] = 'CompVisit1' THEN 'CompOnVisit1'
    		WHEN ASV.[2nd_na] = ASV.[2nd_na] THEN 'NA2'
    		WHEN ASV.[completion_date] = ASV.[2nd_na] THEN 'CompVisit2'
    		WHEN ASV.[2nd_na]>=GETDATE() THEN 'Appt2Pending'
    		ELSE 'UnknAppt2'
    	END
    FROM dbo.contracts AS C 
    INNER JOIN (dbo.property AS P INNER JOIN ((dbo.servicing_jobs AS ASV 
    	INNER JOIN dbo.quicklist_service_status AS QASV ON ASV.status = QASV.id) 
    INNER JOIN dbo.quicklist_service_status_sub AS QASVS ON ASV.status_sub = QASVS.id) 
    ON P.prop_seql = ASV.prop_seql) ON C.id = P.contract_id
    WHERE (((ASV.booking_date)> CONVERT(DATETIME, '2009-01-09 00:00:00', 102)))
    AND ((C.name_short) NOT LIKE 'Test'+'%')

  2. #2
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Cancel this, I just realised how I should do this.

    Apologies!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •