# How to get difference in value over 15min interval?

I have this table:

The 2 important columns here are domainid and Soldtickets.

Soldtickets show how many tickets were sold today.

I make a call to my server and it tells me that I made the call at "2016-03-30 14:18:01.4130000" and that 0 tickets have been sold for the Domain with DomainId 53.

I am trying to figure out how I can find the difference.

I call the server every two minutes and I get data. I want to check the data that I have got the last 15 minutes and see if soldtickets has changes and how much.

Example:

At :

2016-03-30 14:18:01.4130000

For DomainId

53

0 Tickets Sold

At :

2016-03-30 14:26:01.4130000

For DomainId

53

5 Tickets Sold(5 tickest total for today)

At :

2016-03-30 14:30:01.4130000

For DomainId

53

5 Tickets Sold ( same as before, NO change)

I want to know how many were sold the last 15 minutes. In this case it would be 5 tickets but what if at

2016-03-30 14:44:01.4130000 I get back that I have sold a total of 7 tickets for today, then the difference for the last 15 minutes is 2 tickets.

So far I can get the inserts of the last 15 minutes like so:

``````DECLARE @LatestFifteenMinTickets TABLE
(
DomainId bigint,
SoldTickets bigint
)

DECLARE @FifteenMinDate datetime2

SELECT @FifteenMinDate = DATEADD(minute, -15, GETDATE())

INSERT INTO @LatestFifteenMinTickets(DomainId,SoldTickets)
SELECT DomainId, SoldTickets
FROM DomainDetailDataHistory
WHERE [Date] > @FifteenMinDate
``````

I have tried a few scenarios but I honestly don't know how to conntinue.

How can I do this?

EDIT:

Lets say If i get back that I have sold 0 tickets today at 13:00. And the next 15 minutes I get 15 inserts where soldtickets is also 0. the diffirence here is nothing since 0-0 = 0 Which means I have sold 0 tickets the last 15 minutes AND sold 0 tickets for the entire day (so far). But at 14:00 I get back info from server that 10 tickets are sold. The next 15 minutesI keep getting sold tickets = 10 which means that the last 15 min i have sold 0 tickets but for the entire day i have sold 10 tickets –

I Need to check the soldtickets columnt for every unique DomainId and see if there is any difference to the most current one. If there Is i want to know what the difference is in numbers

-
So what you want is 30 minutes ago you had 10 tickets sold, now the last 15 minutes you had 0 tickets sold, so what you want is that it shows you that you have sold -10 tickets? – Stijn Wynants Mar 30 at 13:50

Just group your @LatestFifteenMinTickets table by DomainId and take the difference between `MIN(SoldTickets)` and `MAX(SoldTickets)`. That will give you the number of sold tickets for each domain within the period collected in your table variable:

``````SELECT
DomainId,
SoldToday         = MAX(SoldTickets),
SoldSince15MinAgo = MAX(SoldTickets) - MIN(SoldTickets)
FROM
@LatestFifteenMinTickets
GROUP BY
DomainId
;
``````

Unless SoldTickets can decrease as well as increase (can tickets be returned?), in which case, rather than the minimum and maximum, you should probably take the first and last value. But in order to be able to do that, you should have the Date attribute in your table variable as well. Then the calculation could be organised using the analytic functions FIRST_VALUE and LAST_VALUE:

``````SELECT DISTINCT
DomainId,
SoldToday         = LAST_VALUE(SoldTickets) OVER (PARTITION BY DomainId ORDER BY [Date] ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
SoldSince15MinAgo = LAST_VALUE(SoldTickets) OVER (PARTITION BY DomainId ORDER BY [Date] ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FIRST_VALUE(SoldTickets) OVER (PARTITION BY DomainId ORDER BY [Date] ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM
@LatestFifteenMinTickets
;
``````
-
``````SELECT DomainId
, max(SoldTickets) as current
, max(SoldTickets) - min(SoldTickets) as last15minutes
FROM DomainDetailDataHistory
WHERE [Date] > DATEADD(minute, -15, GETDATE())
GROUP BY DomainId
``````
-