June 14, 2005 at 1:45 pm
I’m trying to take the count from each select and display the results.
--get Applicant Count
select FacilityID, Null As 'HireCnt',COUNT(*) AS 'ApplicantCnt' from Applicant
where PlanYear=2001
and DateHired is null ---- means that they weren’t hired since field is null
GROUP BY FacilityID
--get Hire Count
select FacilityID,COUNT(*) AS 'HireCnt', Null As 'ApplicantCnt' from Applicant
where PlanYear=2001
and DateHired is not null---- means that they were hired since field is not null
GROUP BY FacilityID
The output should look like this:
Facility_id | # of Hires | # of Applicants |
1 | 150 | 50 |
2 | 75 | 50 |
For reason the output is this:
Facility | # of Hires | # of Applicants |
1 | null | 199 |
2 | 105 | null |
Any advice on this is very appreciated. I'm a novice to T-SQL programming so I'm sure the
answer is fairly evident...
Thanks in advance,
GP
June 14, 2005 at 1:54 pm
What you are attempting to perform is frequently called a pivot.
This can be done in SQL by using a case statement inside of a sum.
select FacilityID
, SUM( CASE WHEN DateHired IS NULL then 1 else 0 end ) as NotHiredCnt
, SUM( CASE WHEN DateHired IS NOT NULL then 1 else 0 end ) as HiredCnt
from Applicant
where PlanYear = 2001
GROUP BY FacilityID
SQL = Scarcely Qualifies as a Language
June 14, 2005 at 2:05 pm
You get Null because you select NULL : )
Null As 'HireCnt'
Null As 'ApplicantCnt'
About the numbers they might be correct. It seems that 1 row gives you the No Of Apllicants THAT WHERE NOT hired and another one gives you the no of Applicants that WHERE Hired
SELECT
FacilityID,
SUM( CASE WHEN DateHired is not null THEN 1 ELSE 0 END) AS 'HireCnt',
SUM( CASE WHEN DateHired is null THEN 1 ELSE 0 END) AS 'NotHireCnt',
COUNT(*) As 'ApplicantCnt' --All Applicantes
FROM Applicant
WHERE PlanYear=2001
GROUP BY FacilityID
Vasc
June 14, 2005 at 2:07 pm
Thanks so much for the quick response. That makes perfect sense.
As I'm learning more t-sql, the power of the language is quite amazing...
If i understand correctly, the sum and the nested case statement simply sums all records where the DateHired is not null....or in the second case, when it is null...
Thanks again,
GP
June 14, 2005 at 2:22 pm
Thanks for your response Vasc...
The statement works perfectly and retrieves the data as I wanted it...
What are some good books on t-sql programming?
Thanks,
GP
June 15, 2005 at 12:08 pm
One more quick question regarding my original post.
How should I restructure my query to accommodate more tables?
Should I have separate queries that hit each table from which i needed counts from and then insert those values into a temp table?
The final output from 3 tables should be:
Applicant table: Hire/Applicant counts
Termination table: Termination counts
TransferPromotion table: Promotion counts
Facility | # of Hires | # of Applicants | # of Termination | # Promotions |
1 | 100 | 199 | 2 | 5 |
2 | 105 | 160 | 25 | 10 |
June 15, 2005 at 12:15 pm
I think you'll have to come up with both versions of the code and see which runs faster.
My guess would be that 3 derived tables joined with the facility id will be the one that runs the fastest.
June 15, 2005 at 2:00 pm
Well, this is what i have so far. It seems to work fine as long as I specify the Plan Year in the derived table. Also if there are no records in the termination table for the plan year, no records are return. If I remove this condition, it works, but the data is incorrect.
I'm sure its something w/ my join or how the plan year is referred to...
Please let me know what I'm doing wrong...
SELECT
A.FacilityID,
SUM( CASE WHEN DateHired is not null THEN 1 ELSE 0 END) AS 'HireCnt',
SUM( CASE WHEN DateHired is null THEN 1 ELSE 0 END) AS 'NotHireCnt',
Terms.TermsCnt
FROM Applicant A
RIGHT JOIN
(SELECT FacilityID,
SUM( CASE WHEN DateTerminated is not null THEN 1 ELSE 0 END) AS 'TermsCnt'
FROM Termination
WHERE PlanYear=2002
GROUP BY FacilityID)AS Terms
ON A.FacilityID=Terms.FacilityID
WHERE A.PlanYear=2002
GROUP BY A.FacilityID,Terms.TermsCnt
ORDER BY A.FacilityID asc
June 15, 2005 at 2:02 pm
Should be Left Join not Right Join
Vasc
June 15, 2005 at 2:14 pm
I had tried right join as well and it returns the same set of data.
Do i have specify the plan year in the sub query? As i mentioned in my last post, if no records exist in Termination table for the specified plan year, but records exist in the Applicant table, the query doesn't return anything...
How can I still return data if nothing exists in the Termination table?
June 15, 2005 at 2:17 pm
maybe a full outer join is required??
June 15, 2005 at 2:20 pm
Yep that works great. It return null for the TermsCnt column if there is no data...
How can I display 0 instead of null in that column?
June 15, 2005 at 2:22 pm
ISNULL(ColumnName, 0)
June 15, 2005 at 2:27 pm
Ok, I think this is the final...thanks for your advice...
SELECT
A.FacilityID,
SUM( CASE WHEN DateHired is not null THEN 1 ELSE 0 END) AS 'HireCnt',
SUM( CASE WHEN DateHired is null THEN 1 ELSE 0 END) AS 'NotHireCnt',
ISNULL(Terms.TermsCnt,0)As 'TermsCnt'
FROM Applicant A
FULL OUTER JOIN
(SELECT FacilityID,
SUM( CASE WHEN DateTerminated is not null THEN 1 ELSE 0 END ) AS 'TermsCnt'
FROM Termination
WHERE PlanYear=2003
GROUP BY FacilityID)AS Terms
ON A.FacilityID=Terms.FacilityID
WHERE A.PlanYear=2003
GROUP BY A.FacilityID,Terms.TermsCnt
ORDER BY A.FacilityID asc
June 15, 2005 at 2:32 pm
Nice...
FULL OUTER JOIN is not going to give you the right result : )
LEFT JOIN was K but NOT FULL.
If you do FULL you are going to end up with more NotHireCnt than in reality : ) for the rows that are in Termination but not in Applicant : ))
You have to put an extra cond that the A.FacilityID IS NOT NULL for the FULL JOIN to work
Vasc
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply