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 serve

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
;

StackExchange Database Administrators Q#133814, answer score: 13