SQL QUERY showing Between Dates as specific dates + Data belonging to each date!
This is how a table is presented
SELECT RequestsID, Country, Activity,
[People needed (each day)], [Start date], [End date]
FROM dbo.Requests
There will be a lot of requests, and I would like to sum up the "People needed" per day (!), not as Between Start- and End date.
Also I would like to group by country, and have the possibility to set between which dates I want to get data.
Some days might be empty regarding needed people (0), but the Date should be presented anyway. Note that there can be several requests pointing out the same Dates, and the same Country - but the Activity is then different.
The query should be like (well, it´s not SQL as you can see, just trying to show the logic)
From Requests,
show Country and SUM 'People needed'
where (column not in Requests table-Date) is a Date (will be
a number of dates, I want to set the scope by a Start and End date)
and Requests.Country is @Country
(and the Date(s) above of course is between the Requests Start date and End date...)
And from (a non existing table...?) show Date
Group by Country
I would like to see something like this:
Date Country People needed
06/01/2010 Nigeria 34 // this might be from three different Requests, all pointing out Nigeria. People needed might be (30+1+3 = 34)
06/02/2010 Nigeria 10
06/03/2010 Nigeria 0
06/04/2010 Nigeria 1
06/05/2010 开发者_如何学编程 Nigeria 134
06/01/2010 China 2
06/02/2010 China 0
06/03/2010 China 14
06/04/2010 China 23
06/05/2010 China 33
06/01/2010 Chile 3
06/02/2010 Chile 4
06/03/2010 Chile 0
06/04/2010 Chile 0
06/05/2010 Chile 19
How would you do it?
NOTE: I would like to see some kind of example code, to get started :-)
Normally, I'd suggest having a static calendar table which contains a sequential list of dates. However, using Cade Roux's clever approach of generating a calendar table, you would have something like:
;With Calendar As
(
Select Cast(Floor(Cast(@StartDate As float)) As datetime) As [Date]
Union All
Select DateAdd(d, 1, [Date])
From Calendar
Where DateAdd(d, 1, [Date]) < @EndDate
)
Select C.[Date], R.Country, Sum(R.PeopleNeeded)
From Calendar As C
Left Join Requests As R
On C.[Date] Between R.[Start Date] And R.[End Date]
And ( @Country Is Null Or R.Country = @Country )
Group By C.[Date], R.Country
Option (MAXRECURSION 0);
Now, if it is the case that you want to filter on country such that the only days returned are those for the given country that have data, then you would simply need to change the Left Join to an Inner Join.
ADDITION
From the comments, it was requested to show all countries whether they have a Request or not. To do that, you need to cross join to the Countries table:
With Calendar As
(
Select Cast(Floor(Cast(@StartDate As float)) As datetime) As [Date]
Union All
Select DateAdd(d, 1, [Date])
From Calendar
Where DateAdd(d, 1, [Date]) < @EndDate
)
Select C.[Date], C2.Country, Sum(R.PeopleNeeded)
From Calendar As C
Cross Join Countries As C2
Left Join Requests As R
On C.[Date] Between R.[Start Date] And R.[End Date]
And R.CountryId = C2.CountryId
Group By C.[Date], C2.Country
Option (MAXRECURSION 0);
Typically I would use a tally or pivot table of all dates and then join based on that date being between the range.
A technique similar to that discussed here.
something like this?
select d.Date, c.Country, sum(People) as PeopleNeeded
from Dates d left join Requests r on d.Date between r.Start and r.End
group by d.Date, c.Country
where Dates contains an appropriate range of dates, as in Cade Roux's answer
精彩评论