CODEWITHSUNDEEP
sql-server
Petr
Petr

Reputation: 23

Sum only values if value of next day has the same value

I have a table TAB1 and need to SUM values GROUP BY name column which is easy but I need to SUM only the values = 24 but only if the value with next date is 24 too.

St. like 

SELECT name, SUM(value) AS SUM_VALUE 
FROM TAB1 
WHERE value = 24 AND --next value of the same name with DATEADD(day,1,date) is 24 too
GROUP BY name

Thanks in advance for your help, regards Petr

   name *date       *value  
    ***************************
    text1   *2016-06-01 *8
    text2   *2016-06-01 *4
    text1   *2016-06-02 *24
    text2   *2016-06-02 *8
    text1   *2016-06-03 *24
    text2   *2016-06-03 *24
    text1   *2016-06-04 *6
    text2   *2016-06-04 *24
    text1   *2016-06-05 *24
    text2   *2016-06-05 *24
    text1   *2016-06-06 *24
    text2   *2016-06-06 *8
    text1   *2016-06-07 *24
    text2   *2016-06-07 *24
    text1   *2016-06-08 *6
    text2   *2016-06-08 *24
    text1   *2016-06-09 *24
    text2   *2016-06-09 *24

calculation algorithm
1 - 48 => 0
2 - 72 => 24
1 - 72 => 24
2 - 72 => 24

The result should be

    name    *SUM_VALUE
    ******************
    text1   *24
    text2   *48

Upvotes: 2

Views: 114

Answers (2)

gofr1
gofr1

Reputation: 15977

With INNER JOIN:

SELECT  name,
        SUM([value]) AS SUM_VALUE
FROM (
    SELECT DISTINCT y1.*
    FROM YourTable y1
    INNER JOIN YourTable y2
        ON y2.name = y1.name AND 
            y2.[date] IN (DATEADD(day,1,y1.[date]),DATEADD(day,-1,y1.[date])) AND
            y2.[value] = y1.[value] and y1.[value] = 24) as p
GROUP BY name

With CROSS APPLY:

SELECT  y1.name,
        SUM(y1.[value]) AS SUM_VALUE
FROM YourTable y1
CROSS APPLY (
    SELECT TOP 1 * 
    FROM YourTable 
    WHERE name = y1.name AND 
        [date] IN (DATEADD(day,1,y1.[date]),DATEADD(day,-1,y1.[date])) AND
        [value] = y1.[value]
    ) as p
WHERE y1.[value] = 24
GROUP BY y1.name

For SQL Server 2012 and up you can use LEAD and LAG:

;WITH cte AS (
SELECT  *, 
        CASE WHEN [value] IN (LAG([value],1,0) OVER (PARTITION BY name ORDER BY [date]), LEAD([value],1,0) OVER (PARTITION BY name ORDER BY [date])) THEN 1 ELSE 0 END as r
FROM YourTable
)

SELECT  name, 
        SUM([value]) as SUM_VALUE
FROM cte
WHERE r = 1 and [value] = 24
GROUP BY name

Output the same for all:

name    SUM_VALUE
text1   48
text2   72

It checks if a value of current string is similar to previous and and next value if so it puts 1 in r column.

EDIT1

Use ((SUM([value])/24)/3) * 24 instead of SUM([value])

EDIT2

I use this with data from your question:

;WITH YourTable AS (
SELECT *
FROM (VALUES
('text1', '2016-06-01', 8),
('text2', '2016-06-01', 4),
('text1', '2016-06-02', 24),
('text2', '2016-06-02', 8),
('text1', '2016-06-03', 24),
('text2', '2016-06-03', 24),
('text1', '2016-06-04', 6),
('text2', '2016-06-04', 24),
('text1', '2016-06-05', 24),
('text2', '2016-06-05', 24),
('text1', '2016-06-06', 24),
('text2', '2016-06-06', 8),
('text1', '2016-06-07', 24),
('text2', '2016-06-07', 24),
('text1', '2016-06-08', 6),
('text2', '2016-06-08', 24),
('text1', '2016-06-09', 24),
('text2', '2016-06-09', 24)
) as t(name, [date], [value])
), cte AS (
SELECT  *, 
        CASE WHEN [value] IN (LAG([value],1,0) OVER (PARTITION BY name ORDER BY [date]), LEAD([value],1,0) OVER (PARTITION BY name ORDER BY [date])) THEN 1 ELSE 0 END as r
FROM YourTable
)

SELECT  name, 
        ((SUM([value])/24)/3) * 24 as SUM_VALUE
FROM cte
WHERE r = 1 and [value] = 24
GROUP BY name

Output:

name    SUM_VALUE
text1   24
text2   48

Upvotes: 1

Cleriston
Cleriston

Reputation: 770

Let me know the result...

    select a.[name], sum(a.[value]) as sum_value
    from table a inner join  (select b.[name], b.[date] from table b where b.[value] = 24) as c 
     on a.name = c.name and where dateadd(d, 1, a.date) = c.date
    group by a.[name]

Upvotes: 0

Related Questions