none
Msg 512, Level 16, State 1, Line 1

    Question

  • Good day!

    I would like to seek for some help on the query that ive made. I'm getting an error "Msg 512, Level 16, State 1, Line 1" Below is my query:

    select wonum,
    status,(select a.changedate 
      from   wostatus a 
      where  a.status = 'CREATED' 
      and    a.changedate = (select min(b.changedate) from wostatus b where b.wonum = a.wonum and b.status = 'CREATED')) AS 'Start Date' ,   
    (select a.changedate 
      from   wostatus a 
      where  a.status = 'COMP' 
      and    a.changedate = (select min(b.changedate) from wostatus b where b.wonum = a.wonum and b.status = 'COMP')) as 'Completion Date'
     
      from wostatus
      where status in ('Created', 'Comp')

    The ones that are highlighted is were the error is. What im trying to achieve is get the start and end date of a specific wonum based on its status. The structure of the table is that it only has one changedate column, so the only way which is the start and end date is based on the status column. Now i want to get the ELAPSED TIME bet the satrt and end date.

    I hope you can help me with this one. Thanks a lot

    Sunday, September 08, 2013 7:32 AM

All replies

  • Hi Allan,

    I am not sure what you want to achieve by so many condition to same table, it would be helpful if tell some sample data with desired output you want to achieve.

    Meanwhile i have removed some of condition, you can give a try:

    select wonum
    ,status
    ,DateDiff(Minute, [Start Date], [Completion Date]) as 'ELAPSED TIME'
    from (
    select wonum
    ,status
    ,(
    select min(a.changedate)
    from wostatus a
    where a.status = 'CREATED'
    ) as 'Start Date'
    ,(
    select min(a.changedate)
    from wostatus a
    where a.status = 'COMP'
    ) as 'Completion Date'
    from wostatus b
    where status in (
    'Created'
    ,'Comp'
    )
    ) temp


    Regards Harsh

    Sunday, September 08, 2013 7:57 AM
  • Try the below query. It seems like the subquery is returning multiple "change date" for the criteria you have specified.

    Therefore i have added "TOP 1" in the subquery.

    select wonum,
    status,(select TOP 1 a.changedate 
      from   wostatus a 
      where  a.status = 'CREATED' 
      and    a.changedate = (select min(b.changedate) from wostatus b where b.wonum = a.wonum and b.status = 'CREATED')) AS 'Start Date' ,   
    (select TOP 1 a.changedate 
      from   wostatus a 
      where  a.status = 'COMP' 
      and    a.changedate = (select min(b.changedate) from wostatus b where b.wonum = a.wonum and b.status = 'COMP')) as 'Completion Date'
     
      from wostatus
      where status in ('Created', 'Comp')


    Regards, RSingh

    Sunday, September 08, 2013 9:32 AM
  • thanks for the quick reply harsh. i have attached a screenshot of the output. On the said output i want to add a column (Elapsed Time). Now the start and end date are all in one column inthe table. Now in order for me to get the right date i will use the condition where status = 'Created' for the start date and where status = 'COMP' for the end date.
    • Edited by Allan_PH Sunday, September 08, 2013 11:02 AM
    Sunday, September 08, 2013 11:01 AM
  • thanks Rsingh
    Sunday, September 08, 2013 11:09 AM
  • Hello ,

    Please , could you provide the exact and FULL error message you are getting when you are executing your query ? You will find it just after ""Msg 512, Level 16, State 1, Line 1"

    Please , could you also how are you getting this error ( in SQL Server Management Studio , application , .... ) ?

    It would be useful for the potential repliers who will be able to give you a quicker and possibly more appropriate answer.

    Thanks beforehand.


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Sunday, September 08, 2013 11:20 AM
  • Hi Allan,

    I believe this is what you are looking, you want to move the completion date and start date into same row (as currently they are in different row).

    In this case you need to divide your query into 2 part and join them back:

    Please find query(not tested):

    Select 
    a.wonum
    ,a.status	  
    ,a.Createdchangedate
    ,b.completedchangedate
    from
    (
    select wonum,status,a.changedate as Createdchangedate
    from wostatus a where status = 'CREATED'
    ) Temp1
    inner join 
    (
    select wonum,status,a.changedate as completedchangedate
    from wostatus a where status = 'Comp'
    ) Temp2
    on Temp1.wonum = Temp2.wonum


    Regards Harsh

    • Proposed as answer by Naomi N Tuesday, September 10, 2013 3:12 AM
    Sunday, September 08, 2013 11:34 AM
  • Hi Harsh,

    The thing is that currently creation date and completion date are actually on a single row. Now i want to get the time diff between the creation and completion date. In order for me to do that i need to extract creation date by using this condition where status = 'Created' and  where status = 'COMP' for the completion date. Now, once i have extracted all the dates, i need to get their time difference and check if they are within our SLA(Service Level Agreement)

    So what i want is to set a querry for the creation date and query for completion date and get the time difference

    Thanks,

    Allan

    Sunday, September 08, 2013 1:44 PM
  • Hi Allan,

    The thing is that currently creation date and completion date are actually on a single row. 
    In order for me to do that i need to extract creation date by using this condition where status = 'Created' and  where status = 'COMP' for the completion date

    I checked your sample data and didn’t find any column relates to status, could you please provide more detail about this? I made a simple example about how to use “case when” command to judge whether the record relates to create action or complete action, you can refer to the following codes:

    declare @tempTb table
    (
     workorder int
     ,[date] date
     ,[status] varchar(10)
    )
    insert into @tempTb (workorder,[date],[status]) values(1072,'2012-11-10','Created'),(1072,'2012-11-12','COMP' ),(1241,'2012-10-11','Created'),(1241,'2012-11-13','COMP' )
    ; with cte as
    (
    select workorder
    , case when [status]='Created' then [date]
    else null end
    as startDate
    ,case when [status]='COMP' then [date]
    else null end
    as endDate
     from @tempTb
     )
     select workorder
     ,min(startDate) as StartDate
     ,max(EndDate) as EndDate
     from cte
     group by workorder;

    Allen Li
    TechNet Community Support

    • Proposed as answer by Naomi N Tuesday, September 10, 2013 3:11 AM
    Tuesday, September 10, 2013 2:03 AM
  • Ok let me go into details:

    • i have created a query that has an output showing below:

    • Now i want to add a column after SLAType. The name of the column i want to add is SLAStatus
    • SLAStatus is a column field which will only contain 2 (two) values : FIXED or MISSED
    • Fixed value is when the work order was done within SLA hours. There are 2 types of SLA hours, 1st one is 8hrs which SLA Type is Corrective Maintenance, 2nd is 72 hours which is Preventive Maintenance (Scheduled Maintenance) If it did not meet the SLA Hours, then its MISSED.
    • So in order to achieve the said condition above, i need to get the Cretaed and Completion Date. The structure of this table is that Creation and Completion date are on the same column. So in order to get the right date, you have to check the status, if status is created, thats the creation date. If the status is COMP, that is the completion date.
    • Now once i have the creation and completion date, i have to get the elapsed time, once i have the elapsed time, i will check if its within the SLA Target, in order to get the appropriate SLA hours you have to check the SLA Type if its Corrective Maintenance(8hrs) or Preventive Maintenance (72hrs). If its withn SLA target the value of the SLA Status is FIXED, if not its Missed.

    I hope i was able to explain it clearly :) sorry for the confusion

    Thanks

    Allan

    Wednesday, September 11, 2013 1:08 PM