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


Wierd Counter!


Wierd Counter!

Author
Message
JohnDBA
JohnDBA
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 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-Addicted
SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)

Group: General Forum Members
Points: 462 Visits: 203
Hint: You will have to use ROW_NUMBER OVER ( PARTITION BY ) Smile
JohnDBA
JohnDBA
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

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

Group: General Forum Members
Points: 214646 Visits: 41979
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.
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 (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214646 Visits: 41979
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.
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
ck9663
ck9663
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 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 Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 Visits: 694
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
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: 1686 Visits: 1501
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
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: 1686 Visits: 1501
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 Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214646 Visits: 41979
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.
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