Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

It Cant be Done with SQL Expand / Collapse
Posted Sunday, October 26, 2003 12:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 5, 2008 2:49 PM
Points: 57, Visits: 14
Comments posted to this topic are about the content posted at

Post #17613
Posted Thursday, October 30, 2003 6:37 AM


Group: General Forum Members
Last Login: Saturday, September 19, 2015 6:01 AM
Points: 115, Visits: 18
Just curious why you went the SQL route instead of using Crystals functions and grouping capabilities as a means to display the data in the desired format?

Seems as though if you returned a simple result set, Crystal could have done the same job with less of your time spent getting it to work in SQL...just curious.

Post #84691
Posted Thursday, October 30, 2003 8:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 23, 2015 9:58 PM
Points: 224, Visits: 181
While I agree that the rush of doing something that "can't be done" is what a programmer/dba lives for, I agree with jwiner. Crystal Reports Release 9 has a great deal of functionality that would have greatly simplified the process. You also have to consider that in today’s world, you probably will not support that report or its components forever. When someone comes behind you with less experience, you will have increased their learning curve exponentially. Not only would they have to be a SQL guru to figure out how the code was used to accomplish the goal, but they would also need to be a guru on how you think. On second thought, isn’t that job security.

Post #84692
Posted Thursday, October 30, 2003 8:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 23, 2004 5:36 PM
Points: 7, Visits: 1
I agree with Kepr00.

The question is not whether it CAN be done in SQL but whether it SHOULD be done in SQL. I can and have in the past written some ridiculously complex queries (also for Crystal Reports, which I don't think is a co-incidence). Not one of those queries (and there have been a couple dozen of them) have survived their first encounter with a maintenance programmer.

Anything that takes an expert the better part of a day to concoct is going to be unintelligable to average programmer. Anything that is unintelligable to the average programmer is going to be broken on a regular basis (and blamed for unrelated problems on an even more regular basis).

I recommend NOT writing queries this complex. Use simple looping structures and temporary tables in stored procedures.

Otherwise someone else will spend a week re-writing things in a simple way the first time they blame the undecipherable code for an unrelated problem while you're on vacation.

My 0.02,


Post #84693
Posted Thursday, October 30, 2003 8:39 AM


Group: General Forum Members
Last Login: Monday, May 23, 2016 2:53 PM
Points: 13, Visits: 74
Nicely done. Hats off to sticking with TSQL for the solution.

Good example with lots of step by step help.

IMHO while other languages have features, TSQL is server based and minimal network traffic. And it's a single point of non-dll-hell for maintenance.

And forget inefficiencies. Cursors, table variables, UDFs, and loops just further what we can already do. Who here has to worry that hardware will ever not support us reaching for nested cursors because were not as geeky as some of our more gifted contemporaries....

By the way some of my procs do go to 8,000 lines to do report writer stuff. I simply create a target table and keep appending flushed out summaries and special sort coded fields for a final pretty output.

So thanks again for yet another way to TSQL ginsui guru slice and dice!!!

Post #84694
Posted Thursday, October 30, 2003 8:40 AM

Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 7:51 PM
Points: 1,244, Visits: 3
I would have liked to have seen a performance comparison against a more logical approach using a function that didn't return a list but the figures for a given day (total sales to date and the day sales).

Crystal subreports could invoke that function in a SELECT statement using top 5 from last day of sales and back or top 5 from first day of sales forward.

I think it would be easier to understand and maintain, though it may be slower.

Post #84695
Posted Thursday, October 30, 2003 9:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 20, 2006 9:21 AM
Points: 34, Visits: 1
Doing magic with SQL is sometimes great in a pinch. But when it takes a 4 page article to display the results of a query that doesn't seem very maintainable to me. If the idea is a one off then great. If it is something that needs to be done and maintained ongoing going, than a less complex -- and likely not as fast -- seems in order.

I've seen lots of discussion about how bad CURSORS, FETCH, and WHILE LOOPS are in SQL Server. However, I use them all the time and and our DB Servers don't suffer at all. Our application/web servers have many more performance issues than our database servers. Furthermore, when I need to modify my stored procs, I can do it pretty easily because I'm not having to mess with a really complex query.

> Nick Duckstein

> Nick Duckstein
Post #84696
Posted Thursday, October 30, 2003 10:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 6, 2010 12:33 AM
Points: 34, Visits: 6
a very interesting article though!
Until today I thought, the last row would be used to fill the @counts variable!
Does anybody know, if this is a documented behavior, that every row triggers the assignment expression?


Post #84697
Posted Wednesday, July 7, 2004 5:49 PM



Group: General Forum Members
Last Login: Yesterday @ 5:32 AM
Points: 5,668, Visits: 11,156

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?

Post #125271
Posted Tuesday, September 27, 2005 2:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 21, 2012 1:46 AM
Points: 28, Visits: 59

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)

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


DECLARE @Counts VARCHAR(8000), @Counter INT

IF (@GetFirstCounts = 1)
 SELECT TicketCount, CountDate
  FROM vTicketCount
  WHERE ShowID = @ShowID
  AND ClientName = @ClientName
  ORDER BY CountDate
 set @Counter = -1;
 SELECT TicketCount, CountDate
  FROM vTicketCount
  WHERE ShowID = @ShowID
  AND ClientName = @ClientName
 set @Counter = 1;

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

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

Post #223804
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse