Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    37

    Unanswered: Set-based solution

    Thought I'd got my head round using a set-based approach but my brain's gone dead on this one


    CREATE TABLE #mytable (SWID INT , T INT , DateA DATETIME , DateB DATETIME)

    INSERT #mytable (swid , t , DateA , DateB)

    SELECT 63967 , 1 , CAST('31-Mar-2006' AS DATETIME),CAST( '01-Aug-2006'AS DATETIME)
    UNION ALL
    SELECT 63967 , 1 , CAST('31-Mar-2006' AS DATETIME),CAST( '01-Feb-2007'AS DATETIME)
    UNION ALL
    SELECT 63967 , 0 , CAST('15-Mar-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
    UNION ALL
    SELECT 63967 , 0 , CAST('15-Mar-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
    UNION ALL
    SELECT 63967 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
    UNION ALL
    SELECT 63967 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
    UNION ALL
    SELECT 63967 , 9999 , CAST('31-Jan-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
    UNION ALL
    SELECT 63967 , 9999 ,CAST( '31-Jan-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
    UNION ALL
    SELECT 10051 , 1 ,CAST('31-Mar-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
    UNION ALL
    SELECT 10051 , 1 ,CAST( '31-Mar-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
    UNION ALL
    SELECT 10051 , 0 , CAST('15-Mar-2006' AS DATETIME), CAST('01-Aug-2006'AS DATETIME)
    UNION ALL
    SELECT 10051 , 0 , CAST('15-Mar-2006'AS DATETIME) , CAST( '01-Feb-2007'AS DATETIME)
    UNION ALL
    SELECT 10051 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) ,CAST( '01-Aug-2006'AS DATETIME)
    UNION ALL
    SELECT 10051 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME)
    UNION ALL
    SELECT 10051 , 9999 ,CAST( '31-Jan-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
    UNION ALL
    SELECT 10051 , 9999 ,CAST( '31-Jan-2006'AS DATETIME), CAST('01-Feb-2007'AS DATETIME)
    UNION ALL
    SELECT 10051 , 9999 , CAST('31-Dec-2005'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME)
    UNION ALL
    SELECT 10051 , 9999 ,CAST( '31-Dec-2005' AS DATETIME), CAST('01-Feb-2007' AS DATETIME)
    UNION ALL
    SELECT 10051 , 9999 ,CAST('30-Nov-2005'AS DATETIME) , CAST( '01-Aug-2006' AS DATETIME)
    UNION ALL
    SELECT 10051 , 9999 ,CAST( '30-Nov-2005' AS DATETIME), CAST('01-Feb-2007' AS DATETIME)
    select * from #mytable order by SWID desc, DateA desc


    The Columns where T values are 1 and 0 are OK having already been derived. I need to UPDATE the remaining rows from the Default T Value of 9999
    to Decrementing values (starting at -1) commencing at the highest remaining (ie non 9999 T Value) DateA value and working 'backwards'
    'grouping' on SWID

    The DateB value is irrelevant for this purpose


    The desired output is below with derived T values

    SWID T DateA DateB
    63967 1 2006-03-31 00:00:00.000 2006-08-01 00:00:00.000
    63967 1 2006-03-31 00:00:00.000 2007-02-01 00:00:00.000
    63967 0 2006-03-15 00:00:00.000 2006-08-01 00:00:00.000
    63967 0 2006-03-15 00:00:00.000 2007-02-01 00:00:00.000
    63967 -1 2006-02-28 00:00:00.000 2006-08-01 00:00:00.000
    63967 -1 2006-02-28 00:00:00.000 2007-02-01 00:00:00.000
    63967 -2 2006-01-31 00:00:00.000 2006-08-01 00:00:00.000
    63967 -2 2006-01-31 00:00:00.000 2007-02-01 00:00:00.000
    10051 1 2006-03-31 00:00:00.000 2006-08-01 00:00:00.000
    10051 1 2006-03-31 00:00:00.000 2007-02-01 00:00:00.000
    10051 0 2006-03-15 00:00:00.000 2006-08-01 00:00:00.000
    10051 0 2006-03-15 00:00:00.000 2007-02-01 00:00:00.000
    10051 -1 2006-02-28 00:00:00.000 2006-08-01 00:00:00.000
    10051 -1 2006-02-28 00:00:00.000 2007-02-01 00:00:00.000
    10051 -2 2006-01-31 00:00:00.000 2006-08-01 00:00:00.000
    10051 -2 2006-01-31 00:00:00.000 2007-02-01 00:00:00.000
    10051 -3 2005-12-31 00:00:00.000 2006-08-01 00:00:00.000
    10051 -3 2005-12-31 00:00:00.000 2007-02-01 00:00:00.000
    10051 -4 2005-11-30 00:00:00.000 2006-08-01 00:00:00.000
    10051 -4 2005-11-30 00:00:00.000 2007-02-01 00:00:00.000


    Thanks in advance

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Code:
    while (0=0)
    begin
     update #mytable 
     set #mytable.T=( select 
         case when minT=9998 then 1 else minT 
         end as minT 
        from
         (select MIN(T)-1 as minT
         from #mytable t1 
         where t1.SWID=#mytable.SWID 
         group by SWID
        ) as tm
       )
     where #mytable.DateA=
       (
        select  Max(DateA) as MaxDateA 
        from  #mytable t1
        where  t1.SWID=#mytable.SWID
         and T=9999
        group by SWID
       )
      and #mytable.T=9999
     IF @@ROWCOUNT = 0 break
     
    end
    Last edited by mallier; 03-15-06 at 10:23.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Oct 2005
    Posts
    37
    That's great mallier - thanks.
    I'm not sure I understand why the use of the WHILE... loop ?

  4. #4
    Join Date
    Jun 2003
    Posts
    269
    Quote Originally Posted by simonjw83
    That's great mallier - thanks.
    I'm not sure I understand why the use of the WHILE... loop ?
    update statement only update first maximum set of date records .so it has to go thru loop to update next set of record.try without loop then u will understand.

    hmmmmmmm... its time to send ur cerdit card details
    eg:
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

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