Did author ever think about purpose of the IDENTITY column in his temporary table? It's perfect tool to replace loops!

He needed just to add another column to his temporary table: Counts varchar(8000) NULL to store his @Counts, than update this column where ID <= @NumCounts1 or ID > Max(ID) - @NumCounts2, than set Counts = '-----------------' where Counts is NULL, and in the end select max(ID), Counts group by Counts order by max(ID) (ASC, DESC - whatever you say).

Result will be the same, but no loops inside!

And that MUST be done in TSQL, because it's really set oriented task for data processing.

To do it in Crystal you need to supply all data from DB to Client side, then process it and drop it. Of course if you don't care about network traffic and you Client is equipped with flash new P4 + a lot of RAM, you must use it somehow... But does Intel pay you for marketing?

1. Everyone has limitations, hand me a crystal report and the logic used to create the report and I can figure out how you can get from A to B.  Hand me a report a few months old and the logic that worked then and ask me why it isn't working now, I MIGHT be able to figure out why it no longer works and fix it.  Hand write a report format and ask me to build a crystal report from it, and I'm in deep doo-doo.
2. Somebody reading T-SQL that doesn't really understand it is really in deep doo-doo.  Allowing someone like that to modify T-SQL is crazy.  SOP in most organizations.  Oh well.
3. I hate loops when they aren't needed.
4. I hate duplicate funtions that essentually do the same thing.
5. I like doing things a little more efficiently if possible.
6. Returning a result set might be better for crystal reports, but that wasn't the solution being worked on.
7.  Complaining about a 4 page description?  Get real! Full documentation ALWAYS takes more space than code.  Documentation of a thought process is even worse!
8. Sommerer was wondering if this was documented behavior.  Yes, there are several books that describe how variables are set in result sets.  I've used variables to determine if "this" record matches the previous record in case statements, then set the variable to the field I'm matching at the end of the logic.  @Counts = ISNULL(@Counts + '<BR><BR><BR>', '') is the key that allows you to pass multiple record information into one field.  The first record converts that to '', following records add the prior value and the break commands.
9.  SQL is code, C# is code, FORTRAN is code, having one routine that's much greater than 3K lines is probably a mistake in any language.  I've tried to read FORTRAN code that is greater than 7K lines and no documentation.  Really ugly spagetti code.  Lucky best coding practices got rid of that mindset from most programmers years ago.  Sorry to hear it is still alive and well.
10.  Sergiy said that his solution would have the same result.  No, it wouldn't, his is a result set solution, not a single varchar.  If you did put the column in the table, it wouldn't need to be longer than 100 characters.  I do agree that the IDENTITY column added but not used is key to a recordset solution.

In the second example, she provides the tool needed to get the top n records by putting only the records needed into a new table in the correct order and creating an identity field in that table.  "ID" becomes an index counter in the table, if you want 5 records, you want "ID between 1 and 5".  I added @GetFirstCounts into the argument list so the same function can be used for ascending and descending sorted lists.

Because I removed the loop, I no longer needed @Counter for the loop, however I didn't like the way an aggregate function was used each and every time the prior day's information was retrieved.  The prior day's info was 1 record before or after the current record.  I reused @Counter to get the prior day's info without using an aggregate function.

CREATE FUNCTION GetLastNTicketCounts (@ShowID INT, @ClientName VARCHAR(200), @NumCounts INT, @GetFirstCounts bit)

RETURNS VARCHAR(8000)
/*
 Matching @ShowID and @ClientName variables in vTicketCount, this function will
 return the first (@GetFirstCounts=1) or last (not 1) @NumCounts records by
 CountDate.
 Returns a character string showing CountDate, total and daily ticket counts
 for all records formatted for a crystal report.
*/

AS
BEGIN

DECLARE @t TABLE
(ID INT IDENTITY, TicketCount INT, CountDate DATETIME)
DECLARE @Counts VARCHAR(8000), @Counter INT

IF (@GetFirstCounts = 1)
BEGIN
 INSERT INTO @t
 SELECT TicketCount, CountDate
  FROM vTicketCount
  WHERE ShowID = @ShowID
  AND ClientName = @ClientName
  ORDER BY CountDate
 set @Counter = -1;
END
ELSE
BEGIN
 INSERT INTO @t
 SELECT TicketCount, CountDate
  FROM vTicketCount
  WHERE ShowID = @ShowID
  AND ClientName = @ClientName
  ORDER BY CountDate DESC
 set @Counter = 1;
END

SELECT @Counts = ISNULL(@Counts + '<BR><BR><BR>', '') +
 CONVERT(VARCHAR(20), tc1.CountDate, 101) + ' - ' +
 CAST(tc1.TicketCount AS VARCHAR(10)) + ' (' +
 CAST(tc1.TicketCount - ISNULL(tc2.TicketCount,0) AS VARCHAR(10)) + ')'
FROM @t tc1
 LEFT JOIN @t tc2 ON tc2.ID = tc1.ID+@Counter
 WHERE tc1.ID <= @NumCounts

RETURN ISNULL(@Counts, '')
END

I created a test table:
CREATE TABLE [dbo].[vTicketCount] (
 [vTicketCountID] [int] IDENTITY (1, 1) NOT NULL ,
 [ShowID] [int] NOT NULL ,
 [ClientName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [TicketCount] [int] NOT NULL ,
 [CountDate] [datetime] NOT NULL
) ON [PRIMARY]
a dummy data build routine (OK, I do use loops.  If you have the actual table data, don't do any of this.):
truncate table [dbo].[vTicketCount]
Declare @ShowID int, @ClientName varchar(200),@TicketCount int
, @CountDate datetime, @Count int
set @CountDate=convert(varchar,getdate()-20,101)
set @TicketCount=0
set @Count=0
while (@CountDate<getdate())
begin
 set @Count=@Count+1
 set @TicketCount=@TicketCount+@Count*5
 if (@Count=12)set @TicketCount=@TicketCount-221
 set @ShowID = 1
 set @ClientName = 'First Client'
 insert into dbo.vTicketCount (ShowID,ClientName,TicketCount,CountDate)
  select @ShowID,@ClientName,@TicketCount,@CountDate
 set @ClientName = 'Second Client'
 insert into dbo.vTicketCount (ShowID,ClientName,TicketCount,CountDate)
  select @ShowID,@ClientName,@TicketCount-@Count,@CountDate
 set @ShowID = @ShowID+1
 insert into dbo.vTicketCount (ShowID,ClientName,TicketCount,CountDate)
  select @ShowID,@ClientName,@TicketCount+@Count,@CountDate
 select @CountDate=@CountDate+1
end
select * from dbo.vTicketCount

and a test script:
set nocount on
select dbo.GetLastNTicketCounts(1,'First Client',10,null)
select dbo.GetLastNTicketCounts(2,'First Client',2,0)
select dbo.GetLastNTicketCounts(1,'Second Client',3,1)
select dbo.GetLastNTicketCounts(2,'Second Client',2,0)

Note that her script and mine would give different results in the first test case because I emulated a massive refund on the 12'th day.  Her script would continue giving negative numbers for the following days until the sales recovered and mine gives the actual increases for those days.  In the real world, these UDFs wouldn't give different results, just use different methods to get them.