none
sum of values ​​for every 12 hours of one day. RRS feed

  • Question

  • I'm looking for a way to get the sum of the values ​​in the range of the first and second half of each day for 12 hours

    range of hours of the first half of the day 00:00 - 12:00
    the range of the after noon 12:00 - 24:00

    I was looking for a solution in Google for a long time but could not find it

    CREATE TABLE [dbo].[#TABLE](
    [numreq] [int] NULL,
    [rangetime] [nvarchar](300) NULL
    ) ON [PRIMARY]
    
    insert into [dbo].[#TABLE] values ('1','2019-06-25 01:00:00.000')
    insert into [dbo].[#TABLE] values ('2','2019-06-25 11:00:00.000')
    insert into [dbo].[#TABLE] values ('1','2019-06-25 15:00:00.000')
    insert into [dbo].[#TABLE] values ('14','2019-06-25 20:00:00.000')
    
    insert into [dbo].[#TABLE] values ('1','2019-06-26 03:00:00.000')
    insert into [dbo].[#TABLE] values ('2','2019-06-26 07:00:00.000')
    insert into [dbo].[#TABLE] values ('3','2019-06-26 16:00:00.000')
    insert into [dbo].[#TABLE] values ('4','2019-06-26 21:00:00.000')


    the result I want to get:
    3,first 12 hour 2019-06-25
    15,last 12 hour 2019-06-25
    3,first 12 hour 2019-06-26
    7,last 12 hour 2019-06-26

    Thursday, July 18, 2019 1:17 PM

Answers

  • CREATE TABLE #TABLE(
    [numreq] [int] NULL,
    [rangetime] [nvarchar](300) NULL
    ) ON [PRIMARY]
    
    insert into #TABLE values ('1','2019-06-25 01:00:00.000')
    insert into #TABLE values ('2','2019-06-25 11:00:00.000')
    insert into #TABLE values ('1','2019-06-25 15:00:00.000')
    insert into #TABLE values ('14','2019-06-25 20:00:00.000')
    
    insert into #TABLE values ('1','2019-06-26 03:00:00.000')
    insert into #TABLE values ('2','2019-06-26 07:00:00.000')
    insert into #TABLE values ('3','2019-06-26 16:00:00.000')
    insert into #TABLE values ('4','2019-06-26 21:00:00.000')
    
    
    SELECT CAST(rangetime AS date) Date,  SUM(numreq) AS result
    FROM #TABLE
    GROUP BY CAST(rangetime AS date), CASE WHEN DATEDIFF(HOUR,CAST(rangetime AS date),CAST(rangetime AS datetime)) BETWEEN 0 AND 12 THEN 1 ELSE 0 END
    
    DROP TABLE #TABLE


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by Aleks Roth Sunday, July 21, 2019 2:27 PM
    Thursday, July 18, 2019 1:34 PM

All replies

  • CREATE TABLE #TABLE(
    [numreq] [int] NULL,
    [rangetime] [nvarchar](300) NULL
    ) ON [PRIMARY]
    
    insert into #TABLE values ('1','2019-06-25 01:00:00.000')
    insert into #TABLE values ('2','2019-06-25 11:00:00.000')
    insert into #TABLE values ('1','2019-06-25 15:00:00.000')
    insert into #TABLE values ('14','2019-06-25 20:00:00.000')
    
    insert into #TABLE values ('1','2019-06-26 03:00:00.000')
    insert into #TABLE values ('2','2019-06-26 07:00:00.000')
    insert into #TABLE values ('3','2019-06-26 16:00:00.000')
    insert into #TABLE values ('4','2019-06-26 21:00:00.000')
    
    
    SELECT CAST(rangetime AS date) Date,  SUM(numreq) AS result
    FROM #TABLE
    GROUP BY CAST(rangetime AS date), CASE WHEN DATEDIFF(HOUR,CAST(rangetime AS date),CAST(rangetime AS datetime)) BETWEEN 0 AND 12 THEN 1 ELSE 0 END
    
    DROP TABLE #TABLE


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by Aleks Roth Sunday, July 21, 2019 2:27 PM
    Thursday, July 18, 2019 1:34 PM
  • CREATE TABLE [dbo].[#TABLE](
    [numreq] [int] NULL,
    [rangetime] datetime NULL
    ) 
    
    insert into [dbo].[#TABLE] values 
    ('1','2019-06-25 01:00:00.000')
    , ('2','2019-06-25 11:00:00.000')
    ,('1','2019-06-25 15:00:00.000')
    , ('14','2019-06-25 20:00:00.000')
    ,('1','2019-06-26 03:00:00.000')
    ,('2','2019-06-26 07:00:00.000')
    ,('3','2019-06-26 16:00:00.000')
    , ('4','2019-06-26 21:00:00.000')
    
    Select SUM([numreq]) Total, cast([rangetime] as date)  as dt, 
    CASE when Datediff(hour,cast(cast([rangetime] as date) as datetime),Cast([rangetime]  as  datetime)) /12 =0 then 'first 12 hour' else 'last 12 hour' end
    HrRange
    from [dbo].[#TABLE]
    Group by 
    cast([rangetime] as date) , 
    Datediff(hour,cast(cast([rangetime] as date) as datetime),Cast([rangetime]  as  datetime)) /12
     Order by cast([rangetime] as date) 
    
    drop   TABLE [dbo].[#TABLE]  

    Thursday, July 18, 2019 1:34 PM
    Moderator
  • Here it is:

    CREATE TABLE #TABLE(
    numreq int NULL,
    rangetime nvarchar(300) NULL
    ) 
    GO
    
    insert into #TABLE values ('1','2019-06-25 01:00:00.000')
    insert into #TABLE values ('2','2019-06-25 11:00:00.000')
    insert into #TABLE values ('1','2019-06-25 15:00:00.000')
    insert into #TABLE values ('14','2019-06-25 20:00:00.000')
    
    insert into #TABLE values ('1','2019-06-26 03:00:00.000')
    insert into #TABLE values ('2','2019-06-26 07:00:00.000')
    insert into #TABLE values ('3','2019-06-26 16:00:00.000')
    insert into #TABLE values ('4','2019-06-26 21:00:00.000')
    GO
    
    SELECT CONVERT(date, rangetime) AS Dt, DATEPART(hour, rangetime) / 12, SUM(numreq)
    FROM #TABLE
    group by CONVERT(date, rangetime), DATEPART(hour, rangetime) / 12
    order by CONVERT(date, rangetime), DATEPART(hour, rangetime) / 12
    
    go
    drop table #TABLE


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, July 18, 2019 1:38 PM

  • WITH cte
    AS
    (
    SELECT *, DATEPART(HOUR, CAST(rangetime AS DATETIME)) / 12 cn FROM [#TABLE]
    ) ,cte1
    AS
    (SELECT *,SUM(numreq) OVER (PARTITION BY CAST(rangetime AS DATE),cn ORDER BY (SELECT 0)
     ROWS BETWEEN UNBOUNDED PRECEDING
                             AND CURRENT ROW) sumr
    FROM cte
    ) SELECT MAX(sumr)sumr ,CAST(rangetime AS DATE),
    CASE WHEN cn =0 THEN 'first 12 hour' 
          WHEN cn =1 THEN 'last 12 hour' 
    END
    FROM cte1
    group by CAST(rangetime AS DATE),cn
    ORDER BY CAST(rangetime AS DATE)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, July 18, 2019 1:42 PM
    Answerer
  • HI Aleks Roth

    I hope following query help to you

    select cast(rangetime as date) dt,sum(numreq) total  from #TABLE group by cast (rangetime as date),Datepart(hh,rangetime)/12 order by dt

    Thursday, July 18, 2019 2:09 PM
  • Did you know that by definition a table must have a key? Your attempted DDL made this impossible. I'm also trying to figure out why you think "table" is a good name for a table. You also don't seem to know the ANSI ISO syntax the insertion statement, so you use the old "punch card at a time" idiom from the original Sybase. You also don't seem to know that we have date and time datatypes in SQL and have for quite a few years now. Putting them in strings is an old COBOL trick from over 50 years ago.

    CREATE TABLE Events
    (event_cnt INTEGER NOT NULL,
     event_timestamp DATETIME2(0) NOT NULL PRIMARY KEY);

    One of the many advantages of the standard insertion notation is that the entire set is put in as a unit of work, instead of "punch card by punch card" like we had to in the old days. The order of the rows does not matter.

    INSERT INTO Events
    VALUES 
    (1, '2019-06-25 01:00:00'),
    (1, '2019-06-25 15:00:00'),
    (1, '2019-06-26 03:00:00'),
    (2, '2019-06-26 07:00:00'),
    (2, '2019-06-25 11:00:00'),
    (3, '2019-06-26 16:00:00'),
    (4, '2019-06-26 21:00:00'),
    (14, '2019-06-25 20:00:00'),

    WITH Event_Classes
    AS
    (SELECT event_cnt, CAST(event_timestamp AS DATE) AS event_date,
        WHEN CAST (event_timestamp AS TIME) BETWEEN '00:00:00' AND '12:00:00'
        THEN 'Morning Event' ELSE 'Evening Event' END AS event_timeslot
    FROM Events)
    AS X (event_cnt, event_date, event_timeslot)
     SELECT event_date, event_timeslot, SUM(event_cnt) AS event_cnt_tot
      FROM X 
    GROUP BY event_date, event_timeslot;

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, July 18, 2019 4:36 PM
  • Hi there,

    IMHO, CELCO suggested a cleanest solution.

    Here is its slightly modified version, to make it runnable (I just fixed a couple of typos) in SSMS.

    All credit goes to CELCO.

    DECLARE @Events TABLE (event_cnt INTEGER NOT NULL, event_timestamp DATETIME2(0) NOT NULL PRIMARY KEY); INSERT INTO @Events VALUES (1, '2019-06-25 01:00:00'), (1, '2019-06-25 15:00:00'), (1, '2019-06-26 03:00:00'), (2, '2019-06-26 07:00:00'), (2, '2019-06-25 11:00:00'), (3, '2019-06-26 16:00:00'), (4, '2019-06-26 21:00:00'), (14, '2019-06-25 20:00:00'); ;WITH Event_Classes(event_cnt, event_date, event_timeslot) AS ( SELECT event_cnt, CAST(event_timestamp AS DATE) AS event_date, CASE WHEN CAST (event_timestamp AS TIME) BETWEEN '00:00:00' AND '12:00:00' THEN 'Morning Event' ELSE 'Evening Event' END AS event_timeslot FROM @Events ) SELECT event_date, event_timeslot, SUM(event_cnt) AS event_cnt_tot FROM Event_Classes GROUP BY event_date, event_timeslot

    ORDER BY event_date, event_timeslot DESC;



    Thursday, July 18, 2019 5:00 PM
  • Hi Aleks Roth,

     

    Here's the code you can refer to:

    ;with cte as (
    select *,convert(date,rangetime,120)as [date],
    case when datepart(hour,rangetime)<12 then 'first 12 hour'
    else 
    'late 12 hour'
    end as dep
    from [dbo].[#TABLE])
    
    select * from (
    select [date], dep, numreq
    from  cte)p
    pivot (
    sum(numreq)for [dep] in 
    ([first 12 hour],[late 12 hour]))as pvt 
    
    /*
    date       first 12 hour late 12 hour
    ---------- ------------- ------------
    2019-06-25 3             15
    2019-06-26 3             7
    */

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 19, 2019 7:26 AM