Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Wierd Counter! Expand / Collapse
Author
Message
Posted Thursday, October 23, 2008 6:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 18, 2013 9:18 PM
Points: 51, Visits: 215
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
Post #590904
Posted Thursday, October 23, 2008 6:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 6, 2011 11:54 AM
Points: 154, Visits: 203
Hint: You will have to use ROW_NUMBER OVER ( PARTITION BY ) :)
Post #590909
Posted Thursday, October 23, 2008 6:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 18, 2013 9:18 PM
Points: 51, Visits: 215
Nikhil,
I already tried that and it is more complicated than I thought.
Post #590911
Posted Thursday, October 23, 2008 8:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #590923
Posted Thursday, October 23, 2008 8:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #590924
Posted Friday, October 24, 2008 12:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 7, 2013 4:04 PM
Points: 42, Visits: 107
Is there any sequential unique column that we can use for sorting? If none, can you insert one, an IDENTITY, preferably.

-- CK
Post #590991
Posted Friday, October 24, 2008 3:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 5:35 AM
Points: 211, Visits: 546
The solution to this is very much like a solution I posted earlier, but not without a row identifier as mentioned before.


Post #591044
Posted Friday, October 24, 2008 6:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 11:42 AM
Points: 478, Visits: 1,410
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





Post #591149
Posted Friday, October 24, 2008 6:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 11:42 AM
Points: 478, Visits: 1,410
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/
Post #591153
Posted Friday, October 24, 2008 7:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #591186
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse