May 24, 2007 at 9:31 am
I'm using the query below to summarize shipping performance. It works great except when they split a line item into two shipments. What I'm doing is setting each line item to a 1 or 0 for two differnet scenarios as to whether it shipped on time (the two case statements in the inner most sql). THen I sum those grouping on the soNO and dueDate to combine each order. Then lastly, I sum the total number of orders and the total number of orders where they weren't late. When they split a line item into two or more shipments, it looks like its not on time because (in the example data below, soNO 0338255) 3 were sent one way, 7 another, all 10 shipped on time but I'm looking at each line, 3/10 and 7/10 when its reallky 10/10. Is there some way to sumarize the line items (soKey) so I can compare the total shipped for that sokey to orderQty? I could do all this with a cursor but I'm trying to avoid that.
THanks for any help
Allen
Sample data
field names:
soNo, soKey, dueDate, shipDate, shipper, confirmed, orderQty, shipQty, orderDate, invShip, status, nullship, on_time
data:
033811 033811 1000 2007-05-01 00:00:00.000 2007-05-01 00:00:00.000 117111 Y 4.00000 4.00000 2007-04-28 00:00:00.000 4.00000 Closed 0 0
033825 033825 1000 2007-05-01 00:00:00.000 2007-05-01 00:00:00.000 117195 Y 10.00000 3.00000 2007-04-28 00:00:00.000 10.00000 Closed 0 1
033825 033825 1000 2007-05-01 00:00:00.000 2007-05-01 00:00:00.000 117196 Y 10.00000 7.00000 2007-04-28 00:00:00.000 10.00000 Closed 0 1
033845 033845 1000 2007-05-01 00:00:00.000 2007-05-01 00:00:00.000 117137 Y 160.00000 160.00000 2007-04-28 00:00:00.000 160.00000 Closed 0 0
query I have
SELECT COUNT(CASE WHEN (on_time + nullship) = 0 THEN 1 END) AS ShippedCount,
COUNT(*) AS totalCount
FROM
(
SELECT soNo, dueDate, SUM(on_time) AS on_time, SUM(nullship) AS nullship
FROM
(
SELECT DISTINCT soInfo.soNo, soInfo.Sokey, soInfo.dueDate,
sMaster.shipper,sMaster.shpDate,sMaster.confirmed,
soInfo.orderQty, sItem.shipQty,
soInfo.orderDate, soInfo.invShip, soInfo.status
, nullShip = CASE
WHEN sMaster.shipper IS NULL THEN 1
ELSE 0
END
, on_time = CASE
WHEN soInfo.dueDate >= sMaster.shpDate
and sMaster.confirmed = 'Y'
and sItem.shipQty/soInfo.orderQty >= .95 THEN 0
ELSE 1
END
FROM ((M2MDATA97.dbo.soInfo soInfo
LEFT OUTER JOIN sItem sItem ON soInfo.Sokey=sItem.fsokey)
LEFT OUTER JOIN iMast iMast ON (soInfo.partNo=iMast.partNo)
AND (soInfo.fpartrev=iMast.rev))
LEFT OUTER JOIN sMaster sMaster ON sItem.shipper=sMaster.shipper
WHERE soInfo.dueDate = '2007-05-01' -- day you are running it for
)tblTemp
GROUP BY soNo, dueDate
) tblTwo
Thanks
May 24, 2007 at 10:14 am
Can you post the DDL for all tables involved and sample data for them as well?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 24, 2007 at 10:50 am
Most of the datsa comes from views but I have the data types. The key on all these tables are identity columns.
TABLES:
soInfo.soNo char 6
soInfo.soKey char 6 + char 3 + char 3
soInfo.dueDate datetime
soInfo.orderDate datetime
soInfo.invShip numeric
soInfo.status char 20
soInfo.orderQty numeric
sMaster.shipDate datetime
sMaster.shipper char 6
sMaster.confirmed char 1
sItem.shipQty numeric
derived fields
nullship
on_time
This is the order of sample Data below:
soInfo.soNo char 6
soInfo.soKey char 6 + char 3 + char 3
soInfo.dueDate datetime
sMaster.shipDate datetime
sMaster.shipper char 6
sMaster.confirmed char 1
soInfo.orderQty numeric
sItem.shipQty numeric
soInfo.orderDate datetime
soInfo.invShip numeric
soInfo.status char 20
nullship
on_time
Sample Data data1:
033811
033811 1000
2007-05-01 00:00:00.000
2007-05-01 00:00:00.000
117111
Y
4.00000
4.00000
2007-04-28 00:00:00.000
4.00000
Closed
0
0
Sample Data data2:
033825
033825 1000
2007-05-01 00:00:00.000
2007-05-01 00:00:00.000
117195
Y
10.00000
3.00000
2007-04-28 00:00:00.000
10.00000
Closed
0
1
Sample Data data3:
033825
033825 1000
2007-05-01 00:00:00.000
2007-05-01 00:00:00.000
117196
Y
10.00000
7.00000
2007-04-28 00:00:00.000
10.00000
Closed
0
1
Sample Data data4:
033845
033845 1000
2007-05-01 00:00:00.000
2007-05-01 00:00:00.000
117137
Y
160.00000
160.00000
2007-04-28 00:00:00.000
160.00000
Closed
0
0
The results should be 3 orders, all on time. The 2nd and 3rd records are for the same order but the shipment was split.
Thanks
Thanks
May 24, 2007 at 11:48 am
Can you not refer to invShp instead of shpQty?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 24, 2007 at 11:52 am
What if the order that was split had one on time and one late. Do you need a result set of 3 orders and 2 onTime? or do you need to expand the result set to include 3 orders, 2onTime and 2late?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 24, 2007 at 12:26 pm
If any portion is late, the whole order is considered late. As far as the invShip, it's not always populated, I'm trying to get a clarification on that myself. It may not even need to be in there.
Thanks
Thanks
May 24, 2007 at 1:37 pm
OK, This appears to get what you want. It may not be the most optimized solution though.
/* lets put all of the orders into one table variable to make the aggregation a bit easier to read */
DECLARE @shipInfo TABLE
(soNo char (6)
,soKey char (12)
,shipper char (6)
,dueDate datetime
,shipDate datetime
,confirmed char (1)
,orderDate datetime
,orderQty NUMERIC (9,2)
,shipQty NUMERIC(9,2))
-- Test data --
--INSERT @shipInfo
-- SELECT '777777','777777 1000',117111,'05/01/2007','05/01/2007','Y','05/01/2007',4,4 -- on time
--UNION
-- SELECT '111111','111111 1000',543210,'05/01/2007','05/02/2007','Y','05/01/2007',160,160-- late
--UNION
-- SELECT '222222','222222 1000',543210,'05/01/2007','05/01/2007','Y','05/01/2007',10,6 -- split and both on time
--UNION
-- SELECT '222222','222222 1000',543210,'05/01/2007','05/01/2007','Y','05/01/2007',10,4 -- split and both on time
--UNION
-- SELECT '666666','666666 1000',543210,'05/01/2007','05/01/2007','Y','05/01/2007',10,7 -- split and one late
--UNION
-- SELECT '666666','666666 1000',NULL ,'05/01/2007','05/09/2007','Y','05/01/2007',10,3 -- split and one late and NULL supplier
--UNION
-- SELECT '333333','333333 1000',NULL ,'05/01/2007','05/10/2007','Y','05/01/2007',10,10 -- late and NULL supplier
--UNION
-- SELECT '444444','444444 1000',NULL ,'05/01/2007','05/01/2007','Y','05/01/2007',10,10 -- on time and NULL supplier
--UNION
-- SELECT '555555','555555 1000',543210,'05/01/2007','05/01/2007','Y','05/01/2007',10,10 -- on time
INSERT @shipInfo
SELECT
soInfo.soNo
,soInfo.Sokey
,sMaster.shipper
,soInfo.dueDate
,sMaster.shpDate
,sMaster.confirmed
,soInfo.orderQty
,sItem.shipQty
FROM
M2MDATA97.dbo.soInfo soInfo
LEFT OUTER JOIN sItem sItem
ON soInfo.Sokey=sItem.fsokey
LEFT OUTER JOIN iMast iMast
ON soInfo.partNo=iMast.partNo
AND soInfo.fpartrev=iMast.rev
LEFT OUTER JOIN sMaster sMaster
ON sItem.shipper=sMaster.shipper
WHERE soInfo.dueDate = '2007-05-01'
/* now we can preform the aggregation */
SELECT
numberOfOrders = COUNT(DISTINCT s0.soNo)
,nullShip = SUM(CASE WHEN shipper IS NULL THEN 1 ELSE 0 END)
,on_time = COUNT(DISTINCT s0.soNo) - SUM(on_time)
FROM
@shipInfo s0
LEFT JOIN
/* this will get the on_time (which is actually a 1 if it's not on time */
(SELECT
soKey, dueDate, shipDate, confirmed, orderQty, soNo
,on_time = CASE WHEN dueDate >= shipDate
AND confirmed = 'Y'
/* we do a sub-query here to get the sum total of the order */
AND (SELECT SUM(shipQty) AS shipQty FROM @shipInfo s2 WHERE s2.soNo = s1.soNo)
/ orderQty >= .95
THEN 0 ELSE 1 END
FROM @shipInfo s1
GROUP BY soKey, dueDate, shipDate, confirmed, orderQty, soNo) s3
/* I joined on all columns because it doesn't appear that there is a unique/primary key*/
ON s3.soKey = s0.soKey
AND s3.dueDate = s0.dueDate
AND s3.shipDate = s0.shipDate
AND s3.confirmed = s0.confirmed
AND s3.orderQty = s0.orderQty
AND s3.soNo = s0.soNo
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 24, 2007 at 1:38 pm
An important note. Unless you have a unique/primary key that identifies each unique order line item, any orders with all info the same will cause erronious dups. so if you have a Key, then add it to the table variable and so forth....
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 24, 2007 at 2:36 pm
Thanks Jason for all your effort, I'll give this a try and let you know how it goes.
Thanks
Allen
Thanks
May 25, 2007 at 8:04 am
Jason,
This almost gets what I want. Where doing the most inner select, it shows on_time as 1 if that line item is late, which is good, but when summing up the on_time in outer most select, if one order has 5 line item late, it's subtracting all 5, it should only be subtracting 1. So if 27 sales orders, should show count of 26 on time, but its showing 22 because of the multiple lines items on that one sales order. Looks like need to group one more time maybe, I'm trying to figure out. Any other ideas?
Allen
Thanks
May 25, 2007 at 8:21 am
I beleive I have it now. I added another select on the outside
Select count(numberOfOrders),
COUNT(CASE WHEN (on_time + nullship) = 0 THEN 1 END) AS nullShip
from (
existing SQL
) tblCount
And this gives me the number of Sales orders and the numbers of sales orders on time.
Thanks for all your help.
Thanks
May 25, 2007 at 8:38 am
This seems to work for me. If still getting incorrect results, please post the data that's being calced wrong.
SELECT
numberOfOrders = COUNT(DISTINCT s0.soNo)
,nullShip = SUM(CASE WHEN shipper IS NULL THEN 1 ELSE 0 END)
,on_time = COUNT(DISTINCT s0.soNo) - COUNT(DISTINCT on_time)
FROM
@shipInfo s0
LEFT JOIN
/* this will get the on_time (which is actually a 1 if it's not on time */
(SELECT
soKey, dueDate, shipDate, confirmed, orderQty, soNo
,on_time = CASE WHEN dueDate >= shipDate
AND confirmed = 'Y'
/* we do a sub-query here to get the sum total of the order */
AND (SELECT SUM(shipQty) AS shipQty FROM @shipInfo s2 WHERE s2.soNo = s1.soNo)
/ orderQty >= .95
THEN NULL ELSE CAST(soNo AS INT) END
FROM @shipInfo s1
GROUP BY soKey, dueDate, shipDate, confirmed, orderQty, soNo) s3
/* I joined on all columns because it doesn't appear that there is a unique/primary key*/
ON s3.soKey = s0.soKey
AND s3.dueDate = s0.dueDate
AND s3.shipDate = s0.shipDate
AND s3.confirmed = s0.confirmed
AND s3.orderQty = s0.orderQty
AND s3.soNo = s0.soNo
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 25, 2007 at 2:54 pm
Jason, I ended up using the above with a few tweaks because of some data issues I did't expect. I will try incorporating your new changes in.
Thanks again for helping me solve this one.
Thanks
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply