none
dis aggregate datetime

    Question


  • --I WANT TO DISAGGREGATE THE DATETIME BASED ON INTERVAL-SIZE COLUMN 

    DECLARE @TEMP  TABLE
    ([DATETIME] DATETIME,
    SAMPLES INT,
    INTERVAL INT )
     
    INSERT INTO @TEMP 
    VALUES

    ('10-05-2013 1:15:00',45,05),
    ('10-05-2013 1:30:00',33,05),
    ('10-05-2013 1:45:00',12,05),
    ('10-05-2013 2:00:00',6,05)

     DECLARE @FINALTEMP TABLE 
    ([DATETIME] DATETIME,
    SAMPLES INT)
    INSERT INTO @FINALTEMP
    VALUES
    ('10-05-2013 1:05:00',15),
    ('10-05-2013 1:10:00',15),
    ('10-05-2013 1:15:00',15),
    ('10-05-2013 1:20:00',11),
    ('10-05-2013 1:25:00',11),
    ('10-05-2013 1:30:00',11),
    ('10-05-2013 1:35:00',4),
    ('10-05-2013 1:40:00',4),
    ('10-05-2013 1:45:00',4),
    ('10-05-2013 1:50:00',2),
    ('10-05-2013 1:55:00',2),
    ('10-05-2013 2:00:00',2)


    SELECT * FROM @TEMP

    SELECT * FROM @FINALTEMP

    Wednesday, October 09, 2013 5:25 PM

Answers

  • Hi rama,

    Please try the following codes:

    DECLARE @TEMP  TABLE
    ([DATETIME] DATETIME,
    SAMPLES INT,
    INTERVAL INT )
     
    INSERT INTO @TEMP 
    VALUES
    
    ('10-05-2013 1:15:00',45,05),
    ('10-05-2013 1:30:00',33,05),
    ('10-05-2013 1:45:00',12,05),
    ('10-05-2013 2:00:00',6,05)
    
    ;with cte as
    (
     SELECT [datetime],samples,interval,ROW_NUMBER() over (order by [datetime]) as RowNum
      FROM @TEMP)
      ,cte1 as(
      select case when t1.[datetime] is null then DATEADD(mi,-15,t2.[datetime]) else t1.[DATETIME] end as [Startdatetime],t2.*
      from cte t1
     right outer join cte t2
     on t2.rownum = t1.rownum+1
     )
    select DATEADD(mi,c.interval,c.startdatetime) as [Datetime]
    ,c.SAMPLES/(DATEDIFf(mi,c.startdatetime,c.[datetime])/c.INTERVAL) as Value
    from cte1 c
    union all
    select DATEADD(mi,-c.interval,c.[DATETIME]) as [Datetime]
    ,c.SAMPLES/(DATEDIFf(mi,c.startdatetime,c.[datetime])/c.INTERVAL) as Value
    from cte1 c
    union all
    select c.[DATETIME]
    ,c.SAMPLES/(DATEDIFf(mi,c.startdatetime,c.[datetime])/c.INTERVAL) as Value
    from cte1 c
    order by [datetime] 

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    • Marked as answer by rama rose Wednesday, October 16, 2013 11:41 PM
    Friday, October 11, 2013 8:35 AM
    Moderator

All replies