October 24, 2008 at 8:43 am
ggraber (10/24/2008)
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.
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 guarantee that this method will work correctly.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2008 at 8:49 am
Jeff Moden (10/24/2008)
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 guarantee that this method will work correctly.
Thanks for the reminder!
October 24, 2008 at 9:22 am
Jeff and Graber
Thank you so much, and thank you for the explanation. It helps a lot to understand where my problem was. I really appreciate your help.
October 24, 2008 at 12:00 pm
Jeff Moden (10/24/2008)
ggraber (10/24/2008)
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.
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 guarantee that this method will work correctly.
I figured I'd let you answer one of these, since I've snagged like the past 10 :hehe: (Although this one didn't quite work out the way I had hoped it would
October 24, 2008 at 1:41 pm
JohnDBA (10/24/2008)
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.
You're very welcome, John. Thanks for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2008 at 1:43 pm
Garadin (10/24/2008)
I figured I'd let you answer one of these, since I've snagged like the past 10 :hehe: (Although this one didn't quite work out the way I had hoped it would
Heh... thanks, Seth. I'm gettin' a bit slow in my old age.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2008 at 1:49 pm
October 27, 2008 at 3:37 pm
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.
Thank you so much!!!!!!
-------------------------------------------------------------------------------------------------
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', 12,'a')
INSERT INTO #Counter
VALUES ('08/10/2008 04:12', 4,'a')
INSERT INTO #Counter
VALUES ('08/10/2008 04:18', 11,'a')
INSERT INTO #Counter
VALUES ('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 variables
DECLARE @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.
October 27, 2008 at 3:51 pm
Jeff'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:
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
October 27, 2008 at 3:54 pm
Ggraver,
Than you so much! That was it!
October 27, 2008 at 5:45 pm
Anytime 🙂
October 27, 2008 at 5:49 pm
Thanks for the "cover". 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2008 at 5:58 pm
Jeff Moden (10/27/2008)
Thanks for the "cover". 🙂
My pleasure, Jeff. I'm so glad for a chance to give back (and that Seth didn't grab it first 😉 )
January 23, 2009 at 9:46 am
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.
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply