It Cant be Done with SQL
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cbryant/itcantbedonewithsql.asp
-
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.
-
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.
Steve
-
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,
Steven
-
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!!!
-
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.
-
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 -
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?
Gert
-
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?
_____________
Code for TallyGenerator -
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply