Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Question Unanswered: Selecting x AS x, and then using that AS in the next field

    Hi Guys,

    I wonder if you can help with a query I'm trying to create. I'm aiming to run a select statement which provides the number of days between appointment dates, but within a period of time. i.e. I want to run a report from 01/04/2012 to 31/03/2013, if an appointment starts before April 2012 then I want it to show April 2012, if it ends after March 2013, I want it to show March 2013. This bit was easy enough using CASE.

    The next bit however is causing me a problem. My select statement including the above now looks like

    SELECT... start_date, end_date, CASE... AS v_start_date, CASE... AS v_end_date,
    FROM...

    I now want to do a DateDiff between the two v_dates but it says that these are invalid column names. I'm not sure how to structure an sql statement to allow for these. This is what I have so far;

    Code:
    select	start_date, 
    		CASE	WHEN start_date < '2012-04-01 00:00:00.000' 
    			THEN '2012-04-01 00:00:00.000'
    			WHEN start_date is null
    			THEN '2012-04-01 00:00:00.000' 
    			ELSE start_date
    		END as v_start_date,
    		end_date, 
    		CASE	WHEN end_date > '2013-03-31 00:00:00.000' 
    			THEN '2013-03-31 00:00:00.000'
    			WHEN end_date is null
    			THEN '2013-03-31 00:00:00.000' 
    			ELSE end_Date
    		END as v_end_date,
    		DATEDIFF(DAY, v_start_date, v_end_date) as v_days,
    from		appointments
    where	start_date >= '2012-04-01 00:00:00.000'
    and		( 
    			end_Date is null 
    		or	end_Date <= '2013-03-31 00:00:00.000' 
    		)
    order by emp_no
    Error message says:

    Code:
    Msg 207, Level 16, State 1, Line 24
    Invalid column name 'v_start_date'.
    Msg 207, Level 16, State 1, Line 24
    Invalid column name 'v_end_date'.
    Really appreciate it if someone can provide a way around this or even just advise if this isn't possible.

    Christy
    Last edited by christyxo; 09-06-12 at 06:18.

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Never mind. I've solved it now

    Instead of using v_start_date in the datediff function I replaced it with the whole CASE function and it now works.

    Code:
    select	emp_no, 
    		start_date, 
    		end_date, 
    		DATEDIFF
    	(
    		DAY, 
    		CASE	WHEN date_appointed < '2012-04-01 00:00:00.000' 
    			THEN '2012-04-01 00:00:00.000'
    			WHEN date_appointed is null
    			THEN '2012-04-01 00:00:00.000' 
    			ELSE date_appointed
    		END, 		
    		CASE	WHEN end_date > '2013-03-31 00:00:00.000' 
    			THEN '2013-03-31 00:00:00.000'
    			WHEN end_date is null
    			THEN '2013-03-31 00:00:00.000' 
    			ELSE end_Date
    		END
    	) as v_days,
    from	appointments
    order by emp_no
    Last edited by christyxo; 09-06-12 at 06:28.

  3. #3
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    ...ps I know that I have an issue with how I've phrased end_date > and date_appointment < but I've just corrected these

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Looking at DATEDIFF function it requires only 2 parameters (you have 3):

    DATEDIFF(DAY, v_start_date, v_end_date)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by it-iss.com View Post
    Looking at DATEDIFF function it requires only 2 parameters (you have 3):
    that's because he's actually running microsoft sql server, not mysql

    you can tell by the error messages
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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