MS SQL would be
Select extract(month from incident_date) as TheDate, sum(case ltiormti when 'L' then 1 else 0 end) as LTI,
sum(case ltiormti when 'M' then 1 else 0 end) as MTI
from incident
where companyid = companyid
and
((extract(month from incident_date) >= 3 and extract(year from incident_date) >= 2002)
or (extract(month from incident_date) < 3 and (extract (year from incident_date) = 2003)))
order by incident_date
group by extract(month from incident_date)
Oracle has a choose() function, JetEngine has iif() but I know of nothing in Interbase for this.
On Mon, 24 Mar 2003 11:56:07 +1200, Tracey Maule <[EMAIL PROTECTED]> wrote:
Hi
yes, sql again
i have a simple table, which holds a date (incident_date) and a type (ltiormti) of incident. ltiormti has a value of either L, M, N or null
I need to retrieve how many L's occur in each month, over the last 12 months, and how many M's etc.....
I am creating a report (html) which is to chart ratios of each type of incident. what i was wondering is if i can do something like this without a stored procedure:
Select extract(month from incident_date) as TheDate, //these bits are the bits i need, but cannot do
(select sum(ltiormti) where ltiormti = 'L') as LTI, (select sum(ltiormti) where ltiormti = 'M') as MTI
from incident
where companyid = companyid
and
((extract(month from incident_date) >= 3 and extract(year from incident_date) >= 2002)
or (extract(month from incident_date) < 3 and (extract (year from incident_date) = 2003)))
order by incident_date
if so, how? cheers in advance
Tracey Software Developer / Web Master Logis [EMAIL PROTECTED] (025) 213-1065
--
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED] with body of "unsubscribe delphi"
Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
