Create Stored Procedure to combine 2 tables for use in Crystal Reports

[rradelet]

I am new to SQL Stored Procedures. I use Crystal Reports 8.5 to run reports on a SQL2000 database.

The company's order records are stored in 2 tables: "Orders" and
"ArchivedOrders". I want to be able to create a SQL Stored Procedure that will prompt the Crystal Reports users for FromDate and ToDate parameters and allow them to retrieve data for date ranges that begin in the ArchivedOrders table and end in the Orders table. I have tried using the UNION feature in CR versions 9 and later, but since there are over 4 million records in these tables, it takes forever to process. Plus most of the users only have access to CR 8.5. I am hoping to find a more efficient method using a stored procedure to pull the order data from these 2 tables as well as data from linked fields in the Clients and Salesperson tables.

The field names and data types are identical in the 2 order tables, and the combined records in the 2 tables contain no duplicate records.

For example I would use the following fields to produce a sales by client report, that would include the clients' names and salespersons names from 2 other linked tables:

Orders.OrderNo int
Orders.AcctNo int
Orders.OrderDate varchar
Orders.SubtotalAmount money
Orders.TaxAmount money
Orders.TotalAmount money
ArchivedOrders.OrderNo int
ArchivedOrders.AcctNo int
ArchivedOrders.OrderDate varchar
ArchivedOrders.SubtotalAmount money
ArchivedOrders.TaxAmount money
ArchivedOrders.TotalAmount money

Clients.AcctNo int
Clients.Name varchar
Clients.SalespersonID int
Clients.SalesCommissionStartDate datetime

Salesperson.SalespersonID int
Salesperson.Name varchar

The OrderDate fields in the database are stored as varchar fields:

Jan 23 2006 12:15PM
Jan 23 2006 3:30PM

My first hurdle is how to convert these OrderDate varchar formats to a date format that can be used as from and to date parameters in the Crystal Report.


Can anyone help me get started with creating this Stored Procedure?

 

[gmmastros]

Your biggest problem is going to be the dates stored as varchar. With millions of rows to look through, you certainly want the data indexed.

Before building the query, you should also determine what other possible filter criteria may be used for this report. Specifically, will the data be filtered by Order Number and/or account number?

Next, do you have permissions to change the database structure at all? Clearly, the best thing to do would be to convert your orderdate (varchar) to a DateTime data type. I do recognize that this will likely be a big headache for you. However, there are alternatives that will likely be just as quick without have to change any of the existing columns in the table. Specifically, I'm thinking you could add a computed column to both tables that basically just converts the varchar date to a datetime date. This will allow you to index the computed column to get better performance.

Alternatively, you could create a view that converts the varchar date to a datetime date and also does a UNION ALL between the tables. You could then use this view in the crystal report exactly as though it were a table.

Please understand that you would need appropriate permissions in the database to add a computed column to the tables and/or add a view to the database.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rradelet]

George:

Thanks for the reply.

Here are some answers to your questions:

1) "Specifically, will the data be filtered by Order Number and/or account number?"

I probably won't be doing any other filtering besides the date range. I am trying to produce a report that lists sales grouped by client for any date ranges that exist in the 2 order tables.

2) "do you have permissions to change the database structure at all?"

All the tables are part of a VB application, so I can't make changes to any of the tables.


I can add a view to the database, but for some unknown reason, Crystal Reports data explorer only sees tables and stored procedures from this database. The Crystal option to display views in the data explorer is turned on, and the SQL view permissions are in place, but Crystal does not see them. I tried CR 8.5 & CR 11 with the same result.

It looks like the only option is to proceed with a stored procedure and then see how long it takes to run. I could still use some help getting started.


I think that I have found the right SQL syntax to convert the date strings to datetime mm/dd/yyyy format:

CONVERT(datetime, OrderDate, 101)

 

[gmmastros]

well.... to get you started.

Create Procedure YourProcedureNameHere
  @StartDate DateTime,
  @EndDate DateTime
AS
SET NOCOUNT ON
Declare @ReportEndDate DateTime

Set @ReportEndDate = DateAdd(Day, 1, @EndDate)

Select Client.Name,
       Client.SalesCommisionStartDate,
       SalesPerson.Name,
       CombinedOrders.OrderNo,
       CombinedOrders.AcctNo,
       CombinedOrders.OrderDate,
       CombinedOrders.SubtotalAmount,
       CombinedOrders.TaxAmount,
       CombinedOrders.TotalAmount
From   (
       Select Orders.OrderNo,
              Orders.AcctNo,
              Orders.OrderDate,
              Orders.SubtotalAmount,
              Orders.TaxAmount,
              Orders.TotalAmount
       From   Orders
       Where  CONVERT(Datetime, Orders.OrderDate) >= @StartDate   
              And CONVERT(DateTime, Orders.OrderDate) < @ReportEndDate

       Union All

       Select ArchivedOrders.OrderNo,
              ArchivedOrders.AcctNo,
              ArchivedOrders.OrderDate,
              ArchivedOrders.SubtotalAmount,
              ArchivedOrders.TaxAmount,
              ArchivedOrders.TotalAmount
       From   ArchivedOrders
       Where  CONVERT(Datetime, ArchivedOrders.OrderDate) >= @StartDate   
              And CONVERT(DateTime, ArchivedOrders.OrderDate) < @ReportEndDate
       ) As CombinedOrders
       Inner Join Clients
         On CombinedOrders.AcctNo = Clients.AcctNo
       Inner Join SalesPerson
         On Clients.SalesPersonId = SalesPerson.SalesPersonId
Order By Convert(DateTime, CombinedOrders.OrderDate)

Let me explain a couple things.

Notice the Union All. Union All is faster than Union because Union filters out duplicate rows. According to your initial question, "the combined records in the 2 tables contain no duplicate records". If there are no duplicates, then there wouldn't be anything to filter, so using UNION ALL will return the correct results and will also be faster than using UNION.

Next, notice that I put the filter condition on each table within the UNION ALL part of the derived table. We could have filtered after unioning the data, but filtering early will give us better results.

Notice the @EndDate, @ReportEndDate part. Whenever I ask for date ranges in one of my reports, I usually do not ask for a time. For example, the user enters 1/1/2011 to 1/10/2011. I assume this to mean, "Return any data for any time on 1/10/2011 also". That is why I add one day to the EndDate and then use a < comparison.

User enters 1/10/2011.
I add one day, so: 1/11/2011
I use less than comparison, so and row that has an order date < 1/11/2011 (regardless of time on 1/10/2011) will be returned.

You can test this report in SQL Server Management Studio (or Query Analyzer) like this:

Exec YourProcedureNameHere '20110101','20110110'

All the tables are part of a VB application, so I can't make changes to any of the tables.

Is this an in-house application? I mean, can you talk to the developers and find out if you can add a computed column to the table? If you can, then the computed column could be indexed and your report will likely run many times faster.

If you can't add a computed column, then it may be possibly to add a view with the date converted to DateTime, put an index on the view, and then use the view from within the stored procedure. I guess it all depends on the execution time. After creating the stored procedure and running it, can you let me know how long it takes to execute?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rradelet]

Thanks so much George!

I have had false starts with learning stored procedures before.
The examples in books and online tutorials are usually too basic to be of much use. The existing sp's in our database are at the other
extreme: far to complex for me to understand how they work.

With your help,I was able to create the stored procedure and design a Crystal Sales by Client report that takes less that a minute to execute.
That response is very acceptable. This isn't the type of report that
the users will run 20 times a day; once or twice a month is more likely, so waiting a minute to analyze 4 million records is fine.

The application wasn't developed in house. It is a package from a large transportation software house. Any changes to the tables and application would require hefty customization fees.

Here is the code that was successful:

(I used generic table and field names in my first post. The code
below contains the actual table and field names.)

I didn't include the Salesperson fields yet. Now that I have the basic technique working, I can tweak things later.

In looking at the sp's that already exist in the database, I found
that the developer uses the DATEDIFF function in WHERE clauses, so I used DATEDIFF function instead of your DATEADD method.

Here is the code that was successful:



CREATE procedure spMonthlySalesByClient

@StartDate DateTime,
@EndDate DateTime

AS

SET NOCOUNT ON



Select
Clients.Name,
CombinedOrders.OrderNo,
CombinedOrders.AccountNumber,
CombinedOrders.DeliveredTime,
CombinedOrders.SubtotalAmount,
CombinedOrders.Tax1Amount,
CombinedOrders.TotalAmount
From (
Select FinalizedOrders.OrderNo,
FinalizedOrders.AccountNumber,
FinalizedOrders.DeliveredTime,
FinalizedOrders.SubtotalAmount,
FinalizedOrders.Tax1Amount,
FinalizedOrders.TotalAmount
From FinalizedOrders



WHERE DATEDIFF(day, CONVERT(Datetime, FinalizedOrders.DeliveredTime), @StartDate) <= 0 AND DATEDIFF(day, CONVERT(Datetime, FinalizedOrders.DeliveredTime), @EndDate) >=0



Union All

Select PurgedOrders.OrderNo,
PurgedOrders.AccountNumber,
PurgedOrders.DeliveredTime,
PurgedOrders.SubtotalAmount,
PurgedOrders.Tax1Amount,
PurgedOrders.TotalAmount
From PurgedOrders


WHERE DATEDIFF(day, CONVERT(Datetime, PurgedOrders.DeliveredTime), @StartDate) <= 0 AND DATEDIFF(day, CONVERT(Datetime, PurgedOrders.DeliveredTime), @EndDate) >=0

) As CombinedOrders
Inner Join Clients
On CombinedOrders.AccountNumber = Clients.AccountNumber


Order By Convert(DateTime, CombinedOrders.DeliveredTime)
GO

 

[gmmastros]

If you didn't have to have the convert functions in there, I would advise you to use my code instead. You see, there is a concept in databases call 'SARGABLE'. Basically, it means whether a query can use an index seek (instead of a scan) to return your data. The difference can be huge. For example, your query that runs in 1 minute would probably take less than 1 second if it could use an index. But, since you already have the converts in there, the best you can hope for is an index seek anyway, so adding another function (DateAdd) won't really make any difference in execution time.

And shame on your software vendor. As a professional software company, they should know better. My advice to you is... don't use their table structure, column data types, or code as a basis for what is "good" or "better". Please don't misunderstand me. I'm not saying the application is bad. I *am* saying that it could be better.

Regardless, I'm glad that I was able to help.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom