如何在sql server中以周为单位获取数据 [英] How to get the data in week wise in 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.

推荐答案

下面的查询可以给你周末。休息你可以自己加入我假设。

Below query can give you weekend. Rest you can incorporate yourself i assume.

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'

你需要给我们一些结构来继续。我们无法帮助您在不知道源数据的情况下编写查询。



一般情况下，您需要使用Group By来总结，这里;我扔的东西让你进入了球场。

You're going to need to give us some structure to go on. We can't help you write a query without knowing what your source data looks like.

In general, you need to use Group By to sum things up, here;s something I threw together that gets you in the ballpark.

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中以周为单位获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！