May 11, 2010 at 10:42 pm
The "4th row" does complicate things a bit. You have to reverse the query and run it again and then get rid of the dupes... I think this will be pretty bad on performance but it does get the job done. For the record, a cursor would also have a problem with the "1st row" because of the ascending nature of the problem and the fact that you don't want the first row unless the second row is different...
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 lo.*
FROM ctePreNumber lo
INNER JOIN ctePreNumber hi
ON lo.RowNum+1 = hi.RowNum
AND lo.K9ID = hi.K9ID
AND lo.MedID = hi.MedID
AND lo.Frequency <> hi.Frequency
UNION
SELECT hi.*
FROM ctePreNumber lo
INNER JOIN ctePreNumber hi
ON lo.RowNum+1 = hi.RowNum
AND lo.K9ID = hi.K9ID
AND lo.MedID = hi.MedID
AND lo.Frequency <> hi.Frequency
Results...
RowNumRxIDK9IDMedIDVetIDRxStartDateRxEndDateDoseFrequency
68113112009-07-05 00:00:00.0002009-07-15 00:00:00.0001003
79113112009-07-15 00:00:00.0002010-01-27 00:00:00.0001004
1414311112005-09-14 00:00:00.0002006-09-14 00:00:00.000803
1515311112006-09-14 00:00:00.0002007-09-14 00:00:00.000904
1616311112007-09-14 00:00:00.0002008-09-14 00:00:00.0001005
1718311112008-08-14 00:00:00.0002008-09-14 00:00:00.0001256
20206882010-03-15 00:00:00.0002010-03-17 00:00:00.0002004
21226882010-03-17 00:00:00.0002010-04-15 00:00:00.0002505
222161882010-03-15 00:00:00.0002010-03-17 00:00:00.0002004
232361882010-03-17 00:00:00.0002010-04-15 00:00:00.0002505
I didn't like the join by inequality on the dates because that formed several triangular joins which cause the 23 rows sample to explode to 105 internally processed rows for each
--Jeff Moden
May 11, 2010 at 11:03 pm
Wow, Jeff! I am going to have to study this one long and hard! Thank you so much - I will post back with either more questions or a big, fat YES! I'm sure your code works, but I need to understand it if I am going to learn from it.
Thanks again!
Dobermann
May 12, 2010 at 3:50 am
Dobermann, Jeff, I appologize for being wrong this time. I did not realized in a timely manner the output that Dobermann needs.
Sorry, guys :blush:
May 12, 2010 at 7:03 am
dmoldovan (5/12/2010)
Dobermann, Jeff, I appologize for being wrong this time. I did not realized in a timely manner the output that Dobermann needs.Sorry, guys :blush:
Nah... you're alright. I very much appreciate your humility (one of the signs of an excellent programmer) but it's actually a tougher problem than most people think because, as Dobermann said, you need both the "1st" and the "4th" item (and everything in between) for K9ID #3. Heh... I did the code pretty late at night and was tired and probably shouldn't have posted the code I did because I think performance is going to be pretty bad (4 calls to the same CTE, two self joins) in the face of any scalability. You watch... someone is going to post code that will beat the pants off my code (heh... it might be me later on if I get the chance).
--Jeff Moden
May 12, 2010 at 7:36 am
Dobermann (5/11/2010)
Wow, Jeff! I am going to have to study this one long and hard! Thank you so much - I will post back with either more questions or a big, fat YES! I'm sure your code works, but I need to understand it if I am going to learn from it.Thanks again!
Dobermann
The two queries than have been unioned are identical except for which row of a pair of rows they return. The first query compares two rows from the same table (lets say lo.#3 and hi.#4) and returns lo.#3 if the two rows have a different frequency. The second query does EXACTLY the same thing with EXACTLY the same rows except it returns hi.#4.
Of course, that's going to return some (depending on the data, a lot) of duplicates. For example, for K9ID=3, it will return the middle rows (of the four to be returned twice) (once for each query). The UNION takes care of those duplicates because UNION also does a "distinct" behind the scenes. If you want to see the duplicates, change the UNION to a UNION ALL.
The CTE that creates a ROW_NUMBER is so that I have a known sequential value to compare against so I don't have to rely on a "Triangular Join" for dates when comparing rows. Instead, I can do the equality comparison of lo.RowNum+1 = hi.RowNum.
For more information on the devestating effect that "Triangular Joins" can have, please see the following article...
http://qa.sqlservercentral.com/articles/T-SQL/61539/
The bad part about my code is that I'm "calling" a CTE 4 times which means the CTE will execute 4 times just as if it were a View. An immediate and significant performance improvement can be realized by dumping the output of the CTE to a Temp Table and then using the Temp Table as the source of data... like this...
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.*
FROM #PreNumber lo
INNER LOOP JOIN #PreNumber hi --"LOOP" hint helps the optimizer make the right choice a LOT here
ON lo.RowNum+1 = hi.RowNum
AND lo.K9ID = hi.K9ID
AND lo.MedID = hi.MedID
AND lo.Frequency <> hi.Frequency
UNION ALL
SELECT hi.*
FROM #PreNumber lo
INNER LOOP JOIN #PreNumber hi --"LOOP" hint helps the optimizer make the right choice a LOT here
ON lo.RowNum+1 = hi.RowNum
AND lo.K9ID = hi.K9ID
AND lo.MedID = hi.MedID
AND lo.Frequency <> hi.Frequency
Of course, even though that's a fair bit faster than the CTE verision, it's relatively slow because it still needs to look at the same table 4 times and that's going to be relatively slow even if you have some decent indexes on the new temp table (indexes would probably eliminate the need for the "Loop" hint).
I'll see if I can come up with a WHERE EXISTS example tonight like someone else did on this thread because it might be faster still. I just can't get my head around correlated subqueries late at night or before I've loaded up on coffee in the morning. :hehe:
--Jeff Moden
May 12, 2010 at 3:17 pm
Using your insert code, lets look at K9ID 3, just to make it simple.
How many rows are you expecting to get back? For Dose 100, which record are you wanting to see 16 or 19? I think I know what you are asking but just want to make sure.
May 12, 2010 at 4:10 pm
I offer this...
;WITH DATA
AS
(
SELECT
K9ID,
RxStartDate,
MedID,
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
K9ID,
RxStartDate,
MedID,
Dose,
Frequency
FROM DATA
WHERE RowNum = 1 AND MinFreq<>MaxFreq
And the result....
K9ID RxStartDate MedID Dose Frequency
----------- ----------- ----------- ------ ---------
1 2009-07-05 13 100 3
1 2009-07-15 13 100 4
3 2005-09-14 11 80 3
3 2006-09-14 11 90 4
3 2007-09-14 11 100 5
3 2008-08-14 11 125 6
6 2010-03-15 8 200 4
6 2010-03-17 8 250 5
6 2010-03-15 18 200 4
6 2010-03-17 18 250 5
(10 row(s) affected)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 13, 2010 at 5:36 pm
Just getting an internet connection again (I'm in the boonies with not so much as even dial up). Mister.Magoo, what you did gives perfect results! I have saved it and am going to play around with it and study it.
I need to join that to a few other tables to get all the columns, but that should be a piece of cake at this point.
Jeff, your code worked perfectly, but as you say, it uses a lot of resources. You should have seen it by the time I joined it to 5 other tables. I jumped too fast on that, as I ended up putting back in the unwanted rows in my joins. I took a 'stand back' breather from it, and I need to check my join types as they are the cause of the new issue.
I'm glad in a way to see that it was not just a slam-bam answer that was so very, very simple, as then I would have felt very stupid! 😉
Thanks again & I will be back with an update,
Dobermann
May 14, 2010 at 6:46 am
Dobermann (5/13/2010)
I'm glad in a way to see that it was not just a slam-bam answer that was so very, very simple, as then I would have felt very stupid! 😉
Dobermann
Never stupid if you've learned something and shown the gumption to make attempts at resolving your problems first and to understand suggested resolutions second. We all are learning and if you haven't already noticed, this is usually more than one way to solve your issues. Determining which is best for you and then applying that knowledge down the road for other tasks is a great feeling of accomplishment. Stick around, there is a lot to learn!
-- You can't be late until you show up.
May 14, 2010 at 1:12 pm
Dobermann (5/13/2010)
Just getting an internet connection again (I'm in the boonies with not so much as even dial up). Mister.Magoo, what you did gives perfect results! I have saved it and am going to play around with it and study it.I need to join that to a few other tables to get all the columns, but that should be a piece of cake at this point.
Jeff, your code worked perfectly, but as you say, it uses a lot of resources. You should have seen it by the time I joined it to 5 other tables. I jumped too fast on that, as I ended up putting back in the unwanted rows in my joins. I took a 'stand back' breather from it, and I need to check my join types as they are the cause of the new issue.
I'm glad in a way to see that it was not just a slam-bam answer that was so very, very simple, as then I would have felt very stupid! 😉
Thanks again & I will be back with an update,
Dobermann
BWAA-HHAAA!!!! Yes, I did warn about it but you joined it with 5 other tables and say MY code uses a lot of resources? 😛
It all depends on how you do the joins and whether or not you use "Divide'n'Conquer" methods.
--Jeff Moden
May 14, 2010 at 6:17 pm
tosscrosby (5/14/2010)
Never stupid if you've learned something and shown the gumption to make attempts at resolving your problems first and to understand suggested resolutions second. We all are learning and if you haven't already noticed, this is usually more than one way to solve your issues. Determining which is best for you and then applying that knowledge down the road for other tasks is a great feeling of accomplishment. Stick around, there is a lot to learn!
Hey, I love this forum! There is so much to learn here. What else is nice is that I am on PST (West coast, USA) and I am usually up most of the evenings till 3 or 4 am. Quite a few of the participants here are on the opposite time zone as where I live, so it usually means when I am up and working, most of these folks are doing the same.
Works out great for me!
Dobermann
May 14, 2010 at 7:59 pm
Jeff Moden (5/14/2010)
BWAA-HHAAA!!!! Yes, I did warn about it but you joined it with 5 other tables and say MY code uses a lot of resources? 😛It all depends on how you do the joins and whether or not you use "Divide'n'Conquer" methods.
Hey, I know I dumped more on top of it and used even more resources 🙂 But that's the fun of it. :w00t:
Previously I had thought that Eric's code worked, so I added an ORDER BY clause to it to put the dates in order to make it easier to verify and added a couple more lines of sample data:
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';
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
)
order by k9id, date, drug;
K9IDDrugDoseFreqDate
136072008-08-06 00:00:00.000
1315062008-10-01 00:00:00.000
1310052008-10-05 00:00:00.000
137582009-08-14 00:00:00.000
1320082009-08-22 00:00:00.000
but sadly when I added more sample data, I can see that it really does not. Rows 4 & 5 are duplicated frequencies still.
I will break out my "free ginzu cutting knife" and use it on what I added to your code, sparing no lines of mine from death by knife. Like I mentioned before, I am pretty sure it is in the type of joins that I did. I took your code and nested it inside (your code ) AS PITAFREQ to create a single table that I then joined to all the others.
I will post back as I chop and clean.
Thanks again for all your help!
Dobermann
May 14, 2010 at 9:46 pm
BWAA-HAAA!!! Thanks for the feedback, Dobermann. I do have one more trick up my sleeve if all else fails. I've been trying to keep this in the "well documented code" range and not so much in the "black arts" range. But we can do that if performance falls through.
This has been fun. C'mon back when you get done whittling and let us know how you made out.
--Jeff Moden
May 15, 2010 at 2:05 am
Dobermann (5/14/2010)
Hey, I know I dumped more on top of it and used even more resources 🙂 But that's the fun of it. :w00t:
Previously I had thought that Eric's code worked, so I added an ORDER BY clause to it to put the dates in order to make it easier to verify and added a couple more lines of sample data:
....
Dobermann
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
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 16, 2010 at 12:29 am
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.
Viewing 15 posts - 16 through 30 (of 60 total)
You must be logged in to reply to this topic. Login to reply