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 (dinesht15@gmail.com) writes:
> 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

Here is a query that performs the exercise with Orders in the Northwind
database. I could not do it for your tables, since you did not provide
CREATE TABLE statements for your tables, nor INSERT statements for your
sample data. I hope that you are able to translate the query to your
table. Some remarks:

1) To get the months I make a shortcut, and the query will not produce
the desired result, if there is any month during which no visit at
all took place.
2) To produce the comma-separated list I use FOR XML PATH(''). This
trick is a little difficult to explain quickly, so I only say "It
works".
3) To tranpose the columns, I use MIN(CASE) to pivot the data. The MIN
here only serves to group the different months on the same row.
Since there is only one row per months, MAX would work equally well.

DECLARE @today datetime
SELECT @today = '19971115'

; WITH Months (Month) AS (
SELECT DISTINCT convert(char(6), OrderDate, 112)
FROM Orders
WHERE OrderDate BETWEEN
convert(char(6), dateadd(YEAR, -1, @today), 112) + '01' AND
@today
),
NumberedMonths (Month, Monthno) AS (
SELECT Month, row_number() OVER (ORDER BY Month)
FROM Months
),
OrderDatesMonth (CustomerID, Month, Monthno, dates) AS (
SELECT C.CustomerID, M.Month, M.Monthno,
substring(d.dates, 1, len(d.dates) - 1)
FROM Customers C
CROSS JOIN NumberedMonths M
OUTER APPLY (SELECT convert(char(10), OrderDate, 121) + ',' AS [text()]
FROM Orders O
WHERE O.CustomerID = C.CustomerID
AND O.OrderDate >= M.Month + '01'
AND O.OrderDate < dateadd(MONTH, 1, M.Month +'01')
ORDER BY 1
FOR XML PATH('')) AS d(dates)
)
SELECT CustomerID,
DOV1 = MIN(CASE Monthno WHEN 1 THEN dates END),
DOV2 = MIN(CASE Monthno WHEN 2 THEN dates END),
DOV3 = MIN(CASE Monthno WHEN 3 THEN dates END),
DOV4 = MIN(CASE Monthno WHEN 4 THEN dates END),
DOV5 = MIN(CASE Monthno WHEN 5 THEN dates END),
DOV6 = MIN(CASE Monthno WHEN 6 THEN dates END),
DOV7 = MIN(CASE Monthno WHEN 7 THEN dates END),
DOV8 = MIN(CASE Monthno WHEN 8 THEN dates END),
DOV9 = MIN(CASE Monthno WHEN 9 THEN dates END),
DOV10 = MIN(CASE Monthno WHEN 10 THEN dates END),
DOV11 = MIN(CASE Monthno WHEN 11 THEN dates END),
DOV12 = MIN(CASE Monthno WHEN 12 THEN dates END)
FROM OrderDatesMonth
GROUP BY CustomerID
ORDER BY CustomerID



--
Erland Sommarskog, SQL Server MVP, esquel@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

On Aug 23, 4:26*pm, Dinesh <dinesh...@gmail.com> wrote:
> 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

Dinesh (dinesht15@gmail.com) writes:
> 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

Sorry about that. It occurred me later that I should have given the
link. This sample database came with SQL 2000, but who says that you
ever have seen SQL 2000? Anyway, Northwind is on:
http://www.microsoft.com/downloads/d...displaylang=en

> 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
> )


You have the date column as varchar(10). This is likely to get you
into trouble. How do you prevent from illegal dates being entered?
It's also problematic if you want to make comparisons on the column,
if dates are stored as DD/MM/YYYY. Always stored dates as datetime or
smalldatetime (In SQL 2008 you should use date of course.)

Here is my original query rewritten for your table. I still had to
improvise, because I don't have your Employees table.

DECLARE @today datetime
SELECT @today = convert(char(8), getdate(), 112)

; WITH Months (Month) AS (
SELECT DISTINCT convert(char(6), convert(datetime, Date_Of_Visit), 112)
FROM Superviser_Visit
WHERE Date_Of_Visit BETWEEN
convert(char(6), dateadd(YEAR, -1, @today), 112) + '01' AND
@today
),
NumberedMonths (Month, Monthno) AS (
SELECT Month, row_number() OVER (ORDER BY Month)
FROM Months
),
VisitsMonth (EmpID, Month, Monthno, dates) AS (
SELECT E.EmpID, M.Month, M.Monthno,
substring(d.dates, 1, len(d.dates) - 1)
FROM (SELECT EmpID = 1 UNION ALL SELECT 2) AS E
CROSS JOIN NumberedMonths M
OUTER APPLY (SELECT convert(char(10), S.Date_Of_Visit, 121) + ',' AS [text()]
FROM Superviser_Visit S
WHERE S.EmpID = E.EmpID
AND S.Date_Of_Visit >= M.Month + '01'
AND S.Date_Of_Visit < dateadd(MONTH, 1, M.Month +'01')
ORDER BY 1
FOR XML PATH('')) AS d(dates)
)
SELECT EmpID,
DOV1 = MIN(CASE Monthno WHEN 1 THEN dates END),
DOV2 = MIN(CASE Monthno WHEN 2 THEN dates END),
DOV3 = MIN(CASE Monthno WHEN 3 THEN dates END),
DOV4 = MIN(CASE Monthno WHEN 4 THEN dates END),
DOV5 = MIN(CASE Monthno WHEN 5 THEN dates END),
DOV6 = MIN(CASE Monthno WHEN 6 THEN dates END),
DOV7 = MIN(CASE Monthno WHEN 7 THEN dates END),
DOV8 = MIN(CASE Monthno WHEN 8 THEN dates END),
DOV9 = MIN(CASE Monthno WHEN 9 THEN dates END),
DOV10 = MIN(CASE Monthno WHEN 10 THEN dates END),
DOV11 = MIN(CASE Monthno WHEN 11 THEN dates END),
DOV12 = MIN(CASE Monthno WHEN 12 THEN dates END)
FROM VisitsMonth
GROUP BY EmpID
ORDER BY EmpID


--
Erland Sommarskog, SQL Server MVP, esquel@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

On Aug 25, 1:09*am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Dinesh (dinesh...@gmail.com) writes:
> > 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
>
> Sorry about that. It occurred me later that I should have given the
> link. This sample database came with SQL 2000, but who says that you
> ever have seen SQL 2000? Anyway, Northwind is on:http://www.microsoft.com/downloads/d...D=06616212-035...
>
> > 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
> > * * *)
>
> You have the date column as varchar(10). This is likely to get you
> into trouble. How do you prevent from illegal dates being entered?
> It's also problematic if you want to make comparisons on the column,
> if dates are stored as DD/MM/YYYY. Always stored dates as datetime or
> smalldatetime (In SQL 2008 you should use date of course.)
>
> Here is my original query rewritten for your table. I still had to
> improvise, because I don't have your Employees table.
>
> DECLARE @today datetime
> SELECT @today = convert(char(8), getdate(), 112)
>
> ; WITH Months (Month) AS (
> * *SELECT DISTINCT convert(char(6), convert(datetime, Date_Of_Visit),112)
> * *FROM * Superviser_Visit
> * *WHERE *Date_Of_Visit BETWEEN
> * * * * * * * convert(char(6), dateadd(YEAR, -1, @today), 112) + '01' AND
> * * * * * * * @today
> ),
> NumberedMonths (Month, Monthno) AS (
> * *SELECT Month, row_number() OVER (ORDER BY Month)
> * *FROM * Months
> ), *
> VisitsMonth (EmpID, Month, Monthno, dates) AS (
> * *SELECT E.EmpID, M.Month, M.Monthno,
> * * * * * substring(d.dates, 1, len(d.dates) - 1)
> * *FROM * (SELECT EmpID = 1 UNION ALL SELECT 2) AS E
> * *CROSS *JOIN NumberedMonths M
> * *OUTER *APPLY (SELECT convert(char(10), S.Date_Of_Visit, 121) + ',' AS [text()]
> * * * * * * * * *FROM * Superviser_Visit S
> * * * * * * * * *WHERE *S.EmpID = E.EmpID
> * * * * * * * * * *AND *S.Date_Of_Visit >= M.Month + '01'
> * * * * * * * * * *AND *S.Date_Of_Visit < dateadd(MONTH, 1, M.Month +'01')
> * * * * * * * * *ORDER *BY 1 *
> * * * * * * * * *FOR XML PATH('')) AS d(dates)
> )
> SELECT EmpID,
> * * * *DOV1 *= MIN(CASE Monthno WHEN 1 THEN dates END),
> * * * *DOV2 *= MIN(CASE Monthno WHEN 2 THEN dates END),
> * * * *DOV3 *= MIN(CASE Monthno WHEN 3 THEN dates END), * * *
> * * * *DOV4 *= MIN(CASE Monthno WHEN 4 THEN dates END),
> * * * *DOV5 *= MIN(CASE Monthno WHEN 5 THEN dates END),
> * * * *DOV6 *= MIN(CASE Monthno WHEN 6 THEN dates END),
> * * * *DOV7 *= MIN(CASE Monthno WHEN 7 THEN dates END), * * *
> * * * *DOV8 *= MIN(CASE Monthno WHEN 8 THEN dates END),
> * * * *DOV9 *= MIN(CASE Monthno WHEN 9 THEN dates END),
> * * * *DOV10 = MIN(CASE Monthno WHEN 10 THEN dates END),
> * * * *DOV11 = MIN(CASE Monthno WHEN 11 THEN dates END), * * *
> * * * *DOV12 = MIN(CASE Monthno WHEN 12 THEN dates END)
> FROM * VisitsMonth
> GROUP *BY EmpID
> ORDER *BY EmpID
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@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

Thanks Erland.....
this query solve my problem. Thank you very much

On Aug 25, 10:55*pm, Dinesh <dinesh...@gmail.com> wrote:
> On Aug 25, 1:09*am, Erland Sommarskog <esq...@sommarskog.se> wrote:
>
>
>
>
>
> > Dinesh (dinesh...@gmail.com) writes:
> > > 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
>
> > Sorry about that. It occurred me later that I should have given the
> > link. This sample database came with SQL 2000, but who says that you
> > ever have seen SQL 2000? Anyway, Northwind is on:http://www.microsoft.com/downloads/d...D=06616212-035...
>
> > > 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
> > > * * *)
>
> > You have the date column as varchar(10). This is likely to get you
> > into trouble. How do you prevent from illegal dates being entered?
> > It's also problematic if you want to make comparisons on the column,
> > if dates are stored as DD/MM/YYYY. Always stored dates as datetime or
> > smalldatetime (In SQL 2008 you should use date of course.)
>
> > Here is my original query rewritten for your table. I still had to
> > improvise, because I don't have your Employees table.
>
> > DECLARE @today datetime
> > SELECT @today = convert(char(8), getdate(), 112)
>
> > ; WITH Months (Month) AS (
> > * *SELECT DISTINCT convert(char(6), convert(datetime, Date_Of_Visit), 112)
> > * *FROM * Superviser_Visit
> > * *WHERE *Date_Of_Visit BETWEEN
> > * * * * * * * convert(char(6), dateadd(YEAR, -1, @today),112) + '01' AND
> > * * * * * * * @today
> > ),
> > NumberedMonths (Month, Monthno) AS (
> > * *SELECT Month, row_number() OVER (ORDER BY Month)
> > * *FROM * Months
> > ), *
> > VisitsMonth (EmpID, Month, Monthno, dates) AS (
> > * *SELECT E.EmpID, M.Month, M.Monthno,
> > * * * * * substring(d.dates, 1, len(d.dates) - 1)
> > * *FROM * (SELECT EmpID = 1 UNION ALL SELECT 2) AS E
> > * *CROSS *JOIN NumberedMonths M
> > * *OUTER *APPLY (SELECT convert(char(10), S.Date_Of_Visit, 121) +',' AS [text()]
> > * * * * * * * * *FROM * Superviser_Visit S
> > * * * * * * * * *WHERE *S.EmpID = E.EmpID
> > * * * * * * * * * *AND *S.Date_Of_Visit >= M.Month + '01'
> > * * * * * * * * * *AND *S.Date_Of_Visit < dateadd(MONTH, 1, M.Month +'01')
> > * * * * * * * * *ORDER *BY 1 *
> > * * * * * * * * *FOR XML PATH('')) AS d(dates)
> > )
> > SELECT EmpID,
> > * * * *DOV1 *= MIN(CASE Monthno WHEN 1 THEN dates END),
> > * * * *DOV2 *= MIN(CASE Monthno WHEN 2 THEN dates END),
> > * * * *DOV3 *= MIN(CASE Monthno WHEN 3 THEN dates END), ** *
> > * * * *DOV4 *= MIN(CASE Monthno WHEN 4 THEN dates END),
> > * * * *DOV5 *= MIN(CASE Monthno WHEN 5 THEN dates END),
> > * * * *DOV6 *= MIN(CASE Monthno WHEN 6 THEN dates END),
> > * * * *DOV7 *= MIN(CASE Monthno WHEN 7 THEN dates END), ** *
> > * * * *DOV8 *= MIN(CASE Monthno WHEN 8 THEN dates END),
> > * * * *DOV9 *= MIN(CASE Monthno WHEN 9 THEN dates END),
> > * * * *DOV10 = MIN(CASE Monthno WHEN 10 THEN dates END),
> > * * * *DOV11 = MIN(CASE Monthno WHEN 11 THEN dates END), * * *
> > * * * *DOV12 = MIN(CASE Monthno WHEN 12 THEN dates END)
> > FROM * VisitsMonth
> > GROUP *BY EmpID
> > ORDER *BY EmpID
>
> > --
> > Erland Sommarskog, SQL Server MVP, esq...@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
>
> Thanks Erland.....
> this query solve my problem. Thank you very much- Hide quoted text -
>
> - Show quoted text -

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)

; WITH Months (Month) AS (
SELECT DISTINCT convert(char(6), convert(datetime, Date_Of_Visit),
112)
FROM Superviser_Visit
WHERE Date_Of_Visit BETWEEN
convert(char(6), dateadd(YEAR, -1, @today), 112) + '01'
AND
@today
),
NumberedMonths (Month, Monthno) AS (
SELECT Month, row_number() OVER (ORDER BY Month)
FROM Months
),
VisitsMonth (EmpID, Month, Monthno, dates) AS (
SELECT E.EmpID, M.Month, M.Monthno,
substring(d.dates, 1, len(d.dates) - 1)
FROM (SELECT EmpID = 1 UNION ALL SELECT 2) AS E
CROSS JOIN NumberedMonths M
OUTER APPLY (SELECT convert(char(10), S.Date_Of_Visit, 121) + ','
AS [text()]
FROM Superviser_Visit S
WHERE S.EmpID = E.EmpID
AND S.Date_Of_Visit >= M.Month + '01'
AND S.Date_Of_Visit < dateadd(MONTH, 1, M.Month
+'01')
ORDER BY 1
FOR XML PATH('')) AS d(dates)
)
SELECT EmpID,
DOV1 = MIN(CASE Monthno WHEN 1 THEN dates END),
DOV2 = MIN(CASE Monthno WHEN 2 THEN dates END),
DOV3 = MIN(CASE Monthno WHEN 3 THEN dates END),
DOV4 = MIN(CASE Monthno WHEN 4 THEN dates END),
DOV5 = MIN(CASE Monthno WHEN 5 THEN dates END),
DOV6 = MIN(CASE Monthno WHEN 6 THEN dates END),
DOV7 = MIN(CASE Monthno WHEN 7 THEN dates END),
DOV8 = MIN(CASE Monthno WHEN 8 THEN dates END),
DOV9 = MIN(CASE Monthno WHEN 9 THEN dates END),
DOV10 = MIN(CASE Monthno WHEN 10 THEN dates END),
DOV11 = MIN(CASE Monthno WHEN 11 THEN dates END),
DOV12 = MIN(CASE Monthno WHEN 12 THEN dates END)
FROM VisitsMonth
GROUP BY EmpID
ORDER BY EmpID
) As XYZ
On E.EmpID = XYZ.EmpID

This query is giving the error while executing. I have tried to create
a view for the query provided by you that is also giving syntax error.
Tried to write the following statement at the top of the query

DECLARE @today datetime
SET @today = convert(char(8), getdate(), 112)

but still case is same. So can you help me once more in this case.

Thanks in advance.

Dinesh (dinesht15@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, esquel@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