InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 01:11:24 GMT20http://www.sqlservercentral.com/Forums/Topic590904-338-1.aspx[quote][b]JohnDBA (10/23/2008)[/b][hr]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. [/quote]Hey, John... I'm getting ready to write an article on things like this. Help me out, please. What are the business reasons/rules for doing such a thing? It'll help my understanding of why people need to do such things. The more detailed you can get, the better help it'll be for me. Thanks an awful lot.Fri, 23 Jan 2009 09:46:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspx[quote][b]Jeff Moden (10/27/2008)[/b][hr]Thanks for the "cover". :)[/quote]My pleasure, Jeff. I'm so glad for a chance to give back (and that Seth didn't grab it first ;) )Mon, 27 Oct 2008 17:58:56 GMTGoldie Lesserhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspxThanks for the "cover". :)Mon, 27 Oct 2008 17:49:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspxAnytime :)Mon, 27 Oct 2008 17:45:21 GMTGoldie Lesserhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspxGgraver,Than you so much! That was it!Mon, 27 Oct 2008 15:54:41 GMTJohnDBAhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspxJeff's script has a bug when the counter is equal to 10 and the next value is between 10 and 15.Try this one. It should work for you:[code]UPDATE #Counter2 SET @Counter = Counter = CASE WHEN ([Value] BETWEEN 10 AND 15) AND Code = 'a' AND (@Counter IS NULL OR @Counter = 10) 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[/code]Mon, 27 Oct 2008 15:51:23 GMTGoldie Lesserhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspxJeff helped me with this code, but I do have a problem with row 26. The counter should start with 1 at row 26 because the value is between 20 and 15. I did not covered that instance before, but I realized that I was skipping one every 10 consecutives counts.Thank you so much!!!!!!-------------------------------------------------------------------------------------------------CREATE TABLE #Counter(StartDate datetime,value float,code char(1))INSERT INTO #CounterVALUES ('08/10/2008 01:36', 3, 'a')INSERT INTO #CounterVALUES ('08/10/2008 01:42', 2, 'a')INSERT INTO #CounterVALUES ('08/10/2008 01:48', 2, 'a')INSERT INTO #CounterVALUES ('08/10/2008 01:54', 12, 'a')INSERT INTO #CounterVALUES ('08/10/2008 02:00', 13, 'a')INSERT INTO #CounterVALUES ('08/10/2008 02:06', 3,'a')INSERT INTO #CounterVALUES ('08/10/2008 02:12', 3,'a')INSERT INTO #CounterVALUES ('08/10/2008 02:18', 15,'a')INSERT INTO #CounterVALUES ('08/10/2008 02:24', 15,'b')INSERT INTO #CounterVALUES ('08/10/2008 02:30', 3,'a')INSERT INTO #CounterVALUES ('08/10/2008 02:36', 4,'a')INSERT INTO #CounterVALUES ('08/10/2008 02:42', 5,'a')INSERT INTO #CounterVALUES ('08/10/2008 02:48', 6,'a')INSERT INTO #CounterVALUES ('08/10/2008 02:54', 6,'b')INSERT INTO #CounterVALUES ('08/10/2008 03:00', 12,'b')INSERT INTO #CounterVALUES ('08/10/2008 03:06', 12,'a')INSERT INTO #CounterVALUES ('08/10/2008 03:12', 1,'a')INSERT INTO #CounterVALUES ('08/10/2008 03:18', 12,'a')INSERT INTO #CounterVALUES ('08/10/2008 03:24', 11,'a')INSERT INTO #CounterVALUES ('08/10/2008 03:30', 3,'a')INSERT INTO #CounterVALUES ('08/10/2008 03:36', 3,'a')INSERT INTO #CounterVALUES ('08/10/2008 03:42', 3,'a')INSERT INTO #CounterVALUES ('08/10/2008 03:48', 3,'a')INSERT INTO #CounterVALUES ('08/10/2008 03:54', 3,'a')INSERT INTO #CounterVALUES ('08/10/2008 04:00', 4,'a')INSERT INTO #CounterVALUES ('08/10/2008 04:06', 12,'a')INSERT INTO #CounterVALUES ('08/10/2008 04:12', 4,'a')INSERT INTO #CounterVALUES ('08/10/2008 04:18', 11,'a')INSERT INTO #CounterVALUES ('08/10/2008 04:24', 3,'a')--===== 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 variablesDECLARE @Counter INT, -- Start value is NULL @RowNumber int -- 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, #Counter----------------------------------------------------------------------------------------Jeff helped me with this code, but I do have a problem with row 26. The counter should start with 1 at row 26 because the value is between 20 and 15. I did not covered that instance before, but I realized that I was skipping one every 10 consecutives counts.Mon, 27 Oct 2008 15:37:01 GMTJohnDBAhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspxNah, I just abuse my poor refresh button. (And have RSS feeds to Trillian) :cool:Fri, 24 Oct 2008 13:49:20 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspx[quote][b]Garadin (10/24/2008)[/b][hr]I figured I'd let you answer one of these, since I've snagged like the past 10 :hehe: (Although [url=http://www.sqlservercentral.com/Forums/Topic587032-338-1.aspx] [u]this one[/u] [/url] didn't quite work out the way I had hoped it would[/quote]Heh... thanks, Seth. I'm gettin' a bit slow in my old age.Fri, 24 Oct 2008 13:43:28 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspx[quote][b]JohnDBA (10/24/2008)[/b][hr]Jeff and GraberThank you so much, and thank you for the explanation. It helps a lot to understand where my problem was. I really appreciate your help.[/quote]You're very welcome, John. Thanks for the feedback.Fri, 24 Oct 2008 13:41:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspx[quote][b]Jeff Moden (10/24/2008)[/b][hr][quote][b]ggraber (10/24/2008)[/b][hr][quote][b]Jeff Moden (10/24/2008)[/b][hr][quote][b]ggraber (10/24/2008)[/b][hr]P.S. You may want to read Jeff's cool article on Running Totals which explains how this approach works [url]http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url][/quote]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.[/quote]I figured. Funny how a lot of the questions on the forum revolve around the same few concepts.[/quote]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 [b][i]guarantee [/i][/b]that this method will work correctly.[/quote]I figured I'd let you answer one of these, since I've snagged like the past 10 :hehe: (Although [url=http://www.sqlservercentral.com/Forums/Topic587032-338-1.aspx] [u]this one[/u] [/url] didn't quite work out the way I had hoped it wouldFri, 24 Oct 2008 12:00:22 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspxJeff and GraberThank you so much, and thank you for the explanation. It helps a lot to understand where my problem was. I really appreciate your help.Fri, 24 Oct 2008 09:22:04 GMTJohnDBAhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspx[quote][b]Jeff Moden (10/24/2008)[/b][hr] 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 [b][i]guarantee [/i][/b]that this method will work correctly.[/quote]Thanks for the reminder!Fri, 24 Oct 2008 08:49:57 GMTGoldie Lesserhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspx[quote][b]ggraber (10/24/2008)[/b][hr][quote][b]Jeff Moden (10/24/2008)[/b][hr][quote][b]ggraber (10/24/2008)[/b][hr]P.S. You may want to read Jeff's cool article on Running Totals which explains how this approach works [url]http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url][/quote]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.[/quote]I figured. Funny how a lot of the questions on the forum revolve around the same few concepts.[/quote]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 [b][i]guarantee [/i][/b]that this method will work correctly.Fri, 24 Oct 2008 08:43:42 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspx[quote][b]JohnDBA (10/23/2008)[/b][hr]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. [/quote]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...[code]--===== 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 variablesDECLARE @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[/code]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.Fri, 24 Oct 2008 08:39:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspx[quote][b]Jeff Moden (10/24/2008)[/b][hr][quote][b]ggraber (10/24/2008)[/b][hr]P.S. You may want to read Jeff's cool article on Running Totals which explains how this approach works [url]http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url][/quote]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.[/quote]I figured. Funny how a lot of the questions on the forum revolve around the same few concepts.Fri, 24 Oct 2008 08:29:08 GMTGoldie Lesserhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspxYou just need to add one criteria to the CASE statement. Like this:[code][font="Courier New"][size="2"][color="black"][/color][color="blue"]UPDATE [/color][color="#434343"]#Counter[/color][color="black"]2[/color][color="blue"]SET [/color][color="#434343"]@Counter [/color][color="blue"]= [/color][color="black"]Counter [/color][color="blue"]= [/color][color="magenta"]CASE [/color][color="blue"]WHEN [/color][color="gray"]([/color][color="#434343"]@Counter [/color][color="gray"]>= [/color][color="black"]1 [/color][color="gray"]AND [/color][color="#434343"]@Counter [/color][color="gray"]< [/color][color="black"]10[/color][color="gray"]) OR ([/color][color="black"][Value] [/color][color="gray"]>= [/color][color="black"]10 [/color][color="gray"]AND [/color][color="black"][Value] [/color][color="gray"]<=[/color][color="black"]15 [/color][color="gray"]AND [/color][color="black"]Code [/color][color="blue"]= [/color][color="red"]'a'[/color][color="gray"])                           [/color][color="blue"]THEN [/color][color="#434343"]@Counter [/color][color="gray"]+ [/color][color="black"]1                           [/color][color="blue"]ELSE [/color][color="black"]0 [/color][color="green"]-- restart the counter                           [/color][color="blue"]END   [/color][color="gray"],[/color][color="#434343"]@RowNumber [/color][color="blue"]= [/color][color="black"]RowNumber[/color][color="blue"]FROM [/color][color="#434343"]#Counter[/color][color="black"]2[/color][/size][/font][/code]Fri, 24 Oct 2008 08:28:27 GMTGoldie Lesserhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspxGraber,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 acriteria:- 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!Fri, 24 Oct 2008 08:22:55 GMTJohnDBAhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspx[quote][b]ggraber (10/24/2008)[/b][hr]P.S. You may want to read Jeff's cool article on Running Totals which explains how this approach works [url]http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url][/quote]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.Fri, 24 Oct 2008 07:31:00 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspx[quote][b]AnzioBake (10/24/2008)[/b][hr]The solution to this is very much like a solution I posted earlier, but not without a row identifier as mentioned before.[/quote]So, you shouldn't have a problem posting the code or at least a URL here, huh? ;)Fri, 24 Oct 2008 07:29:32 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspxP.S. You may want to read Jeff's cool article on Running Totals which explains how this approach works [url]http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url]Fri, 24 Oct 2008 06:45:35 GMTGoldie Lesserhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspxThis should work for you:[code][font="Courier New"][size="2"][color="black"][/color][color="green"]-- create a table to store our results-- create a dummy unique identifier on the table[/color][color="blue"]CREATE TABLE [/color][color="#434343"]#Counter[/color][color="black"]2[/color][color="gray"](   [/color][color="black"]RowNumber [/color][color="blue"]INT [/color][color="#434343"]IDENTITY[/color][color="gray"]([/color][color="black"]1[/color][color="gray"],[/color][color="black"]1[/color][color="gray"])   ,[/color][color="black"]StartDate [/color][color="blue"]DATETIME   [/color][color="gray"],[/color][color="black"][Value] [/color][color="blue"]FLOAT   [/color][color="gray"],[/color][color="black"]Code [/color][color="blue"]CHAR[/color][color="gray"]([/color][color="black"]1[/color][color="gray"])   ,[/color][color="black"]Counter [/color][color="blue"]INT[/color][color="gray"])[/color][color="green"]--insert all existing data from original table into our results table[/color][color="blue"]INSERT INTO [/color][color="#434343"]#Counter[/color][color="black"]2[/color][color="gray"]([/color][color="black"]StartDate[/color][color="gray"], [/color][color="black"][Value][/color][color="gray"], [/color][color="black"]Code[/color][color="gray"])[/color][color="blue"]SELECT [/color][color="black"]StartDate   [/color][color="gray"], [/color][color="black"][Value]   [/color][color="gray"], [/color][color="black"]Code [/color][color="blue"]FROM [/color][color="#434343"]#Counter[/color][color="green"]-- create a counter variable-- this will be used to count our rows[/color][color="blue"]DECLARE [/color][color="#434343"]@Counter [/color][color="blue"]INTSET [/color][color="#434343"]@Counter [/color][color="blue"]= [/color][color="black"]0[/color][color="green"]-- this is a dummy variable-- we need it as an anchor to keep our running total[/color][color="blue"]DECLARE [/color][color="#434343"]@RowNumber [/color][color="blue"]INT[/color][color="green"]-- now we will set the counter-- we need to start the counter when the value is between 10 and 15-- accumulate the counter until we get to 10-- start again when we get to the next value between 10 and 15[/color][color="blue"]UPDATE [/color][color="#434343"]#Counter[/color][color="black"]2[/color][color="blue"]SET [/color][color="#434343"]@Counter [/color][color="blue"]= [/color][color="black"]Counter [/color][color="blue"]= [/color][color="magenta"]CASE [/color][color="blue"]WHEN [/color][color="gray"]([/color][color="#434343"]@Counter [/color][color="gray"]>= [/color][color="black"]1 [/color][color="gray"]AND [/color][color="#434343"]@Counter [/color][color="gray"]< [/color][color="black"]10[/color][color="gray"]) OR ([/color][color="black"][Value] [/color][color="gray"]>= [/color][color="black"]10 [/color][color="gray"]AND [/color][color="black"][Value] [/color][color="gray"]<=[/color][color="black"]15[/color][color="gray"])                           [/color][color="blue"]THEN [/color][color="#434343"]@Counter [/color][color="gray"]+ [/color][color="black"]1                           [/color][color="blue"]ELSE [/color][color="black"]0 [/color][color="green"]-- restart the counter                           [/color][color="blue"]END   [/color][color="gray"],[/color][color="#434343"]@RowNumber [/color][color="blue"]= [/color][color="black"]RowNumber[/color][color="blue"]FROM [/color][color="#434343"]#Counter[/color][color="black"]2[/color][color="green"]-- take a look at our results[/color][color="blue"]SELECT [/color][color="gray"]* [/color][color="blue"]FROM [/color][color="#434343"]#Counter[/color][color="black"]2[/color][color="green"]-- clean up[/color][color="blue"]DROP TABLE [/color][color="#434343"]#Counter[/color][color="black"]2[/color][/size][/font][/code]Fri, 24 Oct 2008 06:42:12 GMTGoldie Lesserhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspxThe solution to this is very much like a solution I posted earlier, but not without a row identifier as mentioned before.Fri, 24 Oct 2008 03:16:08 GMTAnzioBakehttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspxIs there any sequential unique column that we can use for sorting? If none, can you insert one, an IDENTITY, preferably.-- CKFri, 24 Oct 2008 00:33:17 GMTck9663http://www.sqlservercentral.com/Forums/Topic590904-338-1.aspx[quote][b]JohnDBA (10/23/2008)[/b][hr]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. [/quote]Couple of questions... 1. How many rows are in the real table?2. Do you want to update the original table with the new count?Thu, 23 Oct 2008 20:02:07 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspx[quote][b]Nikhil Shikarkhane (10/23/2008)[/b][hr]Hint: You will have to use ROW_NUMBER OVER ( PARTITION BY ) :)[/quote]I'd really like to see that for this one. Got code? ;)Thu, 23 Oct 2008 20:00:35 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspxNikhil, I already tried that and it is more complicated than I thought.Thu, 23 Oct 2008 18:40:43 GMTJohnDBAhttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspxHint: You will have to use ROW_NUMBER OVER ( PARTITION BY ) :)Thu, 23 Oct 2008 18:38:28 GMTNikhil Shikarkhanehttp://www.sqlservercentral.com/Forums/Topic590904-338-1.aspxPlease 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. CREATE TABLE #Counter(StartDate datetime,value float,code char(1))INSERT INTO #CounterVALUES ('08/10/2008 01:36', 3, 'a')INSERT INTO #CounterVALUES ('08/10/2008 01:42', 2, 'a')INSERT INTO #CounterVALUES ('08/10/2008 01:48', 2, 'a')INSERT INTO #CounterVALUES ('08/10/2008 01:54', 12, 'a')INSERT INTO #CounterVALUES ('08/10/2008 02:00', 13, 'a')INSERT INTO #CounterVALUES ('08/10/2008 02:06', 3,'a')INSERT INTO #CounterVALUES ('08/10/2008 02:12', 3,'a')INSERT INTO #CounterVALUES ('08/10/2008 02:18', 15,'a')INSERT INTO #CounterVALUES ('08/10/2008 02:24', 15,'b')INSERT INTO #CounterVALUES ('08/10/2008 02:30', 3,'a')INSERT INTO #CounterVALUES ('08/10/2008 02:36', 4,'a')INSERT INTO #CounterVALUES ('08/10/2008 02:42', 5,'a')INSERT INTO #CounterVALUES ('08/10/2008 02:48', 6,'a')INSERT INTO #CounterVALUES ('08/10/2008 02:54', 6,'b')INSERT INTO #CounterVALUES ('08/10/2008 03:00', 12,'b')INSERT INTO #CounterVALUES ('08/10/2008 03:06', 12,'a')INSERT INTO #CounterVALUES ('08/10/2008 03:12', 1,'a')INSERT INTO #CounterVALUES ('08/10/2008 03:18', 12,'a')INSERT INTO #CounterVALUES ('08/10/2008 03:24', 11,'a')INSERT INTO #CounterVALUES ('08/10/2008 03:30', 3,'a')INSERT INTO #CounterVALUES ('08/10/2008 03:36', 3,'a')INSERT INTO #CounterVALUES ('08/10/2008 03:42', 3,'a')INSERT INTO #CounterVALUES ('08/10/2008 03:48', 3,'a')INSERT INTO #CounterVALUES ('08/10/2008 03:54', 3,'a')INSERT INTO #CounterVALUES ('08/10/2008 04:00', 4,'a')INSERT INTO #CounterVALUES ('08/10/2008 04:06', 4,'a')INSERT INTO #CounterVALUES ('08/10/2008 01:48', 4,'a')INSERT INTO #CounterVALUES ('08/10/2008 01:48', 11,'a')INSERT INTO #CounterVALUES ('08/10/2008 01:48', 3,'a')select * from #CounterStartDate value code Counter 2008-08-10 01:36:00.000 3 a null2008-08-10 01:42:00.000 2 a null2008-08-10 01:48:00.000 2 a null2008-08-10 01:54:00.000 12 a 12008-08-10 02:00:00.000 13 a 22008-08-10 02:06:00.000 3 a 3 2008-08-10 02:12:00.000 3 a 42008-08-10 02:18:00.000 15 a 52008-08-10 02:24:00.000 15 b 62008-08-10 02:30:00.000 3 a 72008-08-10 02:36:00.000 4 a 82008-08-10 02:42:00.000 5 a 92008-08-10 02:48:00.000 6 a 102008-08-10 02:54:00.000 6 b null2008-08-10 03:00:00.000 12 b null2008-08-10 03:06:00.000 12 a 12008-08-10 03:12:00.000 1 a 22008-08-10 03:18:00.000 12 a 32008-08-10 03:24:00.000 11 a 42008-08-10 03:30:00.000 3 a 52008-08-10 03:36:00.000 3 a 62008-08-10 03:42:00.000 3 a 72008-08-10 03:48:00.000 3 a 82008-08-10 03:54:00.000 3 a 92008-08-10 04:00:00.000 4 a 102008-08-10 04:06:00.000 4 a null 2008-08-10 01:48:00.000 4 a null2008-08-10 01:48:00.000 11 a 12008-08-10 01:48:00.000 3 a 2Thu, 23 Oct 2008 18:30:12 GMTJohnDBA