How to use custom PIVOT in SQL server ? how to show week no between from and to date using PIVOT operator in SQL server ?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I have one table "tblEmployeeDetails" where employee details are saved.

Emp ID  Emp-Name Age  Date_of_Joining
-------------------------------------
E001    Mahesh   25   18-01-2016
E004    Suresh   29   13-01-2016 
E002    Kumar    23   10-01-2016
E003    Raj      27   26-01-2016

when search from date as '01-01-2016' and to date as 31-01-2016.The summary result will show in the Output as :

[01-2016]  [02-2016]  [03-2016] [04-2016] [05-2016]
----------------------------------------------------
   0           2          1        1         0
----------------------------------------------------

in the output, the header is showing [Week No - Year ],
from date and to date ,(1-jan-2016 to 31-Jan-2016) - 5 weeks are there.
so in the output it is showing 5 weeks with year.
in the 1st week, no employees have joined.
in the 2nd week , 2 employees have joined.
in the 3rd week , 1 employee has joined.
in the 4th week, 1 employee has joined.
in the 5th week, no employees has joined.

i don't know how to show this output..
Please help..
Thanks in advance...

Regards
Mahesh

What I have tried:

Emp ID  Emp-Name Age  Date_of_Joining
-------------------------------------
E001    Mahesh   25   18-01-2016
E004    Suresh   29   13-01-2016 
E002    Kumar    23   10-01-2016
E003    Raj      27   26-01-2016

Posted 27-Nov-16 22:59pm

Mahesh Pattnayak

Updated 28-Nov-16 0:07am

Add a Solution

Comments

OriginalGriff 28-Nov-16 5:11am

What have you tried?
Where are you stuck?
What help do you need?
Show us the SQL you have used, and explain what it does that you didn't expect, or doesn't do that you did.
Use the "Improve question" widget to edit your question and provide better information.

Mahesh Pattnayak 28-Nov-16 5:18am

sir i don't know what to do in this ?

Mahesh Pattnayak 28-Nov-16 5:17am

sir i don't know what to do in this ?

2 solutions

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)

OriginalGriff · Answer 1 · 2016-11-28T00:07:00

Solution 1

Quote:
sir i don't know what to do in this ?

So start by getting the data into a aggregated form:

SQL

SELECT DatePart(ww, Date_of_Joining) AS WeekNo, COUNT(*) AS PerMonth 
FROM tblEmployeeDetails 
GROUP BY DatePart(ww, Date_of_Joining)

This gives you the info you need, without the zeroed info, and without the PIVOT.
Next, add the zero information: That's complex, and the simplest way to do it is to create a table of all the week numbers: a single column, containing 1 to 53 inclusive.

SELECT w.WeekNo AS WeekNo, COUNT(s.EMPId) AS PerMonth 
FROM tblEmployeeDetails s
RIGHT JOIN dbo.WeekNumbers w ON w.WeekNo = DatePart(ww, s.Date_of_Joining)
WHERE w.WeekNo BETWEEN 1 AND 5
GROUP BY w.WeekNo

Now, you have the raw data you need:

WeekNo	PerMonth
1	0
2	0
3	2
4	1
5	1

And all you have left to do is PIVOT that. I'll leave that to you! :laugh:

Posted 28-Nov-16 0:07am

OriginalGriff

Updated 28-Nov-16 0:08am

v2

Comments

Mahesh Pattnayak 28-Nov-16 7:18am

Sir here you have fixed the week no between 1 to 5 . but if the from date is 1-feb-2016 and to date is 1-Apr-2016, then how to get the week no .

OriginalGriff 28-Nov-16 7:24am

Use
DATEPART(ww, ...
It returns the week number from a date.

Mahesh Pattnayak 28-Nov-16 7:23am

and how to pivot the query if the date changed.

OriginalGriff 28-Nov-16 7:24am

Use PIVOT!

Mahesh Pattnayak 28-Nov-16 7:27am

in pivot, we have to mention the column names , but here how to mention the week names dyanamically. because if select 4 months data then 16 weeks, if select 2 months , then 8 weeks .

OriginalGriff 28-Nov-16 8:35am

You need a dynamic PIVOT:
http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/

Mahesh Pattnayak 28-Nov-16 7:30am

sir i m doing a project where i need to enter the from date and to date, according to the date, it will show the summary data week wise . how to show the weeks dyanamically using pivot.. please help.

jamuro77 · Answer 2 · 2016-11-28T00:21:00

Solution 2

I'd say you have an almost identical case in the example 3 at Using PIVOT with SQL Server[^]

I hope that is useful for you

Posted 28-Nov-16 0:21am

jamuro77

Updated 28-Nov-16 0:31am