April 8, 2006 at 9:47 am
Data apx (5 million rows):
Span example:
-------------M---------------
-------------Rx-------------
Needs to b converted to this:
---M---|-------M & Rx------|---Rx--
The time spans can slide either way.
Data example:
MemberID Eff_Date Term_Date Med_COB Rx_COB
1 20050101 20050912 Y N
1 20050310 20051120 N Y
1 20060101 <null> Y N
1 20060101 <null> N Y
Resulting Records need to be in this format:
MemberID Eff_Date Term_Date Med_COB Rx_COB
1 20050101 20050310 Y N
1 20050311 20050912 Y Y
1 20050913 20051120 N Y
1 20060101 <null> Y Y
Any help with this problem would be greatly appreciated. We are running SQL2K. I like most people,would like to stay away from cursors and loops if possible.
Thanks,
Brian
April 8, 2006 at 10:46 am
So youjust need to order by those two bit fields? Are they bit fields or varchar's
(are then 'y' or 1)
I created a table matching your but for simplicity I used int's. If your fields are bit's you probably have to cast them as int's for this to work.
select * from (
SELECT TOP 100 PERCENT
med_cob, rx_cob,
case when rx_cob=0 then (med_cob*3)+rx_cob else med_cob+rx_cob end as seq
FROM dbo.ttmp) t
order by seq desc
I get...
Med, Rx Seq
103
112
011
011
000
You could probably do this in one query but I like sub-queries for readability.
cast bits as ints...
(cast(med_cob as int)*3)
Is that on the right track?
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
April 8, 2006 at 11:11 am
Wow nice. Thank you for the reply.
That is definately on the right track. I am sorry, I think I was a little mis-leading when I said the resulting records need to be in this format. I meant that the result set is what I want to be left with after processing the first set. So there needs to do some date maniplations with it too.
The *_COB fields are char(1), but I could have the extract sent to me with 1 or 0 instead and load them in to a tinyint.
Thanks Again,
Brian
April 10, 2006 at 6:55 am
Hello,
I don't think it will be that simple. As I understand the question, this is not just order by - I don't have time at the moment to check it properly, but it is an interesting problem and I'll look into it later.
If some other people would attempt to solve it, here is the DDL and data I created based on the post:
CREATE TABLE source(MemberID int, Eff_Date datetime, Term_Date datetime, Med_COB varchar(1), Rx_COB varchar(1))
INSERT INTO source (MemberID, Eff_Date, Term_Date, Med_COB, Rx_COB) VALUES (1,'20050101','20050912','Y','N')
INSERT INTO source (MemberID, Eff_Date, Term_Date, Med_COB, Rx_COB) VALUES (1,'20050310','20051120','N','Y')
INSERT INTO source (MemberID, Eff_Date, Term_Date, Med_COB, Rx_COB) VALUES (1,'20060101',NULL,'Y','N')
INSERT INTO source (MemberID, Eff_Date, Term_Date, Med_COB, Rx_COB) VALUES (1,'20050101',NULL,'N','Y')
April 10, 2006 at 2:04 pm
Using Vladan's table source, the data can be rearranged declaratively as follows:
-- first create a time line that is ponctuated by starting date and ending date
create table #t (rowno int identity(1,1), eDate smalldatetime, Club char(2), IsTerm int)
insert #t (eDate, IsTerm)
select * from (
select eDate=eff_date, IsTerm=0 from source
union
select eDate = term_date, IsTerm=1 from source
) tmp
group by eDate, IsTerm
order by 1
-- insert ending dates
insert #t (eDate, IsTerm)
select eDate-1, 1 from #t
where rowno / 2.0 - rowno/2 = 0
and IsTerm <> 1 and rowno <= 6
-- insert starting dates
insert #t (eDate, IsTerm)
select eDate+1, 0 from #t
where rowno / 2.0 - rowno/2 > 0
and IsTerm <> 0 and rowno <= 6
-- repopulate #t so that date points are sorted
select eDate, IsTerm into #a
from #t
order by 1
truncate table #t -- drop table #a
insert #t (eDate, IsTerm)
select * from #a
-- now we have a time line, identify which club(s) the member belongs:
update s
set
Club = (case when a.Eff_date is not null then 'M' else '' end)
+ (case when b.Eff_date is not null then 'R' else '' end)
from #t s join #t e on s.rowno = e.rowno-1 and s.IsTerm = 0
left join source a on a.Med_Cob = 'Y' and (s.eDate between a.eff_date and a.term_Date)
left join source b on b.Rx_COB = 'Y' and (s.eDate between b.eff_date and b.term_Date)
-- the result would look like this:
select Eff_date=s.eDate, Term_Date=e.eDate, s.Club
from #t s join #t e on s.rowno = e.rowno-1 and s.IsTerm = 0
hope this helps.
April 10, 2006 at 2:24 pm
ps. I changed the original data a little bit: I set the termination dates to 1/1/2079 for those where term_date is null. this is to avoid dealing with null fields, and you can set them to null afterwards if you like.
and of course when inserting new dates, you need to select max(rowno) into a variable first. I use 6 because that is max(rowno) for this toy data.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply