November 25, 2009 at 7:42 am
Hi All,
I am stuck with a T-SQL query. I have a table with the following data.
ID Medicine StartDate EndDate Group
1 1 19900601 19940630 0
2 1 19900712 19940606 0
3 1 19940607 19940905 0
4 1 19940906 19961203 0
5 1 20050806 20080318 0
6 1 20080319 20370101 0
7 2 19901211 19940228 0
8 2 19910619 19940209 0
9 2 19940210 19950831 0
10 2 19940210 19940731 0
I have to update the Group filed on the basis of following criteria for each "Medicine ".
Record number 2 has EndDate '19940606' and record number 3 has StartDate '19940607'. These are consecutive dates.
Similarly record number 3 has EndDate '19940905' and record number 4 has StartDate '19940906' which are again consecutive dates.
So record number 2, 3 and 4 can be aligned together. So i have to assign a random number in the Group column for records 2, 3 and 4.
Moreover, record number 5 has EndDate '20080318' and record number 6 has StartDate '20080319'. These are consecutive dates.
So record number 5 and 6 can be aligned together. So i have to assign another random number in the Group column for records 5 and 6.
Same is the case for Medicine 2 where record number 8 has EndDate '19940209' and record number 9 has StartDate '19940210'. These are consecutive dates. So record number 8 and 9 would have another random number in the Group column.
Final result would look like the following:
ID Medicine StartDate EndDate Group
1 1 19900601 19940630 0
2 1 19900712 19940606 11111111
3 1 19940607 19940905 11111111
4 1 19940906 19961203 11111111
5 1 20050806 20080318 11223344
6 1 20080319 20370101 11223344
7 2 19901211 19940228 0
8 2 19910619 19940209 13256497
9 2 19940210 19950831 13256497
10 2 19940210 19940731 0
How can i do this in a query? Please me to solve this problem.
If you have any question please don't hesitate to ask. Thanks in advance.
Regards,
Sulaman
November 25, 2009 at 8:29 am
what methods have you tried so far?
Knowing what you have done will help to focus suggestions and comments.
1 possible solution would be to try a case statement.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 25, 2009 at 9:35 am
when u say "So record number 8 and 9 would have another random number in the Group column" are u saying those two will have the same random number?
November 25, 2009 at 9:47 am
Sulaman, could you please provide the sample data in the manner explained by the article in my signature? That would make it much easier for us to work with and will help you get a tested query.
Edit: Nevermind, it's all numbers so it's not that hard, stand by.
November 25, 2009 at 10:48 am
The setup portion is what I was referring to in my last post. Please provide sample data like this in the future, it makes it easier on us. I have all the extra selects in there to show what is happening on each step, they are not necessary beyond demonstration.
---------------- SETUP ------------------------------------
CREATE TABLE #Test(
ID int,
Medicine int,
StartDate char(8),
EndDate char(8),
GroupID int)
INSERT INTO #Test(ID, Medicine, StartDate, EndDate, GroupID)
SELECT 1, 1, '19900601', '19940630', 0 UNION ALL
SELECT 2, 1, '19900712', '19940606', 0 UNION ALL
SELECT 3, 1, '19940607', '19940905', 0 UNION ALL
SELECT 4, 1, '19940906', '19961203', 0 UNION ALL
SELECT 5, 1, '20050806', '20080318', 0 UNION ALL
SELECT 6, 1, '20080319', '20370101', 0 UNION ALL
SELECT 7, 2, '19901211', '19940228', 0 UNION ALL
SELECT 8, 2, '19910619', '19940209', 0 UNION ALL
SELECT 9, 2, '19940210', '19950831', 0 UNION ALL
SELECT 10, 2, '19940210', '19940731', 0
-- Show the Starting Table
SELECT * FROM #test
-----------------------------------------------------------
------------------- Solution ------------------------------
--This clustered index on ID is required, it may be already in your table,
--if it is not, either add it, or select the data into a temp table first
CREATE CLUSTERED INDEX IX_Quirk ON #Test(ID)
DECLARE @ED char(8),
@GroupID int,
@Medicine int
SET @GroupID = 0
-- Group the consecutive dates
UPDATE #Test
SET @GroupID = GroupID =
CASE WHEN Medicine = @Medicine AND
DATEADD(d,1,(CAST(@ED AS datetime))) = CAST(StartDate AS datetime)
THEN @GroupID
ELSE ABS(CHECKSUM(NEWID()))%1000000
END,
@ED = EndDate,
@Medicine = Medicine
FROM #Test OPTION (MAXDOP 1)
-- Show the groups
SELECT * FROM #Test
--DROP the groups WITH only 1 member
UPDATE #Test
SET GroupID = 0
FROM #Test
INNER JOIN (SELECT GROUPID
FROM #Test
GROUP BY GroupID
HAVING COUNT(*) = 1) SG ON #Test.GroupID = SG.GroupID
-- Final Result
SELECT * FROM #Test
-- Cleanup
DROP TABLE #test
November 25, 2009 at 11:00 am
November 25, 2009 at 9:05 pm
Well done, Seth! 🙂
--Jeff Moden
November 25, 2009 at 11:05 pm
Heh, thanks. You know me, I'm always ready to jump on any chance to use that quirky update! 😉
November 27, 2009 at 9:28 am
Seth, did your update meet all of the requirements of the quirky update?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 27, 2009 at 9:44 am
Seth, did your update meet all of the requirements of the quirky update?
Yessir. Why, does one seem missing to you?
November 27, 2009 at 10:15 am
Just asking is all, since there are quite a few of them and you are (to me) a new entity here. 🙂 I do seem to recall most of the versions of that I have seen being where all the action was on 1 statement, not two like you have (you have @medicine = medicine as third update as opposed to it being inline with the main one on that column).
I also believe strongly that any reference here (or elsewhere for that matter) to the quirky update needs to reference Jeff's article directly so that anyone that may use it will be explicitly aware of the potential issues and limitations and requirements.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 27, 2009 at 10:43 am
TheSQLGuru (11/27/2009)
Just asking is all, since there are quite a few of them and you are (to me) a new entity here. 🙂 I do seem to recall most of the versions of that I have seen being where all the action was on 1 statement, not two like you have (you have @medicine = medicine as third update as opposed to it being inline with the main one on that column).
Nah, not so new, but like you said, new to you, so... Hi :hehe:. Multiple statements like that work fine. Most of mine end up using multiple statements because the criteria is a bit more complicated than just a running total. The quirky update is my standard go-to method for iterative logic. The only real obstacle I've come up against is that SQL's normal "All at Once" processing method will override the iterative nature of this if you attempt to use correlated subqueries as part of the 3 part update(which would likely kill the speed anyways), so anything that you can't represent by increasing variables and requires looking back at the table won't work. I'd post an example, but I can't find one at the moment.
TheSQLGuru (11/27/2009)
I also believe strongly that any reference here (or elsewhere for that matter) to the quirky update needs to reference Jeff's article directly so that anyone that may use it will be explicitly aware of the potential issues and limitations and requirements.
I don't disagree with that, but unfortunately that article is under construction right now. There is a link to it in my signature, but Jeff is in the process of re-writing it to add additional information, so linking it isn't particularly useful at the moment; but I suppose people reading this at a later date could benefit from it.
[Edit] I was mistaken, the rewrite is completed. To see the article TheSQLGuru is referring to, click the link in my signature about running totals.
November 27, 2009 at 12:09 pm
Actually, the rewrite is out there now...
--Jeff Moden
November 27, 2009 at 12:15 pm
Jeff Moden (11/27/2009)
Actually, the rewrite is out there now...
My fault, I checked it again before I posted that, but I didn't fully read it, I just saw that it was still just a couple sentences. I feex.
November 27, 2009 at 12:26 pm
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply