sql - Identify open cases for each week during a year -

-

i trying produce report identifies client cases open during each week of year. have following sql returns clients indicator on whether case open during week 1 of our calendar. client has 2 aspects identifies if case open - mov_start_date , esu_start date should greater end date of period, , mov_end_date/esu_start date should either null or greater start date of period.

the below code works, thought copy left join wk1 , rename wk2 return information week 2 i'm getting error relating ambiguously named columns. additionally, i'm guessing having 52 (one each week) left joins on report isn't particularly advisable, again i'm wondering if there better way of achieving this?

    select a.esu_per_gro_id, a.esu_id, a.status, b.mov_id, b.mov_start_date, b.mov_end_date, a.esu_start_date, a.esu_end_date, ls.cls_desc, nvl2(wk1.prd_period_num,'y','n') "week1"   left join b on b.mov_per_gro_id = a.esu_per_gro_id  left join ls on ls.cls_code = a.status  left join o_periods wk1 on b.mov_start_date < wk1.prd_end_date , (b.mov_end_date null or b.mov_end_date > wk1.prd_start_date) , a.esu_start_date  < wk1.prd_end_date , (a.esu_end_date null or a.esu_end_date > wk1.prd_start_date) , prd_cal_id = 'e1190' , wk1.prd_period_num = 1 , wk1.prd_year = 2012  b.mov_start_date  not null   , a.status <> ('x')

hopefully have provided enough information, if not, happy answer questions. thanks!

sample data (produced above query)

 p id    esu_id status  mov_id  m_start     m_end   desc    week1  1      esu1       new      1m  01/01/2012           boo    y  2      esu2       new     2m   01/03/2012           boo    n

desired output (week1 - week 52)

 p id    esu_id status  mov_id  m_start     m_end   desc    week1 week2  1      esu1       new     1m   01/01/2012           boo    y     y  2      esu2       new     2m   01/03/2012           boo    n     n

i suspect reason creating wk2 join wk1 didn't work column prd_cal_id didn't have table alias on it. however, guessed, 52 joins not going perform well. try following:

select a.esu_per_gro_id,            a.esu_id,            a.status,            b.mov_id,            b.mov_start_date,            b.mov_end_date,            a.esu_start_date,            a.esu_end_date,            ls.cls_desc,            'week' || trim(to_char(pd.prd_period_num)) week_desc     left join b     on b.mov_per_gro_id = a.esu_per_gro_id       left join ls     on ls.cls_code = a.status       left join o_periods pd     on b.mov_start_date < pd.prd_end_date ,        (b.mov_end_date null or         b.mov_end_date > pd.prd_start_date) ,        a.esu_start_date  < pd.prd_end_date ,        (a.esu_end_date null or         a.esu_end_date > pd.prd_start_date) b.mov_start_date not null ,       a.status <> ('x') ,       pd.prd_cal_id = 'e1190' ,       pd.prd_year = 2012 order week_desc

this produces different results original query, having week_desc instead of trying create 52 different columns, 1 each week, think perform better.

share , enjoy.


Comments

Post a Comment

Popular posts from this blog

c# - SVN Error : "svnadmin: E205000: Too many arguments" -

-
i trying reposotries using c# code process svncommand = null; var psi = new processstartinfo("svnadmin"); psi.redirectstandardoutput = true; psi.redirectstandarderror = true; psi.useshellexecute = false; psi.arguments= @"dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump"; using (svncommand = process.start(psi)) { var myoutput = svncommand.standardoutput; var myerror = svncommand.standarderror; debug.write("output :" + environment.newline + environment.newline + myoutput.readtoend() + environment.newline + "error :" + environment.newline + environment.newline + myerror.readtoend()); svncommand.close(); } when use dump command commandline svnadmin dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump it works fine when try ...
Read more

c# - Copy ObservableCollection to another ObservableCollection -

-
how copy observablecollection item observablecollection without reference of first collection? here observablecollection item value changes affecting both collections. code private observablecollection<ratemodel> _allmetalrate = new observablecollection<ratemodel>(); private observablecollection<ratemodel> _metalrateondate = new observablecollection<ratemodel>(); public observablecollection<ratemodel> allmetalrate { { return this._allmetalrate; } set { this._allmetalrate = value; notifypropertychanged("metalrate"); } } public observablecollection<ratemodel> metalrateondate { { return this._metalrateondate; } set { this._metalrateondate = value; notifypropertychanged("metalrateondate"); } } foreach (var item in metalrateondate) allmetalrate.add(item); what causing , how can solve it? you need clone object referenced item before it's a...
Read more

All overlapping substrings matching a java regex -

-
is there api method returns (possibly overlapping) substrings match regular expression? for example, have text string: string t = 04/31 412-555-1235; , , have pattern: pattern p = new pattern("\\d\\d+"); matches strings of 2 or more characters. the matches are: 04, 31, 412, 555, 1235. how overlapping matches? i want code return: 04, 31, 41, 412, 12, 55, 555, 55, 12, 123, 1235, 23, 235, 35. theoretically should possible -- there obvious o(n^2) algorithm enumerates , checks substrings against pattern. edit rather enumerating substrings, safer use region(int start, int end) method in matcher . checking pattern against separate, extracted substring might change result of match (e.g. if there non-capturing group or word boundary check @ start/end of pattern). edit 2 actually, it's unclear whether region() expect zero-width matches. specification vague, , experiments yield disappointing results. for example: string line = "xx90xx"; strin...
Read more