asked
Case math problem
I have this select p.ProjectName, b.BidPackageNumber, c.ConstructionManager, CASE when cp.Race = 'Black' then sum(cp.WorkHourPerWeek) end as BlackHours, CASE when cp.Race = 'White' then sum(cp.WorkHourPerWeek) END as WhiteHours, CASE when cp.Race = 'Asian' then SUM(cp.WorkHourPerWeek) END as AsianHours, CASE when cp.Race = 'NativeIndian' then SUM(cp.WorkHourPerWeek) END As NativeIndianHours, CASE when cp.Race = 'Hispanic' then SUM(cp.WorkHourPerWeek) END as HispanicHours, CASE when cp.Race = 'Other' then SUM(cp.WorkHourPerWeek) end as otherHours FROM CertifiedPayroll cp inner join BidPackage b on b.BidPackage_ID = cp.BidPackage_ID inner join Contractors c on c.Trade_ID = cp.SubContractor_ID inner join Projects p on p.Project_ID = b.Project_ID group by p.ProjectName, b.BidPackageNumber, cp.Race, c.ConstructionManager Now I need for when every Cleveland or Cleve are found in the city column it multiplies by race If there a better way please let me.
Do people (and databases) really still use the term "race"? Anyway - Can you provide some data examples, to show what you have and what you want as a result.
I have to total race. Then total city only if they live in Cleveland. city is in a table called Certified Payroll with the rest of the columns (cp.Race) what I need is for when Cleveland or Cleve is entered. something like this if White and lives in Cleveland or Cleve add sum hours of the race that have Cleveland chosen to display as Cleveland Total. so after it sum the total of each race everyone then check for Cleveland resident then add the sum of Cleveland resident display as Cleveland Resident Total. I hope I am explaining right. This is for a report. I doing this as a view
Do you mean that in addition to breaking it down by race you also need to filter by Cleve or Cleveland? Wouldn't that simply go into the WHERE clause? Or do you mean that you will only break down the hours by race when you have Cleve or Cleveland? If so, you could, I'm not crazy about it, but you could, nest case statements. SELECT ... CASE WHEN (cp.City = 'Cleveland' or cp.City = 'Cleve') THEN CASE when cp.Race = 'Black' then sum(cp.WorkHourPerWeek) end ELSE 42 END as BlackHours But, that's could look funny as a result set.
@!postonoh Then... if I understand what you're saying, you want to break out Cleveland from the rest of the data completely... I'd do that as a sub-select, possibly a CTE. I'm still not sure I understand though.
In the last census, I put down my religion as 'Jedi' - does that mean I'm going to get paid for 42 hour weeks irrespective now? :)
I have to total race. Then total city only if they live in Cleveland. something like this if White and lives in Cleveland or Cleve add sum hours of the race that have Cleveland chosen to display as Cleveland Total. so after it sum the total of each race everyone then check for Cleveland resident then add the sum of Cleveland resident display as Cleveland Resident Total. I hope I am explaining right. This is for a report. I doing this as a view
Maybe [ROLLUP][1] or [CUBE][2] could help you out. [1]:
http://msdn.microsoft.com/en-us/library/bb522495.aspx [2]:
http://msdn.microsoft.com/en-us/library/bb522495.aspx
