[thelist] Stored Procedures

Martyn Haigh martyn.haigh at virgincosmetics.com
Thu Jan 30 06:36:01 CST 2003


Rudy -

Once again - thank you for the help - although I'm sure you'll be happy to
hear I'm having a few problems.  I'm half thinking that I'm causing too much
trouble here and should do it in ASP - just as I don't want to abuse the
list and the other half is thinking if I can get this then I will have
learnt a hell of a lot about SQL and will be a step closer to not having to
ask these questions.  So if you are getting annoyed with these questions
just shout and I'll do it all in ASP.

If you are not getting fed up yet - have a look at this.

I read your article, and I can see the concept behind it - but transplanting
my code in to your template I get this :

select   "detail"
       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,  SQLStartDate  ) < 1
	 AND DATEDIFF(day, g.DateCreated,  SQLEndDate  ) > -1 )
	 GROUP BY CONVERT(varchar(10), g.DateCreated, 105)
 union all
 select  "total"
	 Sum(Sum(CAST(i.OtherEnq as int))),
	 Sum(Sum(CAST(i.CatReq as int))),
	 Sum(Sum(CAST(i.KnowMore as int))),
	 sum(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,  SQLStartDate   ) < 1
	 AND DATEDIFF(day, g.DateCreated,   SQLEndDate ) > -1 )
	 GROUP BY CONVERT(varchar(10), g.DateCreated, 105)
  order by 1, 2


I think what is messing me up is that my details section is using SUM to add
up the total number of bits in that day.  So my total section is becoming a
little confused.

Thinking about it - maybe it's the WHERE section of the total section that
is messing it up a little...

Can you point me in the right direction.

Martyn Haigh

Site Developer
The Virgin Cosmetics Company
City Fields, Chichester, West Sussex PO20 2FP
tel: 01243 622282


-----Original Message-----
From: rudy [mailto:r937 at interlog.com]
Sent: 30 January 2003 12:13
To: thelist at lists.evolt.org
Subject: Re: [thelist] Stored Procedures

> Is there any way of doing this using SQL or am I going mad?

yes, there is

Row sums and the "totals" row
http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid
424565_tax285649,00.html

> Shall I go back and just do it in ASP?

you could, because doing it in a stored procedure is quite efficient


rudy

--
* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester
and archives of thelist go to: http://lists.evolt.org
Workers of the Web, evolt !


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.





More information about the thelist mailing list