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.faci lity
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.faci lity
order by facility 2 1245
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.faci lity 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.faci lity 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)
Hi c0de ,
As Hugo correctly pointed out (see www.aspfaq.com/5006 for a better
way to ask questions in these groups). As a friendly advice please do
post DDL,DML as it become easier for others to test their queries .
All I can see you wish to prepare a report which can be done using
corelated subquery (I can be wrong because I am not having data with me
to check the correctness of my query)
Select F.facilityid,
(select count(*) from tblapplicant a, tblJobDB b where a.jobid =
b.jobid and b.facility=F.facilityid),
(select count(*) from tblArchiveapplicant a, tblArchiveJob b where
a.JobVacancyNumber = b.JobVacancyNumber and b.facility=F.facilityid)
from tblfacilities F
May this query solve your problem. As an another advice please use same
column name in all tables that are to be linked (related) .
With warm regards
Jatinder Singh This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Nathan Bloomfield |
last post by:
Does anyone know if there is any documentation which relates to Access2k + ?
or can anyone help adjust the code?
I am having trouble converting the DAO references.
TITLE :INF: How to...
|
by: Grim Reaper |
last post by:
I know this is probably an easy question, but I could not find/figure it
out.
Basically, I am printing mailing labels with a "Sorting/Grouping" section
that groups the label types together....
|
by: Mike Conklin |
last post by:
This one really has me going. Probably something silly. I'm using
dcount for a report to determine the number of different types of
tests proctored in a semester.
My report is based on a...
|
by: Richard Hollenbeck |
last post by:
I tried to ask this question before on the 14th of January but I never got a
reply. I'm still struggling with the problem. I'll try to rephrase the
question:
I have a crosstab query with rows...
|
by: Tom |
last post by:
This is related to an earlier post entitled "Text in Query/Numbers in
Report"
Brief recap - report has a query as a record source. The query shows
the correct data, the report translating the...
|
by: lorirobn |
last post by:
Hi,
I have a report that works just fine.
Now I would like to add the capability to choose selection criteria to
limit what is displayed. I created several reports that do this, but
they used...
|
by: john |
last post by:
I have a report to print envelopes. The report is based on a query. Now I
need to make 10 more queries to make different selections of addresses.
Every query has the same output fields as the...
|
by: lorirobn |
last post by:
Hi,
I have a report displaying items that are missing from a room. I
created 2 queries, the first getting the items IN the room, and the
second being an "unmatched" query that references the...
|
by: creative1 |
last post by:
Here is how you create a complex data report that involves parent and child commands and you can update information at runtime. Its pretty straight forward to work with simple queries; however,...
|
by: pupilstuff |
last post by:
hi guys i wan to make dyanmic crystal report according to values which i checked from check box
thats all i did
1. I made data set having data table name "Customer"
2 i put four columm id,name...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |