As Werner said, the problem is that tabularx requires at least one X type column. But the problem is also that there is no magic involved: your table will not shrink in size because tabularx cannot make your content smaller, but only stretch the space to make it bigger.
The following does a bit more in order to at least fit the table onto A4 when rotated for viewing in landscape mode.
R and C are new column types, based on X defined using the array package. These are substituted for r and c. The header lines must be handled specially. \centering is added here, with \arraybackslash added in the final column to preserve the use of \\ to end the row.
\hskip0pt is added to every column to allow hyphenation of the first word in the cell.
Implementing this and deleting some of the gunky stuff (e.g. \textbf{}), we get:
\documentclass[a4paper]{article}
\usepackage{geometry,tabularx,booktabs,rotating}
\geometry{scale=.8}
\newcolumntype{R}{>{\raggedleft\arraybackslash\hskip0pt}X}
\newcolumntype{C}{>{\centering\arraybackslash\hskip0pt}X}
\begin{document}
\begin{sidewaystable}[htbp]
\centering
\caption{Add caption}
\begin{tabularx}{\textwidth}{RRCRRC*{5}{R}}
\toprule
\centering\textbf{Specialty} & \centering\textbf{Procedure} & \centering\textbf{Theatre Minutes} & \centering\textbf{Major / Minor Grouping} & \centering\textbf{Mean (historical data)} &\centering\textbf{standard deviation} & \centering\textbf{total number of surgeries} & \centering\textbf{percentile difference for each operation} & \centering\textbf{total time difference of estimate (during 2 years of data)} & \centering\textbf{time difference per day} & \centering\arraybackslash\textbf{number of operations per day} \\
\midrule
\centering\textbf{General Surgery} & Abscess Drainage & 30 & \textbf{major} & 11.17 & 9.02 & \textbf{1135} & 168.64\% & 21375 & 29.28082192 & 1.555 \\
& Appendicectomy & 60 & \textbf{major} & 51.83 & 21.25 & \textbf{800} & 15.77\% & 6540 & 8.95890411 & 1.096 \\
& Biopsy / Excision / Drainage & 30 & \textbf{major} & 36.40 & 28.57 & \textbf{1073} & -17.58\% & -6868 & -9.408219178 & 1.470 \\
& Cholecystectomy & 90 & \textbf{major} & 91.42 & 36.80 & \textbf{782} & -1.56\% & -1114 & -1.526027397 & 1.071 \\
& Dressing/wound & 30 & \textbf{major} & 30.67 & 24.50 & \textbf{363} & -2.18\% & -243 & -0.332876712 & 0.497 \\
& EUA & 30 & \textbf{major} & 31.71 & 34.00 & \textbf{329} & -5.38\% & -561 & -0.768493151 & 0.451 \\
& Hernia Repair & 60 & \textbf{major} & 75.93 & 45.37 & \textbf{521} & -20.97\% & -8297 & -11.36575342 & 0.714 \\
& Laparoscopy (excluding appendicectomy) & 60 & \textbf{major} & 59.41 & 35.32 & \textbf{476} & 1.00\% & 282 & 0.38630137 & 0.652 \\
& Laparotomy (incl. open chole etc) & 210 & \textbf{major} & 152.22 & 88.32 & \textbf{1533} & 37.95\% & 88571 & 121.330137 & 2.100 \\
& Scoping procedure & 90 & \textbf{major} & 103.99 & 84.69 & \textbf{111} & -13.45\% & -1553 & -2.12739726 & 0.152 \\
\bottomrule
\end{tabularx}
\label{tab:addlabel}
\end{sidewaystable}
\end{document}
producing:
This is hardly attractive or, even, especially intelligible, but it does fit on the page, albeit with several complaints about bad boxes.
Beyond this, you need to think about how to present the data. For example, right now, the major/minor column is useless since all cases shown are major. So that could be incorporated into the caption or added as a note beneath the table or whatever would be most appropriate. Similarly, the speciality column is useless. Even if this is only part of a longer table, this kind of information can be presented as headings for sub-divisions within the table, rather than taking up a column which will mostly consist of empty space. In any case, as it is, you can't fit much more on the page, so presumably this is, in fact, a single table and any other data will go in other tables.
Something like this is a bit less indigestible:
But something like this is probably a further improvement:
This was done using threeparttablex (though threeparttable would do here) to create the notes, allowing the headings to be made more concise. The % symbols have also been moved into the relevant heading and the percentile removed, further reducing the lines required here. The result fits with no complaints of bad boxes.
\documentclass[a4paper]{article}
\usepackage{geometry,tabularx,booktabs,rotating,threeparttablex}
\geometry{scale=.8}
\newcolumntype{R}{>{\raggedleft\arraybackslash\hskip0pt}X}
\newcolumntype{L}{>{\raggedright\arraybackslash\hskip0pt}X}
\newcolumntype{C}{>{\centering\arraybackslash\hskip0pt}X}
\begin{document}
\begin{sidewaystable}[htbp]
\centering
\begin{threeparttable}
\caption{Add caption}
\begin{tabularx}{\textwidth}{LCRC*{5}{R}}
\toprule
\multicolumn{8}{l}{\bfseries Speciality: General Surgery / Grouping: major} &\\\cmidrule{1-9} \centering\textbf{Procedure} & \centering\textbf{Theatre Minutes} & \centering\textbf{Mean\tnote{a} } &\centering\textbf{standard deviation} & \centering\textbf{total number of surgeries} & \centering\textbf{\% difference per operation} & \centering\textbf{total time difference of estimate\tnote{b}} & \centering\textbf{time difference per day} & \centering\arraybackslash\textbf{number of operations per day} \\
\midrule
Abscess Drainage & 30 & 11.17 & 9.02 & \textbf{1135} & 168.64 & 21375 & 29.28082192 & 1.555 \\
Appendicectomy & 60 & 51.83 & 21.25 & \textbf{800} & 15.77 & 6540 & 8.95890411 & 1.096 \\
Biopsy / Excision / Drainage & 30 & 36.40 & 28.57 & \textbf{1073} & -17.58 & -6868 & -9.408219178 & 1.470 \\
Cholecystectomy & 90 & 91.42 & 36.80 & \textbf{782} & -1.56 & -1114 & -1.526027397 & 1.071 \\
Dressing/wound & 30 & 30.67 & 24.50 & \textbf{363} & -2.18 & -243 & -0.332876712 & 0.497 \\
EUA & 30 & 31.71 & 34.00 & \textbf{329} & -5.38 & -561 & -0.768493151 & 0.451 \\
Hernia Repair & 60 & 75.93 & 45.37 & \textbf{521} & -20.97 & -8297 & -11.36575342 & 0.714 \\
Laparoscopy (excluding appendicectomy) & 60 & 59.41 & 35.32 & \textbf{476} & 1.00 & 282 & 0.38630137 & 0.652 \\
Laparotomy (incl. open chole etc) & 210 & 152.22 & 88.32 & \textbf{1533} & 37.95 & 88571 & 121.330137 & 2.100 \\
Scoping procedure & 90 & 103.99 & 84.69 & \textbf{111} & -13.45 & -1553 & -2.12739726 & 0.152 \\
\bottomrule
\end{tabularx}
\begin{tablenotes}
\item[a]historical data
\item[b]during 2 years of data
\end{tablenotes}
\label{tab:addlabel}
\end{threeparttable}
\end{sidewaystable}
\end{document}
tabularxonly first its contents to the width if you use at least oneX-column. You don't have any. – Werner Nov 26 '15 at 00:39tabularxwill not shrink the content to fit within the given width. It'll only stretch outX-columns to make the entiretabularxwidth fit. If your table content is too wide, you have to find your own way of shrinking it. One way would be to stack the header content. See How to add a forced line break inside a table cell to manage non-paragraph-like columns (likec,l, andr). – Werner Nov 26 '15 at 00:54