How to sum between dates using SQL?

Question

Look at the following UPDATED scenario (sorry that I fooled you with the un updated version - hope it will be clearer now) - it may seem simple to some people, and hopefully it is!

Explanation: Every row is a Request. Allocated is the number of people requested to an activity, total is the total amount of people for this country available for activities, and diff is... the diff between the total amount of people and Max people used. And "Max people used..." is what everything else hangs on.

SHOULD BE:                      

COLLECTION OF REQUESTS WITH DIFFERENT START- AND END DATES

COUNTRY START DATE  END DATE    ALLOCATED   TOTAL    Max people used for this period    DIFF
China     NOV 1       NOV 2       2           5         4                                 1
China     NOV 3       NOV 4       2           5         4                                 1
China     NOV 1       NOV 4       2           5         4                                 1


MY RESULT:

COLLECTION OF REQUESTS WITH DIFFERENT START- AND END DATES

COUNTRY START DATE  END DATE    ALLOCATED   TOTAL    Max people used for this period    DIFF
China     NOV 1       NOV 2       2           5         4                                 1
China     NOV 3       NOV 4       2           5         4                                 1
China     NOV 1       NOV 4       2           5         6                                -1

(For NOV 1 to NOV 4 "Max people used..." sums ALLOCATED as 2+2+2 instead of 2+2...)

And here you can see how I got "MY RESULT"

SELECT DISTINCT Country.Country,
-- here comes the sum that doesn´t work...
SUM(Requests.[Amount of people per day needed]) AS [Max people used for this period]
FROM         Country INNER JOIN
                      Requests ON Country.CountryID = Requests.CountryID
WHERE     (Country.Country = 'China')
AND (Requests.[End date] >= @busyStartDate)
AND (Requests.[Start date] <= @busyEndDate)
GROUP BY Country.Country

"Max people used..." is the thing not working here - can I get that right, the rest will follow by business logic that you don´t see here :-)

UPDATE

Actually, I have given this up - didn´t get it to work in spite of good answers. Instead I did this (with the help of others, of course) to finally reach my goal ;-)

Answer 1

All-in-one multiple CTE solution (thanks Martin!)

with AllDates as (
  select  StartDate as [day] from Request
  union
  select  Enddate as [day] from Request),
TotalUsage as (
  select  [day], sum(allocated) as TotalAllocated
    from  AllDates
    join  Request
      on  [day] between StartDate and EndDate
    group by [day]),
MaxPerRequest as (
  select  CountryId, StartDate, EndDate, Allocated, MAX(TotalAllocated) as MaxAllocated
    from  Request
    join  TotalUsage
      on  [day] between StartDate and EndDate
    group by CountryId, StartDate, EndDate, Allocated
    )
select StartDate, EndDate, Allocated, MaxAllocated, Country.People - MaxAllocated as Diff
  from MaxPerRequest
  join Country on MaxPerRequest.CountryId = Country.CountryId

Old temp-table solution

Alas I don't think you can nest CTEs, so you'd need something like

with alldates as (
  select StartDate as [day] from request
  union
  select EndDate as [day] from request)
select [Day], sum(allocated) as TotalAllocated
into #TotalUsage
from alldates
join request on [Day] between StartDate and EndDate
group by [Day];

to build the total allocated per day for all interesting dates into a temporary table. (I can never remember which is the best temp table mechanism for SQL Server sorry - it may be better to go with a table variable, and may need to add indexes + keys either way.) You can then select max(TotalAllocated) from that for a given date range.

with MaxPerRequest as (
  select CountryID, StartDate, EndDate, Allocated, MAX(TotalAllocated) as MaxAllocated
    from request
    join #TotalUsage
      on [DAY] between StartDate and EndDate
    group by CountryID, StartDate, EndDate, Allocated
    )
select StartDate, EndDate, Allocated, MaxAllocated, Country.People - MaxAllocated as Diff
  from MaxPerRequest
  join Country on MaxPerRequest.CountryID = Country.CountryID

drop table #TotalUsage

Sorry I can't think of a simpler solution.

Answer 2

I think the search condition you require will look something like this:

SELECT *
  FROM MyTable AS T1
 WHERE CASE 
          WHEN @start_date > T1.start_date THEN @start_date 
          ELSE T1.start_date 
       END 
       < 
       CASE 
          WHEN @end_date > T1.end_date THEN T1.end_date 
          ELSE @end_date 
       END;

...but I would be making many assumptions about your data to come up with much more e.g. are you using the closed-open representation, are you using DATETIME values, etc?

Temporal database design is very difficult to get right and if your SQL DDL isn't correct then your SQL DML will be very tricky indeed. A good book on the subject is Developing Time-Oriented Database Applications in SQL by Rick Snodgrass, available as a free pdf download.