Loop through a recordset to populate columns in a temp table

[laurenq uantrell]

I want to take the contents from a table of appointments and insert the
appointments for any given month into a temp table where all the
appointments for each day are inserted into a single row with a column
for each day of the month.
Is there a simple way to do this?

I have a recordset that looks like:

SELECT
a.Date,
a.Client --contents: Joe, Frank, Fred, Pete, Oscar
FROM
dbo.tblAppointments a
WHERE
a.date between ...(first and last day of the selected month)

What I want to do is to create a temp table that has 31 columns
to hold appointments and insert into each column any appointments for
the date...

CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2
nvarchar(500), Day3 nvarchar(500), etc...)

Then loop through the recordset above to insert into Day1, Day 2, Day3,
etc. all the appointments for that day, with multiple appointments
separated by a comma.

INSERT INTO
#Appointments(Day1)
SELECT
a.Client
FROM
dbo.tblAppointments a
WHERE
a.date = (...first day of the month)

(LOOP to Day31)
The results would look like
Day1 Day2 Day3 ...
Row1 Joe, Pete
Frank,
Fred

Maybe there's an even better way to handle this sort of situation?
Thanks,
lq

[David Portas]

You can use a query to do reports like this without looping or temp tables.
As specified it seems a little strange to list the dates horizontally since
there is apparently no data on the vertical axis but the rest is really just
a matter of formatting once you have the basic query. Formatting is best
done client-side rather than in the database.

DECLARE @dt DATETIME
/* First date of the month */
SET @dt = '20050501'

SELECT
MAX(CASE WHEN DATEDIFF(DAY,@dt,date)=0 THEN client END),
MAX(CASE WHEN DATEDIFF(DAY,@dt,date)=1 THEN client END),
MAX(CASE WHEN DATEDIFF(DAY,@dt,date)=2 THEN client END),
...
MAX(CASE WHEN DATEDIFF(DAY,@dt,date)=30 THEN client END)
FROM tblAppointments
WHERE date >= @dt
AND date < DATEADD(MONTH,1,@dt)
GROUP BY client

--
David Portas
SQL Server MVP
--

[MGFoster]

laurenq uantrell wrote:

I want to take the contents from a table of appointments and insert the
appointments for any given month into a temp table where all the
appointments for each day are inserted into a single row with a column
for each day of the month.
Is there a simple way to do this?

I have a recordset that looks like:

SELECT
a.Date,
a.Client --contents: Joe, Frank, Fred, Pete, Oscar
FROM
dbo.tblAppointments a
WHERE
a.date between ...(first and last day of the selected month)

What I want to do is to create a temp table that has 31 columns
to hold appointments and insert into each column any appointments for
the date...

CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2
nvarchar(500), Day3 nvarchar(500), etc...)

Then loop through the recordset above to insert into Day1, Day 2, Day3,
etc. all the appointments for that day, with multiple appointments
separated by a comma.

INSERT INTO
#Appointments(Day1)
SELECT
a.Client
FROM
dbo.tblAppointments a
WHERE
a.date = (...first day of the month)

(LOOP to Day31)
The results would look like
Day1 Day2 Day3 ...
Row1 Joe, Pete
Frank,
Fred

Maybe there's an even better way to handle this sort of situation?
Thanks,
lq

You're talking about crosstab queries. Here's a page of links that may
be of use:

http://www.google.com/custom?q=cross...D%3A1%3B&hl=en

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[Erland Sommarskog]

laurenq uantrell (la*************@hotmail.com) writes:

I want to take the contents from a table of appointments and insert the
appointments for any given month into a temp table where all the
appointments for each day are inserted into a single row with a column
for each day of the month.
Is there a simple way to do this?
...
Then loop through the recordset above to insert into Day1, Day 2, Day3,
etc. all the appointments for that day, with multiple appointments
separated by a comma.

I'm a afraid that loop is what you will have to do. And write 31
UPDATE statements, one for each day of the month. There are ways to
build comma-separated lists with set-based statements, but the
methods used are unsupported and undefined, and cannot be trusted.
So the only way to build a CSV is to run a cursor.

OK, you don't really need 31 UPDATE statements. You could aggregate
data into a table with one per date, and then at then end run a
31-way self cross-join to produce the final result.

Certainly, a client program is much better apt to do this sort
of thing.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[laurenq uantrell]

David, thanks for that. I thought it must be pretty straignt forward.
The reason I want it all in one row and the days data in columns is
that I want to populate a sincle record on a form that resembles a
monthly calendar but cwhich in fact is simply 31 text boxes. Previously
I had a form that populated 31 different subforms by looping through
the dates on the client side and it is too slow.THanks,
lq

[laurenq uantrell]

David, thanks for that. I thought it must be pretty straight forward.
The reason I want it all in one row and the days data in columns is
that I want to populate a sincle record on a form that resembles a
monthly calendar but cwhich in fact is simply 31 text boxes. Previously
I had a form that populated 31 different subforms by looping through
the dates on the client side and it is too slow.THanks,
lq

[laurenq uantrell]

Erland,
My solution is to run a UDFwithin a View that creates the comma
separated list for each date of appointments. That part works fine on a
single date, and now I'm just figuring out how to loop through the
dates: first day of the month + 31 days.
Thanks.
lq

[laurenq uantrell]

Erland,
You got me in the right direction and the solution works very fast:

The stored procedure:

@ClienID int,
@dt datetime /* first day of the selected month */

AS

DECLARE @dtEnd datetime
SET @dtEnd = DATEADD(DAY,-1,DATEADD(MONTH,1,@dt)) /* last day of the
selected month */

SELECT
dbo.fn_ClientSked(@dt, @ClientID) AS D1,
dbo.fn_ClientSked(DATEADD(DAY,1,@dt), @ClientID) AS D2,
dbo.fn_ClientSked(DATEADD(DAY,2,@dt), @ClientID) AS D3,
etc...(for D4-D28)
CASE WHEN DATEADD(DAY,28,@dt) <= @dtEnd THEN
dbo.fn_ClientSked(DATEADD(DAY,28,@dt), @ClientID) END AS D29,
CASE WHEN DATEADD(DAY,29,@dt) <= @dtEnd THEN
dbo.fn_ClientSked(DATEADD(DAY,29,@dt), @ClientID) END AS D30,
CASE WHEN DATEADD(DAY,30,@dt) <= @dtEnd THEN
dbo.fn_ClientSked(DATEADD(DAY,30,@dt), @ClientID) END AS D31
The UDF:

CREATE function dbo.fn_ClientSked(@dtX as DateTime, @ClientID as int)
returns
nvarchar(500)
AS
begin
declare @ret_value nvarchar(500)
SET @ret_value=''
Select @ret_value=@ret_value + '; ' + AppointmentTitle
FROM dbo.tblAppointments
WHERE
tblAppointments.ClientID = @ClientID
AND
@dtX Between tblAppointments.StartDate AND tblAppointments.EndDate
RETURN CASE WHEN LEN(@ret_value)>0 THEN
RIGHT(@ret_value,LEN(@ret_value)-2) ELSE '' END
end

Note: This particular UDF returns all appointments by day of the month
for the same client to populate a monthly calendar. It can be easily
modified to show appointments by employee, etc.

[Erland Sommarskog]

laurenq uantrell (la*************@hotmail.com) writes:

You got me in the right direction and the solution works very fast:
...
declare @ret_value nvarchar(500)
SET @ret_value=''
Select @ret_value=@ret_value + '; ' + AppointmentTitle
FROM dbo.tblAppointments
WHERE
tblAppointments.ClientID = @ClientID
AND
@dtX Between tblAppointments.StartDate AND tblAppointments.EndDate
RETURN CASE WHEN LEN(@ret_value)>0 THEN
RIGHT(@ret_value,LEN(@ret_value)-2) ELSE '' END
end

While it may work and be fast, it relies on undefined behaviour. See
http://support.microsoft.com/default.aspx?scid=287515.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[laurenq uantrell]

Erland,
Now you have me worried. Because I am using a UDF within the SELECT
statement? Isn't that what they're for?
I chose this solution because the other solutions I conceived required
31 server calls to populate 31 subforms (one for each day of the week.)
With the method above the data is all shoved down the pipe as one row.
Isn't that a preferable solution where a simple string output is all
that's required for each date?
lq

[Erland Sommarskog]

laurenq uantrell (la*************@hotmail.com) writes:

Now you have me worried. Because I am using a UDF within the SELECT
statement? Isn't that what they're for?

No, because of

Select @ret_value=@ret_value + '; ' + AppointmentTitle
FROM dbo.tblAppointments

This can give you want you want, or just one appointment. Or something
else. The result of this construct "concatentate aggregation" is not
defined.

If you want to play safe, write a cursor instead. (But you can still
do this in a UDF.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[laurenq uantrell]

OK, thanks for the info. I hate messing with cursors...
lq