Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-10-27 : 08:11:20
|
| rani writes "I have the following sql that needs to be altered to do some additional date processing. It has to be done in one query because it's being used behind a PB datawindow and in a view and I don't know if it's possible so looking for help from the sql experts.This is the current query: (what it does is give me rows based on the following lob logic. If a 1 exists, I don't want 2,3,or 5. If no 1 exists, I want 2,3,5. I always want 4.select sa.affiliation_nmbr, sa.affiliation_member_seq, sa.group_nmbr, sa.section_nmbr, lcr.lob_cd,lcr.lob_type_begin_dt, lcr.lob_type_end_dt from lob_contract_range lcr inner join sub_affiliation sa on lcr.sub_affiliation_sak = sa.sub_affiliation_sak where (lob_cd in ('1','4') or NOT EXISTS (SELECT 1 FROM lob_contract_range lcr2 WHERE lcr2.lob_cd = '1' AND lcr2.sub_affiliation_sak = lcr.sub_affiliation_sak ))and affiliation_nmbr = '885'This is the sample data that presented a problem:affiliation_nmbr affiliation_member_seq group_nmbr section_nmbr lob_cd lob_type_begin_dt lob_type_end_dt 885 A 36560 0930 1 2003-03-01 2003-07-31 885 A 36560 0930 2 2003-03-01 2004-02-29 Note that the dates overlap. What my results should be is a lob 1 for 3/1 throug 7/31 because a 1 exists, therefore I ignore the 2. I should get a 2 for 8/1 through 2/29/2004 because only a 2 exists for that period. I use this query as a derived table in a bigger query where I then have a date that looks to exist between the date results to see what lob I should use.Some initial thoughts I had were if I could get the results to return a month by month result so for 3/1/03-3/31/03 it's a 1, for april it's a 1, for august it's a 2. or subtract the dates when I have multiple dates. Easy enough but getting it done in one query presents a problem. Maybe I need a whole new way of looking at it.Any thoughts?your help is very greatly appreciated!!!" |
|
|
|
|
|
|
|
sql help, date processing