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.
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),
--===== 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
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.
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
@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.
is pronounced ree-bar and is a Modenism for R
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
How to post code problemsHow to post performance problemsForum FAQs