给定一个包含开始日期和结束日期的项目表以及它们正在发生的区域，我试图让我的结果在给定的时间间隔内输出每周活动项目的数量并按地区分组。
我在项目中有很多记录
region start_date end_date Alabama 2012-07-08 2012-08-15 Texas 2012-06-13 2012-07-24 Alabama 2012-07-25 2012-09-13 Texas 2012-08-08 2012-10-28 Florida 2012-07-03 2012-08-07 Lousiana 2012-07-14 2012-08-12 ....如果我想要一周的结果，我可以做类似的事情
DECLARE @today datetime SET @today ='2012-11-09' SELECT [Region], count(*) as ActiveProjectCount FROM [MyDatabase].[dbo].[Projects] where (datecompleted is null and datestart < @today) OR (datestart < @today AND @today < datecompleted) Group by region order by region asc这产生了
Region ActiveProjectCount Arkansas 15 Louisiana 18 North Dakota 18 Oklahoma 27 ...如何更改此查询以生成看起来像的结果
Region 10/06 10/13 10/20 10/27 Arkansas 15 17 12 14 Louisiana 3 0 1 5 North Dakota 18 17 16 15 Oklahoma 27 23 19 22 ...在每周的时间间隔，我能够看到活动项目的总数（开始和结束日期之间的项目）
Given a table of projects with start and end dates as well as a region that they are taking place, I am trying to get my result to output the number of active projects per week over a given interval and grouped by region.
I have many records in Projects that look like
region start_date end_date Alabama 2012-07-08 2012-08-15 Texas 2012-06-13 2012-07-24 Alabama 2012-07-25 2012-09-13 Texas 2012-08-08 2012-10-28 Florida 2012-07-03 2012-08-07 Lousiana 2012-07-14 2012-08-12 ....If I want results for a single week, I can do something like
DECLARE @today datetime SET @today ='2012-11-09' SELECT [Region], count(*) as ActiveProjectCount FROM [MyDatabase].[dbo].[Projects] where (datecompleted is null and datestart < @today) OR (datestart < @today AND @today < datecompleted) Group by region order by region ascThis produces
Region ActiveProjectCount Arkansas 15 Louisiana 18 North Dakota 18 Oklahoma 27 ...How can I alter this query to produce results that look like
Region 10/06 10/13 10/20 10/27 Arkansas 15 17 12 14 Louisiana 3 0 1 5 North Dakota 18 17 16 15 Oklahoma 27 23 19 22 ...Where on a weekly interval, I am able to see the total number of active projects (projects between start and end date)
最满意答案
你可以做某事 喜欢这个：
with "nums" as ( select 1 as "value" union all select "value" + 1 as "value" from "nums" where "value" <= 52 ) , "intervals" as ( select "id" = "value" , "startDate" = cast( dateadd( week, "value" - 1, dateadd(year, datediff(year, 0, getdate()), 0)) as date ) , "endDate" = cast( dateadd( week, "value", dateadd( year, datediff( year, 0, getdate()), 0 )) as date ) from "nums" ) , "counted" as ( select "intervalId" = I."id" , I."startDate" , I."endDate" , D."region" , "activeProjects" = count(D."region") over ( partition by I."id", D."region" ) from "intervals" I inner join "data" D on D."startDate" <= I."startDate" and D."endDate" > I."endDate" ) select * from ( select "region" , "intervalId" from "counted" ) as Data pivot ( count(Data."intervalId") for intervalId in ("25", "26", "27", "28", "29", "30", "31")) as p间隔可以根据需要定义。
看看SQL-Fiddle
you could do sth. like this:
with "nums" as ( select 1 as "value" union all select "value" + 1 as "value" from "nums" where "value" <= 52 ) , "intervals" as ( select "id" = "value" , "startDate" = cast( dateadd( week, "value" - 1, dateadd(year, datediff(year, 0, getdate()), 0)) as date ) , "endDate" = cast( dateadd( week, "value", dateadd( year, datediff( year, 0, getdate()), 0 )) as date ) from "nums" ) , "counted" as ( select "intervalId" = I."id" , I."startDate" , I."endDate" , D."region" , "activeProjects" = count(D."region") over ( partition by I."id", D."region" ) from "intervals" I inner join "data" D on D."startDate" <= I."startDate" and D."endDate" > I."endDate" ) select * from ( select "region" , "intervalId" from "counted" ) as Data pivot ( count(Data."intervalId") for intervalId in ("25", "26", "27", "28", "29", "30", "31")) as pintervals can be defined as you wish.
see SQL-Fiddle
更多推荐
发布评论