computed fields

[mdarsot]

select
substr(output_svycode_NM,1,2) as SI,
SUM(PROCESSING_BATCHSIZE_QTY) AS LOADS,
to_char(batch_release_dt,'IW') WK
from
PARTS_DATABSE
where
(OUTPUT_SVYCODE_NM like '29%')
and batch_release_dt >='02-FEB-09'
and batch_release_dt <'27-DEC-09'
group by
substr(OUTPUT_SVYCODE_NM,1,2),
to_char(batch_release_dt,'IW')


This gives me result like following

SI LOADS WK
-- ----------- --
29 377068 06
29 512180 07
29 177249 08
29 257117 09
29 391693 10
29 242608 11


What i would like is something like below

SI Week_Sent LOADS WK Expected Return (sent +8days)
-- ---------- ------ ----- ---------------
29 Feb2-Feb8 377068 06 Feb10-Feb16
29 Feb9-Feb15 512180 07 Feb17-Feb23
29 Feb16-Feb22 177249 08 Feb24-Mar02
29 Feb23-Mar01 257117 09 Mar03-Mar09
29 Mar02-Mar08 391693 10 Mar10-Mar16
29 Mar09-Mar15 442608 11 Mar17-Mar22

 

[SantaMufasa]

MDarsot,

There are probably dozens of ways to solve this need, and here is one of them:

select SI
      ,week_sent
      ,LOADS
      ,x.WK
      ,exp_ret
  from (select substr(output_svycode_NM,1,2) si
              ,SUM(PROCESSING_BATCHSIZE_QTY) loads
              ,to_char(batch_release_dt,'IW') wk
          from PARTS_DATABASE
         group by substr(output_svycode_NM,1,2)
                 ,to_char(batch_release_dt,'IW') 
       ) x
      ,(select min(b.dt) beg_dt, min(e.dt)-1 end_dt
              ,to_char(min(b.dt),'fmMonDD')||'-'||to_char(min(e.dt)-1,'fmMonDD') week_sent
              ,to_char(min(b.dt)+8,'fmMonDD')||'-'||to_char(min(e.dt)-1+8,'fmMonDD') exp_ret
              ,wk
          from (SELECT to_date(to_char(sysdate,'YYYY')||LEVEL,'yyyyDDD') dt
                  FROM dual
               CONNECT BY LEVEL <=365) b
              ,(SELECT to_date(to_char(sysdate,'YYYY')||LEVEL,'yyyyDDD') dt
                  FROM dual
               CONNECT BY LEVEL <=365) e
              ,(select level wk from dual connect by level <= 52)
         where to_char(b.dt,'IW') = wk
           and to_char(e.dt,'IW') = wk+1
         group by wk
       ) y
 where x.wk = y.wk
 order by x.wk
/

I didn't have your data with which to test, but I believe it will work fine for you.

Please let me know if it works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ChrisHunt]

My, that seems a bit complicated Santa. If I understand the OP's requirements correctly, this should work instead:

select
    substr(output_svycode_NM,1,2) as SI,
    TO_CHAR(TRUNC(batch_release_dt,'IW'),'FMMonDD')||'-'||
    TO_CHAR(TRUNC(batch_release_dt,'IW')+6,'FMMonDD') week_sent,
    SUM(PROCESSING_BATCHSIZE_QTY) AS LOADS,
    to_char(batch_release_dt,'IW') WK,
    TO_CHAR(TRUNC(batch_release_dt,'IW')+8,'FMMonDD')||'-'||
    TO_CHAR(TRUNC(batch_release_dt,'IW')+14,'FMMonDD') exp_return
from
    PARTS_DATABSE
where
        (OUTPUT_SVYCODE_NM like '29%')
        and batch_release_dt >='02-FEB-09'
        and batch_release_dt <'27-DEC-09'
group by
    substr(OUTPUT_SVYCODE_NM,1,2),
    to_char(batch_release_dt,'IW'),
    TRUNC(batch_release_dt,'IW')

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[SantaMufasa]

My, that seems a bit complicated Santa. If I understand the OP's requirements correctly, this should work instead...

Absolutely correct on both counts, Chris. The method to my madness, however, had to do with the bit about "the OP's requirements"...If the OP's requirements are like most business requirements, requirements creep <grin>. My over-engineering was for the case (that I honestly thought MDarsot would bring up) where every (IW) week in the year needed to have representation, regardless of whether there was a batch_release_dt during each week.


In such case, my original code needs simply the addition of an outer-join symbol "(+)" to have each week in the year represented:

select SI
      ,week_sent
      ,LOADS
      ,y.WK
      ,exp_ret
  from (select substr(output_svycode_NM,1,2) si
              ,SUM(PROCESSING_BATCHSIZE_QTY) loads
              ,to_char(batch_release_dt,'IW') wk
          from PARTS_DATABASE
         group by substr(output_svycode_NM,1,2)
                 ,to_char(batch_release_dt,'IW') 
       ) x
      ,(select min(b.dt) beg_dt, min(e.dt)-1 end_dt
              ,to_char(min(b.dt),'fmMonDD')||'-'||to_char(min(e.dt)-1,'fmMonDD') week_sent
              ,to_char(min(b.dt)+8,'fmMonDD')||'-'||to_char(min(e.dt)-1+8,'fmMonDD') exp_ret
              ,wk
          from (SELECT to_date(to_char(sysdate,'YYYY')||LEVEL,'yyyyDDD') dt
                  FROM dual
               CONNECT BY LEVEL <=365) b
              ,(SELECT to_date(to_char(sysdate,'YYYY')||LEVEL,'yyyyDDD') dt
                  FROM dual
               CONNECT BY LEVEL <=365) e
              ,(select level wk from dual connect by level <= 52)
         where to_char(b.dt,'IW') = wk
           and to_char(e.dt,'IW') = wk+1
         group by wk
       ) y
 where x.wk(+) = y.wk
 order by y.wk
/

SI WEEK_SENT        LOADS         WK EXP_RET
-- ----------- ---------- ---------- -----------
   Jan1-Jan4                       1 Jan9-Jan12
   Jan5-Jan11                      2 Jan13-Jan19
   Jan12-Jan18                     3 Jan20-Jan26
   Jan19-Jan25                     4 Jan27-Feb2
   Jan26-Feb1                      5 Feb3-Feb9
29 Feb2-Feb8       377068          6 Feb10-Feb16
29 Feb9-Feb15      512180          7 Feb17-Feb23
   Feb16-Feb22                     8 Feb24-Mar2
   Feb23-Mar1                      9 Mar3-Mar9
   Mar2-Mar8                      10 Mar10-Mar16
   Mar9-Mar15                     11 Mar17-Mar23
   Mar16-Mar22                    12 Mar24-Mar30
...

But, as I said in the very beginning:

There are probably dozens of ways to solve this need...

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[mdarsot]

Thanks for all your help on this. I need one more thing with it.

select
substr(output_svycode_NM,1,2) as SI,
TO_CHAR(TRUNC(batch_release_dt,'IW'),'FMMonDD')||'-'||
TO_CHAR(TRUNC(batch_release_dt,'IW')+6,'FMMonDD') week_sent,
SUM(PROCESSING_BATCHSIZE_QTY) AS LOADS,
to_char(batch_release_dt,'IW') WK,
TO_CHAR(TRUNC(batch_release_dt,'IW')+8,'FMMonDD')||'-'||
TO_CHAR(TRUNC(batch_release_dt,'IW')+14,'FMMonDD') exp_return
from
PARTS_DATABSE
where
(OUTPUT_SVYCODE_NM like '29%')
and batch_release_dt >='02-FEB-09'
and batch_release_dt <'27-DEC-09'
group by
substr(OUTPUT_SVYCODE_NM,1,2),
to_char(batch_release_dt,'IW'),
TRUNC(batch_release_dt,'IW')



The above gives me results like this
SI Week_Sent LOADS WK Expected Return (sent +8days)
-- ---------- ------ ----- ---------------
29 Feb2-Feb8 377068 06 Feb10-Feb16
29 Feb9-Feb15 512180 07 Feb17-Feb23
29 Feb16-Feb22 177249 08 Feb24-Mar02
29 Feb23-Mar01 257117 09 Mar03-Mar09
29 Mar02-Mar08 391693 10 Mar10-Mar16
29 Mar09-Mar15 442608 11 Mar17-Mar22


The only Change i want to request is that i have 2 strings i want to display one is

All 29U's
All 29UT's

The above is displayign all 29(*) how can i make it display by segment (29U*, 29UT*)

The above gives me results like this
SI Week_Sent LOADS WK Expected Return (sent +8days)
-- ---------- ------ ----- ---------------
29U Feb2-Feb8 377068 06 Feb10-Feb16
29UT Feb2-Feb8 100000 06 Feb10-Feb16
29 Feb9-Feb15 512180 07 Feb17-Feb23
29 Feb16-Feb22 177249 08 Feb24-Mar02
29 Feb23-Mar01 257117 09 Mar03-Mar09
29 Mar02-Mar08 391693 10 Mar10-Mar16
29 Mar09-Mar15 442608 11 Mar17-Mar22

 

[SantaMufasa]

So, MDarsot, given the code we have already helped you create, can you suggest code that gives two additional characters beyond the two ("29") that you are already receiving?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[mdarsot]

if i change below
substr(output_svycode_NM,1,4) as SI and i can get seperate segment.


However i still want to combine anything that is 29U* except 29UT is my delemma.

 

[SantaMufasa]

MDarsot,

The following should work for you:

select SI
      ,week_sent
      ,LOADS
      ,x.WK
      ,exp_ret
  from (select si
              ,SUM(PROCESSING_BATCHSIZE_QTY) loads
              ,wk
          from (select case when substr(output_svycode_NM,1,4) = '29UT' then '29UT'
                            when substr(output_svycode_NM,1,3) = '29U' then '29U'
                            when substr(output_svycode_NM,1,2) = '29' then '29'
                       end si
                      ,PROCESSING_BATCHSIZE_QTY
                      ,to_char(batch_release_dt,'IW') wk
                  from PARTS_DATABASE)
         group by si
                 ,wk
       ) x
      ,(select min(b.dt) beg_dt, min(e.dt)-1 end_dt
              ,to_char(min(b.dt),'fmMonDD')||'-'||to_char(min(e.dt)-1,'fmMonDD') week_sent
              ,to_char(min(b.dt)+8,'fmMonDD')||'-'||to_char(min(e.dt)-1+8,'fmMonDD') exp_ret
              ,wk
          from (SELECT to_date(to_char(sysdate,'YYYY')||LEVEL,'yyyyDDD') dt
                  FROM dual
               CONNECT BY LEVEL <=365) b
              ,(SELECT to_date(to_char(sysdate,'YYYY')||LEVEL,'yyyyDDD') dt
                  FROM dual
               CONNECT BY LEVEL <=365) e
              ,(select level wk from dual connect by level <= 52)
         where to_char(b.dt,'IW') = wk
           and to_char(e.dt,'IW') = wk+1
         group by wk
       ) y
 where x.wk = y.wk
 order by x.wk
/

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”