Hello:
I have a table that has the JobNo and Time1 Columns.
EmpNo | JobNo | Time1
8000 | Office | 4.5
8000 | Sales | 3.5
8000 | 200003-01 | 63
8001 | Office | 7.5
8002 | Sales | 19.25
I would like to add these up and create four columns
EmpNo | Sales | Office | Billable
8000 | 3.5 | 4.5 | 63
8001 | 0 | 7.5 | 0
8002 | 19.25 | 0 | 0
Here's my attempt at a query:
SELECT JobNo, Time1
FROM (SELECT SUM(Time1) AS Office
WHERE (JobNo = 'Office')
UNION ALL
SELECT SUM(Time1) AS Sales
WHERE (JobNo = 'Sales')
UNION ALL
SELECT SUM(Time1) AS Billable
WHERE (JobNo <> 'Office') OR
(JobNo <> 'Sales')) AS derivedtbl_1
GROUP BY derivedtbl_1
Steve Anderson
May 5, 2020 at 9:04 pm
Like this?
Setup:
--Setup:
CREATE TABLE WorkData (
EmpNo INT,
JobNo VARCHAR(10),
Hrs DECIMAL(4,2));
-- add some data
INSERT INTO WorkData VALUES
(8000 , 'Office' , 4.5)
,(8000 , 'Sales' , 3.5)
,(8000 , '200003-01' , 63)
,(8001 , 'Office' , 7.5)
,(8002 , 'Sales' , 19.25);
Setup:
CREATE TABLE WorkData (
EmpNo INT,
JobNo VARCHAR(10),
Hrs DECIMAL(4,2));
-- solution
SELECT EmpNo
, JobNo
, [Sales] = SUM(CASE WHEN JobNo = 'Sales' THEN Hrs ELSE 0 END)
, [Office] = SUM(CASE WHEN JobNo = 'Office' THEN Hrs ELSE 0 END)
, [Billable] = SUM(CASE WHEN JobNo = '200003-01' THEN Hrs ELSE 0 END)
FROM Workdata
GROUP BY EmpNo, JobNo;
You are looking at a cross-tab or pivot of the data:
Select EmpNo
, Sales = sum(Case When JobNo = 'Sales' Then Time1 End)
, Office = sum(Case When JobNo = 'Office' Then Time1 End)
, Billable = sum(Case When JobNo Not In ('Sales', 'Office') Then Time1 End)
From yourTable
Where {some criteria}
Group By
EmpNo;
This is a cross-tab version and generally the easiest and most efficient way.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 5, 2020 at 9:20 pm
Are there always 3 (or less) rows for an employee? Or are there random non-sales/non-office items?
You're really trying to pivot here. You might look at Pivot, though this isn't necessarily great for performance.
Getting this for items that aren't there is tough, as you'll be trying to fill in the space for employees to try and match things up.
May 6, 2020 at 1:41 pm
Simply amazing!!
My next question is, is there a good book you would recommend to teach me all these tricks?
Thank you!!
Steve Anderson
May 6, 2020 at 2:47 pm
May 6, 2020 at 3:00 pm
Simply amazing!!
My next question is, is there a good book you would recommend to teach me all these tricks?
Thank you!!
CROSSTABs are a bit of an ancient "Black Arts" method that not many people cover anymore but, as you've just seen, are still incredibly useful and are quite easy to do. There are a couple of articles that I wrote on the subject that make CROSSTABs pretty easy to understand and then first one also contains a performance comparison between CROSSTABs and PIVOT (which I don't recommend using). The second article explains how to do dynamic CROSSTABs fairly easily. Here are the links...
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
--Jeff Moden
Change is inevitable... Change for the better is not.
June 2, 2020 at 9:02 pm
I played around with the query a little and added percentage columns.
The math works, but it seems to default to six decimal places. 100% - 100.000000. Is there a way to round and keep as an integer?
Thanks!
SELECT TimeData.EmpNo, EmpData.EmpNameFirst, EmpData.EmpNameLast, SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) AS Total, SUM(CASE WHEN JobNo = 'Parts' THEN Time1 ELSE 0 END) AS Parts,
SUM(CASE WHEN JobNo = 'Parts' THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS PParts, SUM(CASE WHEN JobNo = 'Sales' THEN Time1 ELSE 0 END) AS Sales,
SUM(CASE WHEN JobNo = 'Sales' THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS PSales, SUM(CASE WHEN JobNo = 'Office' THEN Time1 ELSE 0 END) AS Office,
SUM(CASE WHEN JobNo = 'Office' THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS POffice, SUM(CASE WHEN JobNo NOT IN ('Parts', 'Sales', 'Office', 'Holiday', 'PTO')
THEN Time1 ELSE 0 END) AS Billable, SUM(CASE WHEN JobNo NOT IN ('Parts', 'Sales', 'Office', 'Holiday', 'PTO') THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS PBillable
FROM TimeData INNER JOIN
EmpData ON TimeData.EmpNo = EmpData.EmpNo
WHERE (TimeData.Date1 BETWEEN @SDate AND @EDate)
GROUP BY TimeData.EmpNo, EmpData.EmpNameLast, EmpData.EmpNameFirst
ORDER BY EmpData.EmpNameLastSteve Anderson
June 2, 2020 at 9:58 pm
You can wrap the calculation in a CAST or CONVERT:
CAST(SUM(CASE WHEN JobNo = 'Parts' THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS numeric(5,2)) AS PParts,I used NUMERIC(5,2) so you get 2 decimal places for the percentages. If you really want an integer - then change from numeric to int or change the 2 to a 0.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 3, 2020 at 1:53 am
I played around with the query a little and added percentage columns.
The math works, but it seems to default to six decimal places. 100% - 100.000000. Is there a way to round and keep as an integer?
Thanks!
SELECT TimeData.EmpNo, EmpData.EmpNameFirst, EmpData.EmpNameLast, SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) AS Total, SUM(CASE WHEN JobNo = 'Parts' THEN Time1 ELSE 0 END) AS Parts,
SUM(CASE WHEN JobNo = 'Parts' THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS PParts, SUM(CASE WHEN JobNo = 'Sales' THEN Time1 ELSE 0 END) AS Sales,
SUM(CASE WHEN JobNo = 'Sales' THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS PSales, SUM(CASE WHEN JobNo = 'Office' THEN Time1 ELSE 0 END) AS Office,
SUM(CASE WHEN JobNo = 'Office' THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS POffice, SUM(CASE WHEN JobNo NOT IN ('Parts', 'Sales', 'Office', 'Holiday', 'PTO')
THEN Time1 ELSE 0 END) AS Billable, SUM(CASE WHEN JobNo NOT IN ('Parts', 'Sales', 'Office', 'Holiday', 'PTO') THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS PBillable
FROM TimeData INNER JOIN
EmpData ON TimeData.EmpNo = EmpData.EmpNo
WHERE (TimeData.Date1 BETWEEN @SDate AND @EDate)
GROUP BY TimeData.EmpNo, EmpData.EmpNameLast, EmpData.EmpNameFirst
ORDER BY EmpData.EmpNameLast
Steve,
Consider the old programming trick of using "Divide'n'Conquer" to "DRY" (Don't Repeat Yourself) your code out for both readability, modifiability, and performance. I don't have your tables to check my work with but the following should produce the same result as your code above as well as keeping your percentages as rounded integers.
WITH
cteDry AS
(--===== This DRYs out the code for readability. It also does most of the work for the CROSSTAB
SELECT EmpNo
,Parts = SUM(IIF(JobNo = 'Parts' ,Time1,0))
,Sales = SUM(IIF(JobNo = 'Sales' ,Time1,0))
,Office = SUM(IIF(JobNo = 'Office',Time1,0))
,Billiable = SUM(IIF(JobNo NOT IN ('Parts','Sales','Office') ,Time1,0))
,Total = SUM(Time1)
FROM dbo.TimeData
WHERE JobNo NOT IN ('Holiday','PTO') --This keeps these out of all calculations in one "swell-foop". :D
AND Date1 >= @SDate AND Date1 < DATEADD(dd,1,@EDate) --Get out of the habit of using BETWEEN. It will burn you someday. :D
GROUP BY EmpNo
)--==== Now the calculations are easy and so is the readability.
SELECT emp.EmpNo, emp.EmpNameFirst, emp.EmpNameLast
,tim.Parts ,PartsPct = CONVERT(INT,ROUND(tim.Parts *100.0/tim.Total,0))
,tim.Sales ,SalesPct = CONVERT(INT,ROUND(tim.Sales *100.0/tim.Total,0))
,tim.Office ,OfficePct = CONVERT(INT,ROUND(tim.Office *100.0/tim.Total,0))
,tim.Billiable ,BilliablePct = CONVERT(INT,ROUND(tim.Billiable*100.0/tim.Total,0))
,tim.Total --Keep in mind that none of this includes 'Holiday' or 'PTO' time.
FROM cteDry tim
JOIN dbo.EmpData emp ON emp.EmpNo = tim.EmpNo
ORDER BY EmpNameLast, EmpNameFirst
;
Also remember that "Set Based" doesn't necessarily mean you have to do everything in a single SELECT (nor even in a single query but we don't need to go that far in this code because of the "blocking operator" of "GROUP BY" in the CTE).
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2020 at 2:39 pm
The real answer, which you don't want to hear, is it any time you do any kind of rounding, you are going to get some error. You just have to decide what kind of error you want; should each individual measurement be rounded according to some rule? Should the total be adjusted to always work out to 100%? This is a design decision and it's always technically wrong.
Please post DDL and follow ANSI/ISO standards when asking for help.
June 3, 2020 at 4:29 pm
Nothing like stating the obvious, Joe.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply