Cross Join With Left Join Query from Linked Server

Question

Have a few CROSS JOIN going on and a UNION ALL and want to get a percent from 2 of the columns. Below you will see my script. Looking for a little help.

DECLARE @Now DATETIME,
@Monday DATETIME,
@Friday DATETIME,
@StartTime VARCHAR(16) ,
@EndTime VARCHAR(16)
SET @StartTime = '00:01:00AM'
SET @EndTime = '11:59:59PM'
SET @Now = GETDATE()
SET @Monday = DATEADD(dd, DATEDIFF(dd, 0, @Now), -1)
SET @Friday = DATEADD(dd, DATEDIFF(dd, 0, @Now), -1)

SELECT t1.EventDate,t1.Sorter, t1.Items,t2.Misfire,t3.DisabledTrays
FROM
(
SELECT EventDate = CONVERT(CHAR(15), CURRENT_TIMESTAMP -1, 106)
   , Items = COUNT(SortID) 
   , Sorter = 'TILT_1' 
FROM  [111.11.1.111].[AAA].[dbo].[SortList]
WHERE CreationTime BETWEEN @Monday + ' ' + @StartTime
                  AND @Friday + ' ' + @EndTime 
                    AND WorkstationID = 'T01'
)t1
CROSS JOIN
(
SELECT EventDate = CONVERT(CHAR(15), CURRENT_TIMESTAMP -1, 106) 
   , Misfire = COUNT(StatsUpd) 
   , 'TILT_1' AS Sorter
FROM  [111.11.1.111].[AAA].[dbo].[MisfireLog]
WHERE RecordedPeriod BETWEEN @Monday + ' ' + @StartTime
                    AND @Friday + ' ' + @EndTime
                        AND WorkstationID = 'T01'
)t2
CROSS JOIN
(
SELECT EventDate = CONVERT(CHAR(15), CURRENT_TIMESTAMP -1, 106)
   , DisabledTrays = COUNT(SorterID)
   , 'TILT_1' AS Sorter
FROM [111.11.1.111].[AAA].[dbo].[DisabledCarriers]
WHERE SorterID  = 'TILT_1'
)t3

UNION ALL

SELECT t1.EventDate,t1.Sorter, t1.Items,t2.Misfire,t3.DisabledTrays
FROM
(
SELECT EventDate = CONVERT(CHAR(15), CURRENT_TIMESTAMP -1, 106)
   , Items = COUNT(SortID) 
   , 'TILT_2' AS Sorter
FROM  [111.11.1.111].[AAA].[dbo].[SortList]
WHERE CreationTime BETWEEN @Monday + ' ' + @StartTime
                AND @Friday + ' ' + @EndTime
                    AND WorkstationID = 'T02'
)t1
CROSS JOIN
(
SELECT EventDate = CONVERT(CHAR(15), CURRENT_TIMESTAMP -1, 106)
   , Misfire = COUNT(StatsUpd) 
   , 'TILT_2' AS Sorter
FROM  [111.11.1.111].[AAA].[dbo].[MisfireLog]
WHERE RecordedPeriod BETWEEN @Monday + ' ' + @StartTime
                    AND @Friday + ' ' + @EndTime
                        AND WorkstationID = 'T02'
)t2
CROSS JOIN
(
SELECT EventDate = CONVERT(CHAR(15),CURRENT_TIMESTAMP -1, 106)
   , DisabledTrays = COUNT(SorterID)
   , 'TILT_2' AS Sorter
FROM [111.11.1.111].[AAA].[dbo].[DisabledCarriers]
WHERE SorterID = 'TILT_2'
)t3;

SELECT 'TILT_1' AS Sorter
   , COUNT (StoreID) AS'NI_Misfires'
FROM [111.11.1.111].[AAA].[dbo].[MisfireLog]
WHERE StoreID = 'NI' AND RecordedPeriod BETWEEN @Monday + ' ' + @StartTime
                    AND @Friday + ' ' + @EndTime AND WorkstationID = 'T01' 

----------------------------------------------------------------------------

With what you see above this is the result set I currently get.

EventDate       Sorter Items       Misfire     DisabledTrays
--------------- ------ ----------- ----------- -------------
23 Aug 2017     TILT_1 4332        30          1
23 Aug 2017     TILT_2 4246        21          2

(2 row(s) affected)

Sorter NI_Misfires
------ -----------
TILT_1 5
TILT_2 5

(2 row(s) affected)

------------------------------------------------------------------------------

What I would like to see is the following. Showing Items- Misfire then divided by Items (as Misfire%) to show 99.30%. I am getting stuck trying to achieve this.

EventDate       Sorter Items       Misfire     Accuracy%    DisabledTrays
--------------- ------ ----------- ----------- ---------    -------------
23 Aug 2017     TILT_1 4332        30          99.30%       1
23 Aug 2017     TILT_2 4246        21          99.51%       2

(2 row(s) affected)

Sorter NI_Misfires
------ -----------
TILT_1 5
TILT_2 5

(2 row(s) affected)

Answer 1

Change your select line to this:

SELECT
t1.EventDate,
t1.Sorter, 
t1.Items,
t2.Misfire,
CONVERT(VARCHAR, ROUND((CONVERT(FLOAT, t1.Items) - CONVERT(FLOAT, t2.Misfire))/CONVERT(FLOAT, t1.Items) * 100, 2)) + '%' AS [Accuracy%],
t3.DisabledTrays