Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    7

    Unanswered: displaying Dynamic week columnwise

    Hi All,

    I want to write a sql query in which i will get the week numbers dynamically column wise between two dates.

    for eaxmeple if i have start date 1-jan-2009 and end date 31-march-2009 then the query

    select weekending,rownum n from(
    select distinct weekending,to_date(:startdate,'ddmonyyyy') FROMDATE, to_date(:enddate,'ddmonyyyy') toDate from AV_ERRDUMP_CATMASTER
    where (DUMPFROM>=to_date(:startdate,'ddmonyyyy') AND DUMPTO<=to_date(:enddate,'ddmonyyyy')) and weekending is not null
    )
    i am getting output like this

    wekending n

    4-jan-2009 1
    11-jan-2009 2
    18-jan-2009 3
    25-jan-2009 4
    1-feb-2009 5
    8-feb-2009 6
    15-feb-2009 7
    22-feb-2009 8
    ete etc

    but i want output like this

    wk1 wk2 wk3 wk4 wk5 wk6
    4-jan-2009 11-jan-2009 18-jan-2009 25-jan-2009 1-feb-2009 8-feb-2009

    for this out i have modified the above query

    select
    decode(n,1,weekending)wk1,
    decode(n,2,weekending)wk2,
    decode(n,3,weekending)wk3,
    decode(n,4,weekending)wk4,
    decode(n,5,weekending)wk5,
    decode(n,6,weekending)wk6,
    decode(n,7,weekending)wk7,
    decode(n,8,weekending)wk8,
    --decode(n,n,weekending)wk||n
    from(
    select weekending,rownum n from(
    select distinct weekending,to_date(:startdate,'ddmonyyyy') FROMDATE, to_date(:enddate,'ddmonyyyy') toDate from AV_ERRDUMP_CATMASTER
    where (DUMPFROM>=to_date(:startdate,'ddmonyyyy') AND DUMPTO<=to_date(:enddate,'ddmonyyyy')) and weekending is not null
    )
    )

    but this query will work only if have fixed start date and end date.
    but it may be (1-jan -2009 and 31-octber-2009) or (1-mar-2009 and 30-apr-2009) or (1-june-2009 and 30-nov-2009)

    Since my both dates are not fixed i am not able to retrieve the week numbers as column wise.

    Can anybody help me in this regard.

    Thanks

  2. #2
    Join Date
    Aug 2009
    Posts
    262
    try &startdate &enddate

    i.e to_date(&startdate,'ddmonyyyy')

  3. #3
    Join Date
    Aug 2009
    Posts
    262
    select
    decode(n,1,weekending)wk1,
    decode(n,2,weekending)wk2,
    decode(n,3,weekending)wk3,
    decode(n,4,weekending)wk4,
    decode(n,5,weekending)wk5,
    decode(n,6,weekending)wk6,
    decode(n,7,weekending)wk7,
    decode(n,8,weekending)wk8,
    --decode(n,n,weekending)wk||n
    from(
    select weekending,rownum n from(
    select distinct weekending,to_date(&startdate,'ddmonyyyy') FROMDATE, to_date(&enddate,'ddmonyyyy') toDate from AV_ERRDUMP_CATMASTER
    where (DUMPFROM>=to_date(&startdate,'ddmonyyyy') AND DUMPTO<=to_date(&enddate,'ddmonyyyy')) and weekending is not null
    )

  4. #4
    Join Date
    Apr 2009
    Posts
    7
    The solution you provided me is to use &startdate instead of :startdate.
    my problem is not with the variables..
    i want to generate the week nos dynamically.
    that is something like decode(n,d,weekending)w
    where n,d,w should be the variables (somthing ;like what we do in for loop for i=1;i<=n;i++) and loop will run till the n'th time).

    below statement is hardcoded. which will not work if i will give my startdate 1jan2009 and enddate 31oct2009.
    and this is also not sensible to write manually every week nos using decode.

    decode(n,1,weekending)wk1,
    decode(n,2,weekending)wk2,
    decode(n,3,weekending)wk3,
    decode(n,4,weekending)wk4,
    decode(n,5,weekending)wk5,
    decode(n,6,weekending)wk6,
    decode(n,7,weekending)wk7,
    decode(n,8,weekending)wk8,

    so can you help me to get somthing like this?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •