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.