[thelist] Stored Procedures

[Martyn Haigh]

Hey everyone - this is kinda an extension on 'SQL Chaos'.

I've got this SQL query - looks a bit like this now (and works well!!) using
SQL Server 7

I've got a bit of ASP grabbing the two vars LabelRS__SQLStartDate and
LabelRS__SQLEndDate from a form (These are two valid dates):

SELECT
CONVERT(varchar(10), g.DateCreated, 105) AS Date,
Sum(CAST(i.OtherEnq as int)) as 'Other Enquiry',
Sum(CAST(i.CatReq as int)) as 'Catalogue Request',
Sum(CAST(i.KnowMore as int)) as 'Know More',
Total = Sum(CAST(i.OtherEnq as int)) + Sum(CAST(i.CatReq as int)) +
Sum(CAST(i.KnowMore as int))
FROM InterestedForm as i
INNER JOIN GeneralForm as g ON i.ID = g.ID
WHERE (DATEDIFF(day, g.DateCreated, " + LabelRS__SQLStartDate + " ) < 1
AND DATEDIFF(day, g.DateCreated, " + LabelRS__SQLEndDate + " ) > -1 )
GROUP BY CONVERT(varchar(10), g.DateCreated, 105)

And using this bit of code it returns a nicely formatted HTML table :

response.write "<table border='1'><tr>"

for each heading in StatsRS.fields
   response.write "<td><strong>" & heading.name & "</strong></td>"
next

response.write "</tr><tr><td>"

response.write StatsRS.getstring(,, "</td><td>",
"</td></tr><tr><td>","&nbsp;")

response.write "</td></tr></table>"

Date		Other Enquiry	Catalogue Request	Know More
Total
28-01-2003 	1 			95 			4
100
29-01-2003 	1 			127 			11
139

I've now decided to turn this SQL query in to a Stored Procedure, and it now
looks something like this : (this hasn't yet been tested - I'm just working
on the idea at the moment)

CREATE procedure sp_Interested_Form_stats
@dateStart date,
@dateEnd date
as
 Begin
SELECT
	CONVERT(varchar(10), g.DateCreated, 105) AS Date,
	Sum(CAST(i.OtherEnq as int)) as 'Other Enquiry',
	Sum(CAST(i.CatReq as int)) as 'Catalogue Request',
	Sum(CAST(i.KnowMore as int)) as 'Know More',
	Total = Sum(CAST(i.OtherEnq as int)) + Sum(CAST(i.CatReq as int)) +
Sum(CAST(i.KnowMore as int))
	FROM InterestedForm as i
	INNER JOIN GeneralForm as g ON i.ID = g.ID
	WHERE (DATEDIFF(day, g.DateCreated, dateStart ) < 1
	 AND DATEDIFF(day, g.DateCreated, dateEnd ) > -1 )
	GROUP BY CONVERT(varchar(10), g.DateCreated, 105)
 end
GO

But what I would really like to be able to do is to tot up the total number
of each column of numbers so it will display a little something like the one
below :

Date		Other Enquiry	Catalogue Request	Know More
Total
28-01-2003 	1 			95 			4
100
29-01-2003 	1 			127 			11
139
Total		2			222			15
239

Is there any way of doing this using SQL or am I going mad?  Shall I go back
and just do it in ASP?

Thank you very much.

Martyn


DISCLAIMER: The information in this email is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this email by
anyone else is unauthorised. If you are not the intended recipient, any
disclosure, copying, distribution or any action taken or omitted to be taken
in reliance on it, is prohibited and may be unlawful.