Loop query help
-
Hi Guys,
Please see below the test data and syntax. What I want to populate a one field based on case logic. I want to populate with 0 if CASE WHEN dis <> LAG(dis) over(ORDER BY dis) OR dis = '' or dis = '00000' or ompt > 16 or epd > 22 then '0' ELSE grab the previous value +1 Until it got 0 zero again.
Here is the end result that I am looking. Any advice would be highly appreciated.
CREATE TABLE g_test(
id int IDENTITY(1,1),
dis varchar(10),
ompt int,
epd int,
Gra int
)
INSERT INTO g_test (dis,ompt,epd)
SELECT '91758',18,26
UNION ALL
SELECT '91720',18,23
UNION ALL
SELECT '98755',16,22
UNION ALL
SELECT '89655',10,16
UNION ALL
SELECT '89777',8,12
UNION ALL
SELECT '52365',18,30
SELECT
ID,
dis,
ompt,
epd,
CASE WHEN dis <> LAG(dis) over(ORDER BY dis) OR dis = '' or dis = '00000' or ompt > 16 or epd > 22 then '0'
else DENSE_RANK () over(partition by id ORDER BY id) end as grc
FROM g_test
-
Afaik the set up is not clear. This seems to produce the correct result
with
gaps_cte as (
select *, case when crit<>lag(crit) over (order by id)
then 1
else 0 end as gap
from #g_test g
cross apply (values (case when ompt > 16 or epd > 22
then 0
else 1 end)) v(crit)),
grp_cte as (
select *, sum(gap) over (order by id) grp
from gaps_cte)
select ID, dis, ompt, epd, crit, gap, grp,
case when crit=0
then 0
else row_number() over (partition by grp order by id) end as grc
from grp_cte
order by ID;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Thanks for your reply.
My question was, I want to Loop through the whole table, If we met the criteria listed above then 0 else Previous "GRA" value +1 until we meet the criteria gain to 0 to break the sequence.
Here I tried the While loop. The only problem is "gra" value is not in the correct order. GRA value should always start with 1 for the new data set sequence.
DECLARE @loopA_id INT ,
@loopA_times INT ,
@id_ExactMatch_SF INT;
SET @loopA_id = 1;
SET @loopA_times = 0;
-- Create Loop Tables
IF OBJECT_ID('tempdb..#ExactMatch_SF') IS NOT NULL
BEGIN
DROP TABLE #ExactMatch_SF;
END;
CREATE TABLE #ExactMatch_SF
(
recalc_codA_id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY ,
id INT,
);
-- Load Loop Table
INSERT INTO #ExactMatch_SF
(
id
)
SELECT
id
FROM g_test
-- Set Loop Times Variable
SET @loopA_times = @@rowcount;
WHILE @loopA_id <= @loopA_times
BEGIN
SELECT @id_ExactMatch_SF = id
FROM #ExactMatch_SF
WHERE recalc_codA_id = @loopA_id;
SELECT gra = CASE WHEN dis = '' OR epd > 22 OR OMPT > 18 THEN '0'
ELSE @loopA_id END
,id
,dbo.g_test.dis
,dbo.g_test.ompt
,dbo.g_test.epd
from g_test
WHERE id = @id_ExactMatch_SF
SET @loopA_id = @loopA_id + 1;
END; -
All of the dis values in your sample data are different, so therefore grc will always be zero. Your sample results therefore do not match your written logic. Please clarify.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
This seems to give your expected results despite not referring to the "dis" column at all.
WITH test_resets AS
(
SELECT *, SUM(CASE WHEN ompt > 16 OR epd > 22 THEN 1 ELSE 0 END) OVER(ORDER BY gt.id ROWS UNBOUNDED PRECEDING) AS reset_grp
FROM #g_test AS gt
)
SELECT t.id
, t.dis
, t.ompt
, t.epd
, COUNT(*) OVER(PARTITION BY t.reset_grp ORDER BY t.id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS gra
FROM test_resets AS t;I also did a comparison of my query with Steve's query. Here are the read stats. Specifically note the scan counts and logical reads on the 'Worktable'.
-- My query
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#g_test______00000000A01B'. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
-- Steve's query
Table 'Worktable'. Scan count 7, logical reads 37, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#g_test___00000000A01B'. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
I want to say thank you very much, everyone. After I carefully reviewed our data and created a sample data. It looks like I need "gra" number based on the "dis" field. Same above logic. I created sample data below. However, the results are not correct. After you run below query you will see the row #4 "gra = 2". It should be 1. The sequence number should start 0,1,2,3... Please guide me what am I doing wrong.
Thanks for your help again!
DECLARE @g_test5 TABLE
(
id int IDENTITY(1,1),
disvarchar(10),
ompt int,
epd int,
Gra int
)
INSERT INTO @g_test5 (dis,ompt,epd)
SELECT '91758',18,26
UNION ALL
SELECT '91720',18,23
UNION ALL
SELECT '98755',16,22
UNION ALL
SELECT '98755',10,16
UNION ALL
SELECT '98755',8,12
UNION ALL
SELECT '98755',18,30
SELECT
ID,
dis,
ompt,
epd,
CASE WHEN dis <> LAG(dis) over(ORDER BY dis) OR dis = '' or dis = '00000' or ompt > 16 or epd > 22 then '0'
else ROW_NUMBER () over(partition by dis ORDER BY id) end as grc
FROM @g_test5 -
It doesn't appear that you've tried to incorporate the techniques that you've already been given. Your latest post only has minor differences from your first post in this thread. You come across as wanting things handed to you on a silver platter rather than actually being interested in learning.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Thanks, Drew for your reply. Did you bother to read my latest post? and did you bother to run the query that I provided?
IF yes, then my question was in the latest Post. The Row_Number should start with 1. However, the query that I provided in the latest post starts with 2.
I hope, now my question is very clear for you as well.
Thanks for the time to type the message above. Appreciate it!
- drew.allen wrote:
I also did a comparison of my query with Steve's query. Here are the read stats. Specifically note the scan counts and logical reads on the 'Worktable'.
Using the preceding row to mark the windowing boundary is a useful simplification I agree. It lets you SUM OVER without consideration of the lagged gap (or whatever it's called). Not the first time it's been offered in reply to my gap/grp code. Maybe I'll switch
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- rocky_498 wrote:
Thanks, Drew for your reply. Did you bother to read my latest post? and did you bother to run the query that I provided?
IF yes, then my question was in the latest Post. The Row_Number should start with 1. However, the query that I provided in the latest post starts with 2.
I hope, now my question is very clear for you as well.
Thanks for the time to type the message above. Appreciate it!
From your code we can read more than you bothered to post
Good luck and thanks for your typing
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply