November 5, 2018 at 6:25 pm
Hello Experts,
Needed some help with a query, the requirement is to identify an Item that is getting ordered but not getting replenished for more than 60 days. In the below example, I need to pull out only "Item 3". The other items have gotten replenished within past 60 days or they have not crossed the 60 days mark without replenishment .
This help will be greatly appreciated. Below is the test table and data.
Note: Successful Replenishment is any order that has received quantity > 0
CREATE TABLE POTest (
POnumber varchar(12),
PODate datetime,
ItemDesc varchar(15),
Submittedqty int,
Receivedqty int,
ReceivedDate datetime,
);
INSERT INTO POTest ( POnumber, PODate, ItemDesc,Submittedqty,Receivedqty,ReceivedDate )
VALUES ('P101', '10/15/2018','Item1',100,100,'10/20/2018'),
('P102', '10/15/2018','Item2',200,0,'10/21/2018'),
('P103', '07/05/2018','Item3',300,0,'07/12/2018'),
('P104', '09/05/2018','Item3',300,0,'09/13/2018'),
('P105', '10/05/2018','Item3',300,0,'10/09/2018'),
('P106', '10/16/2018','Item4',250,250,'10/21/2018'),
('P107', '10/20/2018','Item5',350,0,'10/25/2018'),
('P108', '07/20/2018','Item6',325,0,'07/25/2018'),
('P109', '08/21/2018','Item6',330,0,'08/25/2018'),
('P110', '10/20/2018','Item6',350,350,'10/25/2018');
Result:
Item 3
November 6, 2018 at 2:58 am
misstryguy - Monday, November 5, 2018 6:25 PMHello Experts,
Needed some help with a query, the requirement is to identify an Item that is getting ordered but not getting replenished for more than 60 days. In the below example, I need to pull out only "Item 3". The other items have gotten replenished within past 60 days or they have not crossed the 60 days mark without replenishment .This help will be greatly appreciated. Below is the test table and data.
Note: Successful Replenishment is any order that has received quantity > 0
CREATE TABLE POTest (
POnumber varchar(12),
PODate datetime,
ItemDesc varchar(15),
Submittedqty int,
Receivedqty int,
ReceivedDate datetime,
);INSERT INTO POTest ( POnumber, PODate, ItemDesc,Submittedqty,Receivedqty,ReceivedDate )
VALUES ('P101', '10/15/2018','Item1',100,100,'10/20/2018'),
('P102', '10/15/2018','Item2',200,0,'10/21/2018'),
('P103', '07/05/2018','Item3',300,0,'07/12/2018'),
('P104', '09/05/2018','Item3',300,0,'09/13/2018'),
('P105', '10/05/2018','Item3',300,0,'10/09/2018'),
('P106', '10/16/2018','Item4',250,250,'10/21/2018'),
('P107', '10/20/2018','Item5',350,0,'10/25/2018'),
('P108', '07/20/2018','Item6',325,0,'07/25/2018'),
('P109', '08/21/2018','Item6',330,0,'08/25/2018'),
('P110', '10/20/2018','Item6',350,350,'10/25/2018');Result:
Item 3
Simple aggregation should do
😎
USE TEEST;
GO
SET NOCOUNT ON;
--
DECLARE @POTEST TABLE
(
POnumber varchar(12),
PODate datetime,
ItemDesc varchar(15),
Submittedqty int,
Receivedqty int,
ReceivedDate datetime
);
INSERT INTO @POTEST ( POnumber, PODate, ItemDesc,Submittedqty,Receivedqty,ReceivedDate )
VALUES ('P101', '10/15/2018','Item1',100,100,'10/20/2018'),
('P102', '10/15/2018','Item2',200,0,'10/21/2018'),
('P103', '07/05/2018','Item3',300,0,'07/12/2018'),
('P104', '09/05/2018','Item3',300,0,'09/13/2018'),
('P105', '10/05/2018','Item3',300,0,'10/09/2018'),
('P106', '10/16/2018','Item4',250,250,'10/21/2018'),
('P107', '10/20/2018','Item5',350,0,'10/25/2018'),
('P108', '07/20/2018','Item6',325,0,'07/25/2018'),
('P109', '08/21/2018','Item6',330,0,'08/25/2018'),
('P110', '10/20/2018','Item6',350,350,'10/25/2018');
SELECT
PT.ItemDesc
,DATEDIFF(DAY,MIN(PT.PODate),MAX(PT.ReceivedDate)) AS AGE_IN_DAYS
,SUM(PT.Receivedqty) AS QTYR
FROM @POTEST PT
GROUP BY PT.ItemDesc
HAVING DATEDIFF(DAY,MIN(PT.PODate),MAX(PT.ReceivedDate)) > 59
AND SUM(PT.Receivedqty) = 0;
November 6, 2018 at 10:46 am
Thank you very much for the response. There is one scenario I forgot to add, assumed the 60 days will address that. Item3 should be accounted even if it was replenished 60 days prior. The above query returns regardless of 60 days rule. Where can we tweak the query. Again, thanks for all the help.
Example added with new record.
USE TEEST;
GO
SET NOCOUNT ON;
--
DECLARE @POTEST TABLE
(
POnumber varchar(12),
PODate datetime,
ItemDesc varchar(15),
Submittedqty int,
Receivedqty int,
ReceivedDate datetime
);
INSERT INTO @POTEST ( POnumber, PODate, ItemDesc,Submittedqty,Receivedqty,ReceivedDate )
VALUES ('P101', '10/15/2018','Item1',100,100,'10/20/2018'),
('P102', '10/15/2018','Item2',200,0,'10/21/2018'),
('P100', '06/05/2018','Item3',300,300,'06/12/2018'),
('P103', '07/05/2018','Item3',300,0,'07/12/2018'),
('P104', '09/05/2018','Item3',300,0,'09/13/2018'),
('P105', '10/05/2018','Item3',300,0,'10/09/2018'),
('P106', '10/16/2018','Item4',250,250,'10/21/2018'),
('P107', '10/20/2018','Item5',350,0,'10/25/2018'),
('P108', '07/20/2018','Item6',325,0,'07/25/2018'),
('P109', '08/21/2018','Item6',330,0,'08/25/2018'),
('P110', '10/20/2018','Item6',350,350,'10/25/2018');
SELECT
PT.ItemDesc
,DATEDIFF(DAY,MIN(PT.PODate),MAX(PT.ReceivedDate)) AS AGE_IN_DAYS
,SUM(PT.Receivedqty) AS QTYR
FROM @POTEST PT
GROUP BY PT.ItemDesc
HAVING DATEDIFF(DAY,MIN(PT.PODate),MAX(PT.ReceivedDate)) > 59
AND SUM(PT.Receivedqty) = 0;
November 6, 2018 at 12:27 pm
Also, this statement might help.
"If 60 days has passed since that 1st order with received qty=0 and all orders submitted within 60 days of the 1st order have a received qty=0"
November 6, 2018 at 5:33 pm
When you say "all orders submitted within 60 days" - do you mean all orders in the database?
Are those orders bound to each other somehow?
And - it it always 1 item per order?
_____________
Code for TallyGenerator
November 8, 2018 at 6:48 pm
Each order are different, they are not related. For this sample data purpose it is ok to assume 1 item for each order.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply