Need help adding boolean count

Question

Post reply

Need help adding boolean count

geo039

SSC Rookie

Points: 38
More actions
March 29, 2010 at 2:48 pm

#96646

All
i have a stored procedure that takes position vacancy and filled counts and displays them in a sql report. I have added two more boolean columns into the wqrpositions table (see query below) and need to show that count in a column called frozen and unbudgeted, all other criteria stay the same...i am struggling to fit those two boolean requests into the where clause wo changing the results of the query now. I simple want to include the addl criteria when boolean is true.
SELECT
cc.cdCstCntr,
cc.cdSection,
cc.descCstCntr,
p.cdJobClass,
jc.descJobClass,
COUNT(p.cdPosition) AS TotalPosition,
COUNT(e.espn) AS TotalEmployees,
COUNT(p.cdPosition) - COUNT(e.espn) AS TotalVacancies,
FROM
dbo.CodesJobClass AS jc
INNER JOIN
dbo.WQRPositions AS p ON jc.cdJobClass = p.cdJobClass AND p.cdPositionType IN (1, 2, 3) AND ISNULL(p.expDate, GETDATE()) >= GETDATE()
LEFT OUTER JOIN
dbo.CodesCostCenters AS cc ON p.cdCstCntr = cc.cdCstCntr
LEFT OUTER JOIN
dbo.Employees AS e ON p.ESPN = e.espn
WHERE
(cc.ESPNPersonnelAssigned IS NOT NULL)
AND (ISNULL(cc.expDate, GETDATE()) >= GETDATE())
AND (ISNULL(@costCenters,'')='' or cc.cdCstCntr in (select stringval from fn_ParseString(@costCenters)))
AND (e.cdEmpType IS NULL OR e.cdEmpType = 1)
AND (e.cdEmpStatus IS NULL OR e.cdEmpStatus IN (1, 2, 3, 100, 102))GROUP BY
cc.cdCstCntr,
p.cdJobClass,
jc.descJobClass,
cc.descCstCntr,
cc.cdSection
ORDER BY
cc.cdCstCntr,
jc.descJobClass,
p.cdJobClass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004475 More actions · Answer 1

Duplicate post. Please post possible solutions at the following URL so they all get posted in the same place. Thanks.

http://www.sqlservercentral.com/Forums/Topic892222-23-1.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)