Help Needed For writting a query (SQl Server 2005)

[Dinesh]

Hi experts,

I am working on SQL Server 2005.
Now i have to write a query which will extract some information from a
table.
My main table is having few columns supose 3 columns.

EmpID Supervisor_ID Date_Of_Visit

Now records are entering in this tabl. Date of visit is date of
supervisor vist on which employee supervisor visited his work.
Generally its on monthly basis. A new row is entered in the table on
every visit. But in some cases employee may be visited more than once
may be two times or 10 times depending on the situation, and for each
visit one new row is inserted in the table.

Now i have to extract the information from last one yearppose I am
running this query today In August 2008 then it should show the
employee id and his date of visit from September 2007 to August 2008.
All 12 date of visits should appear in row format.
There will be 13 columns in the result.
One for EmpID and rest 12 for date of visits Starting from DOV1, Dov2
till DOV12. And if there will be more than onve Date_Of_Visit for any
employee then it should come in the same month column seprated by
comma. And if there is no visit in any month then that columns should
appear with null value

for ex

EmpID Supervisor_ID Date_Of_Visit
1 300 20/08/2007
1 300 25/08/2007
1 300 29/08/2007
2 250 21/08/2007
1 300 21/09/2007
2 250 21/09/2007
1 300 21/10/2007
2 250 21/10/2007
1 300 21/11/2007
2 250 21/11/2007
1 300 21/12/2007
2 250 21/12/2007
1 300 21/01/2008
2 250 21/01/2008
1 300 21/02/2008
2 250 21/02/2008
2 250 21/05/2008
1 300 02/08/2008
1 300 11/08/2008
2 250 08/08/2008
2 250 19/08/2008
Now i have to write a query which will give result like below

EmpID DOV1 DOV2 DOV3
DOV4 DOV5 DOV6 DOV7 DOV8 DOV9 DOV10
DOV11 DOV12

1 20/08/2007,25/08/2007,29/08/2007 21/09/2007 21/10/2007
21/11/2007 21/12/2007 21/01/2008 21/02/2008
02/08/2008,11/08/2008

2 21/08/2007 21/09/2007 21/10/2007
21/11/2007 21/12/2007 21/01/2008 21/02/2008
08/08/2008,19/08/2008

How i can get this result, Please help me.

Any help wil be appriciated.

Regards
Dinesh

[Dinesh]

On Aug 23, 4:26*pm, Dinesh <[e-mail]:

Hi experts,

I am working on SQL Server 2005.
Now i have to write a query which will extract some information from a
table.
My main table is having few columns supose 3 columns.

EmpID *Supervisor_ID * *Date_Of_Visit

Now records are entering in this tabl. Date of visit is date of
supervisor vist on which employee supervisor visited his work.
Generally its on monthly basis. A new row is entered in the table on
every visit. But in some cases employee may be visited more than once
may be two times or 10 times depending on the situation, *and for each
visit one new row is inserted in the table.

Now i have to extract the information from last one yearppose I am
running this query today In August 2008 *then it should show the
employee id and his date of visit from September 2007 to August 2008.
All 12 date of visits should appear in row format.
There will be 13 columns in the result.
One for EmpID and rest 12 for date of visits Starting from DOV1, Dov2
till DOV12. And if there will be more than onve Date_Of_Visit for any
employee then it should come in the same month column seprated by
comma. And if there is no visit in any month then that columns should
appear with null value

for ex

EmpID *Supervisor_ID * *Date_Of_Visit
1 * * * 300 * * * * * * 20/08/2007
1 * * * 300 * * * * * * 25/08/2007
1 * * * 300 * * * * * * 29/08/2007
2 * * * 250 * * * * * * 21/08/2007
1 * * * 300 * * * * * * 21/09/2007
2 * * * 250 * * * * * * 21/09/2007
1 * * * 300 * * * * * * 21/10/2007
2 * * * 250 * * * * * * 21/10/2007
1 * * * 300 * * * * * * 21/11/2007
2 * * * 250 * * * * * * 21/11/2007
1 * * * 300 * * * * * * 21/12/2007
2 * * * 250 * * * * * * 21/12/2007
1 * * * 300 * * * * * * 21/01/2008
2 * * * 250 * * * * * * 21/01/2008
1 * * * 300 * * * * * * 21/02/2008
2 * * * 250 * * * * * * 21/02/2008
2 * * * 250 * * * * * * 21/05/2008
1 * * * 300 * * * * * * 02/08/2008
1 * * * 300 * * * * * * 11/08/2008
2 * * * 250 * * * * * * 08/08/2008
2 * * * 250 * * * * * * 19/08/2008

Now i have to write a query which will give result like below

EmpID * * * * * * * DOV1 * * * * * * * * DOV2 * * * DOV3
DOV4 * * *DOV5 * * * DOV6 * * * *DOV7 * * * *DOV8 *DOV9 DOV10
DOV11 * * * DOV12

1 * * *20/08/2007,25/08/2007,29/08/2007 21/09/2007 21/10/2007
21/11/2007 21/12/2007 *21/01/2008 *21/02/2008
02/08/2008,11/08/2008

2 * * * * * * * *21/08/2007 * * * * * * 21/09/2007 21/10/2007
21/11/2007 21/12/2007 *21/01/2008 *21/02/2008
08/08/2008,19/08/2008

How i can get this result, Please help me.

Any help wil be appriciated.

Regardstabse
Dinesh

Dear Sir,
Thanks for the early reply.
I am sneding you the my create table and insert statements for your
convenience. I can not see the Northwind data base so some difficulty
to understand the logic, becuase i am not aware of that databse. given
below is the Create table and insert statment
CREATE TABLE [dbo].[Superviser_Visit](
[EmpID] [int] NOT NULL,
[Supervisor_ID] [int] NOT NULL,
[Date_Of_Visit] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
)

INSERT INTO Superviser_Visit VALUES (1, 300, 20/08/2007)
INSERT INTO Superviser_Visit VALUES (1, 300, 25/08/2007)
INSERT INTO Superviser_Visit VALUES (1, 300, 29/08/2007)
INSERT INTO Superviser_Visit VALUES (2, 250, 21/08/2007)
INSERT INTO Superviser_Visit VALUES (1, 300, 21/09/2007)
INSERT INTO Superviser_Visit VALUES (2, 250, 21/09/2007)
INSERT INTO Superviser_Visit VALUES (1, 300, 21/10/2007)
INSERT INTO Superviser_Visit VALUES (2, 250, 21/10/2007)
INSERT INTO Superviser_Visit VALUES (1, 300, 21/11/2007)
INSERT INTO Superviser_Visit VALUES (2, 250, 21/11/2007)
INSERT INTO Superviser_Visit VALUES (1, 300, 21/12/2007)
INSERT INTO Superviser_Visit VALUES (2, 250, 21/12/2007)
INSERT INTO Superviser_Visit VALUES (1, 300, 21/01/2008)
INSERT INTO Superviser_Visit VALUES (2, 250, 21/01/2008)
INSERT INTO Superviser_Visit VALUES (1, 300, 21/02/2008)
INSERT INTO Superviser_Visit VALUES (2, 250, 21/02/2008)
INSERT INTO Superviser_Visit VALUES (2, 250, 21/05/2008)
INSERT INTO Superviser_Visit VALUES (1, 300, 02/08/2008)
INSERT INTO Superviser_Visit VALUES (1, 300, 11/08/2008)
INSERT INTO Superviser_Visit VALUES (2, 250, 08/08/2008)
INSERT INTO Superviser_Visit VALUES (2, 250, 19/08/2008)
So if possible please give some idea to me now.
Thanks for the help.

Regards
Dinesh

[Erland Sommarskog]

Dinesh (di*******@gmail.com) writes:

Hi Erland,
Why i am getting the error if i am trying to use this query with a
table which is having empID as primary key.
Suppose i am having one employee table and that contains two columns
EmpID and EmpName.
Now if i am writting the query

Select E.EmpName,XYZ.DOV1,XYZ.DOV2,XYZ.DOV3,XYZ.DOV4,XYZ. DOV5,
XYZ.DOV6,XYZ.DOV7,XYZ.DOV8,XYZ.DOV9,XYZ.DOV10,XYZ. DOV11,XYZ.DOV12
From Employee As E Left outer join
(
DECLARE @today datetime
SELECT @today = convert(char(8), getdate(), 112)

What are the variable declarations doing in the middle of the
query?

I'm sorry, but I feel obliged to rant a bit here. My intention with
posting answers to questions in SQL forums is not only to give
direct answers, but also help people to help themselves.

The query I posted is fairly advanced, and employs several tricks
in the book. But the problem you had also called for those tricks.

If you want to play the role of the parrot, and just take the query
as it is, with no intention of understanding what is doing, I have
failed.

To start with, why would you join this query with Employees in the first
place? The Employees table is already in the query, but as you did
not provide it the script, I used a dummy, the derived table E. And
I did point this out in my previous post.

So I'm afraid that you will have to spend some time to understand how
this query works and why it works. After all, you are the one who will
be responsible for it and for maintaining it, not me.

As for the variable, I initially added a variable for my Northwind
example, as Northwind only has data in the 1990s. When I posted my
second query, using your tables, I maintained the variable as it
still seemed handy. But you could easily replace it in the query
if you prefer.

FROM (SELECT EmpID = 1 UNION ALL SELECT 2) AS E

So here is the place where you should put in your Employees table.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx