Non Equi-Join on a time series table I have two tables a and b. A contains start dates and end dates whereas table b contains 10 min bins with values associated to each bin:

a
StartDate            EndDate
2018-03-06 10:13:00  2018-03-06 10:45:00

b
TimeStamp / Amount
2018-03-06 10:10:00 / 100
2018-03-06 10:20:00 / 89
2018-03-06 10:30:00 / 50
2018-03-06 10:40:00 / 78


The result I'd like to acheive is this:

a.StartDate / a.EndDate / b.Amount  
2018-03-06 10:13:00 / 2018-03-06 10:45:00 / 248.0

where b.Amount is equal to = 0.7 * 100 + 89 + 50 + 0.5 * 78


I've started the below query but it's not giving me any results:

SELECT a.StartDate, a.EndDate, b.Amount
FROM a
JOIN b ON a.StartDate &gt; b.TimeStamp
       AND a.EndDate &lt; DATEADD(mm,10,b.TimeStamp)
Your join is incorrect.  It is excluding all rows since TimeStamp can never be before the start date and after the end date.  Change it to:

JOIN b ON a.StartDate &lt; b.TimeStamp
       AND a.EndDate &gt; DATEADD(mm,10,b.TimeStamp)


Also, don't use the contraction, mm to indicate MINUTE (it actually indicates MONTH!).  See this Microsoft Docs page for details.  Use MINUTE:

JOIN b ON a.StartDate &lt; b.TimeStamp
       AND a.EndDate &gt; DATEADD(MINUTE, 10, b.TimeStamp)


To get a calculated total amount, you'll need to use the SUM() aggregate:

SELECT a.StartDate
    , a.EndDate
    , TotalAmount = SUM(b.Amount)
FROM a
JOIN b ON a.StartDate &lt; b.TimeStamp
       AND a.EndDate &gt; DATEADD(MINUTE, 10, b.TimeStamp)
GROUP BY a.StartDate
    , a.EndDate;


Pro-rating the start and end amounts, as you appear to indicate in your question, might be accomplished like this:

SELECT a.StartDate
    , a.EndDate
    , b.TimeStamp
    , b.Amount
    , ProRatedAmount = CASE WHEN DATEDIFF(MINUTE, a.StartDate, b.TimeStamp) &lt; 0 AND DATEDIFF(MINUTE, a.StartDate, b.TimeStamp) &gt; -10
            THEN b.Amount - (DATEDIFF(MINUTE, b.TimeStamp, a.StartDate) / 10.0 * b.Amount)
        WHEN DATEDIFF(MINUTE, a.EndDate, b.TimeStamp) &lt; 0 AND DATEDIFF(MINUTE, a.EndDate, b.TimeStamp) &gt; -10
            THEN DATEDIFF(MINUTE, b.TimeStamp, a.EndDate) / 10.0 * b.Amount
        ELSE b.Amount
        END
FROM a
    JOIN b ON DATEADD(MINUTE, -10, a.StartDate) &lt; b.TimeStamp
       AND b.TimeStamp &lt; DATEADD(MINUTE, 10, a.EndDate);


╔═════════════════════════╦═════════════════════════╦═════════════════════════╦════════╦════════════════╗
║        StartDate        ║         EndDate         ║        TimeStamp        ║ Amount ║ ProRatedAmount ║
╠═════════════════════════╬═════════════════════════╬═════════════════════════╬════════╬════════════════╣
║ 2018-03-06 10:13:00.000 ║ 2018-03-06 10:45:00.000 ║ 2018-03-06 10:10:00.000 ║    100 ║      70.000000 ║
║ 2018-03-06 10:13:00.000 ║ 2018-03-06 10:45:00.000 ║ 2018-03-06 10:20:00.000 ║     89 ║      89.000000 ║
║ 2018-03-06 10:13:00.000 ║ 2018-03-06 10:45:00.000 ║ 2018-03-06 10:30:00.000 ║     50 ║      50.000000 ║
║ 2018-03-06 10:13:00.000 ║ 2018-03-06 10:45:00.000 ║ 2018-03-06 10:40:00.000 ║     78 ║      39.000000 ║
╚═════════════════════════╩═════════════════════════╩═════════════════════════╩════════╩════════════════╝


Aggregating those amounts into a total might be accomplished with a CTE:

;WITH src AS 
(
SELECT a.StartDate
    , a.EndDate
    , Amount = CASE WHEN DATEDIFF(MINUTE, a.StartDate, b.TimeStamp) &lt; 0 AND DATEDIFF(MINUTE, a.StartDate, b.TimeStamp) &gt; -10
            THEN b.Amount - (DATEDIFF(MINUTE, b.TimeStamp, a.StartDate) / 10.0 * b.Amount)
        WHEN DATEDIFF(MINUTE, a.EndDate, b.TimeStamp) &lt; 0 AND DATEDIFF(MINUTE, a.EndDate, b.TimeStamp) &gt; -10
            THEN DATEDIFF(MINUTE, b.TimeStamp, a.EndDate) / 10.0 * b.Amount
        ELSE b.Amount
        END
FROM a
    JOIN b ON DATEADD(MINUTE, -10, a.StartDate) &lt; b.TimeStamp
       AND b.TimeStamp &lt; DATEADD(MINUTE, 10, a.EndDate)
)
SELECT src.StartDate
    , src.EndDate
    , Amount = SUM(src.Amount)
FROM src
GROUP BY src.StartDate
    , src.EndDate
ORDER BY src.StartDate
    , src.EndDate;


╔═════════════════════════╦═════════════════════════╦════════════╗
║        StartDate        ║         EndDate         ║   Amount   ║
╠═════════════════════════╬═════════════════════════╬════════════╣
║ 2018-03-06 10:13:00.000 ║ 2018-03-06 10:45:00.000 ║ 248.000000 ║
╚═════════════════════════╩═════════════════════════╩════════════╝