CODEWITHSUNDEEP
sqlsql-servert-sql
garyamorris
garyamorris

Reputation: 2617

Finding peaks across multiple time series

I want to find the peak value across multiple time series concurrently, like the following. However to make this a little more complicated, I may have 20k+ entities with 200 data points each, so performance is important.

I only require the peak achieved across all entities. So for clarification, let's say my entities are stores and my value is total sales per day, and I want to find out which day was the peak sales day across all 20k stores.

Sample data 

Date Time    Entity Value
01/01/1900  A   8
01/01/1900  B   6
01/01/1900  C   9
02/01/1900  A   4
02/01/1900  B   3
02/01/1900  C   6
03/01/1900  A   7
03/01/1900  B   8
03/01/1900  C   9
04/01/1900  A   1
04/01/1900  B   2
04/01/1900  C   5

OUTPUT

Date Time   A   B   C   Total
01/01/1900  8   6   9   23
02/01/1900  4   3   6   13
03/01/1900  7   8   9   24
04/01/1900  1   2   5   8

However this output is not important, I simple require the peak DateTime and total.

Date Time   Total
03/01/1900  24

Upvotes: 1

Views: 465

Answers (4)

ilhan kaya
ilhan kaya

Reputation: 51

You can use this code block :

select t.date_time ,sum(t.value) as value from test t group by t.date_time order by t.date_time

It gives this output :

https://i.sstatic.net/haU02.png

Upvotes: 0

Vladimir Baranov
Vladimir Baranov

Reputation: 32693

If you need just the date of the maximum sum, then simple GROUP BY would be enough.

SQL Fiddle

SELECT TOP(1)
    dt
    ,SUM(Value) AS Total
FROM T
GROUP BY dt
ORDER BY Total DESC;

Result

dt          Total
1900-01-03  24

Upvotes: 2

Gayathri L
Gayathri L

Reputation: 1487

Try this

SELECT Datetime,A,B,C,Total=SUM([A]+[B]+[C])
FROM Table
PIVOT
(SUM(value)  FOR entity in ([A],[B],[C]))  AS P
GROUP BY Datetime,A,B,C

Upvotes: 0

Vasily
Vasily

Reputation: 5782

here solution using dynamic query 

-- create temp table for data sample
IF OBJECT_ID('Tempdb..#test') IS NOT NULL 
    DROP TABLE #test
CREATE TABLE #test
    (
      [Date Time] DATE ,
      Entity VARCHAR(1) ,
      VALUE INT
    )

INSERT  INTO #test
        ( [Date Time], Entity, VALUE )
VALUES  ( '1900-01-01', 'A', 4 ),
        ( '1900-01-01', 'B', 6 ),
        ( '1900-01-01', 'C', 9 ),
        ( '1900-01-02', 'A', 4 ),
        ( '1900-01-02', 'B', 3 ),
        ( '1900-01-02', 'C', 6 ),
        ( '1900-01-03', 'A', 7 ),
        ( '1900-01-03', 'B', 8 ),
        ( '1900-01-03', 'C', 9 ),
        ( '1900-01-04', 'A', 1 ),
        ( '1900-01-04', 'B', 2 ),
        ( '1900-01-04', 'C', 5 )

--final query

DECLARE @String AS NVARCHAR(MAX) 

SET @String = SUBSTRING(( SELECT    ',[' + CONVERT(VARCHAR, Entity) + ']'
                          FROM      ( SELECT DISTINCT
                                                Entity
                                      FROM      #test
                                    ) AS t
                          ORDER BY  Entity
                        FOR
                          XML PATH('')
                        ), 2, 1000)

SET @String = 'SELECT *, ' + REPLACE(@String, ',', '+') + ' AS Total '
    + 'FROM (SELECT * FROM #test AS [T] PIVOT ( MAX(T.VALUE) FOR [T].[Entity] IN ('
    + @String + ') ) AS P) AS T'
EXEC sys.sp_executesql @String

test is here SQL Fiddle

Upvotes: 0

Related Questions