August 31, 2020 at 4:08 pm
The two main tables are EquipWorkOrder and EquipWorkOrderHrs. I have two columns that I need to pivot in the EquipWorkOrderHrs table when I join the two togeather by EquipWorkOrderID. The other joins are to give info about the equipment and more Info about the work order. Based on the work order you can have only 1 tech or as many as 5 techs. What I have below gives me the current history of the data in the table. In the long run I need to pivot those two columns based on EquipWorkOrderID.
example data
what I get with the current query, I left some columns off
`
EquipWorkOrderID/DateTm/Equip/AccountCode/Descr/Description/Resolution/Status/UserNm/Hrs/and so on
1 / 08/26/2020 08:46:16 /Gator/01234/John Deere Gator/ Test/Test/ Completed/ John / 1/ ...
1/ 08/26/2020 08:46:16 /Gator/01234/John Deere Gator/ Test/Test/ Completed/ Mike / 1/ ..
1/ 08/26/2020 08:46:16 /Gator/01234/John Deere Gator/ Test/Test/ Completed/ Billy / 1/...
1/ 08/26/2020 08:46:16 /Gator/01234/John Deere Gator/ Test/Test/ Completed/ Aaron/ 1/ ...
1/ 08/26/2020 08:46:16 /Gator/01235/John Deere Loader/ Test/Test/ Completed/ Jason/ 2/ ...
2/ 08/26/2020 08:50:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Mark/ 2/ ...
2/ 08/26/2020 08:50:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Trent/ 2/ ...
3/ 08/26/2020 08:52:00 /Welder/01236/Miller Welder/ Test/Test/ Completed/ John/ 2/ ...
4 08/26/2020 08:53:00 /Skidsteer/01236/John Deere Skidsteer/ Test/Test/ Completed/ Mike/ 3/ ...
4 08/26/2020 08:53:00 /Skidsteer/01236/John Deere Skidsteer/ Test/Test/ Completed/ Tony/3/ ...
5 08/26/2020 08:54:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Tim 2/ ...
5 08/26/2020 08:54:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Joe 2/ ...
5 08/26/2020 08:54:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Jeff 2/ ...
5 08/26/2020 08:54:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Mike 2/ ...
`
What I would like to see...
`
EquipWorkOrderID/DateTm/Equip/AccountCode/Descr/Description/Resolution/Status/UserNm1/Hrs1/UserNm2/Hrs2/UserNm3/Hrs3/UserNm4/Hrs4/UserNm5/Hrs5/and so on
1 / 08/26/2020 08:46:16 /Gator/01234/John Deere Gator/ Test/Test/ Completed/ John / 1/Mike/1/Billy/1/Aaron/1/Jason/1/
2/ 08/26/2020 08:50:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Mark/ 2/ Trent/2/
3/ 08/26/2020 08:52:00 /Welder/01236/Miller Welder/ Test/Test/ Completed/ John/ 2/
4 08/26/2020 08:53:00 /Skidsteer/01235/John Deere Skidsteer/ Test/Test/ Completed/ Mike/ 3/ Tony/ 3/...
5 08/26/2020 08:54:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Tim / 5/ Joe/5/Jeff/5/Mike/5/...
`
`
SELECT Top 1000 a.EquipWorkOrderID, a.DateTm, f.Equip, f.AccountCode, f.Descr,a.Description, a.Resolution, g.Status, c.UserNm, b.Hrs, d.Category, i.MaintLoc, j.EquipType, e.Crew, a.MeterReading,k.Type, a.Comments
FROM EquipWorkOrder a
Left Join EquipWorkOrderHrs b
On a.EquipWorkOrderID = b.EquipWorkOrderID
Left Join AppUser c
On c.UserID = b.UserID
Left Join Category d
On a.CategoryID = d.CategoryID
Left Join Crew e
On e.CrewID = a.CrewID
Left Join Equipment f
On a.EquipmentID = f.EquipmentID
Left Join Status g
On a.StatusID = g.StatusID
Left Join PlantLoc h
On a.PlantLocID = h.PlantLocID
Left Join MaintLocation i
On a.MaintLocationID = i.MaintLocationID
Left Join EquipType j
On j.EquipTypeID = a.EquipTypeID
Left Join Type k
On f.TypeID = k.TypeID
order by a.DateTm Desc
`
September 1, 2020 at 5:55 am
Got some sample records? And how about a couple of CREATE TABLE scripts. Or do you have something against tested queries?
September 1, 2020 at 12:49 pm
I am working in Ignition. I do not have access to create statements or sample records.
Here is what I come up but I removed some of the joins until I get something figured out. This pivots the table but it puts 0's in for the hrs values. I all can't see the username (UserNm).
`
Select * From
(
Select
a.EquipWorkOrderID,
c.UserNm,
b.Hrs
From
EquipWorkOrder a
Left Join EquipWorkOrderHrs b
On a.EquipWorkOrderID = b.EquipWorkOrderID
Left Join AppUser c
On c.UserID = b.UserID
) t
Pivot (
Count(Hrs)
For UserNm IN (
[Tech1],
[Tech2],
[Tech3],
[Tech4],
[Tech5])
) AS pivot_table
`
September 1, 2020 at 2:26 pm
I created a table for this... (Far easier to do in SSRS with a matrix visual)
now someone who's better at Pivot than I am can have a play.
--EquipWorkOrderID/DateTm/Equip/AccountCode/Descr/Description/Resolution/Status/UserNm/Hrs
use tempdb;
go
CREATE TABLE WorkOrder (
EquipWorkOrderID INT
,DateTm DATETIME
,Equip VARCHAR(10)
,AccountCode INT
,Descr VARCHAR(20)
,[Description] VARCHAR(10)
,Resolution VARCHAR(10)
,OrderStatus VARCHAR(10)
,UserNm VARCHAR(8)
,Hrs INT);
GO
INSERT INTO WorkOrder VALUES
(1 ,'08/26/2020 08:46:16', 'Gator', '01234', 'John Deere Gator', ' Test', 'Test', ' Completed', ' John ', 1)
,(1,'08/26/2020 08:46:16', 'Gator', '01234', 'John Deere Gator', ' Test', 'Test', ' Completed', ' Mike ', 1)
,(1,'08/26/2020 08:46:16', 'Gator', '01234', 'John Deere Gator', ' Test', 'Test', ' Completed', ' Billy ', 1)
,(1,'08/26/2020 08:46:16', 'Gator', '01234', 'John Deere Gator', ' Test', 'Test', ' Completed', ' Aaron', 1)
,(1,'08/26/2020 08:46:16', 'Gator', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Jason',2)
,(2,'08/26/2020 08:50:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Mark', 2)
,(2,'08/26/2020 08:50:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Trent', 2)
,(3,'08/26/2020 08:52:00', 'Welder', '01236', 'Miller Welder', ' Test', 'Test', ' Completed', ' John', 2)
,(2,'08/26/2020 08:53:00', 'Skidsteer', '01236', 'John Deere Skidsteer', ' Test', 'Test', ' Completed', ' Mike', 2)
,(4, '08/26/2020 08:53:00', 'Skidsteer', '01236', 'John Deere Skidsteer', ' Test', 'Test', ' Completed', ' Tony', 2)
,(5, '08/26/2020 08:54:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Tim', 2)
,(5, '08/26/2020 08:54:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Joe', 2)
,(5, '08/26/2020 08:54:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Jeff', 2)
,(5, '08/26/2020 08:54:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Mike', 2);
September 1, 2020 at 8:16 pm
I think this should get you started (thanks to pietlinden for the sample data):
--EquipWorkOrderID/DateTm/Equip/AccountCode/Descr/Description/Resolution/Status/UserNm/Hrs
Declare @WorkOrder Table (
EquipWorkOrderID INT
, DateTm DATETIME
, Equip VARCHAR(10)
, AccountCode char(5)
, Descr VARCHAR(20)
, [Description] VARCHAR(10)
, Resolution VARCHAR(10)
, OrderStatus VARCHAR(10)
, UserNm VARCHAR(8)
, Hrs INT);
Insert Into @WorkOrder
Values (1 ,'08/26/2020 08:46:16', 'Gator', '01234', 'John Deere Gator', ' Test', 'Test', ' Completed', ' John ', 1)
, (1 ,'08/26/2020 08:46:16', 'Gator', '01234', 'John Deere Gator', ' Test', 'Test', ' Completed', ' Mike ', 1)
, (1 ,'08/26/2020 08:46:16', 'Gator', '01234', 'John Deere Gator', ' Test', 'Test', ' Completed', ' Billy ', 1)
, (1 ,'08/26/2020 08:46:16', 'Gator', '01234', 'John Deere Gator', ' Test', 'Test', ' Completed', ' Aaron', 1)
, (1 ,'08/26/2020 08:46:16', 'Gator', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Jason',2)
, (2 ,'08/26/2020 08:50:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Mark', 2)
, (2 ,'08/26/2020 08:50:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Trent', 2)
, (3 ,'08/26/2020 08:52:00', 'Welder', '01236', 'Miller Welder', ' Test', 'Test', ' Completed', ' John', 2)
, (2 ,'08/26/2020 08:53:00', 'Skidsteer', '01236', 'John Deere Skidsteer', ' Test', 'Test', ' Completed', ' Mike', 2)
, (4 ,'08/26/2020 08:53:00', 'Skidsteer', '01236', 'John Deere Skidsteer', ' Test', 'Test', ' Completed', ' Tony', 2)
, (5 ,'08/26/2020 08:54:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Tim', 2)
, (5 ,'08/26/2020 08:54:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Joe', 2)
, (5 ,'08/26/2020 08:54:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Jeff', 2)
, (5 ,'08/26/2020 08:54:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Mike', 2);
With userRows
As (
Select *
, UserRowNum = row_number() over(Partition By wo.EquipWorkOrderID Order By wo.UserNm)
From @WorkOrder wo
)
Select usr.EquipWorkOrderID
, DateTm = max(usr.DateTm)
, Equip = max(usr.Equip)
, AccountCode = min(usr.AccountCode)
, Descr = max(usr.Descr)
, [Description] = max(usr.[Description])
, Resolution = max(usr.Resolution)
, OrderStatus = max(usr.OrderStatus)
, UserName1 = max(iif(usr.UserRowNum = 1, usr.UserNm, Null))
, UserHours1 = max(iif(usr.UserRowNum = 1, usr.Hrs, Null))
, UserName2 = max(iif(usr.UserRowNum = 2, usr.UserNm, Null))
, UserHours2 = max(iif(usr.UserRowNum = 2, usr.Hrs, Null))
, UserName3 = max(iif(usr.UserRowNum = 3, usr.UserNm, Null))
, UserHours3 = max(iif(usr.UserRowNum = 3, usr.Hrs, Null))
, UserName4 = max(iif(usr.UserRowNum = 4, usr.UserNm, Null))
, UserHours4 = max(iif(usr.UserRowNum = 4, usr.Hrs, Null))
, UserName5 = max(iif(usr.UserRowNum = 5, usr.UserNm, Null))
, UserHours5 = max(iif(usr.UserRowNum = 5, usr.Hrs, Null))
From userRows usr
Group By
usr.EquipWorkOrderID;
You may need to adjust the row_number() function - there are some differences in the data that you may need to account for...for example, if the date/time is actually different for each user - or there is something else that can be used to order the rows appropriately.
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
September 1, 2020 at 8:25 pm
I get this error when I run the query. (Must declare the table variable "@WorkOrder".)
September 1, 2020 at 8:50 pm
Did you try running my example - or just the query? You need to change the example to fit your tables.
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
September 2, 2020 at 1:26 am
Tech,
Is there a fixed maximum number of people that can work on a work order?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 2, 2020 at 3:01 am
Never mind...
Jeff Williams is using the result of your query... that makes the CROSSTAB (which does the pivot) a bit more complicated and, perhaps, a bit slower (maybe not... it's just been my experience that it can).
With that, I suggest the following. There are some notes in the comments that you should read. This replaces your original query, as well.
WITH
ctePreAgg AS
(--==== Determine the display order for the CROSSTAB (pivot).
-- This can also speed things up a bit if there's multiple UserID rows for the same EquipWorkOrderID
SELECT b.EquipWorkOrderID
,c.UserNm
,Hrs = SUM(b.Hrs)
,DisplayOrder = ROW_NUMBER() OVER (PARTITION BY b.EquipWorkOrderID ORDER BY c.UserNm)
FROM #EquipWorkOrderHrs b
JOIN #AppUser c
ON b.UserID = c.UserID
GROUP BY b.EquipWorkOrderID,c.UserNm
)
,cteCrossTab AS
(--==== This does the CROSSTAB (pivot). If you need more that 5 double slots, we'll need to go "dynamic"
SELECT EquipWorkOrderID
,UserNm1 = MAX(CASE WHEN DisplayOrder = 1 THEN UserNm ELSE '' END)
,Hrs1 = MAX(CASE WHEN DisplayOrder = 1 THEN CONVERT(VARCHAR(10),Hrs) ELSE '' END)
,UserNm2 = MAX(CASE WHEN DisplayOrder = 2 THEN UserNm ELSE '' END)
,Hrs2 = MAX(CASE WHEN DisplayOrder = 2 THEN CONVERT(VARCHAR(10),Hrs) ELSE '' END)
,UserNm3 = MAX(CASE WHEN DisplayOrder = 3 THEN UserNm ELSE '' END)
,Hrs3 = MAX(CASE WHEN DisplayOrder = 3 THEN CONVERT(VARCHAR(10),Hrs) ELSE '' END)
,UserNm4 = MAX(CASE WHEN DisplayOrder = 4 THEN UserNm ELSE '' END)
,Hrs4 = MAX(CASE WHEN DisplayOrder = 4 THEN CONVERT(VARCHAR(10),Hrs) ELSE '' END)
,UserNm5 = MAX(CASE WHEN DisplayOrder = 5 THEN UserNm ELSE '' END)
,Hrs5 = MAX(CASE WHEN DisplayOrder = 5 THEN CONVERT(VARCHAR(10),Hrs) ELSE '' END)
,TotalHrs
FROM ctePreAgg
GROUP BY EquipWorkOrderID
)--==== NOW we can add all the other stuff.
SELECT p.EquipWorkOrderID --Note the alias change
,a.DateTm
,f.Equip
,f.AccountCode
,f.Descr
,a.Description
,a.Resolution
,g.STATUS
,p.UserNm --Note the alias change
,p.Hrs --Note the alias change
,d.Category
,i.MaintLoc
,j.EquipType
,e.Crew
,a.MeterReading
,k.Type
,a.Comments
,p.UserNm1, p.Hrs1
,p.UserNm2, p.Hrs2
,p.UserNm3, p.Hrs3
,p.UserNm4, p.Hrs4
,p.UserNm5, p.Hrs5
FROM cteCrossTab p
LEFT JOIN EquipWorkOrder a ON p.EquipWorkOrderID = a.EquipWorkOrderID
LEFT JOIN EquipWorkOrderHrs b ON b.EquipWorkOrderID = a.EquipWorkOrderID
--LEFT JOIN AppUser c ON b.UserID = c.UserID --NOT NEEDED ANYMORE
LEFT JOIN Category d ON d.CategoryID = a.CategoryID
LEFT JOIN Crew e ON e.CrewID = a.CrewID
LEFT JOIN Equipment f ON f.EquipmentID = a.EquipmentID
LEFT JOIN STATUS g ON g.StatusID = a.StatusID
LEFT JOIN PlantLoc h ON h.PlantLocID = a.PlantLocID
LEFT JOIN MaintLocation i ON i.MaintLocationID = a.MaintLocationID
LEFT JOIN EquipType j ON j.EquipTypeID = a.EquipTypeID
LEFT JOIN Type k ON k.TypeID = f.TypeID
ORDER BY a.DateTm DESC
;
For more information on CROSSTABs and Dynamic CROSSTABs and for reasons why neither Mr. Williams or I used the god awful PIVOT operator, please read the articles at the following 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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply