SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Wierd Counter!


Wierd Counter!

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218963 Visits: 42000
ggraber (10/24/2008)
P.S. You may want to read Jeff's cool article on Running Totals which explains how this approach works http://www.sqlservercentral.com/articles/Advanced+Querying/61716/


Heh... that's where I was going with this... I just wanted to know if he wanted his original table to be updated or if I was going to have to use a temp table.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
JohnDBA
JohnDBA
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 232
Graber,

You are very close to what I need. But, row 16 should start with 1 and not row 15 becuase:
- the conter needs to start only when the code is = a in row 15 the first criteria is correct but code start with b ant not with a

criteria:
- If the value is between 10 and 15 and the code is = a then start count once I find the first value that meets the criteria up to 10 and start all over once I find another value that meets the criteria again.

Thank you so much!
Goldie Lesser
Goldie Lesser
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1718 Visits: 1501
You just need to add one criteria to the CASE statement. Like this:


UPDATE #Counter2
SET @Counter = Counter = CASE WHEN (@Counter >= 1 AND @Counter < 10) OR ([Value] >= 10 AND [Value] <=15 AND Code = 'a')
                          
THEN @Counter + 1
                          
ELSE 0 -- restart the counter
                          
END
  
,@RowNumber = RowNumber
FROM #Counter2





Goldie Lesser
Goldie Lesser
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1718 Visits: 1501
Jeff Moden (10/24/2008)
ggraber (10/24/2008)
P.S. You may want to read Jeff's cool article on Running Totals which explains how this approach works http://www.sqlservercentral.com/articles/Advanced+Querying/61716/


Heh... that's where I was going with this... I just wanted to know if he wanted his original table to be updated or if I was going to have to use a temp table.


I figured. Funny how a lot of the questions on the forum revolve around the same few concepts.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218963 Visits: 42000
JohnDBA (10/23/2008)
Please help me!
I have this values that I need to count based on condition.
- If the value is between 10 and 15 and the code is = a then start count once I find the first value that meets the criteria up to 10 and start all over once I find another value that meets the criteria again.


John,

GGraber is exactly on the right track but there's a couple of things that are missed.

Didn't start the count in regards to Code "a" .
Used "0" instead of NULL.
Forgot to include a Clustered Index to GUARANTEE the order of the update (although it worked without in this example, ya gotta have it to guarantee the order... and this only works with Updates, not Selects).

So, using your fine test data and GGraber's good code, here's a very high performance solution using the "running total" method that GGraber cited... as with GGraber's code, the details are in the comments...

--===== Create a temporary working table with a dummy unique identifier (RowNumber)
-- that will also be the object of the Clustered Index as a PK. Notice also,
-- that we've added a "Counter" column to hold the result of your request.
CREATE TABLE #Counter2
(
RowNumber INT IDENTITY(1,1),
StartDate DATETIME,
Value FLOAT,
Code CHAR(1),
Counter INT
)

--===== Copy all existing data from original table into our results table
-- in the correct order.
INSERT INTO #Counter2(StartDate, Value, Code)
SELECT StartDate, Value, Code
FROM #Counter
ORDER BY StartDate

--===== MUST have a clustered primary key to GUARANTEE this will work
ALTER TABLE #Counter2
ADD PRIMARY KEY CLUSTERED (RowNumber)

--===== Create the required local variables
DECLARE @Counter INT, -- Start value is NULL
@RowNumber -- Dummy variable to "anchor" the "running" update

--===== Calculate and store the value of the counter in the table using a "pseudo-cursor"
-- the order of which is driven by a forced scan on the Clustered Index.
-- Rules for the counter value are...
--
-- 1. Any value not in the following criteria will result in NULL for the Counter
-- 2. Data must be processed in order by StartDate.
-- 3. Counter will start at "1" at the first occurance where the Value is between 10 and 15 and
-- and the Code is "a".
-- 4. Count will increase by 1
-- 5. When the Counter reaches the value of 10, reset the Counter to NULL
-- 6. Do until all rows in RowNumber order have been updated.
UPDATE #Counter2
SET @Counter = Counter = CASE WHEN ([Value] BETWEEN 10 AND 15) AND Code = 'a' AND @Counter IS NULL THEN 1 -- Rules 1 & 3
WHEN @Counter = 10 THEN NULL -- Rule 5
ELSE @Counter + 1 -- Rule 4
END,
@RowNumber = RowNumber
FROM #Counter2 WITH(INDEX(0)) --LOOK! Must refer to the clustered index or could bomb! -- Rules 2 and 6

--===== Display the results in RowNumber order
SELECT * FROM #Counter2 ORDER BY RowNumber

--===== Housekeeping so we can run this example again, if we want.
-- This is NOT necessary in final production stored procs because
-- it will automatically drop at the end of the proc (how poetic ;-)
DROP TABLE #Counter2



Someone will likely figure out and post a way to do this with ROW_NUMBER() or RANK(), but this solution works on SQL Server 2000, as well.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218963 Visits: 42000
ggraber (10/24/2008)
Jeff Moden (10/24/2008)
ggraber (10/24/2008)
P.S. You may want to read Jeff's cool article on Running Totals which explains how this approach works http://www.sqlservercentral.com/articles/Advanced+Querying/61716/


Heh... that's where I was going with this... I just wanted to know if he wanted his original table to be updated or if I was going to have to use a temp table.


I figured. Funny how a lot of the questions on the forum revolve around the same few concepts.


Heh... Isn't that the truth?

I missed several of the posts above because I was creating a post and you beat me to one of the things I thought was missing. I gotta say it's a real pleasure to see someone document their code... nice job!

Just a note... You do have to remember that you MUST have a clustered index on the correct columns and then force it to be used to guarantee that this method will work correctly.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Goldie Lesser
Goldie Lesser
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1718 Visits: 1501
Jeff Moden (10/24/2008)
Just a note... You do have to remember that you MUST have a clustered index on the correct columns and then force it to be used to guarantee that this method will work correctly.

Thanks for the reminder!
JohnDBA
JohnDBA
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 232
Jeff and Graber

Thank you so much, and thank you for the explanation. It helps a lot to understand where my problem was. I really appreciate your help.
Garadin
Garadin
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7087 Visits: 4107
Jeff Moden (10/24/2008)
ggraber (10/24/2008)
Jeff Moden (10/24/2008)
ggraber (10/24/2008)
P.S. You may want to read Jeff's cool article on Running Totals which explains how this approach works http://www.sqlservercentral.com/articles/Advanced+Querying/61716/


Heh... that's where I was going with this... I just wanted to know if he wanted his original table to be updated or if I was going to have to use a temp table.


I figured. Funny how a lot of the questions on the forum revolve around the same few concepts.


Heh... Isn't that the truth?

I missed several of the posts above because I was creating a post and you beat me to one of the things I thought was missing. I gotta say it's a real pleasure to see someone document their code... nice job!

Just a note... You do have to remember that you MUST have a clustered index on the correct columns and then force it to be used to guarantee that this method will work correctly.


I figured I'd let you answer one of these, since I've snagged like the past 10 Hehe (Although this one didn't quite work out the way I had hoped it would

Seth Phelabaum
Consistency is only a virtue if you're not a screwup. ;-)

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218963 Visits: 42000
JohnDBA (10/24/2008)
Jeff and Graber

Thank you so much, and thank you for the explanation. It helps a lot to understand where my problem was. I really appreciate your help.


You're very welcome, John. Thanks for the feedback.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search