none
SQL Query - Select same column twice RRS feed

  • Question

  • I am wondering if someone can help me out here.  I am trying to create a query that will use the same column twice based about different criteria from another column in the same table.

    Here is what I have, but it isn't working.  Any help would be great appreciated!  Thanks.

    SELECT Job_Operation.Job AS "Job No.",
    Job_Operation.Sched_Start AS "Material Scheduled Start"
    (WHERE Job_Operation.Work_Center = 'MATERIAL'),
    Job_Operation.Sched_Start AS "Cutting Scheduled Start"
    (WHERE Job_Operation.Work_Center = 'CUTTING'),
    FROM Job_Operation
    INNER JOIN Job ON Job_Operation.Job = Job.Job
    WHERE Job.Status = 'Active'
    AND DATEDIFF (d, "Material Scheduled Start", "Cutting Scheduled Start") > 7

    Thursday, June 9, 2011 8:43 PM

Answers

  • Gray,

    Sorry, my fault (I have just typed it in a notepad as I do not have the same tables in my DB as you have I cannot run the query...).

    Try this one:

    SELECT 
    	j.Job AS "Job No.",
    	jm.Sched_Start AS "Material Scheduled Start",
    	jc.Sched_Start AS "Cutting Scheduled Start"
    FROM Job j
    	INNER JOIN Job_Operation jm ON jm.Job = j.Job
    	INNER JOIN Job_Operation jc ON jc.Job = j.Job
    WHERE 
    	j.Status = 'Active'
    	AND jm.Work_Center = 'MATERIAL'
    	AND jc.Work_Center = 'CUTTING'
    	AND DATEDIFF (d, jm.Sched_Start, jc.Sched_Start) > 7



    Regards, Peter

    //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    • Marked as answer by Gray Wolf 7 Tuesday, June 14, 2011 3:44 PM
    Tuesday, June 14, 2011 3:32 PM

All replies

  • My t-sql is rusty, maybe something like:

     

    select job_operation as jobno,

            (select job_operation_sched_start from job_operation inner join job on joboperation.job = job.job where job.status = 'Active' AND work_center = 'MATERIAL1')  as materialscheduledstart,

            (select job_operation_sched_start from job_operation inner join job on joboperation.job = job.job where job.status = 'Active' AND work_center = 'CUTTING')  as cuttingscheduledstart

    from joboperation

      inner join job on joboperation.job = job.job

    where job.status = 'Active' AND

       datediff(d, 'materialschedulestart', 'cuttingschedulestart') > 7

    Thursday, June 9, 2011 9:06 PM
  • Thanks, I think this brings me closer.  I am still getting an error though.

    Invalid column name 'materialscheduledstart'

    Invalid column name 'cuttingscheduledstart'

     

    Thursday, June 9, 2011 9:48 PM
  • Hi Gray Wolf 7,

     

    I have tried to find out what is your table schema looks and what is your main goal.

    If I am right then your task can be solved somehow like this:

     

    SELECT 
    	j.Job AS "Job No.",
    	jm.Sched_Start AS "Material Scheduled Start",
    	jc.Sched_Start AS "Cutting Scheduled Start"
    FROM Job j
    	INNER JOIN Job_Operation jm ON Job_Operation.Job = j.Job
    	INNER JOIN Job_Operation jc ON Job_Operation.Job = j.Job
    WHERE 
    	j.Status = 'Active'
    	AND jm.Work_Center = 'MATERIAL'
    	AND jc.Work_Center = 'CUTTING'
    	AND DATEDIFF (d, jm.Sched_Start, jc.Sched_Start) > 7

     


    The query you are currently trying is wrong because you cannot use columns in the Where clause that have been defined this way. Instead you can repeat the whole select in the where, but I think it is neither readable or effective.

     

    If my query above is not what you want, please let us know what is your table structure and a little bit more about the issue you are facing with.

     


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    Friday, June 10, 2011 8:43 AM
  • Peter,

    This doesn't work.  It produces errors:

    Msg 107, Level 16, State 3, Line 3
    The column prefix 'Job_Operation' does not match with a table name or alias name used in the query.
    Msg 107, Level 16, State 3, Line 3
    The column prefix 'Job_Operation' does not match with a table name or alias name used in the query.

    There is a table named Job_Operation so I am not sure why it is complaining.

    Any other ideas?  I really appreciate any help that anyone can give me.

     

    Tuesday, June 14, 2011 2:54 PM
  • Gray,

    Sorry, my fault (I have just typed it in a notepad as I do not have the same tables in my DB as you have I cannot run the query...).

    Try this one:

    SELECT 
    	j.Job AS "Job No.",
    	jm.Sched_Start AS "Material Scheduled Start",
    	jc.Sched_Start AS "Cutting Scheduled Start"
    FROM Job j
    	INNER JOIN Job_Operation jm ON jm.Job = j.Job
    	INNER JOIN Job_Operation jc ON jc.Job = j.Job
    WHERE 
    	j.Status = 'Active'
    	AND jm.Work_Center = 'MATERIAL'
    	AND jc.Work_Center = 'CUTTING'
    	AND DATEDIFF (d, jm.Sched_Start, jc.Sched_Start) > 7



    Regards, Peter

    //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    • Marked as answer by Gray Wolf 7 Tuesday, June 14, 2011 3:44 PM
    Tuesday, June 14, 2011 3:32 PM
  • select jo.job,
       jo.sched_start as 'Material'
    into #material
    from job_operation jo
      inner join job j
       on jo.job = j.job
    where j.status = 'Active'
     AND jo.work_center = 'MATERIAL'
    
    
    select jo.job,
       jo.sched_start as 'Cutting'
    into #cutting
    from job_operation jo
      inner join job j
       on jo.job = j.job
    where j.status = 'Active'
     AND jo.work_center = 'CUTTING'
    
    
    
    select j.job as Job No,
       ISNULL(m.material,'') as 'Material Schedule Start',
       ISNULL(c.cutting,'') as 'Cutting Schedule Start'
    from job j
      left outer join #material m
        on j.job = m.job
      left outer join #cutting c
        on j.job = c.job
    where j.status = 'Active'
      AND DATEDIFF(d, m.material, c.cutting) > 7
    
    

    Tuesday, June 14, 2011 3:43 PM
  • Peter,

    You're the BEST!!!!!

    It works and I receive the correct results.

    Thank you so much for taking the time to help me out.

    Tuesday, June 14, 2011 3:45 PM
  • Gray,

    You are welcome :)


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    Tuesday, June 14, 2011 3:56 PM