Subquery to grab percentage by unique criteria

Belair58 · December 11, 2019, 9:10pm

Hello,

I'm needing some (alot) help developing a subquery that will tell me the percentage of records in that are in Time that equal On-Time for each unique value in Cycle Code. Here's my primary query.

SELECT DISTINCT 
od.Work_Code AS Work_Code,
CASE WHEN
CONVERT(char(10),od.Projected_Ship_Date,101) < CONVERT(char(10),tt.Time_End,101)
THEN 'Late'
WHEN rsn.Status = 'Closed'
THEN 'Late'
ELSE 'On-Time'
END AS Time,
od.Product_Code AS Product_Code,
CAST(LEFT(od.Part_Family_Code,2) AS varchar) AS FBGM, 
CASE WHEN cd.Commissionable = 0
          THEN ROUND(qbd.Profit_Percent, 2) 
           ELSE ROUND(qbd.Profit_Percent + cd.Commission_Percent, 2)  END AS Budgeted,
ROUND(ISNULL(qbd.Profit_Percent+cd.Commission_Percent,qbd.Profit_Percent),2) AS Old_Budgeted,
CASE WHEN vo.Miscellaneous_Charge__Unit_Cost IS NULL
          THEN ROUND((oh.Gross_Amount-(oh.Total_Labor_Cost+oh.Total_Material_Cost))/ISNULL(NULLIF           (oh.Gross_Amount,0),1),4)*100 
           ELSE
           ROUND((oh.Gross_Amount-(oh.Total_Labor_Cost+oh.Total_Material_Cost
           +vo.Miscellaneous_Charge__Unit_Cost))/ISNULL(NULLIF(oh.Gross_Amount,0),1),4)*100
           END AS Actual,
 rsn.Status AS Manager_Review_Status, 
 oh.Gross_Amount AS Gross_Amount,
 oh.Total_Labor_Cost AS Total_Labor_Cost,
 oh.Total_Material_Cost AS Total_Material_Cost,
 vo.Miscellaneous_Charge__Unit_Cost AS Misc_Unit_Cost,
 vo.Miscellaneous_Charge__Miscellaneous_Charge_Code AS Misc_Charge_Code,
 CONVERT(char(10),od.Projected_Ship_Date,101) AS Projected_Ship_Date,
 od.User_Text2 AS Reporting_Cycle_Code,
 CONVERT(char(10),tt.Time_End,101) AS End_Ticket_Date,
 oh.Order_Number AS Order_Number,
  sco.Salesman_Name AS Salesman_Name,
 cd.Commission_Percent AS Commission_Percent,
 od.Product_Code AS Product_Code,
 od.Part_Number AS Part_Number,
 cd.Commissionable AS Commissionable,
 tt.Pieces_Good AS Pieces_Good
 
 
 FROM dbo.Order_Detail od 
 LEFT OUTER JOIN dbo.Routing_Header rh ON od.Order_Header_ID = rh.Order_Header_ID
 LEFT OUTER JOIN dbo.vRouting_Step_Number_Line_Item_Specification rsn ON rh.Routing_Header_ID = rsn.Routing_Header_ID
 LEFT OUTER JOIN dbo.Time_Ticket tt ON rsn.Routing_Header_ID = tt.Routing_Header_ID    
 LEFT OUTER JOIN dbo.Order_Header oh ON od.Order_Header_ID = oh.Order_Header_ID
 LEFT OUTER JOIN dbo.Quantity_Break_Detail qbd ON od.Order_Detail_ID = qbd.Order_Detail_ID
 LEFT OUTER JOIN dbo.Billing_Detail bd ON oh.Part_Number_ID = bd.Part_Number_ID
 LEFT OUTER JOIN dbo.vQV_SalesmanCode_Orders sco ON od.Order_Detail_ID = sco.Order_Detail_ID
 LEFT OUTER JOIN dbo.Commission_Distribution cd ON od.Order_Detail_ID = cd.Order_Detail_ID
 LEFT OUTER JOIN dbo.vSalesman_Code sc ON cd.Salesman_Code = sc.Salesman_Code
 LEFT OUTER JOIN dbo.Employee_Code ec ON od.Employee_Code_Routed_By = ec.Employee_Code
 LEFT OUTER JOIN dbo.User_Code uc ON ec.User_Code = uc.User_Code
 FULL OUTER JOIN dbo.vOrder vo ON oh.Order_Header_ID = vo.Order_Header_ID
 
 WHERE 
 od.Product_Code LIKE 'IN CL%' AND 
 qbd.Profit_Percent  <>  0 AND
 od.Profit_Percent  <>  0 AND
 od.Part_Family_Code  NOT IN  ( '' , '0% - FBGM - WO' , '99' , '99% - FBGM' ) AND 
 od.Company_Code ='AIN' AND
 ROUND(ISNULL(qbd.Profit_Percent+cd.Commission_Percent,qbd.Profit_Percent),2) < 99 AND
 rsn.Operation_Code  =  'FINAL MANAGER REVIEW' AND
 rsn.Status  IN  ( 'Closed' , 'Finished', 'Pending', 'Future', 'Current' ) AND
od.User_Text2 LIKE  ('%WENDY%') AND 
(tt.Pieces_Good IS NULL OR tt.Pieces_Good = 1)

Order By od.Work_Code ASC

This gives me data that looks like this.

28A | Late | IN CL | ...|...|...|...|
28A | On-Time| IN CL | ...|...|...|...|
28A | On-Time| IN CL | ...|...|...|...|
28A | On-Time| IN CL | ...|...|...|...|
28B | On-Time| IN CL | ...|...|...|...|
28B | On-Time| IN CL | ...|...|...|...|
28B | Late | IN CL | ...|...|...|...|
28C | On-Time| IN CL | ...|...|...|...|

I'm looking to get Results like
28A | 75 | IN CL
28B | 66.6 | IN CL
28C | 100 | IN CL

Any help or direction would be greatly appreciated.

Wim_Leys · December 11, 2019, 9:53pm

You have presented us a very complex query and a lot of LEFT OUTER JOINs.

Can you provide us with a simplified query, the tables that are used in your simplified query and and test data for those tables?

What data set your query gives,
what data set you want and
the business rules to get the data set you want.

ScottPletcher · December 11, 2019, 11:02pm

My best guess, based on the little info we have so far. I'm afraid the JOINs might lead to an overcount, but since I don't know your data, I'd have no way to adjust for that anyway.
Also, I correctly the very wrong date comparison you were doing. You can't accurately compare dates in 101 (mm/dd/yyyy) format!

SELECT
od.Work_Code AS Work_Code,
CAST(SUM(CASE WHEN CA1.Time = 'On-Time' THEN 1 ELSE 0 END) * 100.0 / 
    COUNT(*) AS decimal(5, 2)) AS [OnTime%],
od.Product_Code
 
 FROM dbo.Order_Detail od 
 LEFT OUTER JOIN dbo.Routing_Header rh ON od.Order_Header_ID = rh.Order_Header_ID
 LEFT OUTER JOIN dbo.vRouting_Step_Number_Line_Item_Specification rsn ON rh.Routing_Header_ID = rsn.Routing_Header_ID
 LEFT OUTER JOIN dbo.Time_Ticket tt ON rsn.Routing_Header_ID = tt.Routing_Header_ID    
 LEFT OUTER JOIN dbo.Order_Header oh ON od.Order_Header_ID = oh.Order_Header_ID
 LEFT OUTER JOIN dbo.Quantity_Break_Detail qbd ON od.Order_Detail_ID = qbd.Order_Detail_ID
 LEFT OUTER JOIN dbo.Billing_Detail bd ON oh.Part_Number_ID = bd.Part_Number_ID
 LEFT OUTER JOIN dbo.vQV_SalesmanCode_Orders sco ON od.Order_Detail_ID = sco.Order_Detail_ID
 LEFT OUTER JOIN dbo.Commission_Distribution cd ON od.Order_Detail_ID = cd.Order_Detail_ID
 LEFT OUTER JOIN dbo.vSalesman_Code sc ON cd.Salesman_Code = sc.Salesman_Code
 LEFT OUTER JOIN dbo.Employee_Code ec ON od.Employee_Code_Routed_By = ec.Employee_Code
 LEFT OUTER JOIN dbo.User_Code uc ON ec.User_Code = uc.User_Code
 FULL OUTER JOIN dbo.vOrder vo ON oh.Order_Header_ID = vo.Order_Header_ID
 CROSS APPLY (
    SELECT 
    CASE WHEN CONVERT(char(8), od.Projected_Ship_Date,112) < CONVERT(char(8),tt.Time_End,112)
    THEN 'Late'
    WHEN rsn.Status = 'Closed'
    THEN 'Late'
    ELSE 'On-Time'
    END AS Time
 ) AS CA1 
 WHERE 
 od.Product_Code LIKE 'IN CL%' AND 
 qbd.Profit_Percent  <>  0 AND
 od.Profit_Percent  <>  0 AND
 od.Part_Family_Code  NOT IN  ( '' , '0% - FBGM - WO' , '99' , '99% - FBGM' ) AND 
 od.Company_Code ='AIN' AND
 ROUND(ISNULL(qbd.Profit_Percent+cd.Commission_Percent,qbd.Profit_Percent),2) < 99 AND
 rsn.Operation_Code  =  'FINAL MANAGER REVIEW' AND
 rsn.Status  IN  ( 'Closed' , 'Finished', 'Pending', 'Future', 'Current' ) AND
od.User_Text2 LIKE  ('%WENDY%') AND 
(tt.Pieces_Good IS NULL OR tt.Pieces_Good = 1)

GROUP BY od.Work_Code
ORDER BY od.Work_Code ASC

Wim_Leys · December 12, 2019, 1:43am

Scott's right about the inaccurate date comparison.

There is actually no reason to convert a date(time) to a string before
comparing dates. You can replace

WHEN CONVERT(CHAR(10), od.Projected_Ship_Date, 101) < CONVERT(CHAR(10), tt.Time_End, 101)

by

WHEN od.Projected_Ship_Date < tt.Time_End

Saves a few conversions.

harishgg1 · December 12, 2019, 7:57am

hi

based on the query
I tried to create a script for creating the table structure as per the query
there may be many more columns but just for resolving the query i came up with this script

I have to also create a script for populating sample data

please click arrow to the left for drop create tables Script

drop table dbo.Order_Detail  
 drop table dbo.Routing_Header
 drop table dbo.vRouting_Step_Number_Line_Item_Specification 
 drop table dbo.Time_Ticket 
 drop table dbo.Order_Header 
 drop table dbo.Quantity_Break_Detail
 drop table dbo.Billing_Detail
 drop table dbo.vQV_SalesmanCode_Orders
 drop table dbo.Commission_Distribution
 drop table dbo.vSalesman_Code
 drop table dbo.Employee_Code 
 drop table dbo.User_Code
 drop table dbo.vOrder
 go 
 
 create table dbo.Order_Detail 
 (
    Work_Code varchar(100),
	Projected_Ship_Date date,
	Part_Family_Code varchar(100),
	User_Text2 varchar(100),
	Product_Code varchar(100),
	Part_Number int,
	Order_Header_ID int,
	Order_Detail_ID int,
	Employee_Code_Routed_By varchar(100),
	Profit_Percent float,
	Company_Code varchar(100)
)
 go 
 
 create table dbo.Routing_Header
 (
 Order_Header_ID int,
 Routing_Header_ID int
 )
 go 
 
 create table dbo.vRouting_Step_Number_Line_Item_Specification 
 (
 Status varchar(100),
 Routing_Header_ID int,
 Operation_Code  varchar(100)
 )
 go 
 
 create table dbo.Time_Ticket 
 (
 	Time_End varchar(100),
	Pieces_Good varchar(100) NULL ,
	Routing_Header_ID    int
 )
 go 
 
 create table dbo.Order_Header 
 (
  Gross_Amount float null ,
  Total_Labor_Cost float,
  Total_Material_Cost float,
  Order_Number int,
  Order_Header_ID int,
  Part_Number_ID 	int
 )
 go 
 
 create table dbo.Quantity_Break_Detail
 (
   Profit_Percent float null ,
   Order_Detail_ID int
 )
 go 
 
 
 create table dbo.Billing_Detail
 (
 Part_Number_ID int
 )
 go 
 
 create table dbo.vQV_SalesmanCode_Orders
 (
    Salesman_Name varchar(100),
    Order_Detail_ID int 
 )
 go 
 
 create table dbo.Commission_Distribution
 (
  Commissionable varchar(100),
  Commission_Percent float null,
  Order_Detail_ID int,
  Salesman_Code	varchar(100)
 )
 go 
 
 create table dbo.vSalesman_Code
 (
 Salesman_Code varchar(100)
 )
 go 
 
 create table dbo.Employee_Code 
 (
 User_Code varchar(100),
 Employee_Code varchar(100)
 )
 go 
 
 create table dbo.User_Code
 (
 User_Code varchar(100)
 )
 go 
 
 create table dbo.vOrder
 (
 Order_Header_ID int,
 Miscellaneous_Charge__Unit_Cost float null
 )
 go

harishgg1 · December 12, 2019, 10:58am

hi

i tried to create sample data

please click arrow to the left for SAMPLE Data Script

 create table dbo.Order_Detail 
 (
    Work_Code varchar(100),
	Projected_Ship_Date date,
	Part_Family_Code varchar(100),
	User_Text2 varchar(100),
	Product_Code varchar(100),
	Part_Number int,
	Order_Header_ID int,
	Order_Detail_ID int,
	Employee_Code_Routed_By varchar(100),
	Profit_Percent float,
	Company_Code varchar(100)
)
 go 
 
 insert into dbo.Order_Detail select '28A','2019-10-11','famili','Wendy','IN CL',1,123,11,'sams',100.00,'AIN'
 insert into dbo.Order_Detail select '28A','2019-12-11','famili','Wendy','IN CL',1,123,11,'sams',100.00,'AIN'
 insert into dbo.Order_Detail select '28A','2019-12-10','famili','Wendy','IN CL',1,123,11,'sams',100.00,'AIN'
 insert into dbo.Order_Detail select '28A','2019-12-09','famili','Wendy','IN CL',1,123,11,'sams',100.00,'AIN'

 
insert into dbo.Order_Detail select '28B','2019-12-11','famili','Wendy','IN CL',1,123,11,'sams',100.00,'AIN'
insert into dbo.Order_Detail select '28B','2019-12-09','famili','Wendy','IN CL',1,123,11,'sams',100.00,'AIN'
insert into dbo.Order_Detail select '28B','2019-10-11','famili','Wendy','IN CL',1,123,11,'sams',100.00,'AIN'
 
 create table dbo.Routing_Header
 (
 Order_Header_ID int,
 Routing_Header_ID int
 )
 go 
 
 insert into dbo.Routing_Header select 123,223
 
 create table dbo.vRouting_Step_Number_Line_Item_Specification 
 (
 Status varchar(100),
 Routing_Header_ID int,
 Operation_Code  varchar(100)
 )
 go 
 
 insert into  dbo.vRouting_Step_Number_Line_Item_Specification select 'Finished',223,'FINAL MANAGER REVIEW'
 
 create table dbo.Time_Ticket 
 (
 	Time_End  date,
	Pieces_Good int NULL ,
	Routing_Header_ID    int
 )
 go 
 
 insert into dbo.Time_Ticket select '2019-11-11',1,223
 
 create table dbo.Order_Header 
 (
  Gross_Amount float null ,
  Total_Labor_Cost float,
  Total_Material_Cost float,
  Order_Number int,
  Order_Header_ID int,
  Part_Number_ID 	int
 )
 go 
 
 insert into dbo.Order_Header select 190.00,2000,3000,1,123,1
 
 create table dbo.Quantity_Break_Detail
 (
   Profit_Percent float null ,
   Order_Detail_ID int
 )
 go 
 
 insert into dbo.Quantity_Break_Detail select 30,11
 
 create table dbo.Billing_Detail
 (
 Part_Number_ID int
 )
 go 
 
 insert into dbo.Billing_Detail select 1
 
 create table dbo.vQV_SalesmanCode_Orders
 (
    Salesman_Name varchar(100),
    Order_Detail_ID int 
 )
 go 
 
 insert into dbo.vQV_SalesmanCode_Orders select 'Hapio',11
 
 create table dbo.Commission_Distribution
 (
  Commissionable varchar(100),
  Commission_Percent float null,
  Order_Detail_ID int,
  Salesman_Code	varchar(100)
 )
 go 
 insert into dbo.Commission_Distribution select 'YES',30,456,'SOP'
 
 create table dbo.vSalesman_Code
 (
 Salesman_Code varchar(100)
 )
 go 
 insert into dbo.vSalesman_Code select 'SOP'
 
 create table dbo.Employee_Code 
 (
 User_Code varchar(100),
 Employee_Code varchar(100)
 )
 go 
 
 insert into dbo.Employee_Code select 'SSSW','sams'
 
 create table dbo.User_Code
 (
 User_Code varchar(100)
 )
 go 
 insert into dbo.User_Code select 'SSSW'
 
 create table dbo.vOrder
 (
 Order_Header_ID int,
 Miscellaneous_Charge__Unit_Cost float null,
 Miscellaneous_Charge__Miscellaneous_Charge_Code varchar(100)
 )
 go 
 
 insert into dbo.vOrder select 123,13.45,'OK'

@Belair58
I'm looking to get Results like
28A | 75 | IN CL
28B | 66.6 | IN CL
28C | 100 | IN CL

Any help or direction would be greatly appreciated.

ok you want to get results like this

what is 75 , 66.6 , 100 ????

Belair58 · December 12, 2019, 1:55pm

harishgg1,
Sorry, I was doing 3 things at once on this. The 75, 66.6, 100 is the percentage of records that show on-Time per Work_Code. I've worked to narrow the query down to only the most needed data, and made the change from above about the date. So if I have 10 total records for 28A and 5 of them are on-time it should be 50, 20 records for 28B and 2 of them are On-Time it should show 10.
Thanks for looking at this I really appreciate it.

SELECT 

CASE 
WHEN od.Projected_Ship_Date < tt.Time_End
THEN 'Late'
WHEN rsn.Status = 'Closed'
THEN 'Late'
WHEN (rsn.Status = 'Pending' AND od.Projected_Ship_Date < GETDATE())
THEN 'Late'
ELSE 'On-Time'
END AS Time,
od.Work_Code AS Work_Code,
rsn.Status AS Manager_Review_Status, 
oh.Order_Number AS Order_Number,
od.Product_Code AS Product_Code 
 
FROM dbo.Order_Detail od 
LEFT OUTER JOIN dbo.Routing_Header rh ON od.Order_Header_ID = rh.Order_Header_ID
LEFT OUTER JOIN dbo.vRouting_Step_Number_Line_Item_Specification rsn ON rh.Routing_Header_ID = rsn.Routing_Header_ID
LEFT OUTER JOIN dbo.Time_Ticket tt ON rsn.Routing_Header_ID = tt.Routing_Header_ID    
LEFT OUTER JOIN dbo.Order_Header oh ON od.Order_Header_ID = oh.Order_Header_ID
LEFT OUTER JOIN dbo.Quantity_Break_Detail qbd ON od.Order_Detail_ID = qbd.Order_Detail_ID
LEFT OUTER JOIN dbo.Billing_Detail bd ON oh.Part_Number_ID = bd.Part_Number_ID
LEFT OUTER JOIN dbo.Commission_Distribution cd ON od.Order_Detail_ID = cd.Order_Detail_ID
 
WHERE 
od.Product_Code LIKE 'IN CL%' AND 
od.Profit_Percent  <>  0 AND
od.Part_Family_Code  NOT IN  ( '' , '0% - FBGM - WO' , '99' , '99% - FBGM' ) AND 
od.Company_Code ='AIN' AND
ROUND(ISNULL(qbd.Profit_Percent+cd.Commission_Percent,qbd.Profit_Percent),2) < 99 AND
rsn.Operation_Code  =  'FINAL MANAGER REVIEW' AND
rsn.Status  IN  ( 'Closed' , 'Finished', 'Pending', 'Future', 'Current' ) AND
od.User_Text2 LIKE  ('%WENDY%') AND 
(tt.Pieces_Good IS NULL OR tt.Pieces_Good = 1)

ORDER BY od.Work_Code ASC

ScottPletcher · December 12, 2019, 2:59pm

I figured there must be a time included which caused them to strip to day only. IF there's not a time, of course a straight comparison would work.

Belair58 · December 16, 2019, 5:31pm

Scott,

Thank you so much for helping me with this. I had to add od.Product_Code to the GROUP BY, but it works great. I didn't realize that you had changed the code. I thought the code in your post was just mine again. When I came back to check on it I saw the difference.

SELECT
od.Work_Code AS Work_Code,
CAST(SUM(CASE WHEN CA1.Time = 'On-Time' THEN 1 ELSE 0 END) * 100.0 / 
    COUNT(*) AS decimal(5, 2)) AS [OnTime%],
od.Product_Code
 
FROM dbo.Order_Detail od 
LEFT OUTER JOIN dbo.Routing_Header rh ON od.Order_Header_ID = rh.Order_Header_ID
LEFT OUTER JOIN dbo.vRouting_Step_Number_Line_Item_Specification rsn ON rh.Routing_Header_ID = rsn.Routing_Header_ID
LEFT OUTER JOIN dbo.Time_Ticket tt ON rsn.Routing_Header_ID = tt.Routing_Header_ID    
LEFT OUTER JOIN dbo.Order_Header oh ON od.Order_Header_ID = oh.Order_Header_ID
LEFT OUTER JOIN dbo.Quantity_Break_Detail qbd ON od.Order_Detail_ID = qbd.Order_Detail_ID
LEFT OUTER JOIN dbo.Billing_Detail bd ON oh.Part_Number_ID = bd.Part_Number_ID
LEFT OUTER JOIN dbo.Commission_Distribution cd ON od.Order_Detail_ID = cd.Order_Detail_ID
 
CROSS APPLY (
    SELECT 
    CASE WHEN CONVERT(char(8), od.Projected_Ship_Date,112) < CONVERT(char(8),tt.Time_End,112)
    THEN 'Late'
    WHEN rsn.Status = 'Closed'
    THEN 'Late'
    ELSE 'On-Time'
    END AS Time
 ) AS CA1 

WHERE 
od.Product_Code LIKE 'IN CL%' AND 
od.Profit_Percent  <>  0 AND
od.Part_Family_Code  NOT IN  ( '' , '0% - FBGM - WO' , '99' , '99% - FBGM' ) AND 
od.Company_Code ='AIN' AND
ROUND(ISNULL(qbd.Profit_Percent+cd.Commission_Percent,qbd.Profit_Percent),2) < 99 AND
rsn.Operation_Code  =  'FINAL MANAGER REVIEW' AND
rsn.Status  IN  ( 'Closed' , 'Finished', 'Pending', 'Future', 'Current' ) AND
od.User_Text2 LIKE  ('%WENDY%') AND 
(tt.Pieces_Good IS NULL OR tt.Pieces_Good = 1)

GROUP By od.Work_Code, od.Product_Code
ORDER BY od.Work_Code ASC

Is this working like a standard subquery? Where the Cross Apply Select is being evaluated first and then the CA1 is evaluating that data to generate the percentage?

Thanks again.

ScottPletcher · December 16, 2019, 5:42pm

The CROSS APPLY (CA) is generated before the SELECT results. CA is effectively a type of join, so it's generated at the time of the other joins.

Here I'm just using it to generate an alias name. The really cool thing about CAs is that they can cascade use of aliases, that is, an earlier alias can be used to define later alias(es) in another CROSS APPLY.

Examples:

--Example1
SELECT *,
    LEFT(test_string, comma1 - 1) AS first_value,
    SUBSTRING(test_string, comma1 + 1, comma2 - comma1 - 1) AS second_value,
    SUBSTRING(test_string, comma2 + 1, comma3 - comma2 - 1) AS third_value,
    SUBSTRING(test_string, comma3 + 1, 200) AS last_value
FROM (
    SELECT 'abc,de,ghijkl,mnop' AS test_string
) AS data
CROSS APPLY (
    SELECT CHARINDEX(',', test_string) AS comma1
) AS aliases1
CROSS APPLY (
    SELECT CHARINDEX(',', test_string, comma1 + 1) AS comma2
) AS aliases2
CROSS APPLY (
    SELECT CHARINDEX(',', test_string, comma2 + 1) AS comma3
) AS aliases3
--Example2
SELECT *
FROM (
    SELECT 1 AS col1, 2 AS col2, 'A' AS col3, 'dog' AS col4
) AS data
CROSS APPLY (
    SELECT col1 * 10 + col2 AS alias_1_2, REPLICATE(col3, 5) + REPLICATE(col4, 2) AS alias_3_4
) AS aliases1
CROSS APPLY (
    SELECT alias_1_2 * col2 AS alias_1_2_col2, SUBSTRING(alias_3_4, 7, 5) AS alias_3_4_substring
) AS aliases2