Hi,
This is how I might do it in SQL Server:
SELECT
SUM(CASE WHEN deliverymethod = 'F' THEN
1
ELSE
0
END) AS FAXES,
SUM(CASE WHEN deliverymethod = 'E' THEN
1
ELSE
0
END) AS EMAILS
FROM
EReferralDailyMailoutHistory g INNER JOIN
DeliveryStatusMaster d ON
g.deliverystatus = d.statuscode
AND
g.deliverydate > #createodbcdate(arguments.argsStartDate)#
AND
g.deliverydate < #createodbcdate(arguments.argsEndDate)#
perhaps this will give you an idea of an alternate method of using NULLIF and
are now able to find the Oracle equivlalent of CASE.
>>> "Mike Kear" <[EMAIL PROTECTED]> 08/05/2007 2:47 pm >>>
I have a need to report on various statuses in a status field. The
values in the field can be DX, EM, FX, PT and a few others and i need
to have sql work out how many of each code are in the status field for
a particular day.
I can call all the status field, then loop over it using query of
queries, but i managed ot do it all in one go in SQLServer useing the
NULLIF function. Does anyone know if there's a similar function in
Oracle?
Here's the relevant query in SQLServer that givies what i want - it's
the equivalient in oracle that eludes me right now:
SELECT
g.deliverydate, g.deliverystatus,d.statusdescription,
faxes = ( count(*) - COUNT( NULLIF(deliverymethod, 'F')) ) ,
emails = ( count(*) - COUNT( NULLIF(deliverymethod, 'E')) ) ,
posts = ( count(*) - COUNT( NULLIF(deliverymethod, 'P')) ) ,
Phone = ( count(*) - COUNT( NULLIF(deliverymethod, 'T')) ) ,
( COUNT (*) ) as total
FROM
EReferralDailyMailoutHistory g , DeliveryStatusMaster d
WHERE
g.deliverystatus = d.statuscode
AND
g.deliverydate > #createodbcdate(arguments.argsStartDate)#
AND
g.deliverydate < #createodbcdate(arguments.argsEndDate)#
GROUP BY
g.deliverystatus ,d.statusdescription, g.deliverydate
order by g.deliverystatus desc, g.deliverydate
This gives me a nice query with a count for each of the different
status and delivery codes i want to report on.
--
Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"cfaussie" group.
To post to this group, send email to cfaussie@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en
-~----------~----~----~----~------~----~------~--~---
