March 15, 2019 at 1:35 pm
Hi,
I am trying to get the output as shown. I am looking for latest modified date rows for each "Type" column value inside a "Pro" column. If the Qty1 is null in latest modified date then it should look for not Null value of Qty1 from previous last modified date rows and update the output table. If both Qty1 and Qty2 is null is latest modified rows then it should go to last (previous to latest modified date)modified rows and update the output. if the rows are updated from Previous modified dates then it should update the old column to "old" else blank. Thanks for helping.
Input data looks like below-
DROP TABLE IF EXISTS #mytable;
CREATE TABLE #mytable(
Id INTEGER
,Pro VARCHAR(1)
,Type VARCHAR(5)
,Qty1 NUMERIC(4,0)
,Qty2 NUMERIC(4,0)
,ModifiedDate DATETIME NOT NULL
);
INSERT INTO #mytable(Id,Pro,Type,Qty1,Qty2,ModifiedDate) VALUES (1,'A','Type1',6000,6000,'12-6-2018 12:00 AM');
INSERT INTO #mytable(Id,Pro,Type,Qty1,Qty2,ModifiedDate) VALUES (1,'A','Type2',3000,3000,'12-7-2018 12:00 AM');
INSERT INTO #mytable(Id,Pro,Type,Qty1,Qty2,ModifiedDate) VALUES (1,'A','Type3',88,86,'12-8-2018 12:00 AM');
INSERT INTO #mytable(Id,Pro,Type,Qty1,Qty2,ModifiedDate) VALUES (1,'A','Type1',4000,2000,'3-3-2019 4:44 PM');
INSERT INTO #mytable(Id,Pro,Type,Qty1,Qty2,ModifiedDate) VALUES (1,'A','Type2',2000,1000,'3-4-2019 4:44 PM');
INSERT INTO #mytable(Id,Pro,Type,Qty1,Qty2,ModifiedDate) VALUES (1,'A','Type3',0,0,'3-5-2019 4:44 PM');
INSERT INTO #mytable(Id,Pro,Type,Qty1,Qty2,ModifiedDate) VALUES (2,'B','Type1',22,0,'12-4-2018 12:00 AM');
INSERT INTO #mytable(Id,Pro,Type,Qty1,Qty2,ModifiedDate) VALUES (2,'B','Type1',NULL,NULL,'3-8-2019 4:00 AM');
INSERT INTO #mytable(Id,Pro,Type,Qty1,Qty2,ModifiedDate) VALUES (2,'B','Type1',55,NULL,'3-9-2019 12:00 AM');
INSERT INTO #mytable(Id,Pro,Type,Qty1,Qty2,ModifiedDate) VALUES (3,'C','Type1',3000,2000,'3-6-2019 10:00 PM');
INSERT INTO #mytable(Id,Pro,Type,Qty1,Qty2,ModifiedDate) VALUES (3,'C','Type2',100,65,'3-6-2019 12:00 PM');
INSERT INTO #mytable(Id,Pro,Type,Qty1,Qty2,ModifiedDate) VALUES (3,'C','Type1',NULL,NULL,'3-7-2019 12:00 AM');
INSERT INTO #mytable(Id,Pro,Type,Qty1,Qty2,ModifiedDate) VALUES (3,'C','Type2',NULL,NULL,'3-7-2019 12:00 AM');
Output should be-
DROP TABLE IF EXISTS #mytable2;
CREATE TABLE #mytable2(
Id INTEGER
,Pro VARCHAR(1)
,Type VARCHAR(5)
,Qty1 NUMERIC(4,0)
,Qty2 NUMERIC(4,0)
,ModifiedDate DATETIME
,Old VARCHAR(3)
);
INSERT INTO #mytable2(Id,Pro,Type,Qty1,Qty2,ModifiedDate,Old) VALUES (1,'A','Type1',4000,2000,'3-3-2019 4:44 PM',NULL);
INSERT INTO #mytable2(Id,Pro,Type,Qty1,Qty2,ModifiedDate,Old) VALUES (1,'A','Type2',2000,1000,'3-4-2019 4:44 PM',NULL);
INSERT INTO #mytable2(Id,Pro,Type,Qty1,Qty2,ModifiedDate,Old) VALUES (1,'A','Type3',0,0,'3-5-2019 4:44 PM',NULL);
INSERT INTO #mytable2(Id,Pro,Type,Qty1,Qty2,ModifiedDate,Old) VALUES (2,'B','Type1',55,0,'3-9-2019 12:00 AM','old');
INSERT INTO #mytable2(Id,Pro,Type,Qty1,Qty2,ModifiedDate,Old) VALUES (3,'C','Type1',3000,2000,'3-7-2019 12:00 AM','old');
INSERT INTO #mytable2(Id,Pro,Type,Qty1,Qty2,ModifiedDate,Old) VALUES (3,'C','Type2',100,65,'3-7-2019 12:00 AM','old');
March 20, 2019 at 6:49 am
Try this:DROP TABLE IF EXISTS #mytable;
CREATE TABLE #mytable(
Id INTEGER
,Pro VARCHAR(1)
,[Type] VARCHAR(5)
,Qty1 NUMERIC(4,0)
,Qty2 NUMERIC(4,0)
,ModifiedDate DATETIME NOT NULL
);
INSERT INTO #mytable (Id, Pro, [Type], Qty1, Qty2, ModifiedDate)
VALUES (1,'A','Type1',6000,6000,'12-06-2018 12:00 AM'),
(1,'A','Type2',3000,3000,'12-07-2018 12:00 AM'),
(1,'A','Type3',88, 86, '12-08-2018 12:00 AM'),
(1,'A','Type1',4000,2000,'03-03-2019 4:44 PM'),
(1,'A','Type2',2000,1000,'03-04-2019 4:44 PM'),
(1,'A','Type3',0, 0, '03-05-2019 4:44 PM'),
(2,'B','Type1',22, 0, '12-04-2018 12:00 AM'),
(2,'B','Type1',NULL,NULL,'03-08-2019 4:00 AM'),
(2,'B','Type1',55, NULL,'03-09-2019 12:00 AM'),
(3,'C','Type1',3000,2000,'03-06-2019 10:00 PM'),
(3,'C','Type2',100, 65, '03-06-2019 12:00 PM'),
(3,'C','Type1',NULL,NULL,'03-07-2019 12:00 AM'),
(3,'C','Type2',NULL,NULL,'03-07-2019 12:00 AM');
WITH MAX_DATES AS (
SELECT Id, Pro, [Type], MAX(ModifiedDate) AS MaxDate
FROM #mytable
GROUP BY Id, Pro, [Type]
)
SELECT MT.*
FROM #mytable AS MT
INNER JOIN MAX_DATES AS MD
ON MT.Id = MD.Id
AND MT.Pro = MD.Pro
AND MT.[Type] = MD.[Type]
AND MT.ModifiedDate = MD.MaxDate
ORDER BY
MT.Id,
MT.Pro,
MT.[Type];
DROP TABLE #mytable;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 20, 2019 at 2:27 pm
Here is my take on the solution. Let me know if you see any issues.
WITH SortedData
AS (
SELECT Id, Pro, Type, Qty1, Qty2, ModifiedDate,
ROW_NUMBER() OVER (PARTITION BY Pro, Type ORDER BY ModifiedDate) AS SortSeq,
CAST('' AS VARCHAR(5)) AS Old
FROM #mytable
),
MaxData
AS (
SELECT Pro, Type, MAX(SortSeq) AS MaxSortSeq
FROM SortedData
GROUP BY Pro, Type
),
RecursiveData
AS (
SELECT s.Id, s.Pro, s.Type, s.Qty1, s.Qty2, s.ModifiedDate, s.SortSeq, s.Old
FROM SortedData s
WHERE s.SortSeq = 1
UNION ALL
SELECT s.Id, s.Pro, s.Type,
CASE WHEN s.Qty1 IS NOT NULL THEN s.Qty1 ELSE r.Qty1 END AS Qty1,
CASE WHEN s.Qty2 IS NOT NULL THEN s.Qty2 ELSE r.Qty2 END AS Qty2,
s.ModifiedDate, s.SortSeq, --s.Old
CAST(CASE WHEN s.Qty1 IS NULL OR s.Qty2 IS NULL THEN 'Old' ELSE '' END AS VARCHAR(5)) AS Old
FROM RecursiveData r
JOIN SortedData s
ON s.Pro = r.Pro
AND s.Type = r.Type
AND s.SortSeq = r.SortSeq + 1
)
SELECT r.Id, r.Pro, r.Type, r.Qty1, r.Qty2, r.ModifiedDate, r.Old--, r.SortSeq
FROM RecursiveData r
JOIN MaxData m
ON m.Pro = r.Pro
AND m.Type = r.Type
AND m.MaxSortSeq = r.SortSeq
ORDER BY r.Pro, r.Type, ModifiedDate
March 21, 2019 at 10:37 am
fahey.jonathan - Wednesday, March 20, 2019 2:27 PMHere is my take on the solution. Let me know if you see any issues.
WITH SortedData
AS (
SELECT Id, Pro, Type, Qty1, Qty2, ModifiedDate,
ROW_NUMBER() OVER (PARTITION BY Pro, Type ORDER BY ModifiedDate) AS SortSeq,
CAST('' AS VARCHAR(5)) AS Old
FROM #mytable
),
MaxData
AS (
SELECT Pro, Type, MAX(SortSeq) AS MaxSortSeq
FROM SortedData
GROUP BY Pro, Type
),
RecursiveData
AS (
SELECT s.Id, s.Pro, s.Type, s.Qty1, s.Qty2, s.ModifiedDate, s.SortSeq, s.Old
FROM SortedData s
WHERE s.SortSeq = 1UNION ALL
SELECT s.Id, s.Pro, s.Type,
CASE WHEN s.Qty1 IS NOT NULL THEN s.Qty1 ELSE r.Qty1 END AS Qty1,
CASE WHEN s.Qty2 IS NOT NULL THEN s.Qty2 ELSE r.Qty2 END AS Qty2,
s.ModifiedDate, s.SortSeq, --s.Old
CAST(CASE WHEN s.Qty1 IS NULL OR s.Qty2 IS NULL THEN 'Old' ELSE '' END AS VARCHAR(5)) AS Old
FROM RecursiveData r
JOIN SortedData s
ON s.Pro = r.Pro
AND s.Type = r.Type
AND s.SortSeq = r.SortSeq + 1
)
SELECT r.Id, r.Pro, r.Type, r.Qty1, r.Qty2, r.ModifiedDate, r.Old--, r.SortSeq
FROM RecursiveData r
JOIN MaxData m
ON m.Pro = r.Pro
AND m.Type = r.Type
AND m.MaxSortSeq = r.SortSeq
ORDER BY r.Pro, r.Type, ModifiedDate
I see a potential performance issue with the recursive CTE, which is not necessary.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 21, 2019 at 10:11 pm
sgmunson - Thursday, March 21, 2019 10:37 AMI see a potential performance issue with the recursive CTE, which is not necessary.
Steve, I looked at your solution, but it does not seem to get the same answer as the desired result. Here is the desired result, from the OP's output: Id Pro Type Qty1 Qty2 ModifiedDate Old
----------- ---- ----- --------------------------------------- --------------------------------------- ----------------------- ----
1 A Type1 4000 2000 2019-03-03 16:44:00.000 NULL
1 A Type2 2000 1000 2019-03-04 16:44:00.000 NULL
1 A Type3 0 0 2019-03-05 16:44:00.000 NULL
2 B Type1 55 0 2019-03-09 00:00:00.000 old
3 C Type1 3000 2000 2019-03-07 00:00:00.000 old
3 C Type2 100 65 2019-03-07 00:00:00.000 old
Here is the result of your query: Id Pro Type Qty1 Qty2 ModifiedDate
----------- ---- ----- --------------------------------------- --------------------------------------- -----------------------
1 A Type1 4000 2000 2019-03-03 16:44:00.000
1 A Type2 2000 1000 2019-03-04 16:44:00.000
1 A Type3 0 0 2019-03-05 16:44:00.000
2 B Type1 55 NULL 2019-03-09 00:00:00.000
3 C Type1 NULL NULL 2019-03-07 00:00:00.000
3 C Type2 NULL NULL 2019-03-07 00:00:00.000
Please note that your version does not have "3000" for Qty1 in the fifth row. Also note that your version does not have "2000" for Qty2 in the fifth row. In fact, your version does not match 3 out of the six rows for columns Qty1 and Qty2. Also note that your version does not have the column labeled "Old", which was part of the request. The OP was kind enough to provide sample data with the expected output, but your solution does not match the output.I learn a lot from this forum, and I'm happy to learn from you. Please provide a solution that does not use a CTE and that satisfies the requirements of the solution. I considered using LEAD/LAG, but I can't figure out how to know how far back the query needs to go to get the prior value if the current value is null; note B/Type1, where the most current row has to go back two rows to find a non-null value. Using a CTE was the only way I could figure out how to ensure a value. Is there a better way?
March 28, 2019 at 11:14 am
fahey.jonathan - Thursday, March 21, 2019 10:11 PMsgmunson - Thursday, March 21, 2019 10:37 AMI see a potential performance issue with the recursive CTE, which is not necessary.Steve, I looked at your solution, but it does not seem to get the same answer as the desired result. Here is the desired result, from the OP's output:
Id Pro Type Qty1 Qty2 ModifiedDate Old
----------- ---- ----- --------------------------------------- --------------------------------------- ----------------------- ----
1 A Type1 4000 2000 2019-03-03 16:44:00.000 NULL
1 A Type2 2000 1000 2019-03-04 16:44:00.000 NULL
1 A Type3 0 0 2019-03-05 16:44:00.000 NULL
2 B Type1 55 0 2019-03-09 00:00:00.000 old
3 C Type1 3000 2000 2019-03-07 00:00:00.000 old
3 C Type2 100 65 2019-03-07 00:00:00.000 old
Here is the result of your query:Id Pro Type Qty1 Qty2 ModifiedDate
----------- ---- ----- --------------------------------------- --------------------------------------- -----------------------
1 A Type1 4000 2000 2019-03-03 16:44:00.000
1 A Type2 2000 1000 2019-03-04 16:44:00.000
1 A Type3 0 0 2019-03-05 16:44:00.000
2 B Type1 55 NULL 2019-03-09 00:00:00.000
3 C Type1 NULL NULL 2019-03-07 00:00:00.000
3 C Type2 NULL NULL 2019-03-07 00:00:00.000
Please note that your version does not have "3000" for Qty1 in the fifth row. Also note that your version does not have "2000" for Qty2 in the fifth row. In fact, your version does not match 3 out of the six rows for columns Qty1 and Qty2. Also note that your version does not have the column labeled "Old", which was part of the request. The OP was kind enough to provide sample data with the expected output, but your solution does not match the output.I learn a lot from this forum, and I'm happy to learn from you. Please provide a solution that does not use a CTE and that satisfies the requirements of the solution. I considered using LEAD/LAG, but I can't figure out how to know how far back the query needs to go to get the prior value if the current value is null; note B/Type1, where the most current row has to go back two rows to find a non-null value. Using a CTE was the only way I could figure out how to ensure a value. Is there a better way?
I see the problem now. The solution is simple:DROP TABLE IF EXISTS #mytable;
CREATE TABLE #mytable(
Id INTEGER
,Pro VARCHAR(1)
,[Type] VARCHAR(5)
,Qty1 NUMERIC(4,0)
,Qty2 NUMERIC(4,0)
,ModifiedDate DATETIME NOT NULL
);
INSERT INTO #mytable (Id, Pro, [Type], Qty1, Qty2, ModifiedDate)
VALUES (1,'A','Type1',6000,6000,'12-06-2018 12:00 AM'),
(1,'A','Type2',3000,3000,'12-07-2018 12:00 AM'),
(1,'A','Type3',88, 86, '12-08-2018 12:00 AM'),
(1,'A','Type1',4000,2000,'03-03-2019 4:44 PM'),
(1,'A','Type2',2000,1000,'03-04-2019 4:44 PM'),
(1,'A','Type3',0, 0, '03-05-2019 4:44 PM'),
(2,'B','Type1',22, 0, '12-04-2018 12:00 AM'),
(2,'B','Type1',NULL,NULL,'03-08-2019 4:00 AM'),
(2,'B','Type1',55, NULL,'03-09-2019 12:00 AM'),
(3,'C','Type1',3000,2000,'03-06-2019 10:00 PM'),
(3,'C','Type2',100, 65, '03-06-2019 12:00 PM'),
(3,'C','Type1',NULL,NULL,'03-07-2019 12:00 AM'),
(3,'C','Type2',NULL,NULL,'03-07-2019 12:00 AM');
WITH MAX_DATES AS (
SELECT Id, Pro, [Type], MAX(ModifiedDate) AS MaxDate
FROM #mytable
WHERE NOT (
Qty1 IS NULL
AND
Qty2 IS NULL
)
GROUP BY Id, Pro, [Type]
)
SELECT MT.*
FROM #mytable AS MT
INNER JOIN MAX_DATES AS MD
ON MT.Id = MD.Id
AND MT.Pro = MD.Pro
AND MT.[Type] = MD.[Type]
AND MT.ModifiedDate = MD.MaxDate
ORDER BY
MT.Id,
MT.Pro,
MT.[Type];
DROP TABLE #mytable;
If you want to insist on no CTE, then do it this way:DROP TABLE IF EXISTS #mytable;
CREATE TABLE #mytable(
Id INTEGER
,Pro VARCHAR(1)
,[Type] VARCHAR(5)
,Qty1 NUMERIC(4,0)
,Qty2 NUMERIC(4,0)
,ModifiedDate DATETIME NOT NULL
);
INSERT INTO #mytable (Id, Pro, [Type], Qty1, Qty2, ModifiedDate)
VALUES (1,'A','Type1',6000,6000,'12-06-2018 12:00 AM'),
(1,'A','Type2',3000,3000,'12-07-2018 12:00 AM'),
(1,'A','Type3',88, 86, '12-08-2018 12:00 AM'),
(1,'A','Type1',4000,2000,'03-03-2019 4:44 PM'),
(1,'A','Type2',2000,1000,'03-04-2019 4:44 PM'),
(1,'A','Type3',0, 0, '03-05-2019 4:44 PM'),
(2,'B','Type1',22, 0, '12-04-2018 12:00 AM'),
(2,'B','Type1',NULL,NULL,'03-08-2019 4:00 AM'),
(2,'B','Type1',55, NULL,'03-09-2019 12:00 AM'),
(3,'C','Type1',3000,2000,'03-06-2019 10:00 PM'),
(3,'C','Type2',100, 65, '03-06-2019 12:00 PM'),
(3,'C','Type1',NULL,NULL,'03-07-2019 12:00 AM'),
(3,'C','Type2',NULL,NULL,'03-07-2019 12:00 AM');
SELECT MT.*
FROM #mytable AS MT
INNER JOIN (
SELECT Id, Pro, [Type], MAX(ModifiedDate) AS MaxDate
FROM #mytable
WHERE NOT (
Qty1 IS NULL
AND
Qty2 IS NULL
)
GROUP BY Id, Pro, [Type]
) AS MD
ON MT.Id = MD.Id
AND MT.Pro = MD.Pro
AND MT.[Type] = MD.[Type]
AND MT.ModifiedDate = MD.MaxDate
ORDER BY
MT.Id,
MT.Pro,
MT.[Type];
DROP TABLE #mytable;
I'm doubtful that the presence of the CTE in this case would matter much.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 28, 2019 at 1:20 pm
Another variation. For a huge file, I hope there is a covering index on the Pro, Type, and Modified Date columns.
select id, Pro, [Type], Qty1,Qty2, ModifiedDate, Old
from (select id, pro, [type]
, coalesce(qty1,lead(qty1,1) over(partition by pro, [type] order by modifieddate desc),0) as qty1
, coalesce(qty2,lead(qty2,1) over(partition by pro, [type] order by modifieddate desc),0) as qty2
, ModifiedDate, row_number() over(partition by pro, [type] order by modifieddate desc) as rowid
, case when qty1 is null or qty2 is null then 'old' end as Old
from #mytable
) cte1
where rowid = 1
order by pro, type
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 28, 2019 at 1:25 pm
If you aren't afraid of CTEs, use this syntax. 😉
;with cte1 as (select id, pro, [type]
, coalesce(qty1,lead(qty1,1) over(partition by pro, [type] order by modifieddate desc),0) as qty1
, coalesce(qty2,lead(qty2,1) over(partition by pro, [type] order by modifieddate desc),0) as qty2
, ModifiedDate, row_number() over(partition by pro, [type] order by modifieddate desc) as rowid
, case when qty1 is null or qty2 is null then 'old' end as Old
from #mytable
)
select id, Pro, [Type], Qty1,Qty2, ModifiedDate, Old
from cte1
where rowid = 1
order by pro, type
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 28, 2019 at 11:45 pm
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply