| Author |
Topic |
|
fragglefish
Starting Member
2 Posts |
 Posted - 2005-10-15 : 06:58:27
|
Hello  I am trying to create a crosstab query in SQL. It is all done within one table (dbo.contacts). I want to use the appointmentagency and appointmentdate field to create a crosstab query detailing how many appointments i have booked for each appointmentageny for the next 7 days.appointmentdate ---->appointmentagency||\/the appointmentageny field is just a text field. The appointmentdate field is a date time.. but i have written a function the extracts the date only... (dbo.fn_dateonly).I am really struggling with this i havent as yet been able to write a crosstab query in SQl and would appreciate any and all assistance that you could offer. If anyone has any ideas you could email me on. dave.clarke@quaycomms.co.ukCheers |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
|
|
fragglefish
Starting Member
2 Posts |
Posted - 2005-10-15 : 07:41:39
|
| HelloI have followed the link and am still having some problems with the code. i used EXECUTE spcrosstab 'SELECT [appointmentagency] FROM dbo.contacts cWHERE appointmentdate > getdate()+7 and appointmentdate < getdate()+14,GROUP BY appointmentagency', 'count(appointmentagency)', 'appointmentdate', 'contacts'And i get the following error message:Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'app'.Does the code look correct? |
 |
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-10-15 : 11:27:55
|
| I think you are missing/having extra a single qoute in your stored procedure...Print the select statement and check it.Karunakaran |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-10-15 : 16:34:52
|
| Crosstab queries with dynamic column headers are notoriously difficult to do in TSQL. But wise DBAs know that there is simply NEVER a good reason to do dynamic crosstabs in SQL Server.Why?Because the dynamic nature of the resulting dataset makes it impossible to use, not only in any further server-side processing, but for any canned reporting application (such as Crystal) as well. And if you are generating your report using Query Analyzer, well, guess what? QA is a development tool, not a reporting tool!Export your data to a true reporting tool, such as Active Reports, Crystal Reports, MS Access, or Microsoft Excel, all of which can create sexy crosstabs at the push of a button.SQL Server is a server only, and should not be used for either reporting or data entry. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-10-15 : 19:34:52
|
quote:
Originally posted by blindman
Crosstab queries with dynamic column headers are notoriously difficult to do in TSQL.
Good thing Jeff doesn't agree with ya:http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspxNeither do I, for that matter.  quote:
But wise DBAs know that there is simply NEVER a good reason to do dynamic crosstabs in SQL Server.Why?Because the dynamic nature of the resulting dataset makes it impossible to use, not only in any further server-side processing, but for any canned reporting application (such as Crystal) as well.
Crystal can certainly do cross tabs, but if you don't have it already it is simply not worth spending money on just to get them.And I do plenty of extra server-side processing of cross tabs using the code in the article (I'm biased, I wrote it) It's also easy to extend to do more advanced processing than would be possible or convenient in Crystal. In fact, the only thing that makes them "impossible" to use is trying to use it with Crystal; ASP/ASP.Net handles them very nicely in fact. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-10-16 : 01:54:51
|
| You are entitled to your opinion, but mine is not based upon a lack of experience either.I use crosstabs pretty frequently, but only with fixed column headers. After 10 years of database application development and hundreds of reports, I have NEVER needed to use SQL Server to create dynamic crosstabs for the presentation layer. It makes an interesting coding problem, but for practical use I consider maSQLbation.As far as Crystal goes, heck, I wasn't recommending it. I think Crystal is one of the worst application on the market. But no cross-tabing you do in SQL Server is going to be as powerful or as easy as using a pivot table in Microsoft Excel, and practically everybody already has a copy of that.I stand by the principle that SQL Server is not a presentation layer, query analyzer is not a reporting tool, and neither should be used as such. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-10-16 : 01:56:39
|
| maSQLbation: code that is fun to write, but not necessarily productive. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-10-16 : 10:21:34
|
| Yeah, I saw your statement at the end of one article: "To make a long story short: Pivoting at the presentation layer results in clearer, shorter code and much better performance."There are other reasons for leaving data presentation out of the database layer as well.Which doesn't mean that your dynamic cross-tab code isn't impressive. I thought it was very elegant, and wish I had written it. I must sheepishly admit that I, too, engage in maSQLbation occasionally. |
|
|
|
|
Crosstab query in SQL