#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    1
    Rep Power
    0

    Create a view to show the backup status in every 10 mins


    I have a question,

    my table have following data:

    userID, startTime, EndTime
    覧覧覧覧覧覧
    101, 04/11/2013 11:00:00, 04/11/2013 11:55:00
    102, 04/11/2013 11:00:00, 04/11/2013 11:24:00
    103, 04/11/2013 11:20:00, 04/11/2013 11:45:00
    104, 04/11/2013 11:30:00, 04/11/2013 11:35:00
    105, 04/11/2013 11:40:00, 04/11/2013 11:55:00
    can I use the view to show the backup status in every 10 mins?

    I wonder the result as following:

    time, count
    覧覧覧覧覧
    04/11/2013 11:00:00, 2
    04/11/2013 11:10:00, 2
    04/11/2013 11:20:00, 3
    04/11/2013 11:30:00, 3
    04/11/2013 11:40:00, 3
    04/11/2013 11:50:00, 2
    04/11/2013 12:00:00, 0



    04/11/2013 11:00:00 04/11/2013 11:09:59 have 2 jobs, 101 & 102
    04/11/2013 11:10:00 04/11/2013 11:19:59 have 2 jobs, 101 & 102
    04/11/2013 11:20:00 04/11/2013 11:29:59 have 3 jobs, 101 & 102 & 103

    04/11/2013 11:50:00 04/11/2013 11:59:59 have 2 jobs, 101 & 105
    04/11/2013 12:00:00 04/11/2013 12:09:59 have 0 job
    I wonder if you can give me a helpthanks a lot
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2007
    Posts
    168
    Rep Power
    7
    This might help
    Code:
    set dateformat dmy
    
    select m.dt, COUNT(t.userID)
    from 
    (select --dt=dateadd(mi,10*number, DATEADD(dd,datediff(dd,'',GETDATE()),''))
     dt=dateadd(mi,10*number, '20131104')
     from master..spt_values where TYPE='P'
      and number<145
    )m
    left join 
    --Test data------------------------------------------------------------
    (select                                                              --
     101, '04/11/2013 11:00:00', '04/11/2013 11:55:00' union all select  --
     102, '04/11/2013 11:00:00', '04/11/2013 11:24:00' union all select  --
     103, '04/11/2013 11:20:00', '04/11/2013 11:45:00' union all select  --
     104, '04/11/2013 11:30:00', '04/11/2013 11:35:00' union all select  --
     105, '04/11/2013 11:40:00', '04/11/2013 11:55:00' )t(               --
     userID, startTime, EndTime)                                         --
     ----------------------------------------------------------------------
       on m.dt between t.startTime and t.EndTime
    where m.dt between '20131104 10:45' and '20131104 12:15'
    group by m.dt
    order by m.dt

IMN logo majestic logo threadwatch logo seochat tools logo