Wierd Counter!

  • 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.

    CREATE TABLE #Counter

    (

    StartDate datetime,

    value float,

    code char(1)

    )

    INSERT INTO #Counter

    VALUES ('08/10/2008 01:36', 3, 'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 01:42', 2, 'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 01:48', 2, 'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 01:54', 12, 'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 02:00', 13, 'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 02:06', 3,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 02:12', 3,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 02:18', 15,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 02:24', 15,'b')

    INSERT INTO #Counter

    VALUES ('08/10/2008 02:30', 3,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 02:36', 4,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 02:42', 5,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 02:48', 6,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 02:54', 6,'b')

    INSERT INTO #Counter

    VALUES ('08/10/2008 03:00', 12,'b')

    INSERT INTO #Counter

    VALUES ('08/10/2008 03:06', 12,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 03:12', 1,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 03:18', 12,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 03:24', 11,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 03:30', 3,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 03:36', 3,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 03:42', 3,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 03:48', 3,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 03:54', 3,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 04:00', 4,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 04:06', 4,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 01:48', 4,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 01:48', 11,'a')

    INSERT INTO #Counter

    VALUES ('08/10/2008 01:48', 3,'a')

    select * from #Counter

    StartDate value code Counter

    2008-08-10 01:36:00.0003anull

    2008-08-10 01:42:00.0002anull

    2008-08-10 01:48:00.0002anull

    2008-08-10 01:54:00.00012a1

    2008-08-10 02:00:00.00013a2

    2008-08-10 02:06:00.0003a3

    2008-08-10 02:12:00.0003a4

    2008-08-10 02:18:00.00015a5

    2008-08-10 02:24:00.00015b6

    2008-08-10 02:30:00.0003a7

    2008-08-10 02:36:00.0004a8

    2008-08-10 02:42:00.0005a9

    2008-08-10 02:48:00.0006a10

    2008-08-10 02:54:00.0006bnull

    2008-08-10 03:00:00.00012bnull

    2008-08-10 03:06:00.00012a1

    2008-08-10 03:12:00.0001a2

    2008-08-10 03:18:00.00012a3

    2008-08-10 03:24:00.00011a4

    2008-08-10 03:30:00.0003a5

    2008-08-10 03:36:00.0003a6

    2008-08-10 03:42:00.0003a7

    2008-08-10 03:48:00.0003a8

    2008-08-10 03:54:00.0003a9

    2008-08-10 04:00:00.0004a10

    2008-08-10 04:06:00.0004anull

    2008-08-10 01:48:00.0004anull

    2008-08-10 01:48:00.00011a1

    2008-08-10 01:48:00.0003a2

  • Hint: You will have to use ROW_NUMBER OVER ( PARTITION BY ) 🙂

  • Nikhil,

    I already tried that and it is more complicated than I thought.

  • Nikhil Shikarkhane (10/23/2008)


    Hint: You will have to use ROW_NUMBER OVER ( PARTITION BY ) 🙂

    I'd really like to see that for this one. Got code? 😉

    --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)

  • 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.

    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?

    --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)

  • Is there any sequential unique column that we can use for sorting? If none, can you insert one, an IDENTITY, preferably.

    -- CK

  • The solution to this is very much like a solution I posted earlier, but not without a row identifier as mentioned before.

  • This should work for you:

    [font="Courier New"]

    -- create a table to store our results

    -- create a dummy unique identifier on the table

    CREATE TABLE #Counter2

    (

       RowNumber INT IDENTITY(1,1)

       ,StartDate DATETIME

       ,[Value] FLOAT

       ,Code CHAR(1)

       ,Counter INT

    )

    --insert all existing data from original table into our results table

    INSERT INTO #Counter2(StartDate, [Value], Code)

    SELECT StartDate

       , [Value]

       , Code

    FROM #Counter

    -- create a counter variable

    -- this will be used to count our rows

    DECLARE @Counter INT

    SET @Counter = 0

    -- this is a dummy variable

    -- we need it as an anchor to keep our running total

    DECLARE @RowNumber INT

    -- 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

    UPDATE #Counter2

    SET @Counter = Counter = CASE WHEN (@Counter >= 1 AND @Counter < 10) OR ([Value] >= 10 AND [Value] <=15)

                               THEN @Counter + 1

                               ELSE 0 -- restart the counter

                               END

       ,@RowNumber = RowNumber

    FROM #Counter2

    -- take a look at our results

    SELECT *

    FROM #Counter2

    -- clean up

    DROP TABLE #Counter2

    [/font]

  • 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/

  • AnzioBake (10/24/2008)


    The solution to this is very much like a solution I posted earlier, but not without a row identifier as mentioned before.

    So, you shouldn't have a problem posting the code or at least a URL here, huh? 😉

    --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)

  • 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.

    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)

  • 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!

  • You just need to add one criteria to the CASE statement. Like this:

    [font="Courier New"]

    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

    [/font]

  • 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.

  • 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.

    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)

Viewing 15 posts - 1 through 15 (of 28 total)

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