Archive for the 'SQL' Category



SQL Syntax for several common date ranges

Friday 21 June 2019 @ 8:26 pm

I have been writing lots of SQL commands lately for my reports.  I am doing this so much now that I regularly tap into my SQL cheat sheet which shows the most commonly used SQL functions and calculations for the seven SQL flavors that I see the most.

Recently a customer needed several date range functions to be used in the WHERE clause of a SQL Command. The list was quite similar to the date range function list available in Crystal. He wanted the following functions: YearToDate, MonthtoDate, LastFullMonth, LastFullYear, LastFullWeek, Last7days, PreviousDay.

So I did some research and found some excellent discussions on the best approach to take. For instance, I read that you don’t want to use any functions on the database field itself. I had started to use the Oracle TRUNC() function to strip the time off of the database field, but I read that this can prevent the query engine from using an index. However functions don’t affect the index when used to calculate the literal dates that you are comparing. I also read that when you don’t strip the time from the DB field it gets harder to use a BETWEEN comparison. The most knowledgeable posts I read avoid Between and instead used the raw DB field in two separate comparisons like this:

db.field >= CalculatedRangeStart and
db.field < CalculatedRangeEnd

The CalculatedRangeEnd is always the day AFTER your range ends, so you can use < to get all times on the last day.

This is what i came up with for SQL Server:

Previous Day
X.DATE >= DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) -1,0) and
X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) ,0)

LastFullWeek
X.DATE >= DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP)
- DATEPART(dw, CURRENT_TIMESTAMP) -6,0)
and X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP)
- DATEPART(dw, CURRENT_TIMESTAMP) +1,0)

Last 7 days(to yesterday)
X.DATE >= DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP)-7 ,0)
and X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) ,0)

Month to Date(to yesterday)
X.DATE >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
and X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) ,0)

YearToDate(to yesterday)
X.DATE >= DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)
and X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) ,0)

LastFullMonth
X.DATE >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP)-1, 0)
and X.DATE < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)

LastFullYear
X.DATE >= DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP)-1, 0)
and X.DATE < DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)

And this is what i came up with for for Oracle:

Previous Day
X.DATE >= TRUNC(sysdate) - 1 and X.DATE < TRUNC(sysdate)

LastFullWeek
X.DATE >= trunc(sysdate - 7,'WW') and X.DATE < trunc(sysdate,'WW')

Last 7 days (to yesterday)
X.DATE >= TRUNC(sysdate) - 7 and X.DATE < TRUNC(sysdate)

Month to Date(to yesterday)
X.DATE >= TRUNC(Sysdate,'MM') and X.DATE < TRUNC(sysdate)

YearToDate(to yesterday)
X.DATE >= TRUNC(Sysdate,'Year') and X.DATE < TRUNC(sysdate)

LastFullMonth
X.DATE >= TRUNC(TRUNC(Sysdate,'MM') - 1, 'MM') and X.DATE < TRUNC(Sysdate,'MM')

LastFullYear
X.DATE >= TRUNC(TRUNC(Sysdate,'Year') - 1,'Year') and X.DATE < TRUNC(Sysdate,'Year')




Better SQL for showing the last record

Monday 24 December 2018 @ 6:25 pm

I recently wrote about the SQL to return the last record in SQL Server. Adam Butt of APB reports wrote to remind me that if you are using in SQL Server or Oracle you can do this more efficiently using a RowNumber “Over” a Partition. He also gave me an example which has helped me understand the pieces that go into this method. There are three components:
RowNumber() will number all the rows in your results, like a Crystal running total. However, it won’t work unless you give it an OVER clause to put the records in order. The OVER clause is just an “ORDER BY clause put in parentheses right after the word OVER. This statement would number all the records in your results from 1 to n:

ROW_NUMBER() OVER( ORDER BY Order.OrdDate DESC) AS RowNum

You add one more piece when you want the Row Number to start over with each group, the “Partition By”. Partition By is similar to Group By but happens inside the OVER clause. This statement would number the records in your results from 1 to n within each Customer:

ROW_NUMBER()
OVER( PARTITION BY Customer.Name ORDER BY Order.OrdDate DESC) AS RowNum

Notice that in both cases the ORDER BY is “DESC” or descending which means the latest record for each customer will be first or Row_Number 1. We can use that in the SQL below to make sure we only see the last record for each customer.

SELECT * FROM ( SELECT Order.OrdDate, Orders.OrderNo, Orders.Amount, Customer.Name,ROW_NUMBER() OVER(PARTITION BY Customer.Name ORDER BY Order.OrdDate DESC) AS RowNum FROM Orders Orders
INNER JOIN Customer Customer ON Orders.CustomerID=Customer.ID
) X
WHERE
X.RowNum= 1

By changing the final WHERE clause you can also do things like select the last 3 orders for each customer:

X.RowNum <= 3

And the OVER() can also be used without the row_number() to create running totals in your SQL results. For instance this expression would create a running sum of order amounts for each customer:

sum(orders.amount) OVER( PARTITION BY Customer.Name
ORDER BY Order.OrdDate DESC) AS RunningAmt




SQL to return the last record (SQL Server)

Friday 7 December 2018 @ 10:23 pm

A few years ago I wrote a post that showed a more efficient way to find a ‘last’ record in a SQL command, while pulling several values from that record.

The example I used then was written in the SQL Syntax for MS Access, but the same method can be used in other flavors of SQL. The main difference is the syntax for converting the date to a string. Since I just had to look up that syntax for MS SQL Server I decided to post this here so I can find it easily next time.  Here is a brief recap of what it does:

In SQL it is very easy to add a GROUP BY clause to a query and then calculate the Min() or Max() date for that group. For instance the last order date for a customer. What is not so simple is to return other fields from each last order record, like the Order Number and Amount. The typical solution is to find the maximum in initial query, and then link that to another query that matches that Max() value back to the matching record as an inner join. But this requires at least to queries, and can get complicated if the GROUP BY field and the Max() field are in separate tables.

I find that it is simpler and more efficient to do the Max() on a combined expression that includes all the fields you want. This works as long as you start the expression with the date converted to a string in “yyyymmdd” format. This format ensures that the Max() of the combined expression will be the date and values from the last record.

Take a simple example. We need to write SQL to return each customer’s last order. And from that order you also want to return the customer name, order date, order number and amount. The SQL would look like this:

SELECT
Customer.ID,
max(
CONVERT(varchar(16), Order.OrdDate, 121) + '*' +
Orders.OrderNo + '*' +
Orders.Amount + '*' +
Customer.Name
) as MaxSTring
FROM Orders Orders INNER JOIN Customer Customer ON Orders.CustomerID=Customer.ID
WHERE (Orders.OrdDate>={ts '2018-11-01 00:00:00'} AND Orders.OrdDate<{ts '2018-12-01 00:00:00'})
GROUP BY Customer.ID

This returns one row per customer and the row might look something like this:

20180630*12345*250.87*Joe Smith

You can then write a formula in Crystal to split the string back into pieces. For instance the Customer Name formula would look like this:

Split ({Command.MaxString}, '*') [4]

In English this says, split the MaxString into a list (array) based on the asterisks. Take the 4th element.

Some of you might be wondering why we don’t just do this in the report.  You have a point since this is simple in Crystal:

Group by customer, sort by date, hide the Details and Group Header. Show all the fields on the Group Footer.

This works great, but is not very efficient if you have a large volume of data, because it requires Crystal to read all the details and display the last one. The SQL version above would only return one record per customer to the report.




Using “Order Links” to improve performance

Wednesday 21 March 2018 @ 9:19 pm

I have written once before about using the “Order Links” feature of the database expert. In that article I used “Order Links” to help me prevent a SQL error in the pervasive database engine. This past week I used the same feature to improve the performance of a report.

This report had many tables but there was one, the Dept table, that provide the primary filter for the report. The user would select a specific department each time they refreshed. But when I looked at the SQL generated by CR, I noticed that the Dept table was the last table joined into the data set. In my mind, that meant that Crystal was bringing in thousands of linked records that would eventually be discarded because they were linked to the wrong department. If the SQL could apply the filter up front we would greatly reduce the number of linked records we would generate, and that would speeds things up.

So I suggested to the user that they go into the “Links” tab of the Database Expert and right-click to find the “Order Links” option. I had them move the Dept join to the top of the joins list and they immediately saw a dramatic improvement in performance. It may not work in every report or with every database (this report was Oracle based), but when there are lots of tables and the report is slow, it is another option to try.




SQL Function “cheat sheet” for writing commands

Sunday 8 October 2017 @ 5:44 pm

In recent years, I have been doing lots of SQL command-based reports, which means writing lots of queries in every imaginable flavor of SQL. It was initially frustrating trying to keep the syntax changes straight and I got tired of doing web searches for the same functions over and over.

My solution was to create a “cheat sheet” grid to store my most commonly used SQL functions and the correct syntax to use in each of the database platforms. Once I looked up a function, I would add it to the grid and now the grid answers most of my questions. There are about 40 functions listed and I have most of them completed for the 7 flavors of SQL I see most (plus Crystal Reports formula syntax):

  • Microsoft SQL Server
  • Oracle
  • MySQL
  • Pervasive
  • Progress
  • PostGre SQL
  • MS Access

I have empty columns for Providex and DB2. They are empty because I haven’t had any recent projects on those platforms and don’t have an environment where I can experiment.

I hope some of you will find this grid useful. You can also share the file with others as long as you leave the heading in place. If any of you want to fill in some of the gaps or suggest an improvement, your contributions would be appreciated.  And thanks to John Pelot of Skyward, Inc for filling in many of the progress functions.

 




Using NotePad++ for formulas or SQL Commands

Thursday 24 August 2017 @ 5:50 pm

If you have to write SQL commands or complicated Crystal formulas, it helps to have a good text editor. My favorite is NotePad++. Not only is it free, but it can do many helpful things beyond what Notepad can do:

  • Number each line.
  • Highlight the corresponding paren/bracket whenever you select a paren/bracket
  • Highlight all the instances of any word that you select
  • Record keystroke macros to automatically repeat a series of commands
  • Select a vertical strip of characters from within of a larger block of text (called “column mode”). (e.g. highlight the 5th and 6th characters in all rows at once)
  • Find all of the differences between two text blocks or two lists, using the “compare” plugin.
  • “Fold” (collapse and expand) sections between parentheses or keywords

It can even format the code for 80 different programming languages, including SQL, showing reserved words and comments in different colors. Within a week I will have a UDL (User Defined Language) for Crystal Reports formula syntax. That will allow NotePadd++ to format a Crystal formula to look the same as it would in Crystal, with the comments in green and the functions in blue.

If you want to try out NotePad++ the best place to get it is through Ninite.com.  I have written about Ninite before. The advantage of Ninite is that it lets you select multiple items from a list of 80+ freeware apps. Then it gives you a tiny exe that installs all the applications you chose (and nothing else) with one click. No questions, no toolbars no ‘bonus’ apps.  It is one of the few places left where you can get freeware without junk.




Today’s date in SQL Server

Thursday 9 March 2017 @ 9:31 pm

I haven’t written many posts on SQL topics. But recently it seems that much of my work involves writing reports based on complex SQL queries. Part of this involves converting Crystal formula logic into SQL syntax. The fun part is that the syntax varies from one flavor of SQL to another, especially for date calculations.

So today’s post is specific to SQL Server syntax, one of the most common flavors. I often need date calculations that are relative to today’s date. In a Crystal formula I would use CurrentDate. In SQL Server syntax the closest equivalent function is GetDate(). I have used it for quite a while assuming that it is the same as CurrentDate. But I just recently discovered that GetDate() includes both the date and the time, which changes things. If you run a report at 2pm on March 8th and the WHERE clause says:

WHERE orders.Date >= GetDate() -2

you might expect to get all the records on March 6th, but you probably wouldn’t. If your Orders.Date field doesn’t store times you would not get any records from the 6th. If that field does have times you would get records, but only those after 2pm on the 6th. So if you want the calculation above to behave like the CurrentDate function in Crystal you have to remove the time from GetDate().

I found two ways to strip off the time off any DateTime value. The one I see listed most often is this one:

DateDiff(d, 0, GetDate())

or this one submitted by Ralph Wahlert:

Cast(GetDate() as Date)

These works great in a WHERE or ON clause, but have one flaw. If you include them in your SELECT clause so that you can show the date on the report the first will appear as a number in Crystal and the second will appear as a string. To use them in the SELECT and have them as dates, you have to convert them to DateTimes:

cast(DateDiff(d, 0, GetDate()) as DateTime)
cast(cast(GetDate() as Date) as DateTime)

These two work in the WHERE/ON clauses as well as the SELECT. In each case they will return a DateTime value but with the time portion set to 12:00am.




Getting MAS 90 to run reports with SQL commands

Sunday 10 April 2016 @ 8:48 pm

I recently learned how to fool MAS 90 into running a custom report based on a SQL command. This might only apply to older versions but I thought it was worth sharing just in case.

I created a report for a customer based on a command. It ran fine within Crystal but when added as a custom report in the MAS custom reports folder it generated an error.  We learned that MAS does a conversion step with custom reports and has to verify that all the tables exist. Our command obviously wouldn’t be found as a table so the conversion would fail.

After some web searching I found an old forum thread that described a workaround for using external tables in a custom report.  A few experiments confirmed that the same technique works for reports based on a SQL command.

The steps were to:
1) Use a similar report that uses only standard tables, and let SAGE do the conversion step on that report.
2) Open the real report (the one that uses a command) and go to File > Summary Info.
3) Modify the “keywords” section to say “Converted to version 4.40” (or your version) and save the real report.
4) Replace the first report that Sage converted with the real one.

Apparently SAGE marks the converted reports in the keywords section and skips that step if it has been done. So by adding that phrase we cause SAGE to skip the conversion, and the report seems to run fine.

My customer is using an MAS 90 4.4 which was replaced with 4.5 in 2012, but maybe this will help someone else. And if this works for you in a newer version, please let me know.




Reset ‘locked’ SQL in Crystal Reports.

Sunday 10 January 2016 @ 5:15 pm

I was helping a customer troubleshoot a misbehaving report. He could add criteria to the report, but when he removed the original criteria from the selection formula the it was still applied. So I looked at the SQL that CR was generating and found that it was not being updated based his our changes.  After a few more tries my customer asked me about an unfamiliar button below the “SHOW SQL QUERY” window. Only then did I noticed the extra button that said RESET.   I had not seen that button in about 10 years, but I now knew the problem with this report.

This report was originally created in an early version of Crystal, probably v8.x. In these early versions you were allowed to tweak the FROM and WHERE clauses in the automatically generated SQL.  This is no longer allowed because now we can create SQL Commands.  Back then, when you modified the SQL it became ‘locked’ and Crystal would no longer update it. If you added more selection criteria the new criteria would not be incorporated into the SQL. Instead it would be applied after the data came back from the database.  When you wanted to revert to automatically generated SQL you would hit the RESET button.

In current versions of CR the RESET button is not needed so it doesn’t normally appear.  But it will appear automatically if you open an old report that has locked SQL.  This allows you to eliminate the use of an obsolete feature.  So I checked the SQL to see if the original tweaks were still essential.  There were no UNION queries, subqueries or filters built into outer joins. So I reset the SQL and the RESET button disappeared. After that the report behaved like a normal report.




More efficient SQL for returning the last record

Saturday 21 November 2015 @ 4:57 pm

I wrote up a method I called the “wormhole” years ago. Normally when you calculate the maximum of a column you can refer to that maximum in any formula in the report. The wormhole technique takes advantage of this behavior to transport other values from the same row as the maximum value. For instance, I can move several values from the last record of the report and show them at the top of the report. To do this I append the other fields to the original, take the maximum of the combined string and then split the maximum value back into pieces. Recently I realized that this method can also be used in SQL expressions to return multiple values, and in SQL commands to simplify certain queries.

Say you have a table of transactions by customer. You want to generate a list that shows the customer name and three fields from their last order (date, order ID and amount). This is simple to do in Crystal without resorting to SQL, but it requires that Crystal bring back ALL of the orders. You then either sort and display only the group footer, or you do a group selection to select the maximum order date for each group.

But if you only want the maximum record to come back from the database then you need to do the work in SQL. There are several methods to return just the last record. The most common involves running two queries and joining them. The first query would pull a list of ALL of the orders for each customer and would return the four fields. The second query would use a “Group By” on the customer and return only the last or Max() date, for each customer. Then you would inner join these two results on both the Customer ID and the Order Date to have all the fields from the first query but limited to the date from the second query. Here are is an example of this query that can be run as a SQL command in the Xtreme sample database:

SELECT `Customer`.`Customer ID`, `Customer`.`Customer Name`, `Orders`.`Order ID`, 
`Orders`.`Order Amount`, `Orders`.`Order Date`
FROM (`Customer` `Customer` 
INNER JOIN `Orders` `Orders` ON `Customer`.`Customer ID`=`Orders`.`Customer ID`)
INNER JOIN ( 
   SELECT `Customer`.`Customer ID`, max(`Orders`.`Order ID`) as LastOrder
   FROM `Customer` `Customer` INNER JOIN `Orders` `Orders` 
   ON `Customer`.`Customer ID`=`Orders`.`Customer ID`
   Group By `Customer`.`Customer ID`) LastOrd
ON `Orders`.`Order ID`=`LastOrd`.`LastOrder` 
and `Customer`.`Customer ID`=`LastOrd`.`Customer ID`

This works fine, but it has to do two separate queries.  This can slow things down, especially if you have to do this several different times. The alternate approach would be:

SELECT `Customer`.`Customer ID`, `Customer`.`Customer Name`, 
max ( 
Format (`Orders`.`Order Date`, 'yyyy/mm/dd') & '-' & 
format (str(`Orders`.`Order ID`) , '00000') & '-' & 
format (str(`Orders`.`Order Amount`), '000000.00') 
) as MaxStr
FROM (`Customer` `Customer` 
INNER JOIN `Orders` `Orders` ON `Customer`.`Customer ID`=`Orders`.`Customer ID`)
Group by `Customer`.`Customer ID`, `Customer`.`Customer Name`

Lines 2 to 6 calculate the maximum of a combined string for each customer. The string includes the Date, the ID and the Amount of the transaction. The string starts with the date formatted as Year-Month-Day. The maximum of that string will grab the last order, and will also include the values of the other three fields. It returns the same values as the fist query and only takes one pass. The only downside is that you get your values inside a combined string.  To split them apart will take a few extra formulas in Crystal, but you might find this worth the extra effort because the query should run faster.

This technique is especially useful when you do a subquery inside a SQL Expression field. SQL Expression fields can only ever return a single “value”, but with this method that one value can include a combination of several fields from the same row of data.




«« Previous Posts
Jeff-Net

Recrystallize Pro

Crystal Reports Server