How to get the data in week wise in sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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.

Posted 28-Jul-15 1:37am

syedkhaleel

Updated 28-Jul-15 1:38am

v2

Add a Solution

2 solutions

Solution 2

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

Posted 28-Jul-15 4:58am

Ben J. Boyle

Comments

[no name] 29-Jul-15 2:48am

Thanks a lot.

[no name] 29-Jul-15 4:04am

Ben,i tried using above query it works fine but in the result i dont see third week of the month i.e 2015-06-19.it displays data for other weeks except third week.
2015-06 2015-06-05 00:00:00.000 1
2015-06 2015-06-12 00:00:00.000 1
2015-06 2015-06-26 00:00:00.000 2
2015-06 2015-07-03 00:00:00.000 1

Ben J. Boyle 29-Jul-15 9:07am

Do you actually have data in that week?

If you don't and you want the week to appear anyway, you may be better off using a table that contains the weeks and the relevant date ranges and joining that in the query. The grouping and sum structure would stay the same but there's be no need for the math on the dates to sort out the ends of the weeks.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Accepted Answer · 2015-07-28T01:57:00

Solution 1

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

SQL

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'

Posted 28-Jul-15 1:57am

_Asif_

Comments

[no name] 28-Jul-15 8:12am

Thanks for solution. but i have another problem here. actually i need to get number of joinees in that weekend in new starts columns.it is the sum of the headcount in that week end.i am not sure how to populate this.if you dnt mind can u assit me to solve this.