December 19, 2018 at 8:48 am
Hello all,
I just found this forum and I hope you all can help me. I need to create a SQL query that is a bit tricky. It's a little hard to explain qualitatively so I will just show what I want to accomplish with the data.
I have the following table called F0911:
GMLDA | D3650Dept | D365OObject
-------------------------------------------
3 | 00 | 01000
4 | 00 | 01010
5 | 00 | 01020
6 | 00 | 01100
7 | 00 | 01100
5 | 00 | 01200
6 | 00 | 01210
7 | 00 | 01210
6 | 00 | 01250
7 | 00 | 01250
5 | 00 | 01290
6 | 00 | 01299
5 | 00 | 01300
6 | 00 | 01310
7 | 00 | 01310
6 | 00 | 01320
7 | 00 | 01320
6 | 00 | 01330
7 | 00 | 01330
6 | 00 | 01340
The SQL code to get this table is as follows:select Top(20) gmlda, d365ODept, D365OObject
FROM [JDE].[F0901]
WHERE ISNUMERIC(D365OObject) = 1
group by gmlda, d365ODept, D365OObject
order by d365ODept, D365OObject
Now, for what I am trying to accomplish. I need to create a new column to be created and will go through each row in the table and retrieve the first D365ODept value on a row that is beneath the current row and has a GMLDA that is less than or equal to the current row. So, to show this visually the new table would be:
gmlda | d365ODept | D365OObject | New Column
--------------------------------------------------
3 | 00 | 01000 | NULL
4 | 00 | 01010 | NULL
5 | 00 | 01020 | 01200
6 | 00 | 01100 | 01200
7 | 00 | 01100 | 01200
5 | 00 | 01200 | 01290
6 | 00 | 01210 | 01250
7 | 00 | 01210 | 01250
6 | 00 | 01250 | 01290
7 | 00 | 01250 | 01290
5 | 00 | 01290 | 01300
6 | 00 | 01299 | 01300
5 | 00 | 01300 | NULL
6 | 00 | 01310 | 01320
7 | 00 | 01310 | 01320
6 | 00 | 01320 | 01330
7 | 00 | 01320 | 01330
6 | 00 | 01330 | 01340
7 | 00 | 01330 | 01340
6 | 00 | 01340 | NULL
The tables did not come out as nice as I formatted them. If there is an alternate way to format them, let me know and I will edit the post. I have also included an attachment of the data above in an excel spreadsheet so that it is easier to read.
Is something like this possible? Any help would be appreciated, thank you.
December 19, 2018 at 11:11 am
VegaFL - Wednesday, December 19, 2018 8:48 AMHello all,I just found this forum and I hope you all can help me. I need to create a SQL query that is a bit tricky. It's a little hard to explain qualitatively so I will just show what I want to accomplish with the data.
I have the following table called F0911:
GMLDA | D3650Dept | D365OObject
-------------------------------------------
3 | 00 | 01000
4 | 00 | 01010
5 | 00 | 01020
6 | 00 | 01100
7 | 00 | 01100
5 | 00 | 01200
6 | 00 | 01210
7 | 00 | 01210
6 | 00 | 01250
7 | 00 | 01250
5 | 00 | 01290
6 | 00 | 01299
5 | 00 | 01300
6 | 00 | 01310
7 | 00 | 01310
6 | 00 | 01320
7 | 00 | 01320
6 | 00 | 01330
7 | 00 | 01330
6 | 00 | 01340The SQL code to get this table is as follows:
select Top(20) gmlda, d365ODept, D365OObject
FROM [JDE].[F0901]
WHERE ISNUMERIC(D365OObject) = 1
group by gmlda, d365ODept, D365OObject
order by d365ODept, D365OObjectNow, for what I am trying to accomplish. I need to create a new column to be created and will go through each row in the table and retrieve the first D365ODept value on a row that is beneath the current row and has a GMLDA that is less than or equal to the current row. So, to show this visually the new table would be:
gmlda | d365ODept | D365OObject | New Column
--------------------------------------------------
3 | 00 | 01000 | NULL
4 | 00 | 01010 | NULL
5 | 00 | 01020 | 01200
6 | 00 | 01100 | 01200
7 | 00 | 01100 | 01200
5 | 00 | 01200 | 01290
6 | 00 | 01210 | 01250
7 | 00 | 01210 | 01250
6 | 00 | 01250 | 01290
7 | 00 | 01250 | 01290
5 | 00 | 01290 | 01300
6 | 00 | 01299 | 01300
5 | 00 | 01300 | NULL
6 | 00 | 01310 | 01320
7 | 00 | 01310 | 01320
6 | 00 | 01320 | 01330
7 | 00 | 01320 | 01330
6 | 00 | 01330 | 01340
7 | 00 | 01330 | 01340
6 | 00 | 01340 | NULLThe tables did not come out as nice as I formatted them. If there is an alternate way to format them, let me know and I will edit the post. I have also included an attachment of the data above in an excel spreadsheet so that it is easier to read.
Is something like this possible? Any help would be appreciated, thank you.
One problem. While your sample data happens to be in the order of D365OObject then gmlda, this may or may not always be true. So unless you can guarantee a specific order, you may not always see those results in that order. The following query assigns that order and uses it to solve the problem. Test it and see if it consistently produces the correct result.WITH ORDERED_DATA AS (
SELECT TOP(20)
F.gmlda,
F.d365ODept,
F.D365OObject,
ROW_NUMBER() OVER(ORDER BY F.D3650Object, F.gmlda) AS RowNum
FROM [JDE].[F0901] AS F
WHERE ISNUMERIC(F.D365OObject) = 1
GROUP BY
F.gmlda,
F.d365ODept,
F.D365OObject
ORDER BY
F.d365ODept,
F.D365OObject
)
SELECT
OD.gmlda,
OD.d365ODept,
OD.D365OObject,
D.d365ODept AS New_Column
FROM ORDERED_DATA AS OD
OUTER APPLY (
SELECT TOP (1) D2.d365ODept
FROM ORDERED_DATA AS D2
WHERE D2.RowNum > OD.RowNum
AND D2.gmlda < OD.gmlda
ORDER BY D2.RowNum
) AS D
ORDER BY
OD.RowNum;
EDIT: added the order by to the OUTER APPLY query. My Bad!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 20, 2018 at 7:28 am
Thank you so much sgmunson. This query is exactly what we needed. I greatly appreciate the help. Thank you, sir!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply