Post reply

Comparing two rows in one table for changes in column

  • May 16, 2010 at 12:15 pm

    #1168033

    amrakhjaaa (5/16/2010)

    You could attempt this with a JOIN and a SELECT DISTINCT, but I think that probably a WHERE EXISTS would perform better, based on the assumption that there are maybe less than 100 rows for each execution. Also make sure you have an index covering the K9ID, DRUG, and DATE columns so this is an efficient lookup.

    Hyundai Tucson Parts

    Cool... there's plenty of test data on this thread... let's see your 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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 16, 2010 at 6:45 pm

    #1168090

    mister.magoo (5/15/2010)

    DECLARE @TABLE TABLE

    (

    K9ID INT,

    Drug INT,

    Dose INT,

    Freq INT,

    Date DATETIME

    );

    INSERT INTO @TABLE

    SELECT 1,3,100,5,'2008-10-5' UNION ALL

    SELECT 1,3,150,6,'2008-10-01' UNION ALL

    SELECT 1,3,75,8,'2009-8-14' UNION ALL

    SELECT 1,3,150,8,'2008-7-11' UNION ALL

    SELECT 1,3,60,7, '2008-8-6' UNION ALL

    SELECT 1,3,200,8, '2009-8-22';

    ;WITH DATA

    AS

    (

    SELECT

    K9ID,

    Date,

    Drug,

    Dose,

    Freq ,

    ROW_NUMBER() OVER ( PARTITION BY K9ID,Drug,Freq

    ORDER BY K9ID, Drug, Date )

    AS RowNum,

    MIN(Freq) OVER ( PARTITION BY K9ID,Drug )

    AS MinFreq,

    MAX(Freq) OVER ( PARTITION BY K9ID,Drug )

    AS MaxFreq

    FROM @TABLE

    )

    SELECT

    K9ID,

    Date,

    Drug,

    Dose,

    Freq

    FROM DATA

    WHERE RowNum = 1 AND MinFreq<>MaxFreq

    As far as I can see, mine still works...:cool:

    K9ID Date Drug Dose Freq

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

    1 2008-10-05 00:00:00.000 3 100 5

    1 2008-10-01 00:00:00.000 3 150 6

    1 2008-08-06 00:00:00.000 3 60 7

    1 2008-07-11 00:00:00.000 3 150 8

    You know, Mister.Magoo, I cannot for the life of me remember why I forgot about it, except perhaps all the broken internet connections I had and how many times I had to retype the same messages over and over again. I think I just lost sight of it. Anyhow, I have copied it over and am going to study it and test it again. I can't see any reason why it would not still work.

    I will report back when I am done,

    Thanks for your help and understanding,

    Dobermann

  • May 16, 2010 at 7:02 pm

    #1168093

    Jeff, I think I have gremlins in my computer... today when I added all my other joins to your code, it worked perfectly right out the gate. And, I cannot find my other attempt files that did not work. Who knows what I did... but I obviously need to take a step back and a breather from it for a day. Plus, as Mister.Magoo says, his code did work, too. Why I forgot about that is beyond me.

    Anyhow, I am going to play around with his code today and study it.

    As for yours, I ended up with the UNION then joined to my other tables:

    WITH ctePreNumber AS

    (

    SELECT ROW_NUMBER() OVER (ORDER BY K9ID, MedID, RxStartDate) AS RowNum,

    RxID, K9ID, MedID, VetID, RxStartDate, RxEndDate, Dose, Frequency

    FROM RX

    )

    SELECT CONVERT(char, RxStartDate, 1) AS 'Rx Date',

    Drug, Dose, Frequency as 'Freq',

    (Dose*Frequency) AS 'Total Daily',

    CONVERT(char, DateDiag, 1) AS 'Date Diag',

    K9Name AS 'K9 Name', BreedName AS 'Breed Name', CMType

    FROM

    (

    SELECT LOW.*

    FROM ctePreNumber AS LOW

    INNER JOIN ctePreNumber AS HIGH

    ON LOW.RowNum+1= HIGH.RowNum

    AND LOW.K9ID= HIGH.K9ID

    AND LOW.MedID= HIGH.MedID

    AND LOW.Frequency<> HIGH.Frequency

    UNION

    SELECT HIGH.*

    FROM ctePreNumber AS LOW

    INNER JOIN ctePreNumber AS HIGH

    ON LOW.RowNum+1= HIGH.RowNum

    AND LOW.K9ID= HIGH.K9ID

    AND LOW.MedID= HIGH.MedID

    AND LOW.Frequency<> HIGH.Frequency

    ) AS PITAFreq

    JOIN MED ON PITAFreq.MedID = MED.MedID

    JOIN K9 ON PITAFreq.K9ID = K9.K9ID

    JOIN BREED ON K9.BreedID = BREED.BreedID

    JOIN CMTYPE ON K9.CMID = CMTYPE.CMID

    ORDER BY K9Name, Drug, [Rx Date], Freq, Dose

    Question for you... for both the LOW and the HIGH table, you are first doing a self inner join on the cte table. Then you combine them in a Union. How in the world did you come up with that? It works, but I don't really follow the logic as to why. Would you perhaps explain your logic?

    Thanks!

    Dobermann

  • May 16, 2010 at 10:19 pm

    #1168102

    Dobermann (5/16/2010)

    Jeff, I think I have gremlins in my computer... today when I added all my other joins to your code, it worked perfectly right out the gate. And, I cannot find my other attempt files that did not work. Who knows what I did... but I obviously need to take a step back and a breather from it for a day. Plus, as Mister.Magoo says, his code did work, too. Why I forgot about that is beyond me.

    Anyhow, I am going to play around with his code today and study it.

    As for yours, I ended up with the UNION then joined to my other tables:

    WITH ctePreNumber AS

    (

    SELECT ROW_NUMBER() OVER (ORDER BY K9ID, MedID, RxStartDate) AS RowNum,

    RxID, K9ID, MedID, VetID, RxStartDate, RxEndDate, Dose, Frequency

    FROM RX

    )

    SELECT CONVERT(char, RxStartDate, 1) AS 'Rx Date',

    Drug, Dose, Frequency as 'Freq',

    (Dose*Frequency) AS 'Total Daily',

    CONVERT(char, DateDiag, 1) AS 'Date Diag',

    K9Name AS 'K9 Name', BreedName AS 'Breed Name', CMType

    FROM

    (

    SELECT LOW.*

    FROM ctePreNumber AS LOW

    INNER JOIN ctePreNumber AS HIGH

    ON LOW.RowNum+1= HIGH.RowNum

    AND LOW.K9ID= HIGH.K9ID

    AND LOW.MedID= HIGH.MedID

    AND LOW.Frequency<> HIGH.Frequency

    UNION

    SELECT HIGH.*

    FROM ctePreNumber AS LOW

    INNER JOIN ctePreNumber AS HIGH

    ON LOW.RowNum+1= HIGH.RowNum

    AND LOW.K9ID= HIGH.K9ID

    AND LOW.MedID= HIGH.MedID

    AND LOW.Frequency<> HIGH.Frequency

    ) AS PITAFreq

    JOIN MED ON PITAFreq.MedID = MED.MedID

    JOIN K9 ON PITAFreq.K9ID = K9.K9ID

    JOIN BREED ON K9.BreedID = BREED.BreedID

    JOIN CMTYPE ON K9.CMID = CMTYPE.CMID

    ORDER BY K9Name, Drug, [Rx Date], Freq, Dose

    Question for you... for both the LOW and the HIGH table, you are first doing a self inner join on the cte table. Then you combine them in a Union. How in the world did you come up with that? It works, but I don't really follow the logic as to why. Would you perhaps explain your logic?

    Thanks!

    Dobermann

    Heh... Gremlins are an integral part of life and computers... when they go wrong, they blame it on Gremlins without understanding that it's not the Gremlins fault... they just stopped holding stuff together for you. 😀

    For an explanation of what the two self joins and UNION does, please refer to my previous post where I explained it all...

    http://qa.sqlservercentral.com/Forums/Topic919513-391-1.aspx#bm920468

    I'll also tell you that even if my code and Magoo's code end's up being too slow, we're not out of tricks, yet. I'm just trying to keep the code in "level flight" instead of "going vertical" because I guarantee your "architect" will seriously squawk at the "going vertical" method.

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 16, 2010 at 10:29 pm

    #1168105

    I almost forgot... I've not tested Magoo's code, but just eye-balling it tells me it's probably going to be faster than all the self-join stuff I did. Since his code also works correctly, you would probably be better off using his code. It's also "level flight" code with good performance.

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 17, 2010 at 1:28 am

    #1168130

    My goodness I'm getting brain dead Jeff! Thanks for the kick! I've saved your post to my computer this time so I don't ask a third time!

    Sadly, here is an update.....

    Mister.Magoo's code is easier for me to follow and understand. Basically, I read that it is grouping by K9ID, MedID (Drug) & Frequency, from which it is counting the number of distinct rows that fit that bill. As such, if the RowNum is anything but 1, it means it is the same Frequency. Add to this the ORDER BY clause for the PARTITION includes the RxStartDate (Date) to set them in perfect order before the PARTITION BY clause. RowNum is not only a row number, but also a row counter. In addition to what I previously mentioned, the WHERE clause also excludes when the MIN and the MAX Frequencies match (ie, a single line per Rx).`

    Now the bad news. Neither Jeff's nor Mister.Magoo's is quite right. I added more sample data for K9ID = 3. Here is the revised creation code:

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

    DoseSMALLINT NOT 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, 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/5', '9/14/6', 80, 3 UNION ALL

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

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

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

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

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

    SELECT 3, 11, 11, '01/01/07', '01/8/7', 100, 3 UNION ALL

    SELECT 3, 11, 11, '10/10/9', NULL, 75, 1 UNION ALL

    SELECT 3, 11, 11, '9/20/9', '10/10/9', 250, 3 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;

    Here are the two versions. First is Mister.Magoo's followed by Jeff's, then a simple statement to list all Rx's for K9ID = 3 in RxStartDate order. Mister.Magoo's yields 5 rows, Jeff's 8 and the correct number would be 7:

    --=====Run Mister.Magoo's Code

    ;WITH cteRowNum AS

    (

    SELECT RxID, K9ID, MedID, VetID, RxStartDate, RxEndDate, Dose, Frequency,

    ROW_NUMBER() OVER ( PARTITION BY K9ID, MedID, Frequency

    ORDER BY K9ID, MedID, RxStartDate ) AS RowNum,

    MIN(Frequency) OVER ( PARTITION BY K9ID, MedID ) AS MinFreq,

    MAX(Frequency) OVER ( PARTITION BY K9ID, MedID ) AS MaxFreq

    FROM #RX

    )

    SELECT CONVERT(char, RxStartDate, 1) AS 'Rx Date',

    MedID, Dose, Frequency as 'Freq',

    (Dose*Frequency) AS 'Total Daily'

    FROM cteRowNum

    WHERE RowNum = 1 AND MinFreq<>MaxFreq AND K9ID = 3

    ORDER BY K9ID, MedID, RxStartDate, Frequency, Dose

    --=====Run Jeff's code

    ;WITH ctePreNumber AS

    (

    SELECT ROW_NUMBER() OVER (ORDER BY K9ID, MedID, RxStartDate) AS RowNum,

    RxID, K9ID, MedID, VetID, RxStartDate, RxEndDate, Dose, Frequency

    FROM #RX

    )

    SELECT CONVERT(char, RxStartDate, 1) AS 'Rx Date',

    MedID, Dose, Frequency as 'Freq',

    (Dose*Frequency) AS 'Total Daily'

    FROM

    (

    SELECT LOW.*

    FROM ctePreNumber AS LOW

    INNER JOIN ctePreNumber AS HIGH

    ON LOW.RowNum+1= HIGH.RowNum

    AND LOW.K9ID= HIGH.K9ID

    AND LOW.MedID= HIGH.MedID

    AND LOW.Frequency<> HIGH.Frequency

    UNION

    SELECT HIGH.*

    FROM ctePreNumber AS LOW

    INNER JOIN ctePreNumber AS HIGH

    ON LOW.RowNum+1= HIGH.RowNum

    AND LOW.K9ID= HIGH.K9ID

    AND LOW.MedID= HIGH.MedID

    AND LOW.Frequency<> HIGH.Frequency

    ) AS PITAFreq

    WHERE K9ID = 3

    ORDER BY K9ID, MedID, RxStartDate, Frequency, Dose

    --=====List all RXs for K9ID = 3 ordered by RxStartDate

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

    (Dose*Frequency) AS 'Total Daily', K9ID

    FROM #RX

    WHERE K9ID = 3

    ORDER BY RxStartDate

    This is what it should look like:

    RxStartDateMedIDDoseFreqTotal DailyK9ID

    2005-09-14118032403

    2006-09-14119043603

    2007-01-011110033003

    2007-09-141110055003

    2008-08-141112567503

    2009-09-201125037503

    2009-10-1011751753

    I am beginning to think that perhaps rather than having one query for changes in Frequencies (which we have been working on) and a matching one modified for changes in Dose, which was the original plan, that it might make more analytical sense and be easier to code to have one query that shows all the changes in Dose*Frequency AS TotalDaily occurances, sorted by RxStartDate. As such, a row would be included whenever the TotalDaily changes from the previous Rx. That would be a piece of cake to code, as it is basically a sorted listing of the RX table with the Total Daily calculation thrown in.

    The result, then, would look like this:

    RxStartDateMedIDDoseFreqTotal DailyK9ID

    2005-09-14118032403

    2006-09-14119043603

    2007-01-011110033003

    2007-09-141110055003

    2008-08-141112567503

    2008-09-141111066603

    2009-09-141110066003

    2009-09-201125037503

    2009-10-1011751753

    But if you guys want to keep trying to solve the original problem, I'm game if you are. It's a great learning experience, one which I am thoroughly enjoying and would like to see an answer to at this point even if the final query does get changed to just Total Daily. Maybe we should make it the Quiz of The Week. :hehe:

    Dobermann

  • May 17, 2010 at 3:40 pm

    #1168485

    Quite right about that code not working....;-)

    I don't think that method will extend to what you are trying to do, but I am sure Jeff's will work with only a tweak or two.

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • May 17, 2010 at 4:17 pm

    #1168500

    Dobermann (5/17/2010)

    My goodness I'm getting brain dead Jeff! Thanks for the kick! I've saved your post to my computer this time so I don't ask a third time!

    Sadly, here is an update.....

    Heh... I understand that you can't correct a blank piece of paper but the paper is changing a lot and quickly. Before I spend any more time on this, are you sure you have the requirements hammered out and that you've posted those requirements as your latest request?

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 18, 2010 at 12:32 am

    #1168602

    I'm not trying to be daft, but I don't see a change in the requirements. Maybe I'm just too close to it to see. I added one new column, TotalDaily, as a possible alternative if we (read you and the others helping) were tired of trying to get this to work. However, I would still want to find a solution to this if you folks are up to it. Personally, I think it would be quite interesting to see some of your code that you say is "going vertical", as I really enjoy trying to wrap my head around good creative code. It's how I learn the best.

    What changed in the last post is that I added more sample data in an attempt to double check that it worked as I thought it did. When I did that, then the new sample data with its potential for duplicate Frequencies (now with and without duplicate Doses, too), showed the problem. In order to avoid that happening another time, I have added a lot more sample data for K9ID = 3 which should test for all the potentiality that I can think of:

    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;

    Using the above sample data, following is what the results should look like:

    RxStartDateMedIDDoseFreqK9ID

    2005-09-14118033

    2006-09-14119043

    2007-09-141125033

    2007-09-281110023

    2008-01-081110053

    2008-09-141125083

    2009-09-141125033

    2009-10-101111063

    2009-12-161125013

    2010-02-141125043

    2010-05-011125073

    (MedID and Drug are basically the same column)

    For clarity, here is the question that we have been working on:

    When, if ever, did the frequency change on an existing drug Rx? It is considered a change in frequency if there is a different frequency between the two consecutive frequencies based upon date order of the RxStartDate. Change, then, is a change from the immediate last frequency, not just any previous frequency.

    Sorry if I caused confusion. It certainly was not my intent. As always, I sincerely appreciate all the help!

    Dobermann

  • May 18, 2010 at 7:39 am

    #1168814

    Heh... maybe it is I who is going brain dead concerning changes in requirements but ...

    Dobermann (5/17/2010)

    I am beginning to think that perhaps rather than having one query for changes in Frequencies (which we have been working on) [font="Arial Black"]and a matching one modified for changes in Dose, which was the original plan[/font],

    Dobermann (5/11/2010)

    Stumped. I need to find out when the value in Col3 [font="Arial Black"](Dose) changes [/font]from the previous date (col5):

    K9ID Drug Dose Freq Date

    Col1 Col2 Col3 Col4 Col5

    1 3 100 5 2008-10-5

    1 3 150 6 2008-10-01

    1 3 75 8 2009-8-14

    1 3 150 8 2008-7-11

    If I ignore the dates (which I should not, but for testing I am - I still need to add that in), I want to see the [font="Arial Black"]frequency changes per drug per K9[/font], I only want to see rows 1, 2 & 3, yet I also get row 4 because I am using DISTINCT, which obviously is not correct coding. I've tried a self join, but it still brings up rows 3 & 4 when I don't want row 4.

    What am I missing?

    Here is what I have:

    SELECT DISTINCT RX1.K9ID, CONVERT(char, RX1.RxStartDate, 1) AS 'Date of Rx', Drug,

    RX1.[font="Arial Black"]Frequency[/font], DateDiag, K9Name, BreedName AS Breed, CMTYPE

    FROM RX AS RX1 JOIN RX AS RX2 ON RX1.K9ID = RX2.K9ID

    AND RX1.MedID = RX2.MedID

    AND RX1.[font="Arial Black"]Frequency [/font]<> RX2.[font="Arial Black"]Frequency[/font]

    JOIN MED ON RX1.MedID = MED.MedID

    JOIN K9 ON RX1.K9ID = K9.K9ID

    JOIN BREED ON K9.BreedID = BREED.BreedID

    JOIN CMTYPE ON K9.CMID = CMTYPE.CMID

    ORDER BY 1, 3, 2, 4

    Any help is HIGHLY appreciated!

    Dobermann

    It may have been your original plan but I'm not seeing that in the original post. 🙂 Ya gotta admit that, at the very least, it's a wee bit confusing. :hehe:

    Anyway... if you make a calculated column in the table to do the Dose*Frequency as the Daily total, life will become easier.

    Also, which code had the problem with the additional data? Magoo's or mine?

    I'm on my way to work in a second so I'll try to get back to this tonight.

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 18, 2010 at 2:25 pm

    #1169104

    Ah, I see what I did. All along it was my plan to get this query on Frequency working right, then I could just change all the references from Frequency to Dose on my computer and I would have the second required query which was identical to Frequency except it was based on Dose. The logic and coding would be identical with the exception of me flip flopping Dose wherever I saw Frequency. Did that make sense? As for my Dose*Frequency, I was thinking out loud in case we could not get this resolved or in case you guys were tired of trying to get it to work. I know you must have busy lives outside this forum.

    Mister.Magoo's had too few lines and yours had too many lines. But Jeff, please hold off on playing around with this until I get back to you. You may have pointed out a huge error on my part, in that the code you and others provided was indeed for Dose, and somehow I got my head around Frequency. It may be in fact that the others do work but that I was looking at the wrong column. I am going to look again at it, plus I will have to revise the sample data for Dose testing, as all the potential problems that I could think of were also geared towards Frequency at this point. I will have to re-gear them towards Dose. All of this will take me a bit of time, plus another internet connection... you gotta love hotel's free wifi that just kind of drifts a bit too far from their hotel 😉

    I want to make doubly sure that I get my part exactly right this time.

    Dobermann

  • May 18, 2010 at 3:06 pm

    #1169127

    Dobermann (5/18/2010)

    I kinda figured that out afterwards but thanks for the feedback.

    On the Dose/Frequency thing... it shouldn't make a difference. If the code works on one it should work on the other because they're only numbers and the computer doesn't really care which column they're in. I'll still take a look later on tonight to see why you think the new data is doubling things up because the inherent nature of the code is to eliminate such dupes.

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 18, 2010 at 6:50 pm

    #1169184

    Ah... I see why you say too many rows showed up. We need to define what actually needs to happen when the Frequency changes. The current definition is to show what it changed from and what it changed to. The problem is when you get something like this...

    5

    8 Went from 5 to 8 so 5 and 8 showed up

    8 Nothing showed for this until...

    3 We went from 8 to 3 so 8 and 3 showed up making it appear to be duplicated.

    I'm not quite sure why it appeared to work correctly before but, the fact of the matter is, it doesn't now. :hehe:

    I do have an idea, though... I'll be back.

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 18, 2010 at 7:13 pm

    #1169185

    Here you go... I guarantee that it only finds changes, now...

    --===== 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 ROW_NUMBER() OVER (ORDER BY K9ID, MedID, RxStartDate) AS RowNum,

    RxID, K9ID, MedID, VetID, RxStartDate, RxEndDate, Dose, Frequency

    INTO #PreNumber

    FROM #Rx;

    SELECT lo.K9ID, lo.MedID,

    lo.RxID AS Prev_RxID, hi.RxID AS New_RxID,

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

    lo.RxStartDate AS Prev_RxStartDate, lo.RxEndDate AS Prev_RxEndDate,

    hi.RxStartDate AS New_RxStartDate, hi.RxEndDate AS New_RxEndDate,

    lo.Dose AS Prev_Dose, hi.Dose AS New_Dose,

    lo.Frequency AS Prev_Frequency, hi.Frequency AS New_Frequency

    FROM #PreNumber lo

    INNER JOIN #PreNumber hi

    ON lo.RowNum+1 = hi.RowNum

    AND lo.K9ID = hi.K9ID

    AND lo.MedID = hi.MedID

    AND lo.Frequency <> hi.Frequency;

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 18, 2010 at 8:44 pm

    #1169190

    Okay, after studying the queries and the results, I am confident that these queries only deal with Frequency changes. This sample data is meant to test every possibility that I can think of for Frequency changes. Below creates and populates the table, then runs Mister.Magoo's, Jeff's and then a plain list:

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

    --Run Mister.Magoo's Code

    ;WITH cteRowNum AS

    (

    SELECT RxID, K9ID, MedID, VetID, RxStartDate, RxEndDate, Dose, Frequency,

    ROW_NUMBER() OVER ( PARTITION BY K9ID, MedID, Frequency

    ORDER BY K9ID, MedID, RxStartDate ) AS RowNum,

    MIN(Frequency) OVER ( PARTITION BY K9ID, MedID ) AS MinFreq,

    MAX(Frequency) OVER ( PARTITION BY K9ID, MedID ) AS MaxFreq

    FROM #RX

    )

    SELECT CONVERT(char, RxStartDate, 1) AS 'Rx Date',

    MedID, Dose, Frequency as 'Freq'

    FROM cteRowNum

    WHERE RowNum = 1 AND MinFreq<>MaxFreq

    ORDER BY K9ID, MedID, RxStartDate, Frequency, Dose

    --Run Jeff's code

    ;WITH ctePreNumber AS

    (

    SELECT ROW_NUMBER() OVER (ORDER BY K9ID, MedID, RxStartDate) AS RowNum,

    RxID, K9ID, MedID, VetID, RxStartDate, RxEndDate, Dose, Frequency

    FROM #RX

    )

    SELECT CONVERT(char, RxStartDate, 1) AS 'Rx Date',

    MedID, Dose, Frequency as 'Freq'

    FROM

    (

    SELECT LOW.*

    FROM ctePreNumber AS LOW

    INNER JOIN ctePreNumber AS HIGH

    ON LOW.RowNum+1= HIGH.RowNum

    AND LOW.K9ID= HIGH.K9ID

    AND LOW.MedID= HIGH.MedID

    AND LOW.Frequency<> HIGH.Frequency

    UNION

    SELECT HIGH.*

    FROM ctePreNumber AS LOW

    INNER JOIN ctePreNumber AS HIGH

    ON LOW.RowNum+1= HIGH.RowNum

    AND LOW.K9ID= HIGH.K9ID

    AND LOW.MedID= HIGH.MedID

    AND LOW.Frequency<> HIGH.Frequency

    ) AS PITAFreq

    ORDER BY K9ID, MedID, RxStartDate, Frequency, Dose

    --List all RXs ordered by RxStartDate

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

    FROM #RX

    ORDER BY K9ID, MedID, RxStartDate

    Here is what it should look like (11 rows):

    RxStartDateMedIDDoseFreq

    2005-09-1411803

    2006-09-1411904

    2007-09-14112503

    2007-09-28111002

    2007-10-15111005

    2008-09-14112508

    2009-09-14112503

    2009-10-10111106

    2009-12-16112501

    2010-02-14112504

    2010-05-01112507

    Magoo's has 8 rows. It leaves out any duplicate Frequency values entirely, not taking into account that there may have been another value in between, ie, 3, 4, 3, 2 vs 3, 4, 2:

    Rx Date MedIDDoseFreq

    09/14/05 11803

    09/14/06 11904

    09/28/07 111002

    10/15/07 111005

    09/14/08 112508

    10/10/09 111106

    12/16/09 112501

    05/01/10 112507

    Your code has 16 rows. It only removes duplicate Frequency values when there are 3 or more in a row, and then it still leaves two behind, ie, 6, 6, 1, 1, 4, 4, 7 vs 6, 1, 4, 7 (see last 10 rows in raw data below this set to see the actual total data of 6, 6, 1, 1, 1, 4, 4, 4, 4, 7):

    Rx Date MedIDDoseFreq

    11/14/05 112503

    09/14/06 11904

    09/14/07 112503

    09/28/07 111002

    10/15/07 111005

    01/08/08 111005

    09/14/08 112508

    01/01/09 111258

    09/14/09 112503

    10/10/09 111106

    11/14/09 111006

    12/16/09 112501

    01/10/10 112501

    02/14/10 112504

    04/15/10 11754

    05/01/10 112507

    This is the raw data without any exclusions so you can see the 6, 6, 1, 1, 1, 4, 4, 4, 4, 7 (triple 1, four 4s) pattern difference:

    RxStartDateMedIDDoseFreq

    2005-09-1411803

    2005-11-14112503

    2006-09-1411904

    2007-09-14112503

    2007-09-28111002

    2007-10-15111005

    2008-01-08111005

    2008-09-14112508

    2009-01-01111258

    2009-09-14112503

    2009-10-10111106

    2009-11-14111006

    2009-12-16112501

    2010-01-01112501

    2010-01-10112501

    2010-02-14112504

    2010-03-0811754

    2010-04-0111754

    2010-04-1511754

    2010-05-01112507

    There is only one other code submission to double check, and that is Eric's. I gave it the same field names, same result columns and same sample data as above. I also added an ORDER BY clause. It yielded 19 rows:

    DECLARE @TABLE TABLE

    (

    RxIDINT NOT NULL PRIMARY KEY IDENTITY,

    K9ID INT NOT NULL,

    MedIDINT NOT NULL,

    VetIDINT NOT NULL,

    RxStartDateDATE NOT NULL,

    RxEndDateDATENULL,

    DoseSMALLINTNOT NULL,

    FrequencyTINYINTNOT NULL

    );

    INSERT INTO @TABLE

    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;

    select CONVERT(char, A.RxStartDate, 1) AS 'Rx Date',

    A.MedID, A.Dose, A.Frequency as 'Freq'

    from @TABLE A

    where exists

    (

    select 1

    from @TABLE B

    where b.k9id = a.k9id and b.MedID = a.MedID and b.RxStartDate < a.RxStartDate

    )

    ORDER BY K9ID, MedID, RxStartDate

    ;

    This is the result of Eric's code. It includes everything except the very first row:

    Rx Date MedIDDoseFreq

    11/14/05 112503

    09/14/06 11904

    09/14/07 112503

    09/28/07 111002

    10/15/07 111005

    01/08/08 111005

    09/14/08 112508

    01/01/09 111258

    09/14/09 112503

    10/10/09 111106

    11/14/09 111006

    12/16/09 112501

    01/01/10 112501

    01/10/10 112501

    02/14/10 112504

    03/08/10 11754

    04/01/10 11754

    04/15/10 11754

    05/01/10 112507

    Finally, just to make sure that I did not mess with anything meaningful in Eric's code because his prior posting with his limited sample data did look like it worked yet it does not work now, I made but one change. I added the complete sample data that all the other attempts here used, structured to match his declared structure. Same 19 rows as above.

    DECLARE @TABLE TABLE

    (

    K9ID INT,

    Drug INT,

    Dose INT,

    Freq INT,

    Date DATETIME

    );

    INSERT INTO @TABLE

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    SELECT 3, 11, 250, 4, '2/14/10';

    select A.*

    from @TABLE A

    where exists

    (

    select 1

    from @TABLE B

    where b.k9id = a.k9id and b.drug = a.drug and b.date < a.date

    );

    Sorry this post is so long, but it was unavoidable if I was going to show code, results and what it should be, along with showing that we are back to apples and apples again.

    I hope this analysis helps redeem me 🙂

    Dobermann

    • Viewing 15 posts - 31 through 45 (of 60 total)

    You must be logged in to reply to this topic. Login to reply