Database and SQL questions

I’ve been learning SQL Server and Crystal Reports, and I thought that this board would have some good resources for questions in general. I know we have some DBAs out there.

Right now I don’t have any specific questions, but thought I would share some of the learning resources that I’ve been given or found.

First, thanks @dakboy for the following:

  • Find out when your local PASS chapter meets. Sign up for the virtual chapters, pop into the monthly webinars.
  • There’s a SQL Saturday in Silicon Valley in March. Free (except for lunch). If you go, let me know and I’ll give you some tips on whose sessions to sit in on.
  • Look up “Accidental DBA” for lots of good beginner resources.
  • Subscribe to Brent Ozar Unlimited’s YouTube channel
  • Follow Grant Fritchey. Despite the domain & blog name, he’s not scary.

Next, a couple of classes/tutorials that I’ve found:
MIT Class on Database Systems
Crystal Reports Tutorials

Who knows? Maybe I’ll be able to start answering questions as I learn, too!

Edit: I also use this for some basic examples of syntax and commands. > W3Schools.

Additional Edit: Cleaning up links in dakboy’s contribution.

I moved 9 posts to a new topic: SQL and Database Learning Resources

I’ve used SQL Report Server a bit before, but the company I’m with now uses Crystal Reports. I don’t mind learning it, though, since that means I’ll just have more skills.

Nabiki, if you can, try to push Report Server. The main selling point is that Crystal Reports is a resource HOG and will chew through network bandwidth like nobody’s business. I had one client where they had to stop everything in the company (they had over 30 locations across the country) if they had to run a report outside my carefully crafted low usage times. They had one VP who thought she was most important than everyone else and was always shutting her location down to run some report she thought she needed. After I was called in to consult and pointed this out, and charged them the appropriate Stupid Tax, my low usage “guidelines” (VPs words) were then engraved in stone and placed on her desk. Report Server is kinder to network resources and leaves a much smaller footprint if you’re running reports during peak usage times.

That might explain why all of the report in this company run at night… I’ll mention it for certain.

Thanks, Nabiki - I’ve been learning SQL for my job, and this’ll be great.

SSRS is also a lot easier to monitor on the back-end because it’s so tightly integrated into SQL Server. Tools like Idera & SQL Sentry can watch for scheduled runs that don’t happen, long-running jobs, etc. right in the same interface as the rest of your SQL Server environment monitoring.

My boss said that he wants to migrate to SQL reports server, but we have hundreds of reports already in Crystal. Is there an easy way to convert them?

I was thinking of suggesting the phased approach. I just ran my first Crystal report, and it took five minutes to generate a four page report. One month of data for one customer. That’s pretty awful performance, all right.

But is it Crystal or the query that’s tanking you?

I moved 15 posts to a new topic: SQL and Database: Nulls AND data?

I’m learning new stuff every day! Today, I have an interesting problem that I’m not sure how to solve.

My output looks like this:

The screened numbers should be
36 for 1/2
47 for 1/3
The confirmed numbers should be
2 for 1/2
5 for 1/3

There shouldn’t be any numbers in the screened column if they were confirmed, and I think that’s where the problem is coming in. I think it has to do with the grouping at the end. How do I group the screens and confirms separately from the names, but all on the date?

GROUP BY CONVERT (VARCHAR(10),Z.Date, 120), X.Name

CONVERT (VARCHAR(10),Z.Date, 120)

A continuation of the question above, is there a way to record ‘Screened’ and ‘Confirmed’ on one line of date only, but still have the names grouped by date?

Not knowing your data, my initial thought is that you’ll need to do sub-queries to get everything into a single record.

As for the previous question, it may not be just the GROUP BY, it may be something in your JOIN as well.

Got the data right. Now to try to figure out how to do part of the data in a vertical pivot and part of it horizontal. I’ve kind of gotten it in Crystal by overlapping two pivot tables.

So, the table would look kind of like one of these, with the top being the ideal.

Under the Name column, are “Pos” and “Neg” a count of each type of response that you can get, or just a yes/no?

If the former…I’m not sure there’s a non-hideous way of doing that with SQL, but I could easily be proven wrong. I’m thinking some kind of Frankenstein join with a PIVOT involved. But you may be better off doing that part in the reporting tool, not SQL itself.

Unfortunately, the Pos & Neg have to be quantity pos or neg.

Is it normal for a query that takes 11 seconds to run in SQL server to take several minutes in Crystal? I hope I’m not impacting performance right now…

Arrrgggggghhh! Why can’t I just do a pivot table in Crystal reports?! The crosstab does me no good because it wants to modify my data somehow (sum, avg, etc) I just want it to show the data in the pivot table format.

/me wanders off in frustration

Crystal Reports is of the devil.

Can TRIM be used to remove specific text rather than just spaces? I haven’t been able to find anything that would give me the syntax for that, though it’s hinted that it’s possible. In addition, can you specify multiple strings to remove at the beginning of a field, such as LTRIM X or Y from (field)?