Re: Report query

On Wed, 27 Jul 2005 19:28:42 GMT, c0de w via SQLMonster.com wrote:

>
>I am trying to create a query which should return 4 columns
>Facility | NoOfActiveApplicants | NoOfArchivedApplicants | TotalApplicants
>I would be glad to even have just 3 columns since, the Total can be computed
>in the display table (ColdFusion interface)
>
>Facility | NoOfActiveApplicants | NoOfArchivedApplicants
>
>So far I have the following query, which returns just 2 rows with both
>NoOfActiveApplicants & NoOfArchivedApplicants under the same column.
>Am stuck here, any help is apprecialted. Thanks in advance!
>
>select
> NoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end)
>, Facility = case when c.facility is null then c.JobDBFacilityName else c.
>facility end
> from tblapplicant a, tblJobDB b, tblfacilities c
>where a.jobid = b.jobid
> and b.facility = c.facilityid
>group by c.facilityid,c.JobDBFacilityName,c.facility,b.facility
>union all
>select
> NoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end)
>, Facility = case when c.facility is null then c.JobDBFacilityName else c.
>facility end
> from tblArchiveapplicant a, tblArchiveJob b, tblfacilities c
>where a.JobVacancyNumber = b.JobVacancyNumber
> and b.facility = c.facilityid
>group by c.facilityid,c.JobDBFacilityName,c.facility,b.facility
>order by facility

Hi c0de,

Hard to say without knowing anything about structure of the tables in
your database (see www.aspfaq.com/5006 for a better way to ask questions
in these groups). But you might see if the following helps you:

SELECT Facility, NoOfActiveApplicants, NoOfArchivedApplicants,
NoOfActiveApplicants + NoOfArchivedApplicants AS
TotalApplicants
FROM (SELECT COALESCE(facility, JobDBFacilityName) AS Facility,
(SELECT COUNT(*)
FROM tblapplicant AS a
INNER JOIN tblJobDB AS b
ON a.jobid = b.jobid
WHERE b.facility = c.facilityid) AS
NoOfActiveApplicants,
(SELECT COUNT(*)
FROM tblArchiveapplicant AS a
INNER JOIN tblArchiveJob AS b
ON a.JobVacancyNumber = b.JobVacancyNumber
WHERE b.facility = c.facilityid) AS
NoOfArchivedApplicants
FROM tblfacilities AS c) AS der
ORDER BY Facility

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.

Relevant Pages

  • Re: Counting mulitple groups
    ... I don't understand what the query is attempting to accomplish and i, ... >> then the Devices that belong to each unit in that facility. ... >> used to test patients as well. ... >> count duplicates. ...
    (microsoft.public.access.queries)
  • Re: Report Not working
    ... Here is simple sql query which i did. ... FROM [Surgery by Facility Query 01] ... Jeff Boyce wrote: ...
    (microsoft.public.access.reports)
  • Returning partial rows with Linq
    ... How do I specify what columns to include in a Linq to Dataset query? ... A facility can have several stations. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Returning partial rows with Linq
    ... this dataset contains the results of a query of stations. ... The combination of customer, facility, and station uniquely identifies a station. ... What I want is, for a given customer, query the table and get a list of facilities. ... This means that if you create a linq query like you did above, you are simply traversing DataRow objects in a datatable and if you want to return DataRow objects, they too have to be part of a DataTable. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: adding database to another
    ... > originally set up for each facility. ... Queries are great! ... Data viewed through and written to a Query is just the same as ...
    (microsoft.public.access.tablesdbdesign)