Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Wierd Counter!


Wierd Counter!

Author
Message
JohnDBA
JohnDBA
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 232
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.000 3 a null
2008-08-10 01:42:00.000 2 a null
2008-08-10 01:48:00.000 2 a null
2008-08-10 01:54:00.000 12 a 1
2008-08-10 02:00:00.000 13 a 2
2008-08-10 02:06:00.000 3 a 3
2008-08-10 02:12:00.000 3 a 4
2008-08-10 02:18:00.000 15 a 5
2008-08-10 02:24:00.000 15 b 6
2008-08-10 02:30:00.000 3 a 7
2008-08-10 02:36:00.000 4 a 8
2008-08-10 02:42:00.000 5 a 9
2008-08-10 02:48:00.000 6 a 10
2008-08-10 02:54:00.000 6 b null
2008-08-10 03:00:00.000 12 b null
2008-08-10 03:06:00.000 12 a 1
2008-08-10 03:12:00.000 1 a 2
2008-08-10 03:18:00.000 12 a 3
2008-08-10 03:24:00.000 11 a 4
2008-08-10 03:30:00.000 3 a 5
2008-08-10 03:36:00.000 3 a 6
2008-08-10 03:42:00.000 3 a 7
2008-08-10 03:48:00.000 3 a 8
2008-08-10 03:54:00.000 3 a 9
2008-08-10 04:00:00.000 4 a 10
2008-08-10 04:06:00.000 4 a null
2008-08-10 01:48:00.000 4 a null
2008-08-10 01:48:00.000 11 a 1
2008-08-10 01:48:00.000 3 a 2
Nikhil Shikarkhane
Nikhil Shikarkhane
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 203
Hint: You will have to use ROW_NUMBER OVER ( PARTITION BY ) Smile
JohnDBA
JohnDBA
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 232
Nikhil,
I already tried that and it is more complicated than I thought.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45002 Visits: 39880
Nikhil Shikarkhane (10/23/2008)
Hint: You will have to use ROW_NUMBER OVER ( PARTITION BY ) Smile


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

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45002 Visits: 39880
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ck9663
ck9663
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 111
Is there any sequential unique column that we can use for sorting? If none, can you insert one, an IDENTITY, preferably.

-- CK
AnzioBake
AnzioBake
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 672
The solution to this is very much like a solution I posted earlier, but not without a row identifier as mentioned before.



Goldie Lesser
Goldie Lesser
SSC-Addicted
SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)

Group: General Forum Members
Points: 486 Visits: 1482
This should work for you:



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






Goldie Lesser
Goldie Lesser
SSC-Addicted
SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)

Group: General Forum Members
Points: 486 Visits: 1482
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/
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45002 Visits: 39880
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? Wink

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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