如何在sql server中以周为单位获取数据

问题描述

嗨伙计们，



我试图以下面的格式获取数据。

我是结构，显示周末的周数专栏。



任何人都可以为我提供以下结果的解决方案。

Hi folks,

I am trying to get the data in below format.
I am struct with displaying the weeks in weekending column.

Can anybody provide me the solution to below result.

Month |	Weekending | New Starts | Roll Offs | Voluntary Rolloffs | Involuntary Rolloffs 
Jun-15	5-Jun		10		3		1                 1
	12-Jun		0		0		0                 0
	19-Jun		4		1		2                 1
	26-Jun		3		1		1                 0

先谢谢。

Thanks in Advance.

推荐答案

declare @d1 datetime, @d2 datetime
select @d1 = '6/1/2015',@d2= '6/30/2015'
;with dates ( date )
as
(
select @d1
union all
select dateadd(d,1,date)
from dates
where date < @d2
)
select cast(datename(m, date) as varchar(3))+'-'+cast(year(date) as varchar(4)), date from dates where datename(dw,date) = 'Friday'

Declare @basedate datetime
select @basedate = '2000-01-08 00:00:00.000' -- This needs to be a Friday


Declare @start datetime
Declare @end datetime

Select @start = '2015-06-01 00:00:00.000'
Select @end = '2015-07-01 00:00:00.000'

Select CONVERT(VARCHAR(7), Coalesce(enddate, startdate), 126) as month,
       DATEADD(WEEK, DATEDIFF(WEEK, @basedate,  Coalesce(enddate, startdate)), @basedate)-1 AS ENDWEEK, -- end of week
       sum(case when (StartDate >= @start and StartDate < @end) then 1 else 0 end) as NewStarts,
       sum(case when (EndDate >= @start and EndDate < @end) then 1 else 0 end) as Rolloffs,
       sum(case when (EndDate >= @start and EndDate < @end) and EndReason = 'VOLUNTARY' then 1 else 0 end) as VoluntaryRolloffs,
       sum(case when (EndDate >= @start and EndDate < @end) and EndReason <> 'VOLUNTARY' then 1 else 0 end) as InvoluntaryRolloffs
  from Emps 
 where (StartDate >= @start and StartDate < @end)
    or (EndDate >= @start	and EndDate < @end)

Group by CONVERT(VARCHAR(7), Coalesce(enddate, startdate), 126),
       DATEADD(WEEK, DATEDIFF(WEEK, @basedate,  Coalesce(enddate, startdate)), @basedate)-1

这篇关于如何在sql server中以周为单位获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！