How do I create a conditional counter?

Question

Post reply

How do I create a conditional counter?

JohnDBA

SSC Eights!

Points: 812
More actions
October 8, 2008 at 10:22 am

#191906

I am trying to create a column in a temp table (stored procedure) with a counter based on certain values.
I need to start counting only if the value is between 10 and 15 but once I find the first value that meets my criteria,
I need to count up to 10 and start all over once I find another value that meets my criteria.
Can any one help me please???? See sample data and desired output below:
CREATE TABLE #Conditional
(
StartDate datetime,
value float
)
INSERT INTO #Conditional
VALUES ('08/10/2008 01:48', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 01:54', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 02:00', 13)
INSERT INTO #Conditional
VALUES ('08/10/2008 02:06', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 02:12', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 02:18', 15)
INSERT INTO #Conditional
VALUES ('08/10/2008 02:24', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 02:30', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 02:36', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 02:42', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 02:48', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 02:54', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 03:00', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 03:06', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 03:12', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 03:18', 12)
INSERT INTO #Conditional
VALUES ('08/10/2008 03:24', 11)
INSERT INTO #Conditional
VALUES ('08/10/2008 03:30', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 03:36', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 03:42', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 03:48', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 03:54', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 04:00', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 04:06', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 01:48', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 01:48', 3)
INSERT INTO #Conditional
VALUES ('08/10/2008 01:48', 3)
StartDate value Counter
2008-08-10 01:48:00.000 3
2008-08-10 01:54:00.000 3
2008-08-10 02:00:00.000 13 1
2008-08-10 02:06:00.000 3 2
2008-08-10 02:12:00.000 3 3
2008-08-10 02:18:00.000 15 4
2008-08-10 02:24:00.000 3 5
2008-08-10 02:30:00.000 3 6
2008-08-10 02:36:00.000 3 7
2008-08-10 02:42:00.000 3 8
2008-08-10 02:48:00.000 3 9
2008-08-10 02:54:00.000 3 10
2008-08-10 03:00:00.000 3
2008-08-10 03:06:00.000 3
2008-08-10 03:12:00.000 3
2008-08-10 03:18:00.000 12 1
2008-08-10 03:24:00.000 11 2
2008-08-10 03:30:00.000 3 3
2008-08-10 03:36:00.000 3 4
2008-08-10 03:42:00.000 3 5
2008-08-10 03:48:00.000 3 6
2008-08-10 03:54:00.000 3 7
2008-08-10 04:00:00.000 3 8
2008-08-10 04:06:00.000 3 9
2008-08-10 01:48:00.000 3 10
2008-08-10 01:48:00.000 3
2008-08-10 01:48:00.000 3

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

it's kind of easy...you use a SUM() of CASE statement to logicilly decide whether you want to add the values or zero:

select * from #Conditional

select sum(CASE WHEN VALUE BETWEEN 10 AND 15 THEN VALUE ELSE 0 END) AS SumOf10to15s,

StartDate

from #Conditional

group by StartDate

--if you were grouping by date, and not datetime:

select sum(CASE WHEN VALUE BETWEEN 10 AND 15 THEN VALUE ELSE 0 END) AS SumOf10to15s,

convert(varchar,StartDate,110) as StartDate

from #Conditional

group by convert(varchar,StartDate,110)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JohnDBA SSC Eights! Points: 812 More actions · Answer 2

Lowell,

I really appreciate you idea but my output needs to be excatly like this:

I need to have the numbers from 1 to 10 per the condition.

StartDate value Counter

2008-08-10 01:48:00.000 3

2008-08-10 01:54:00.000 3

2008-08-10 02:00:00.000 13 1

2008-08-10 02:06:00.000 3 2

2008-08-10 02:12:00.000 3 3

2008-08-10 02:18:00.000 15 4

2008-08-10 02:24:00.000 3 5

2008-08-10 02:30:00.000 3 6

2008-08-10 02:36:00.000 3 7

2008-08-10 02:42:00.000 3 8

2008-08-10 02:48:00.000 3 9

2008-08-10 02:54:00.000 3 10

2008-08-10 03:00:00.000 3

2008-08-10 03:06:00.000 3

2008-08-10 03:12:00.000 3

2008-08-10 03:18:00.000 12 1

2008-08-10 03:24:00.000 11 2

2008-08-10 03:30:00.000 3 3

2008-08-10 03:36:00.000 3 4

2008-08-10 03:42:00.000 3 5

2008-08-10 03:48:00.000 3 6

2008-08-10 03:54:00.000 3 7

2008-08-10 04:00:00.000 3 8

2008-08-10 04:06:00.000 3 9

2008-08-10 01:48:00.000 3 10

2008-08-10 01:48:00.000 3

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 3

CREATE TABLE #OrderedSet ( SeqId int IDENTITY(1,1), StartDate datetime, value float, Sequence int )

INSERT INTO #OrderedSet ( StartDate, value )

SELECT StartDate, Value FROM #Conditional

DECLARE @SequenceId int

WHILE EXISTS ( SELECT 1 FROM #OrderedSet WHERE value BETWEEN 10 AND 15 AND Sequence IS NULL )

BEGIN

SELECT @SequenceId = MIN(SeqId) FROM #OrderedSet WHERE value BETWEEN 10 AND 15 AND Sequence IS NULL

UPDATE OS

SET Sequence = SeqId - @SequenceId + 1

FROM #OrderedSet OS

WHERE OS.SeqId BETWEEN @SequenceId AND @SequenceId + 9

END

SELECT * FROM #OrderedSet

you need to have something to sort on to do this. a CTE coule also be used with ROW_NUMBER if you have an identity on your table.

JohnDBA SSC Eights! Points: 812 More actions · Answer 4

SSC Journeyman,

Thank you very much for the idea. However, this is not what I need.

Your suggestion produce this: IneedThis.

2008-08-10 01:48:00.00030

2008-08-10 01:54:00.00030

2008-08-10 02:00:00.000131 1

2008-08-10 02:06:00.00030 2

2008-08-10 02:12:00.00030 3

2008-08-10 02:18:00.000152 4

2008-08-10 02:24:00.00030 5

2008-08-10 02:30:00.00030 6

2008-08-10 02:36:00.00030 7

2008-08-10 02:42:00.00030 8

2008-08-10 02:48:00.00030 9

2008-08-10 02:54:00.00030 10

2008-08-10 03:00:00.00030

2008-08-10 03:06:00.00030

2008-08-10 03:12:00.00030

2008-08-10 03:18:00.000123 1

2008-08-10 03:24:00.000114 2

2008-08-10 03:30:00.00030 3

2008-08-10 03:36:00.00030 4...

I'm out of ideas.

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 5

your original dataset posted was

2008-08-10 01:48:00.000 3

2008-08-10 01:54:00.000 3

2008-08-10 02:00:00.000 13 1

2008-08-10 02:06:00.000 3 2

2008-08-10 02:12:00.000 3 3

2008-08-10 02:18:00.000 15 4

2008-08-10 02:24:00.000 3 5

2008-08-10 02:30:00.000 3 6

2008-08-10 02:36:00.000 3 7

2008-08-10 02:42:00.000 3 8

2008-08-10 02:48:00.000 3 9

2008-08-10 02:54:00.000 3 10

2008-08-10 03:00:00.000 3

2008-08-10 03:06:00.000 3

2008-08-10 03:12:00.000 3

2008-08-10 03:18:00.000 12 1

2008-08-10 03:24:00.000 11 2

2008-08-10 03:30:00.000 3 3

2008-08-10 03:36:00.000 3 4

2008-08-10 03:42:00.000 3 5

2008-08-10 03:48:00.000 3 6

2008-08-10 03:54:00.000 3 7

2008-08-10 04:00:00.000 3 8

2008-08-10 04:06:00.000 3 9

2008-08-10 01:48:00.000 3 10

2008-08-10 01:48:00.000 3

which has no order to it there are values of 2008-08-10 01 at the top and bottom. So I assigned an id to it (which would be impacted by any clustered indexes ). In you newest dataset it looks like you ordered it by date.

Running this...

CREATE TABLE #Conditional ( StartDate datetime, value float )

INSERT INTO #Conditional VALUES ('08/10/2008 01:48', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 01:54', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 02:00', 13)

INSERT INTO #Conditional VALUES ('08/10/2008 02:06', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 02:12', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 02:18', 15)

INSERT INTO #Conditional VALUES ('08/10/2008 02:24', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 02:30', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 02:36', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 02:42', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 02:48', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 02:54', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 03:00', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 03:06', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 03:12', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 03:18', 12)

INSERT INTO #Conditional VALUES ('08/10/2008 03:24', 11)

INSERT INTO #Conditional VALUES ('08/10/2008 03:30', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 03:36', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 03:42', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 03:48', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 03:54', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 04:00', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 04:06', 3)

INSERT INTO #Conditional VALUES ('08/10/2008 01:48', 3)

CREATE TABLE #OrderedSet ( SeqId int IDENTITY(1,1), StartDate datetime, value float, Sequence int )

INSERT INTO #OrderedSet ( StartDate, value )

SELECT StartDate, Value FROM #Conditional

DECLARE @SequenceId int

WHILE EXISTS ( SELECT 1 FROM #OrderedSet WHERE value BETWEEN 10 AND 15 AND Sequence IS NULL )

BEGIN

SELECT @SequenceId = MIN(SeqId) FROM #OrderedSet WHERE value BETWEEN 10 AND 15 AND Sequence IS NULL

UPDATE OS

SET Sequence = SeqId - @SequenceId + 1

FROM #OrderedSet OS

WHERE OS.SeqId BETWEEN @SequenceId AND @SequenceId + 9

END

SELECT * FROM #OrderedSet

..produced your requested results.

SeqIdStartDatevalueSequence

12008-08-10 01:48:00.0003NULL

22008-08-10 01:54:00.0003NULL

32008-08-10 02:00:00.000131

42008-08-10 02:06:00.00032

52008-08-10 02:12:00.00033

62008-08-10 02:18:00.000154

72008-08-10 02:24:00.00035

82008-08-10 02:30:00.00036

92008-08-10 02:36:00.00037

102008-08-10 02:42:00.00038

112008-08-10 02:48:00.00039

122008-08-10 02:54:00.000310

132008-08-10 03:00:00.0003NULL

142008-08-10 03:06:00.0003NULL

152008-08-10 03:12:00.0003NULL

162008-08-10 03:18:00.000121

172008-08-10 03:24:00.000112

182008-08-10 03:30:00.00033

192008-08-10 03:36:00.00034

202008-08-10 03:42:00.00035

212008-08-10 03:48:00.00036

222008-08-10 03:54:00.00037

232008-08-10 04:00:00.00038

242008-08-10 04:06:00.00039

252008-08-10 01:48:00.000310

262008-08-10 01:48:00.0003NULL

272008-08-10 01:48:00.0003NULL

If dates get added to the table in a specific order then you can sort by the date field for different results.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

CREATE TABLE #Conditional

(

StartDate datetime,

value float,

x int

)

INSERT INTO #Conditional

VALUES ('08/10/2008 01:48', 3, 0) -- starting out with a zero value in the new "x" column

etc

--------------------------------

declare @b-2 int

set @b-2 = 0

update #conditional

set@b-2 = x = casewhen @b-2 between 1 and 9 then @b-2 + 1

when @b-2 = 0 and value >= 10 then 1

else 0

end

select * from #conditional

Warning: You can't count on the #conditional table being read in StartDate order, even if that is the basis for the clustered index. Jeff Moden has a good article on here about running totals. Check it out.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

JohnDBA SSC Eights! Points: 812 More actions · Answer 7

Joel Ewald,

Thank you very much! Yes, this is perfect! 🙂 I really appreciate your help!

12008-08-10 01:48:00.0003NULL

22008-08-10 01:54:00.0003NULL

32008-08-10 02:00:00.000131

42008-08-10 02:06:00.00032

52008-08-10 02:12:00.00033

62008-08-10 02:18:00.000154

72008-08-10 02:24:00.00035

82008-08-10 02:30:00.00036

92008-08-10 02:36:00.00037

102008-08-10 02:42:00.00038

112008-08-10 02:48:00.00039

122008-08-10 02:54:00.000310

132008-08-10 03:00:00.0003NULL

142008-08-10 03:06:00.0003NULL

152008-08-10 03:12:00.0003NULL

162008-08-10 03:18:00.000121

172008-08-10 03:24:00.000112

182008-08-10 03:30:00.00033...

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 8

Um... this is actually a cross post with a different name. The bad part about cross posting is that it split resources, the unsuing discussions, and the answers. Please don't cross post...

So far as the loop solution goes, nice job, but still RBAR which means it will be slower that what is found at the other end of this cross post...

http://www.sqlservercentral.com/Forums/Topic590904-338-1.aspx#bm591265

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)