February 21, 2019 at 9:10 am
Hi friends,
I would like to split the date into from and to date range based up on Quantity change and also need to ignore the record if Date is missing in between them. Any suggestions would be really appreciated.
DECLARE @T TABLE (RecordID int, dateID datetime2(0), Quantity int);
INSERT INTO @T (RecordID, dateID, Quantity) VALUES
(1, '2017-02-03' , 2),
(1, '2017-02-04' , 2),
(2, '2017-02-03' , 3),
(2, '2017-02-05' , 4),
(2, '2017-02-06' , 4),
(3, '2017-02-05' , 4),
(3, '2017-02-06' , 5),
(4, '2017-02-03' , 7),
(4, '2017-02-04' , 7),
(4, '2017-02-06' , 7)
SELECT * FROM @T
/* Expected Output */
SELECT 1 AS RecordID, '2017-02-03 00:00:00' AS FromDate, '2017-02-04 00:00:00' AS ToDate, 2 AS Quantity
UNION
SELECT 2 AS RecordID, '2017-02-03 00:00:00' AS FromDate, '2017-02-03 00:00:00' AS ToDate, 3 AS Quantity
UNION
SELECT 2 AS RecordID, '2017-02-05 00:00:00' AS FromDate, '2017-02-06 00:00:00' AS ToDate, 4 AS Quantity
UNION
SELECT 3 AS RecordID, '2017-02-05 00:00:00' AS FromDate, '2017-02-05 00:00:00' AS ToDate, 4 AS Quantity
UNION
SELECT 3 AS RecordID, '2017-02-06 00:00:00' AS FromDate, '2017-02-06 00:00:00' AS ToDate, 5 AS Quantity
UNION
SELECT 4 AS RecordID, '2017-02-03 00:00:00' AS FromDate, '2017-02-04 00:00:00' AS ToDate, 7 AS Quantity
UNION
SELECT 4 AS RecordID, '2017-02-05 00:00:00' AS FromDate, '2017-02-05 00:00:00' AS ToDate, 7 AS Quantity
Thanks,
Charmer
February 21, 2019 at 9:24 am
Use MIN and MAX dateID, grouped by RecordID and Quantity. That'll work for your sample data, but it'll give strange results if you ever go back to the same Quantity for the same RecordID.
John
February 21, 2019 at 9:50 am
Why do you have 2 results for record ID 4 the quantity didn't change?
February 21, 2019 at 9:55 am
ZZartin - Thursday, February 21, 2019 9:50 AMWhy do you have 2 results for record ID 4 the quantity didn't change?
It's the stock count. Client send us of what's the stock Count for a day. So its getting tracked everyday. Let's say that tracking what is the count of an item in a store on a daily basis.. I think I need to change the sample data. So that you will have a clear picture. My bad. Sorry.
Thanks,
Charmer
February 21, 2019 at 10:13 am
SELECT t.RecordId,
t.dateId FromDate,
x.dateID ToDate,
t.Quantity
FROM @T t
CROSS APPLY(SELECT TOP(1) *
FROM @T x
WHERE x.RecordID = t.RecordID
AND x.dateID >= t.dateID
AND NOT EXISTS(SELECT *
FROM @T z
WHERE z.RecordID = x.RecordID
AND z.dateID > t.dateID
AND z.dateID < x.DateId
AND z.Quantity <> t.Quantity)
ORDER BY x.dateID DESC) x
WHERE NOT EXISTS(SELECT *
FROM @T z
WHERE z.RecordID = t.RecordID
AND z.dateID < t.dateID
AND z.Quantity = t.Quantity
AND NOT EXISTS(SELECT *
FROM @T y
WHERE y.RecordID = z.RecordID
AND y.Quantity <> t.Quantity
AND y.dateID > z.dateID
AND y.dateId < t.dateID))
February 21, 2019 at 12:15 pm
This is a fairly standard gaps and islands problem.
WITH gaps_and_islands AS
(
SELECT *, DATEADD(DAY, -ROW_NUMBER() OVER(PARTITION BY RecordID, Quantity ORDER BY dateID), dateID) AS grp
FROM @T
)
SELECT RecordID, MIN(dateID) AS FromDate, MAX(dateID) AS ToDate, Quantity
FROM gaps_and_islands
GROUP BY RecordID, Quantity, grp
ORDER BY RecordID, MIN(dateID), Quantity
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 22, 2019 at 2:47 am
drew.allen - Thursday, February 21, 2019 12:15 PMThis is a fairly standard gaps and islands problem.
WITH gaps_and_islands AS
(
SELECT *, DATEADD(DAY, -ROW_NUMBER() OVER(PARTITION BY RecordID, Quantity ORDER BY dateID), dateID) AS grp
FROM @T
)
SELECT RecordID, MIN(dateID) AS FromDate, MAX(dateID) AS ToDate, Quantity
FROM gaps_and_islands
GROUP BY RecordID, Quantity, grp
ORDER BY RecordID, MIN(dateID), QuantityDrew
Wow, this is working. Great. Thank you , Drew.
Thanks,
Charmer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply