Trying to Get Rid of Cursor for Creating Group Number
-
Here is the basic scenario, I have a table that can have site values 1-12, each site can have seq values 1-4. I need to get the max Value for runs of T's, any S will reset. I hope I included enough examples to give a sense of what I am looking for.
Here is the code I am currently using:
DECLARE @Site int, @Seq int, @concur char(1), @newGroupNo int=1, @previousSite int=0
DECLARE cur CURSOR FOR
SELECT Site,Seq,Concur FROM #tempTvol order by Site, SeqOPEN cur
FETCH NEXT FROM cur
INTO @site,@Seq,@concur
WHILE @@FETCH_STATUS = 0
BEGIN
IF @previousSite<>@Site
SET @newGroupNo=1
UPDATE #tempTvol SET groupnumber=@newGroupNo WHERE tdsiteseqno=@Site AND Seq>=@Seq
IF @concur = 'S'
BEGIN
SET @newGroupNo+=1
UPDATE #tempTvol SET groupnumber=@newGroupNo WHERE tdsiteseqno=@Site AND Seq>=@Seq
END
SET @previousSite=@Site
FETCH NEXT FROM cur
INTO @site,@seq,@concurEND
CLOSE cur;
DEALLOCATE cur;UPDATE T SET Value = (SELECT MAX(Value) FROM #tempTvol V WHERE T.Site=V.Site AND T.groupnumber=V.groupnumber)
-
Not certain that I've read this right but here is a suggestion
😎
USE TEEST;
GO
SET NOCOUNT ON;CREATE TABLE #tempTvol( Site int,
Seq int,
Concur char(1),
Value float)
INSERT INTO #tempTvol
VALUES(1,1,NULL,1.8)
,(1,2,'T',2.0)
,(1,3,'S',2.2)
,(1,4,'T',2.4)
,(2,1,NULL,1.5)
,(2,2,'S',1.7)
,(2,3,'T',1.8)
,(3,1,NULL,2.0)
,(3,2,'T',2.2)
,(3,3,'T',2.4);SELECT
TT.Site
,TT.Seq
,TT.Concur
,TT.Value
,ROW_NUMBER() OVER
(
PARTITION BY TT.Site
,TT.Concur
ORDER BY TT.Site ASC
) AS groupnumber
FROM #tempTvol TT;DROP TABLE #tempTvol
Output
Site Seq Concur Value groupnumber
----------- ----------- ------ ---------------------- --------------------
1 1 NULL 1.8 1
1 3 S 2.2 1
1 4 T 2.4 1
1 2 T 2 2
2 1 NULL 1.5 1
2 2 S 1.7 1
2 3 T 1.8 1
3 1 NULL 2 1
3 2 T 2.2 1
3 3 T 2.4 2 -
Yes, I tried that, but it doesn't work. The group number changes base on an occurrence of 'S'. In the case of Site 1 seq 1 and 2 should be grouped together(1), then seq 3 and 4 should be grouped together(2).
If seq 1 is null and the rest are all 'S', there should be groups 1, 2, 3, 4 and no values will be promoted.
Grouping results would be as followsTable1 Output Site Seq Concur Value Site Seq Concur Value Group 1 1 NULL 1.8 1 1 NULL 2 1 1 2 T 2 1 2 T 2 1 1 3 S 2.2 1 3 S 2.4 2 1 4 T 2.4 1 4 T 2.4 2 2 1 NULL 1.5 2 1 NULL 1.5 1 2 2 S 1.7 2 2 S 1.8 2 2 3 T 1.8 2 3 T 1.8 2 3 1 NULL 2 3 1 NULL 2.4 1 3 2 T 2.2 3 2 T 2.4 1 3 3 T 2.4 3 3 T 2.4 1 -
Try this:
SET NOCOUNT ON;CREATE TABLE #tempTvol (
[Site] int,
Seq int,
Concur char(1),
[Value] float
);
INSERT INTO #tempTvol
VALUES (1, 1, NULL, 1.8)
,(1, 2, 'T', 2.0)
,(1, 3, 'S', 2.2)
,(1, 4, 'T', 2.4)
,(2, 1, NULL, 1.5)
,(2, 2, 'S', 1.7)
,(2, 3, 'T', 1.8)
,(3, 1, NULL, 2.0)
,(3, 2, 'T', 2.2)
,(3, 3, 'T', 2.4);SELECT T.[Site], T.Seq, T.Concur, T.[Value],
1 + SUM(CASE WHEN T.Concur = 'S' THEN 1 ELSE 0 END) OVER(PARTITION BY T.[Site] ORDER BY T.Seq) AS GroupNo
FROM #tempTvol AS TDROP TABLE #tempTvol;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
This will get you the results you're looking for in the OP...
Based on the original data, I don't see a need to screw with that group number thing if all you're trying to do is get the calculated output value.-- create test data...
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
BEGIN DROP TABLE #TestData; END;
GOCREATE TABLE #TestData (
[Site] INT NOT NULL,
Seq INT NOT NULL,
Concur CHAR(1) NULL,
[Value] DECIMAL(9,1) NOT NULL,
PRIMARY KEY CLUSTERED ([Site], Seq)
);
INSERT #TestData ([Site], Seq, Concur, [Value]) VALUES
(1, 1, NULL, 1.8),
(1, 2, 'T', 2),
(1, 3, 'S', 2.2),
(1, 4, 'T', 2.4),
(2, 1, NULL, 1.5),
(2, 2, 'S', 1.7),
(2, 3, 'T', 1.8),
(3, 1, NULL, 2),
(3, 2, 'T', 2.2),
(3, 3, 'T', 2.4);
GO
--SELECT * FROM #TestData td;--==========================================================
-- the actual solution...
WITH
cte_test_data_lead AS (
SELECT
td.[Site],
td.Seq,
td.Concur,
td.[Value],
next_concur = LEAD(td.Concur, 1) OVER (PARTITION BY td.[Site] ORDER BY td.Seq)
FROM
#TestData td
)
SELECT
tdl.Site,
tdl.Seq,
tdl.Concur,
--tdl.Value,
--tdl.next_concur,
--tv.T_Val,
--bv.bin_val,
output_val = ISNULL(
CONVERT(DECIMAL(9,1), SUBSTRING(MIN(bv.bin_val) OVER (PARTITION BY tdl.[Site] ORDER BY tdl.Seq ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ), 5, 5))
+ CASE WHEN tdl.next_concur = 'S' THEN NULL ELSE 0 END
, tdl.[Value]
)
FROM
cte_test_data_lead tdl
CROSS APPLY ( VALUES (CASE WHEN tdl.Concur = 'T' AND (tdl.next_concur <> 'T' OR tdl.next_concur IS NULL) THEN tdl.[Value] END) ) tv (T_Val)
CROSS APPLY ( VALUES (CONVERT(BINARY(4), tdl.Seq) + CONVERT(BINARY(5), tv.T_Val)) ) bv (bin_val)
ORDER BY
tdl.[Site],
tdl.Seq;
GOResults...
Site Seq Concur output_val
----------- ----------- ------ ---------------------------------------
1 1 NULL 2.0
1 2 T 2.0
1 3 S 2.4
1 4 T 2.4
2 1 NULL 1.5
2 2 S 1.8
2 3 T 1.8
3 1 NULL 2.4
3 2 T 2.4
3 3 T 2.4 -
Given the sample data and the desired output, the solution is simple, but somehow I have the suspicion that this may not reflect the entire problem.
😎We can describe the logic as:
1. if the previous Concur value is NULL then the Group is 1
2. if the previous Concur value is not NULL and it is the same as the current value then the Group is 1
3. if it is neither 1) or 2) then the Group is 2USE TEEST;
GO
SET NOCOUNT ON;CREATE TABLE #tempTvol( Site int,
Seq int,
Concur char(1),
Value float);
INSERT INTO #tempTvol
VALUES(1,1,NULL,1.8)
,(1,2,'T',2.0)
,(1,3,'S',2.2)
,(1,4,'T',2.4)
,(2,1,NULL,1.5)
,(2,2,'S',1.7)
,(2,3,'T',1.8)
,(3,1,NULL,2.0)
,(3,2,'T',2.2)
,(3,3,'T',2.4);
;WITH PRE_GROUP AS
(
SELECT
TT.Site
,TT.Seq
,TT.Concur
,LAG(TT.Concur,1,TT.Concur) OVER
(
PARTITION BY TT.Site
ORDER BY TT.Seq
) AS LAG_C
,TT.Value
FROM #tempTvol TT
)
SELECT
PG.Site
,PG.Seq
,PG.Concur
,PG.Value
,CASE
WHEN PG.LAG_C IS NULL THEN 1
WHEN PG.Concur IS NOT NULL AND PG.Concur = PG.LAG_C THEN 1
ELSE 2
END AS [Group]
FROM PRE_GROUP PG
;DROP TABLE #tempTvol;
Output
Site Seq Concur Value Group
----- ---- ------ ------ ------
1 1 NULL 1.8 1
1 2 T 2 1
1 3 S 2.2 2
1 4 T 2.4 2
2 1 NULL 1.5 1
2 2 S 1.7 1
2 3 T 1.8 2
3 1 NULL 2 1
3 2 T 2.2 1
3 3 T 2.4 1 - Eirikur Eiriksson - Thursday, April 5, 2018 12:21 AM
There was a statement of the original problem that said that there was a scenario where the group numbers would be 1, 2, 3, 4, but that scenario, while described, was not in the sample data. My solution should produce that result. I provided it the way I did because it seemed likely this was part of a larger whole that we aren't privy to...
EDIT: here's the piece of the OP's second post that I'm referring to: "If seq 1 is null and the rest are all 'S', there should be groups 1, 2, 3, 4 and no values will be promoted."
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Thursday, April 5, 2018 6:16 AM
I saw that Steve, thought this might make the OP at least think of posting a full problem example, didn't want to speculate on it 😉
😎 -
Here's that scenario in the test data:
SET NOCOUNT ON;CREATE TABLE #tempTvol (
[Site] int,
Seq int,
Concur char(1),
[Value] float
);
INSERT INTO #tempTvol
VALUES (1, 1, NULL, 1.8)
,(1, 2, 'T', 2.0)
,(1, 3, 'S', 2.2)
,(1, 4, 'T', 2.4)
,(2, 1, NULL, 1.5)
,(2, 2, 'S', 1.7)
,(2, 3, 'T', 1.8)
,(3, 1, NULL, 2.0)
,(3, 2, 'T', 2.2)
,(3, 3, 'T', 2.4)
,(4, 1, NULL, 1.6)
,(4, 2, 'S', 1.9)
,(4, 3, 'S', 2.1)
,(4, 4, 'S', 2.3);SELECT T.[Site], T.Seq, T.Concur, T.[Value],
1 + SUM(CASE WHEN T.Concur = 'S' THEN 1 ELSE 0 END) OVER(PARTITION BY T.[Site] ORDER BY T.Seq) AS GroupNo
FROM #tempTvol AS TDROP TABLE #tempTvol;
And here's the results:
Site Seq Concur Value GroupNo
1 1 NULL 1.8 1
1 2 T 2 1
1 3 S 2.2 2
1 4 T 2.4 2
2 1 NULL 1.5 1
2 2 S 1.7 2
2 3 T 1.8 2
3 1 NULL 2 1
3 2 T 2.2 1
3 3 T 2.4 1
4 1 NULL 1.6 1
4 2 S 1.9 2
4 3 S 2.1 3
4 4 S 2.3 4Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - Eirikur Eiriksson - Thursday, April 5, 2018 6:24 AM
Yeah, I get that. However, I think you "speculated" on the meaning of "no values get promoted", by computing the result. I wasn't looking to go that far, figuring that such a statement about not being promoted was too vague, and given the stated objective, it seemed highly likely that we just weren't privy to the whole picture, and given the obfuscation it already had, it also seemed likely we wouldn't ever get that anyway. Of course, I suppose I probably shouldn't "assume"....:hehe:.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Jason,
That looks like the answer I'm looking for, but I get an error converting varbinary to numeric. I will see if I can work that out.
The logic you describe is slightly off. The group number should change any time an 'S' is encountered.
Thanks to everyone, I think I will be able to figure it out now. I just hope it scales well.
- Chris Souchik - Thursday, April 5, 2018 6:44 AM
Chris,
Did you see my solution?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Thursday, April 5, 2018 6:29 AM
ASS (yo) U (and) ME 😀
😎 - Chris Souchik - Thursday, April 5, 2018 6:44 AM
Resetting is not the same as changing, you stated the former in the OP.
😎
Here is a solution that does work for any number of groups, no conversion problems either, given that your sample set is accurate. It is based on the same logic as Steve posted earlier.
USE TEEST;
GO
SET NOCOUNT ON;CREATE TABLE #tempTvol( Site int,
Seq int,
Concur char(1),
Value float);
INSERT INTO #tempTvol
VALUES(1,1,NULL,1.8)
,(1,2,'T',2.0)
,(1,3,'S',2.2)
,(1,4,'T',2.4)
,(2,1,NULL,1.5)
,(2,2,'S',1.7)
,(2,3,'T',1.8)
,(3,1,NULL,2.0)
,(3,2,'T',2.2)
,(3,3,'T',2.4);
;WITH PRE_GROUP AS
(
SELECT
TT.Site
,TT.Seq
,TT.Concur
,CASE
WHEN TT.Concur = 'S' THEN 1
ELSE 0
END AS T_GROUP
,TT.Value
FROM #tempTvol TT
)
SELECT
PG.Site
,PG.Seq
,PG.Concur
,PG.Value
,1 + SUM(T_GROUP) OVER
(
PARTITION BY PG.Site
ORDER BY PG.Seq
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS [Group]
FROM PRE_GROUP PG
;DROP TABLE #tempTvol;
- Eirikur Eiriksson - Thursday, April 5, 2018 12:21 AM
I'm reading it a little differently...
Based on the example, it appears that the "Concor = 'S'" marks the beginning of a new "group". Nothing more complex than that...
So... Logically, the first occurrence of an 'S' will be the start of "group 2"... The second is the start "group 3" and so on...
Anything that comes before the first 'S' is, by default, "group 1.
WITH
cte_group_end AS (
SELECT
td .Site,
td.Seq,
gn = ROW_NUMBER() OVER (PARTITION BY td.[Site] ORDER BY td.Seq) + 1
FROM
#TestData td
WHERE
td.Concur = 'S'
)
SELECT
td.Site,
td.Seq,
td.Concur,
td.Value,
gn = ISNULL(MAX(ge.gn) OVER (PARTITION BY td.[Site] ORDER BY td.Seq ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 1)
FROM
#TestData td
LEFT JOIN cte_group_end ge
ON td.[Site] = ge.[Site]
AND td.Seq = ge.Seq;Results...
Site Seq Concur Value gn
----------- ----------- ------ ------- ----
1 1 NULL 1.8 1
1 2 T 2.0 1
1 3 S 2.2 2
1 4 T 2.4 2
2 1 NULL 1.5 1
2 2 S 1.7 2
2 3 T 1.8 2
3 1 NULL 2.0 1
3 2 T 2.2 1
3 3 T 2.4 1
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply