Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have this table:

enter image description here

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

share|improve this question
    
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
up vote 12 down vote accepted

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
;
share|improve this answer
SELECT DomainId
     , max(SoldTickets) as current
     , max(SoldTickets) - min(SoldTickets) as last15minutes
  FROM DomainDetailDataHistory
 WHERE [Date] > DATEADD(minute, -15, GETDATE()) 
 GROUP BY DomainId
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.