May 11, 2010 at 2:35 am
Stumped. I need to find out when the value in Col3 (Dose) changes 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 frequency changes per drug per K9, 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.Frequency, 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.Frequency <> RX2.Frequency
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
May 11, 2010 at 3:12 am
Hay Post your data in the form of INSERT statements which will help people who want to help you. you should probably study the article in the link below. It'll help you to get good solid answers quicker. [Wink]
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Nag
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 11, 2010 at 3:21 am
I din't understand your requirement clearly; But stil, as far as i understood, here is a piece of cdoe that might interest you.
Look at how i am posting the data so that it becomes easy for other to work on your request:
Sample data and Table :
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'
Code for the request (assuming my understanding is correct):
SELECT * FROM @TABLE
SELECT K9ID, Drug , Freq
FROM @TABLE
GROUP BY K9ID, Drug , Freq
As Nagesh suggested, go thro the article he is pointing to and give us clear picutre of what u need. I want to see what you want, then only i can prepare what u want 😉
Cheers!!
May 11, 2010 at 3:23 am
I think that a cursor, which accesses the data ordered by date, is useful in your case.
May 11, 2010 at 8:22 am
Dobermann (5/11/2010)
Stumped. I need to find out when the value in Col3 (Dose) changes from the previous date (col5):...
...
... I only want to see rows 1, 2 & 3 ...
OK, I'm still guessing about what business problem is being presented, but I'm guessing that what you're giving us is a history of drug dosages, and you want to know which of these dosages are updates from the original. So, return each row where exists another row with the same matching key value and an earlier Date.
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.
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';
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
);
K9ID Drug Dose Freq Date
----------- ----------- ----------- ----------- ----------
1 3 100 5 2008-10-05
1 3 150 6 2008-10-01
1 3 75 8 2009-08-14
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 11, 2010 at 2:22 pm
dmoldovan (5/11/2010)
I think that a cursor, which accesses the data ordered by date, is useful in your case.
Very useful - NOT. With a few additional items for clarity (including a larger rowset), which has already been requested, a set-based solution is most likely doable.
-- You can't be late until you show up.
May 11, 2010 at 5:54 pm
Okay, I've read how I was to post my question and I think I have done it correctly now. 🙂
I am still having problems. The first query has too few rows (see K9ID 3) while the second query has too many rows (again, see K9ID 3).
--===== 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,
RxStartDate DATE NOT NULL,
RxEndDate DATE NULL,
Dose SMALLINT NOT NULL,
FrequencyTINYINT 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
VALUES
(1, 11, 11, '7/3/9', '1/27/10', 40, 4),
(1, 1, 11, '7/3/9', '1/27/10', 40, 4),
(1, 8, 11, '7/3/9', '1/27/10', 40, 4),
(1, 16, 11, '7/3/9', '1/27/10', 40, 4),
(1, 23, 11, '7/3/9', '7/13/9', 40, 4),
(1, 11, 11, '7/13/9', '7/23/9', 60, 4),
(1, 11, 11, '7/23/9', '1/27/10', 80, 4),
(1, 13, 11, '7/5/9', '7/15/9', 100, 3),
(1, 13, 11, '7/15/9', '1/27/10', 100, 4),
(2, 6, 14, '10/31/9', '11/8/9', 20, 3),
(2, 14, 14, '10/31/9', '11/8/9', 120, 3),
(2, 2, 14, '10/31/9', '11/8/9', 20, 3),
(2, 4, 14, '10/31/9', '11/8/9', 120, 3),
(3, 11, 11, '9/14/5', '9/14/6', 80, 3),
(3, 11, 11, '9/14/6', '9/14/7', 90, 4),
(3, 11, 11, '9/14/7', '9/14/8', 100, 5),
(3, 11, 11, '9/14/8', '9/14/9', 110, 6),
(3, 11, 11, '8/14/8', '9/14/8', 125, 6),
(3, 11, 11, '9/14/9', NULL, 100, 6),
(6, 8, 8, '3/15/10', '3/17/10', 200, 4),
(6, 18, 8, '3/15/10', '3/17/10', 200, 4),
(6, 8, 8, '3/17/10', '4/15/10', 250, 5),
(6, 18, 8, '3/17/10', '4/15/10', 250, 5)
SELECT DISTINCT #RX1.K9ID, #RX1.RxStartDate, #RX1.MedID, #RX1.Dose, #RX1.Frequency
FROM #RX AS #RX1 JOIN #RX AS #RX2 ON #RX1.K9ID = #RX2.K9ID
AND #RX1.MedID = #RX2.MedID
AND #RX1.Frequency <> #RX2.Frequency
WHERE EXISTS
(SELECT *
FROM #RX
WHERE #RX1.K9ID = #RX2.K9ID
AND #RX1.MedID = #RX2.MedID
AND #RX1.RxStartDate < #RX2.RxStartDate )
ORDER BY 1, 3, #RX1.RxStartDate, 5
SELECT DISTINCT #RX1.K9ID, #RX1.RxStartDate, #RX1.MedID, #RX1.Dose, #RX1.Frequency
FROM #RX AS #RX1 JOIN #RX AS #RX2 ON #RX1.K9ID = #RX2.K9ID
AND #RX1.MedID = #RX2.MedID
AND #RX1.Frequency <> #RX2.Frequency
ORDER BY 1, 3, #RX1.RxStartDate, 5
May 11, 2010 at 8:30 pm
Heh... you do realize that by using the 2008 VALUES clause, that all of the people still stuck in the world of 2005 or earlier either can't help you or won't help you because they don't have the time to change your code... right? And I do have to tell you, some of them are absolute Ninjas that can splash this problem in about two heartbeats if they have the correct data to work with. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2010 at 8:40 pm
Now that you have posted the sample data and table structure, half the job is prettily done 🙂 Now can you give us some visual representation of how your expected result will look like?
Cheers!
May 11, 2010 at 8:49 pm
Also, didn't eric_russell's code do it for you?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2010 at 8:52 pm
Jeff Moden (5/11/2010)
Heh... you do realize that by using the 2008 VALUES clause, that all of the people still stuck in the world of 2005 or earlier either can't help you or won't help you because they don't have the time to change your code... right? And I do have to tell you, some of them are absolute Ninjas that can splash this problem in about two heartbeats if they have the correct data to work with. 😉
Actually, Jeff, no I did not realize that VALUES was a 2008 clause. I am new to SQL and have learned what I have learned by pickup up a book and reading, studying existing code and the like. Quite honestly, I looked at the other code that Eric posted and did not understand the INSERT INTO SELECT code. I have not seen anything like that before and UNION at the end of each line just confused me even more, as it is really not trying to make a UNION join.
I'm trying, trust me, I'm trying!
Dobermann
:hehe:
May 11, 2010 at 9:03 pm
dmoldovan (5/11/2010)
I think that a cursor, which accesses the data ordered by date, is useful in your case.
So... let's see the cursor. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2010 at 9:07 pm
Dobermann (5/11/2010)
Jeff Moden (5/11/2010)
Heh... you do realize that by using the 2008 VALUES clause, that all of the people still stuck in the world of 2005 or earlier either can't help you or won't help you because they don't have the time to change your code... right? And I do have to tell you, some of them are absolute Ninjas that can splash this problem in about two heartbeats if they have the correct data to work with. 😉Actually, Jeff, no I did not realize that VALUES was a 2008 clause. I am new to SQL and have learned what I have learned by pickup up a book and reading, studying existing code and the like. Quite honestly, I looked at the other code that Eric posted and did not understand the INSERT INTO SELECT code. I have not seen anything like that before and UNION at the end of each line just confused me even more, as it is really not trying to make a UNION join.
I'm trying, trust me, I'm trying!
Dobermann
:hehe:
Understood... on all fronts. That's why I posted what I did so you can get better and quicker help in the future.
As for trying, I can't ask for more than that... I'm sure others will work on your problem because, as Cold Coffee said, you took the time to post data they can work with and it shows that you're trying, as well. I'll do the conversion so I can take a crack at it in 2k5, as well. Not to worry... the solution will work in 2k8 if I can make it fly. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2010 at 9:10 pm
ColdCoffee (5/11/2010)
Now that you have posted the sample data and table structure, half the job is prettily done 🙂 Now can you give us some visual representation of how your expected result will look like?Cheers!
Hopefully this correctly fills the bill!
K9ID RxStartDate MedID Dose Frequency
12009-07-05 131003
12009-07-15 131004
32005-09-14 11803
32006-09-14 11904
32007-09-14 111005
32008-08-14 111256
62010-03-15 82004
62010-03-17 82505
62010-03-15 182004
62010-03-17 182505
Only 4 rows for K9ID 3, representing 3,4,5,6 in frequencies.
Thanks,
Dobermann
PS - I keep trying, but no matter what I do I cannot get the first two lines in the above to line up correctly over the proper columns.
May 11, 2010 at 9:21 pm
Jeff Moden (5/11/2010)
Also, didn't eric_russell's code do it for you?
I could not convert it to 2008. I feel like I have tried all types of convolutions of it, lastly trying:
SELECT DISTINCT RX1.K9ID, RX1.RxStartDate, RX1.MedID,
RX1.Dose, RX1.Frequency
FROM RX AS RX1 JOIN RX AS RX2 ON RX1.K9ID = RX2.K9ID
AND RX1.MedID = RX2.MedID
AND RX1.Frequency <> RX2.Frequency
WHERE EXISTS
(SELECT *
FROM RX
WHERE RX1.K9ID = RX2.K9ID
AND RX1.MedID = RX2.MedID
AND RX1.RxStartDate < RX2.RxStartDate )
ORDER BY 1, 3, 2, 5
but the above leaves out the 4th row of K9ID #3, frequency of 6 (row 4 for it).
Viewing 15 posts - 1 through 15 (of 60 total)
You must be logged in to reply to this topic. Login to reply