Comparing two rows in one table for changes in column

Heh... go back and check the data I used in my last post again.

It should not list K9ID 1, as the Frequency never changes from 4.

K9ID = 1 does indeed have a frequency change for MedID = 13 on 2009-07-15 where the frequency jumps from 3 to 4.

It lists 10 rows for K9ID 3 instead of the 11 rows that there are (missing the first one).

Actually it's not missing. The first date is the first Prev_RxStartDate information listed. Trying to put this all in one column is what caused the apparent duplication before. Having separate columns for Prev and New information solves that problem.

It is missing both first rows for the two entries for K9ID 6 (two separate drugs had changes).

Same thing here... those rows aren't missing from the result... there in the Prev_ columns. Again, the reason why I did it this way is because the other way makes it look like dupes occur.

It turns out that my previous code was really spitting out dupes... it just looked that way due to the stacking that occurs for vertical presentation. This latest version of code does exactly the same thing as my original except it gets rid of what people think are dupes by putting the prev and new RX's on the same row in separate sets of columns.

Oooooohhh.... I think I get it now. Remember when I said we need to define what a change in frequency is? We still need to do that (at least in my poor ol' mind). So... lets say we have the following data for K9ID=3 (simplified here for discussion)...

RowNum MedID RxStartDate RxEndDate Frequency

1 11 2006-01-01 2006-12-31 3

2 11 2007-01-01 2007-12-31 8 --Note the start date

3 11 2008-01-01 2008-12-31 8

4 11 2009-01-01 2009-12-31 8 --Note the end date

5 11 2010-01-01 2010-12-31 4

Are you saying that the RxStart and End Dates should be combined for rows 2, 3, and 4 so that the process looks at the data as if it were the following (note the now single row for Frequency = 8)?

RowNum MedID RxStartDate RxEndDate Frequency

1 11 2006-01-01 2006-12-31 3

2 11 2007-01-01 2009-12-31 8 --Note both start and end dates

5 11 2010-01-01 2010-12-31 4

If so... how do you want to handle dates that have a gap in them (which means the frequency apparently dropped to 0)?

RowNum MedID RxStartDate RxEndDate Frequency

1 11 2006-01-01 2006-12-31 3

2 11 2007-01-01 2007-12-31 8

3 11 2008-01-01 2008-05-01 8 --Note the gap between this end date...

4 11 2009-01-01 2009-12-31 8 --...and this start date

5 11 2010-01-01 2010-12-31 4

I take it we are in agreement now that K9ID=1 does not have a frequency change as defined in the query and should not have been returned in your results, right? Each MedID is treated separately (within all the other requirements of course). Also, is there any way to put all the Frequencies in one column again like you had it before?

Are you saying that the RxStart and End Dates should be combined for rows 2, 3, and 4 so that the process looks at the data as if it were the following (note the now single row for Frequency = 8)?

Yes. Having RxEndDate info would be above the original request. If it can be included in the manner in which you have it in the 2nd call out box (MAX RxStartDate, MIN RxEndDate), I think this additional info would be very good and nice, indeed. But, it was never in the original request. Originally, RxEndDate was not even looked at. But now that you have brought it up...

Let me put it this way... can you put it in as further explained below? If you can, wow! Great! Please do it!

If so... how do you want to handle dates that have a gap in them (which means the frequency apparently dropped to 0)?

I would just ignore the fact that it has a gap in it date-wise and act as though it was continuous instead. I'm sure there could be at least a few reasons for such an instance, such as the K9 is hospitalized during such break and no formal Rx script was written to have been entered. In an ideal world, medication data during hospitalization periods should be included, but unfortunately our real world does fall short at times.

Jeff, what makes the coding of this query such a pain? I don't mean that to sound the wrong way or snotty towards anyone at all. Would it be easier if it just had to compare the current row with the next row in the sort order, or is that, too, a pain to do (theoretically speaking, not thinking of the query at hand)? Obviously, I have not been able to figure that out either, but I cannot believe that with all the improvements in the software over the last decades that there is not some keyword such as COMPARE to save the grey matter from all this commotion. Surely people have had to do this time and time again. I remember this problem back in the days of dBaseII. Seems to me if I remember correctly all we had to do then was to store the current row value to a variable and then compare that variable to the next row using an IF THEN routine. Here in SQL when I try to set a variable and create much the same code, I am either not able to set the value of it within the SELECT statement ('A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.') or I am out of the statement and no longer at that row. Do you see it the same way?

Old age is a pain in the brain!

Dobermann

The code isn't actually difficult to write... the first code I wrote actually worked but there's the human perception that it does not because it looks like there are dupes when we list both the previous row and the row of the change. That's why I went to the "paired" column output to show that there aren't really any dupes but when listed in a single column, it looks that way.

I guess that depends on which of the several data sets you're talking about. The data set I used (one of yours) in my last bit of code is as follows and it clearly shows a frequency change for K9ID = 1 and MedID = 13.

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#RX','U') IS NOT NULL

DROP TABLE #RX,#Prenumber

GO

--===== Create the test table with

CREATE TABLE #RX

(

RxID INT NOT NULL PRIMARY KEY IDENTITY(1,1),

K9ID INT NOT NULL,

MedID INT NOT NULL,

VetID INT NOT NULL,

RxStartDate DATETIME NOT NULL,

RxEndDate DATETIME NULL,

Dose SMALLINT NOT NULL,

Frequency TINYINT NOT NULL

);

CREATE INDEX IX_RX_K9ID

ON #RX (K9ID);

CREATE INDEX IX_RX_MedID

ON #RX (MedID);

CREATE INDEX IX_RX_VetID

ON #RX (VetID);

CREATE INDEX IX_RX_RxStartDate

ON #RX (RxStartDate);

INSERT INTO #RX

SELECT 1, 11, 11, '7/3/9', '7/13/9', 40, 4 UNION ALL

SELECT 1, 1, 11, '7/3/9', '1/27/10', 40, 4 UNION ALL

SELECT 1, 8, 11, '7/3/9', '1/27/10', 40, 4 UNION ALL

SELECT 1, 16, 11, '7/3/9', '1/27/10', 40, 4 UNION ALL

SELECT 1, 23, 11, '7/3/9', '7/13/9', 40, 4 UNION ALL

SELECT 1, 11, 11, '7/13/9', '7/23/9', 60, 4 UNION ALL

SELECT 1, 11, 11, '7/23/9', '9/3/9', 80, 4 UNION ALL

SELECT 1, 13, 11, '7/5/9', '7/15/9', 100, 3 UNION ALL

SELECT 1, 13, 11, '7/15/9', '1/27/10', 100, 4 UNION ALL

SELECT 1, 11, 11, '9/3/9', '1/27/10', 40, 4 UNION ALL

SELECT 2, 6, 14, '10/31/9', '11/8/9', 20, 3 UNION ALL

SELECT 2, 14, 14, '10/31/9', '11/8/9', 120, 3 UNION ALL

SELECT 2, 2, 14, '10/31/9', '11/8/9', 20, 3 UNION ALL

SELECT 2, 4, 14, '10/31/9', '11/8/9', 120, 3 UNION ALL

SELECT 3, 11, 11, '9/14/7', '9/28/7', 250, 3 UNION ALL

SELECT 3, 11, 11, '11/14/5', '9/14/6', 250, 3 UNION ALL

SELECT 3, 11, 11, '5/1/10', NULL, 250, 7 UNION ALL

SELECT 3, 11, 11, '9/14/6', '9/14/7', 90, 4 UNION ALL

SELECT 3, 11, 11, '1/10/10', '2/14/10', 250, 1 UNION ALL

SELECT 3, 11, 11, '1/1/9', '9/14/9', 125, 8 UNION ALL

SELECT 3, 11, 11, '9/28/07', '01/8/8', 100, 2 UNION ALL

SELECT 3, 11, 11, '1/8/8', '9/14/8', 100, 5 UNION ALL

SELECT 3, 11, 11, '9/14/9', '10/10/9', 250, 3 UNION ALL

SELECT 3, 11, 11, '9/14/5', '11/14/5', 80, 3 UNION ALL

SELECT 3, 11, 11, '12/16/9', '12/31/9', 250, 1 UNION ALL

SELECT 3, 11, 11, '1/1/10', '1/10/10', 250, 1 UNION ALL

SELECT 3, 11, 11, '11/14/9', '12/16/9', 100, 6 UNION ALL

SELECT 3, 11, 11, '10/10/9', '11/14/9', 110, 6 UNION ALL

SELECT 3, 11, 11, '9/14/8', '12/31/8', 250, 8 UNION ALL

SELECT 3, 11, 11, '3/8/10', '5/1/10', 75, 4 UNION ALL

SELECT 3, 11, 11, '2/14/10', '3/8/10', 250, 4 UNION ALL

SELECT 4, 4, 2, '4/7/10', '4/15/10', 40, 3 UNION ALL

SELECT 5, 16, 14, '4/7/6', '6/15/7', 100, 1 UNION ALL

SELECT 5, 13, 14, '4/7/6', '6/15/7', 100, 1 UNION ALL

SELECT 5, 2, 14, '4/7/6', '6/15/7', 100, 1 UNION ALL

SELECT 6, 8, 8, '3/15/10', '3/17/10', 200, 4 UNION ALL

SELECT 6, 18, 8, '3/15/10', '3/17/10', 200, 4 UNION ALL

SELECT 6, 8, 8, '3/17/10', '4/15/10', 250, 5 UNION ALL

SELECT 6, 18, 8, '3/17/10', '4/15/10', 250, 5 UNION ALL

SELECT 7, 4, 17, '1/1/8', '5/7/8', 200, 3 UNION ALL

SELECT 8, 11, 20, '1/14/10', NULL, 750, 3;

SELECT *

FROM #Rx

WHERE K9ID = 1 AND MedID = 13

ORDER BY K9ID, MedID, RxStartDate

Yes, there is a way to put all of the frequencies back in one column again but then we're going to end up dealing with what you may or may not consider to be a dupe. The current rules I'm following is to list the previous row and the row of the change. Without additional information in the definition of what should be displayed, it will always look like there are dupes when multiple frequency changes are separated by a group of 2 or more steady state frequencies.

Even if we consolidate such groupings of steady state frequencies, if the rule is to display the row before the change and the row of the change, there will still be the perception of duplication. Even if consolidation of the steady state frequency rows works for everything else, there is still the requirement to display the original frequency row but only if a frequency change actually occured. That requirement alone makes this a bit of a pain to write code for this. If the rule were to only display the "new" frequency rows, this would be a cake walk.

As a side bar, you've posted a heck of a lot of sample data and it appears to be all different. I believe you may have enough information to have created a sample set with all contingencies accounted for. If you could post that (one more time) with marks indicating which rows should be displayed and maybe the reason # from a numbered list of reasons (interpretted as requirements), then maybe we could finally knock this bad boy out of the park. 🙂

So Jeff, do you provide new reading glasses with your code??? For the life of me, all I can do is apologize for not seeing my own data. I might offer up that I was hooked on MedID=11 and obviously ignored MedID=13, but the fact remains that I have to learn to step back a little further and look better. I've never been this bad before ~ I just don't know what has gotten into me.

Okay, as you requested, below is my "master" sample data that I have which I used to create the notated results listing you requested. It also includes a simple list statement. It includes all the various mini updates I have posted. I think I made it mentally more complicated by constantly trying to reduce the amount that I posted vs just posting the entire sample data.

-- If the test table already exists, drop it

IF OBJECT_ID('TempDB..#RX','U') IS NOT NULL

DROP TABLE #RX

-- Create the test table with

CREATE TABLE #RX

(

RxIDINT NOT NULL CONSTRAINT PK_RX_RxID PRIMARY KEY IDENTITY,

K9ID INT NOT NULL,

MedIDINT NOT NULL,

VetIDINT NOT NULL,

RxStartDateDATE NOT NULL,

RxEndDateDATENULL,

DoseSMALLINTNOT NULL,

FrequencyTINYINTNOT NULL

)

CREATE INDEX IX_RX_K9ID

ON #RX (K9ID)

CREATE INDEX IX_RX_MedID

ON #RX (MedID)

CREATE INDEX IX_RX_VetID

ON #RX (VetID)

CREATE INDEX IX_RX_RxStartDate

ON #RX (RxStartDate)

INSERT INTO #RX

SELECT 1, 11, 11, '7/3/9', '7/13/9', 40, 4 UNION ALL

SELECT 1, 1, 11, '7/3/9', '1/27/10', 40, 4 UNION ALL

SELECT 1, 8, 11, '7/3/9', '1/27/10', 40, 4 UNION ALL

SELECT 1, 16, 11, '7/3/9', '1/27/10', 40, 4 UNION ALL

SELECT 1, 23, 11, '7/3/9', '7/13/9', 40, 4 UNION ALL

SELECT 1, 11, 11, '7/13/9', '7/23/9', 60, 4 UNION ALL

SELECT 1, 11, 11, '7/23/9', '9/3/9', 80, 4 UNION ALL

SELECT 1, 13, 11, '7/5/9', '7/15/9', 100, 3 UNION ALL

SELECT 1, 13, 11, '7/15/9', '1/27/10', 100, 4 UNION ALL

SELECT 1, 11, 11, '9/3/9', '1/27/10', 40, 4 UNION ALL

SELECT 2, 14, 14, '10/31/9', '11/2/9', 20, 5 UNION ALL

SELECT 2, 14, 14, '11/3/9', '11/8/9', 120, 3 UNION ALL

SELECT 2, 2, 14, '10/31/9', '11/8/9', 20, 3 UNION ALL

SELECT 2, 4, 14, '10/31/9', '11/8/9', 120, 3 UNION ALL

SELECT 3, 11, 11, '9/14/7', '9/28/7', 250, 3 UNION ALL

SELECT 3, 11, 11, '11/14/5', '9/14/6', 250, 3 UNION ALL

SELECT 3, 11, 11, '5/1/10', NULL, 250, 7 UNION ALL

SELECT 3, 11, 11, '9/14/6', '9/14/7', 90, 4 UNION ALL

SELECT 3, 11, 11, '1/10/10', '2/14/10', 250, 1 UNION ALL

SELECT 3, 11, 11, '1/1/9', '9/14/9', 125, 8 UNION ALL

SELECT 3, 11, 11, '10/15/7', '1/8/8', 100, 5 UNION ALL

SELECT 3, 11, 11, '9/28/07', '10/15/7', 100, 2 UNION ALL

SELECT 3, 11, 11, '1/8/8', '9/14/8', 100, 5 UNION ALL

SELECT 3, 11, 11, '9/14/9', '10/10/9', 250, 3 UNION ALL

SELECT 3, 11, 11, '9/14/5', '11/14/5', 80, 3 UNION ALL

SELECT 3, 11, 11, '12/16/9', '12/31/9', 250, 1 UNION ALL

SELECT 3, 11, 11, '1/1/10', '1/10/10', 250, 1 UNION ALL

SELECT 3, 11, 11, '11/14/9', '12/16/9', 100, 6 UNION ALL

SELECT 3, 11, 11, '10/10/9', '11/14/9', 110, 6 UNION ALL

SELECT 3, 11, 11, '9/14/8', '12/31/8', 250, 8 UNION ALL

SELECT 3, 11, 11, '3/8/10', '4/1/10', 75, 4 UNION ALL

SELECT 3, 11, 11, '4/1/10', '4/15/10', 75, 4 UNION ALL

SELECT 3, 11, 11, '4/15/10', '5/1/10', 75, 4 UNION ALL

SELECT 3, 11, 11, '2/14/10', '3/8/10', 250, 4 UNION ALL

SELECT 4, 4, 2, '4/7/10', '4/15/10', 40, 3 UNION ALL

SELECT 5, 16, 14, '4/7/6', '6/15/7', 100, 1 UNION ALL

SELECT 5, 13, 14, '4/7/6', '6/15/7', 100, 1 UNION ALL

SELECT 5, 2, 14, '4/7/6', '6/15/7', 100, 1 UNION ALL

SELECT 6, 8, 8, '3/15/10', '3/17/10', 200, 4 UNION ALL

SELECT 6, 18, 8, '3/15/10', '3/17/10', 200, 4 UNION ALL

SELECT 6, 8, 8, '3/17/10', '4/15/10', 250, 5 UNION ALL

SELECT 6, 18, 8, '3/17/10', '4/15/10', 250, 5 UNION ALL

SELECT 7, 4, 17, '1/1/8', '5/7/8', 200, 3 UNION ALL

SELECT 8, 11, 11, '9/14/7', '9/28/7', 250, 3 UNION ALL

SELECT 8, 11, 11, '1/8/8', '9/14/8', 100, 5 UNION ALL

SELECT 8, 11, 11, '9/14/5', '11/14/5', 80, 3 UNION ALL

SELECT 8, 11, 11, '2/14/10', '3/8/10', 250, 4 UNION ALL

SELECT 8, 11, 11, '9/14/8', '12/31/8', 250, 8 UNION ALL

SELECT 8, 11, 11, '1/10/10', '2/14/10', 250, 1 UNION ALL

SELECT 8, 11, 11, '1/1/9', '9/14/9', 100, 8 UNION ALL

SELECT 8, 11, 11, '10/15/7', '1/8/8', 100, 5 UNION ALL

SELECT 8, 11, 11, '9/14/9', '10/10/9', 250, 3 UNION ALL

SELECT 8, 11, 11, '1/1/10', '1/10/10', 250, 1 UNION ALL

SELECT 8, 11, 11, '11/14/5', '9/14/6', 250, 3 UNION ALL

SELECT 8, 11, 11, '10/10/9', '11/14/9', 110, 6 UNION ALL

SELECT 8, 11, 11, '5/1/10', NULL, 250, 7 UNION ALL

SELECT 8, 11, 11, '11/14/9', '12/16/9', 100, 6 UNION ALL

SELECT 8, 11, 11, '9/28/07', '10/15/7', 75, 2 UNION ALL

SELECT 8, 11, 11, '9/14/6', '9/14/7', 90, 4 UNION ALL

SELECT 8, 11, 11, '12/16/9', '12/31/9', 250, 1 UNION ALL

SELECT 8, 11, 11, '3/8/10', '5/1/10', 75, 4;

SELECT K9ID, MedID, RxStartDate, Dose, Frequency as 'Freq'

FROM #RX

ORDER BY K9ID, MedID, RxStartDate

Key to inclusion/exclusions from below ordered list:

1=Include, new frequency by K9ID, MedID, RxStartDate

3=Exclude, second or more matching frequency by K9ID, MedID, RxStartDate

4=Exclude, sole script by K9ID, MedID (ie, only 1 script written for that MedID for that K9)

5=Exclude, multiple scripts, but all same frequency by K9ID, MedID, RxStartDate

K9IDMedIDRxStartDateDoseFreqKey

112009-07-034044

182009-07-034044

1112009-07-034045

1112009-07-136045

1112009-07-238045

1112009-09-034045

1132009-07-0510031

1132009-07-1510041

1162009-07-034044

1232009-07-034044

222009-10-312034

242009-10-3112034

2142009-10-312051

2142009-11-0312031

3112005-09-148031

3112005-11-1425033

3112006-09-149041

3112007-09-1425031

3112007-09-2810021

3112007-10-1510051

3112008-01-0810053

3112008-09-1425081

3112009-01-0112583

3112009-09-1425031

3112009-10-1011061

3112009-11-1410063

3112009-12-1625011

3112010-01-0125013

3112010-01-1025013

3112010-02-1425041

3112010-03-087543

3112010-04-017543

3112010-04-157543

3112010-05-0125071

442010-04-074034

522006-04-0710014

5132006-04-0710014

5162006-04-0710014

682010-03-1520041

682010-03-1725051

6182010-03-1520041

6182010-03-1725051

742008-01-0120034

8112005-09-148031

8112005-11-1425033

8112006-09-149041

8112007-09-1425031

8112007-09-287521

8112007-10-1510051

8112008-01-0810053

8112008-09-1425081

8112009-01-0110083

8112009-09-1425031

8112009-10-1011061

8112009-11-1410063

8112009-12-1625011

8112010-01-0125013

8112010-01-1025013

8112010-02-1425041

8112010-03-087543

8112010-05-0125071

I hope the above is what you were asking for. Are you going to do some of that "black arts/high flying" coding that I am dying to see?

If the rule were to only display the "new" frequency rows, this would be a cake walk.

Are you saying if we did not show the first row like what we had before?

Just as an aside, there is a slight error in your code - your lo & hi VetID AS statements say RxID vs VetID - no big deal though.

lo.VetID AS Prev_RxID, hi.VetID AS Prev_RxID,

🙂

Thanks again,

Dobermann

That's exactly what I had in mind. I'll try to get to this after work tonight.

Heh... just had to post back before I went to work. There's a trick that uses the difference between two different partitions used while creating ROW_NUMBER that might take care of this problem nicely.

Heh... this whole thread is proof postive that one picture is worth a thousand words. As soon as I saw the pattern in the data you posted with the "key" as to whether to keep or not, I recognized this whole thing as a very simple and classic "how many times did the machine turn on or off" problem with a now classic solution. The "picture" even made it easy to figure out how to exclude K9ID/MedID groups that had no frequency change. The neat thing about this solution in 2k5 is that it eliminates the need for a "Quirky" update and, done correctly, is actually just a bit faster than a "Quirky" update (hard to believe but it is).

So without further fanfare, here's the long awaited solution to this problem. I've verified each row that's supposed to be returned in the result set. The other cool part about this whole thing is that no interim Temp Table is required and the code is terribly simple.

/**********************************************************************************************************************

GOAL:

1. For sets identified by K9ID/MedID, if frequency changes are present, list the first row in each "group" of

adjacent identical frequencies defined by K9ID/MedID/Frequency in date order.

2. If no frequency changes are present for a given K9ID/MedID combo, do not list any rows for the K9ID/MedID combo.

Key to inclusion/exclusions from below ordered list:

1*=Include, new frequency by K9ID, MedID, RxStartDate (* means that I've verified the row manually)

2=(removed)

3=Exclude, second or more matching frequency by K9ID, MedID, RxStartDate

4=Exclude, sole script by K9ID, MedID (ie, only 1 script written for that MedID for that K9)

5=Exclude, multiple scripts, but all same frequency by K9ID, MedID, RxStartDate

K9ID MedID RxStartDate Dose Freq Key

1 1 2009-07-03 40 4 4

1 8 2009-07-03 40 4 4

1 11 2009-07-03 40 4 5

1 11 2009-07-13 60 4 5

1 11 2009-07-23 80 4 5

1 11 2009-09-03 40 4 5

1 13 2009-07-05 100 3 1*

1 13 2009-07-15 100 4 1*

1 16 2009-07-03 40 4 4

1 23 2009-07-03 40 4 4

2 2 2009-10-31 20 3 4

2 4 2009-10-31 120 3 4

2 14 2009-10-31 20 5 1*

2 14 2009-11-03 120 3 1*

3 11 2005-09-14 80 3 1*

3 11 2005-11-14 250 3 3

3 11 2006-09-14 90 4 1*

3 11 2007-09-14 250 3 1*

3 11 2007-09-28 100 2 1*

3 11 2007-10-15 100 5 1*

3 11 2008-01-08 100 5 3

3 11 2008-09-14 250 8 1*

3 11 2009-01-01 125 8 3

3 11 2009-09-14 250 3 1*

3 11 2009-10-10 110 6 1*

3 11 2009-11-14 100 6 3

3 11 2009-12-16 250 1 1*

3 11 2010-01-01 250 1 3

3 11 2010-01-10 250 1 3

3 11 2010-02-14 250 4 1*

3 11 2010-03-08 75 4 3

3 11 2010-04-01 75 4 3

3 11 2010-04-15 75 4 3

3 11 2010-05-01 250 7 1*

4 4 2010-04-07 40 3 4

5 2 2006-04-07 100 1 4

5 13 2006-04-07 100 1 4

5 16 2006-04-07 100 1 4

6 8 2010-03-15 200 4 1*

6 8 2010-03-17 250 5 1*

6 18 2010-03-15 200 4 1*

6 18 2010-03-17 250 5 1*

7 4 2008-01-01 200 3 4

8 11 2005-09-14 80 3 1*

8 11 2005-11-14 250 3 3

8 11 2006-09-14 90 4 1*

8 11 2007-09-14 250 3 1*

8 11 2007-09-28 75 2 1*

8 11 2007-10-15 100 5 1*

8 11 2008-01-08 100 5 3

8 11 2008-09-14 250 8 1*

8 11 2009-01-01 100 8 3

8 11 2009-09-14 250 3 1*

8 11 2009-10-10 110 6 1*

8 11 2009-11-14 100 6 3

8 11 2009-12-16 250 1 1*

8 11 2010-01-01 250 1 3

8 11 2010-01-10 250 1 3

8 11 2010-02-14 250 4 1*

8 11 2010-03-08 75 4 3

8 11 2010-05-01 250 7 1*

**********************************************************************************************************************/

WITH

cteCreateGroups AS

( --=== Use the difference between two different methods of row numbering to create "groups" of contiguous frequencies

-- that will create a new group even if the frequency value repeats but has a "break" in it. Note that the

-- actual value of the group number means absolutely nothing except that they uniquely identify adjacent rows

-- of identical frequencies. In SQL Server 2000 and earlier, the "Quirky" update would be the best way to do

-- this quickly and easily.

SELECT K9ID, MedId, RxStartDate, Frequency,

ROW_NUMBER() OVER (ORDER BY K9ID, MedId, RxStartDate)

- ROW_NUMBER() OVER (PARTITION BY K9ID, MedId, Frequency ORDER BY K9ID, MedId, RxStartDate) AS FrequencyGroup

FROM #Rx

)

,

cteNumberAndExclude AS

( --=== Using the pseudo groups created above, number the rows in each pseudo group so we know what the first row

-- of each group is. While we're here, the WHERE EXISTS excludes any rows where no freqency change has

-- occurred for a given K9ID/MedID combination.

SELECT K9ID, MedId, RxStartDate, Frequency, FrequencyGroup,

ROW_NUMBER() OVER (PARTITION BY FrequencyGroup ORDER BY K9ID, MedId, RxStartDate) AS FrequencyGroupOccurance

FROM cteCreateGroups cg

WHERE EXISTS (SELECT 1

FROM #Rx rx

WHERE rx.K9ID = cg.K9ID

AND rx.MedID = cg.MedID

GROUP BY rx.K9ID, rx.MedID

HAVING COUNT(DISTINCT rx.Frequency) > 1) --Needs at least 1 frequency change to qualify to keep

) --=== All set. All we need to do now is display the first row of each grouping that remains

-- in the correct order.

SELECT rx.*

FROM cteNumberAndExclude ne

INNER JOIN #Rx rx

ON rx.K9ID = ne.K9ID

AND rx.MedID = ne.MedID

AND rx.RxStartDate = ne.RxStartDate

WHERE ne.FrequencyGroupOccurance = 1

ORDER BY rx.K9ID, rx.MedID, rx.RxStartDate

;

... and here are the results...

RxID K9ID MedID VetID RxStartDate RxEndDate Dose Frequency

----------- ----------- ----------- ----------- ----------------------- ----------------------- ------ ---------

8 1 13 11 2009-07-05 00:00:00.000 2009-07-15 00:00:00.000 100 3

9 1 13 11 2009-07-15 00:00:00.000 2010-01-27 00:00:00.000 100 4

11 2 14 14 2009-10-31 00:00:00.000 2009-11-02 00:00:00.000 20 5

12 2 14 14 2009-11-03 00:00:00.000 2009-11-08 00:00:00.000 120 3

25 3 11 11 2005-09-14 00:00:00.000 2005-11-14 00:00:00.000 80 3

18 3 11 11 2006-09-14 00:00:00.000 2007-09-14 00:00:00.000 90 4

15 3 11 11 2007-09-14 00:00:00.000 2007-09-28 00:00:00.000 250 3

22 3 11 11 2007-09-28 00:00:00.000 2007-10-15 00:00:00.000 100 2

21 3 11 11 2007-10-15 00:00:00.000 2008-01-08 00:00:00.000 100 5

30 3 11 11 2008-09-14 00:00:00.000 2008-12-31 00:00:00.000 250 8

24 3 11 11 2009-09-14 00:00:00.000 2009-10-10 00:00:00.000 250 3

29 3 11 11 2009-10-10 00:00:00.000 2009-11-14 00:00:00.000 110 6

26 3 11 11 2009-12-16 00:00:00.000 2009-12-31 00:00:00.000 250 1

34 3 11 11 2010-02-14 00:00:00.000 2010-03-08 00:00:00.000 250 4

17 3 11 11 2010-05-01 00:00:00.000 NULL 250 7

39 6 8 8 2010-03-15 00:00:00.000 2010-03-17 00:00:00.000 200 4

41 6 8 8 2010-03-17 00:00:00.000 2010-04-15 00:00:00.000 250 5

40 6 18 8 2010-03-15 00:00:00.000 2010-03-17 00:00:00.000 200 4

42 6 18 8 2010-03-17 00:00:00.000 2010-04-15 00:00:00.000 250 5

46 8 11 11 2005-09-14 00:00:00.000 2005-11-14 00:00:00.000 80 3

59 8 11 11 2006-09-14 00:00:00.000 2007-09-14 00:00:00.000 90 4

44 8 11 11 2007-09-14 00:00:00.000 2007-09-28 00:00:00.000 250 3

58 8 11 11 2007-09-28 00:00:00.000 2007-10-15 00:00:00.000 75 2

51 8 11 11 2007-10-15 00:00:00.000 2008-01-08 00:00:00.000 100 5

48 8 11 11 2008-09-14 00:00:00.000 2008-12-31 00:00:00.000 250 8

52 8 11 11 2009-09-14 00:00:00.000 2009-10-10 00:00:00.000 250 3

55 8 11 11 2009-10-10 00:00:00.000 2009-11-14 00:00:00.000 110 6

60 8 11 11 2009-12-16 00:00:00.000 2009-12-31 00:00:00.000 250 1

47 8 11 11 2010-02-14 00:00:00.000 2010-03-08 00:00:00.000 250 4

56 8 11 11 2010-05-01 00:00:00.000 NULL 250 7

(30 row(s) affected)

I've never heard of that classic problem before. And what is a Quirky update?

No, please, let's get ALL the fanfare that we can for this!!! I hear trumpets, see flags waving in the wind, people applauding, the noise is deafening!!!

I don't know how to thank you. You have persevered to fruition on this - above and beyond what could have ever been expected. I am extremely grateful to you. I am going to study it and learn from it, too.

Again, thank you ever so much!!! You have made my week, no, month!

Dobermann

PS - Did you happen to notice how many Views this topic has received?

The classic "how many times did the machine turn on or off" problem is usually accompanied by data that looks like the result set of the following code...

SELECT machine.MachineID,

DATEADD(mi,t.Number,'20100522') AS SampleTime,

CASE WHEN ABS(CHECKSUM(NEWID()))%2 = 0 THEN 'Off' ELSE 'On' END AS [State]

FROM (SELECT 1 UNION ALL SELECT 2) machine(MachineID)

CROSS JOIN Master.dbo.spt_Values t

WHERE t.Type = 'P'

AND t.Number BETWEEN 0 AND 10

ORDER BY MachineID, SampleTime

As stated in the title of the problem, the goal is to find out how many times the machine turned on or off during the given times. The problem would normally be easily solved with just a self join to see where the states changed except for one thing... you have to consider the original state as part of the counts of on/off and if you want to display only the rows where there is a state change. As you can see, it's very similar to your problem of displaying rows only where states changed. Heh, you added in the extra bit of complexity where you only wanted to display a given K9ID/MedID combination if there was at least 1 state change but the problem is essentially the same.

Two of the more traditional but slower methods to solve this problem in SQL Server 2000 involve either a Cursor/While Loop to "step" through the rows to compare for changes in adjacent rows or do a self join and an extra lookup for the first row for a MachineID.

The super high speed "Black Arts" method that I use in 2K to solve this problem is known as the "Quirky" update. The "Quirky" update has some extraordinay uses and, if you follow the rules, is very safe and 100% reliable to use. Please see the following article (I do have to make one change concerning the execution plan but the rest of the article is spot on) for more about the "Quirky" update. If you ever decide to use it, you absolutely MUST follow all the rules near the end of the article or I can guarantee that it will fail someday when you least expect it.

http://www.sqlservercentral.com/articles/T-SQL/68467/

Heh... it was a fun problem and you were patient with my ignorance and misunderstanding of your requirements which went a very long way with me.

Actually, there are two things you can do for me and you've already stated that you're going to do the first thing...

1. "Study it and learn from it".

When I first started out in SQL Server, the internet was still relatively young and there wasn't much help available in learning SQL Sever. I took a week long course on implementing SQL Server. The instructor and the course material were absolutely stupid. Further, all but one of the books I bought on the subject weren't much help, either. I made a vow that I'd try not to be like that instructor or the people that wrote those books and that I would help others where ever I could because we're all in this together.

So the other thing that I would ask of you is simple...

2. Pass the knowledge forward to others.

🙂

I think that you might get away without a macro. But that depends on how strict your data patters are. You stated that there are two possible sub groups: '-1' and '-2'. But when I check the uploaded file farabitimetable.ods in the other topic I see sub groups named '-4', '-22' etc. So can you be more specific about this.

You are also stating the algorithm that you want to apply, it is based on the order of the data. But when I look to your data it seems to me that you have a kind of key value that matches with the next record to be found: Day+Period+Student-group and Subject. We can use this key value with a VLOOKUP() to construct the concatenation of the two fields.

When we apply auto filter we can select all the records that you need and copy them into a new sheet.

Let me now if this manual action is acceptable and we might work it out.

Mercedes Benz 450SLC Parts

I'm pretty sure you posted this to the wrong thread. Two people posted with the exact same thread name.