Delta of values in same table over time - SQL Server

Question

I have a table of data as below in SQL Server:

+-------+------------+-------------------------+--------------------------+
|  ID   |       IP   |              Date       |                 NumFails |
+-------+------------+-------------------------+--------------------------+
| 21365 | 172.16.2.1 | 2016-05-16 00:20:54.000 |                      200 |
| 21457 | 172.16.3.1 | 2016-05-16 00:21:05.000 |                      295 |
| 21478 | 172.16.4.1 | 2016-05-16 00:22:46.000 |                      128 |
| 24255 | 172.16.2.1 | 2016-05-16 12:22:01.000 |                      213 |
| 24318 | 172.16.3.1 | 2016-05-16 12:22:12.000 |                      297 |
| 24366 | 172.16.4.1 | 2016-05-16 12:23:52.000 |                      243 |
| 25699 | 172.16.2.1 | 2016-05-16 18:21:31.000 |                      226 |
| 25794 | 172.16.3.1 | 2016-05-16 18:21:41.000 |                      347 |
| 25811 | 172.16.4.1 | 2016-05-16 18:22:51.000 |                      270 |
| 27142 | 172.16.2.1 | 2016-05-17 00:22:45.000 |                      227 |
| 27193 | 172.16.3.1 | 2016-05-17 00:22:55.000 |                      347 |
| 27251 | 172.16.4.1 | 2016-05-17 00:23:59.000 |                      270 |
+-------+------------+-------------------------+--------------------------+

I have an idea of how to do this programmatically, but I'm too new to SQL to know how to do this: I want to get the delta of NumFails given a specific time period. For this, I want to be able to do a query that:

Selects IP address from time period A (<2016-05-17 01:00:00.000 and >2016-05-17 00:00:00.000) and matching IP address from time period B (<2016-05-16 01:00:00.000 and >2016-05-16 00:00:00.000) and returns IP address and the difference from period A numfails result MINUS period b numfails result. This is done for every unique IP address in time period A (all are unique) comparing against time period B.

Any easy way to do such a thing? I want to run the report on a daily basis, so period A will shift to today's date, and period B will be the previous day's date. I can pre-populate that with the calling SQL, but I have no clue what to build to grab the two values and do the difference and report.

Can you post required output for sample data above.More info on getting help fast or even upvotes here:https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — TheGameiswar, May 17 '16 at 14:17
See http://stackoverflow.com/questions/634568/how-to-get-difference-between-two-rows-for-a-column-field — Slippery Pete, May 17 '16 at 14:23
Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. — Juan Carlos Oropeza, May 17 '16 at 14:34

score 0 · Accepted Answer · answered May 17 '16 at 14:34

This solution assumes each IP will exist in both timeframes.

declare @StartPeriodA datetime
declare @EndPeriodA datetime
declare @StartPeriodB datetime
declare @EndPeriodB datetime

set @StartPeriodA = '2016-05-17 00:00:00.000'
set @EndPeriodA = '2016-05-17 01:00:00.000'
set @StartPeriodB = '2016-05-16 00:00:00.000'
set @EndPeriodB = '2016-05-16 01:00:00.000'

select a.IP, a.PeriodAFailures - b.PeriodBFailures as 'FailureDifference'
from
(
    select IP, sum(NumFails) as PeriodAFailures
    from YourTable
    where Date between @StartPeriodA and @EndPeriodA
    group by IP
) a
inner join 
(
    select IP, sum(NumFails) as PeriodBFailures
    from YourTable
    where Date between @StartPeriodB and @EndPeriodB
    group by IP
) b on a.IP = b.IP

You can manipulate the dates as needed.

Awesome, thanks so much. I have a slightly better understanding now of how SQL actually can be used to grab and manipulate the values. — Matthew LaComb, May 17 '16 at 20:45

Delta of values in same table over time - SQL Server

1 Answers1