March 29, 2010 at 3:00 pm
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
March 29, 2010 at 5:17 pm
Your question is a bit unclear. What are the two new boolean columns you want to add? How do you intend to use them?
Rob Schripsema
Propack, Inc.
March 29, 2010 at 5:20 pm
March 30, 2010 at 7:17 am
Use case statement and sum function.
Example:
...
sum(case addl when 'P' then 1 else 0 end) AS TotalPosition,
sum(case addl when 'E' then 1 else 0 end) AS TotalEmployees,
sum(case addl when 'F' then 1 else 0 end) AS Frozen,
sum(case addl when 'U' then 1 else 0 end) AS UNBudget,
...
March 30, 2010 at 8:24 am
Rob
In the select statement I am getting count columns for total vacant, and filled. I added two new flags to the table as to whether a position is frozen or unbudgeted. I want to add those count blnisfrozen as Frozen and count blnisunbudgeted as Unbudgeted. However, I am struggling to modify the query to include those counts when true to the where clause or is there another way to do it.
The reason I'm stuck is TotalVacancies takes the # of positions in the table and subtracts those that have employees assigned to them. frozen and unbudgeted are boolean all I need is to take positions that are checked as true, however I'm struggling to figure out how to include the addl criteria the way the current query is written.
Hope this makes sense
March 30, 2010 at 9:34 am
OK, that helps some. Sounds like you want to do a count of the true (or false) values in a boolean (bit) field.
Two ways. One was just suggested above.
1) Use a CASE statement.
select
sum(CASE IsFrozen WHEN 1 then 1 else 0 END) as FrozenCount,
sum(CASE IsBudgeted WHEN 1 then 1 else 0 END) as BudgetedCount
FROM MyTable
2) Since a boolean in SQL is a bit field (0 or 1), you could cast the value to an int and simply do a sum.
select
sum(CAST(IsFrozen as int)) as FrozenCount,
sum(CAST(IsBudgeted as int)) as BudgetedCount
from MyTable
In either case, if a NULL value is allowed in the column, it will count as FALSE in these counts.
Does that help?
Rob Schripsema
Propack, Inc.
March 30, 2010 at 9:51 am
Oops, maybe I missed the point? You say you want to add the new criteria to the WHERE clause. Like this?
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))
AND (blnIsFrozen = 0) AND (blnIsUnbudgeted = 0) // <-- like this?
Or are we still missing the point?
Rob Schripsema
Propack, Inc.
March 30, 2010 at 10:27 am
Here's a little example of what you're looking to do:
DROP table #Fruits
create table #Fruits (FruitGroup CHAR(1), FruitName varchar(20), iLike BIT)
INSERT INTO #Fruits (FruitGroup, FruitName, iLike)
SELECT 'B', 'Strawberry', 1 UNION ALL
SELECT 'B', 'Raspberry', 1 UNION ALL
SELECT 'B', 'Blackberry', 1 UNION ALL
SELECT 'B', 'Blueberry', 0 UNION ALL
SELECT 'A', 'CoxApple', 1 UNION ALL
SELECT 'A', 'CrabApple', 0 UNION ALL
SELECT 'A', 'BramleyApple', 0
SELECT FruitGroup,
SUM([Rows]) AS FruitsInGroup,
SUM(CASE iLike WHEN 1 THEN [Rows] ELSE 0 END) AS FruitsILike
FROM (
SELECT FruitGroup, iLike, COUNT(*) AS [Rows]
FROM #Fruits
GROUP BY FruitGroup, iLike
) d
GROUP BY FruitGroup
I reckon you could probably do the inner aggregate as a derived table from WQRPositions, something like this:
FROM dbo.CodesJobClass AS jc
INNER JOIN (
SELECT cdJobClass, blnisfrozen, blnisunbudgeted, COUNT(*) AS Part_Position
FROM dbo.WQRPositions
WHERE cdPositionType IN (1, 2, 3)
AND ISNULL(expDate, GETDATE()) >= GETDATE()
GROUP BY cdJobClass, blnisfrozen, blnisunbudgeted) AS p
ON jc.cdJobClass = p.cdJobClass
LEFT OUTER JOIN dbo.CodesCostCenters AS cc ON p.cdCstCntr = cc.cdCstCntr
To make this work, you will need to know and understand the cardinality of each of the tables in your query.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 30, 2010 at 2:11 pm
Yes the Sum statement is doing what I want. Now all I need to do is subtract the sum of frozen and unbudgeted from total position and i've got it
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,
SUM(case p.blnisfrozen when '1' then 1 else 0 end) As Frozen,
SUM(case p.blnIsUnBudgeted when '1' then 1 else 0 end) as Unbudgeted
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply