How to display data for future 8 weeks from the given date(date can change based on selection)

Hi,

Maybe

MyDate +7, MyDate +14, MyDate +21, MyDate +28, ...

Select allOpen in new window

REgards

Given the amount of detail in the original post, I'm going to go with

SELECT 
'Style , Dept,Class,Subclass,04-Sep-17      11-Sep-17      18-Sep-17      25-Sep-17      02-Oct-17      09-Oct-17      16-Oct-17      23-Oct-17' 
FROM   dual 

Select allOpen in new window

If you can't be bothered to give enough information in the original question, I'm not going to try to guess.  Nobody here is a mind reader.  If you can't provide details, nobody can provide help.

Getting desired dates
row based:

select to_date('04.09.2017', 'dd.mm.yyyy') + (7 * (level - 1)) as the_date
  from dual
connect by level <= 8;

Select allOpen in new window

column based:

with base_sel as
 (select to_date('04.09.2017', 'dd.mm.yyyy') start_date
    from dual)
select a.start_date,
       a.start_date + to_char(7 * 1) next_monday1,
       a.start_date + to_char(7 * 2) next_monday2,
       a.start_date + to_char(7 * 3) next_monday3,
       a.start_date + to_char(7 * 4) next_monday4,
       a.start_date + to_char(7 * 5) next_monday5,
       a.start_date + to_char(7 * 6) next_monday6,
       a.start_date + to_char(7 * 7) next_monday7
  from base_sel a;

Select allOpen in new window

Thank you so much alexander which is helpfull

The listing of 8 weeks beginning with a specific date is easily accomplished (as Alex140181 has shown. However, I suspect that just a listing doesn't complete what it is that you are trying to accomplish. Are those dates to be used to join with some table or something else? Let us know your total intent and perhaps we can provide you with a more complete solution.

You're welcome, BUT as johnsone already mentioned, that was kind of guessed, too!
We need a bit more information to help ;-)

Actually that dates should come in horizontal not in vertical can you please help me how to get that without using pivot table

@hima: you really should provide much more information as the other experts already have mentioned before!!

select to_date('04.09.2017', 'dd.mm.yyyy') + (7 * (level - 1)) as the_date
  from dual
connect by level <= 8;

Results:

04-09-17
11-09-17
18-09-17
25-09-17
02-10-17
09-10-17
16-10-17
23-10-17

But my requirement is to display dates in horizontal instead of vertical like:
04-09-17,11-09-17,18-09-17,25-09-17,02-10-17,09-10-17,16-10-17,23-10-17

hima,
that's just the tip of the iceberg, isn't it

after we provide the pivot for you
your next question will be:
"how do i get data in those columns ?"

and our repeated counter question wil be "again" ... can your provide more details of what your trying to do ?

I totally agree with Geert!!
By the way, I already provided a "horizontal" solution/distribution of those desired values within my post above, you need to read carefully ;-)
By the way (part 2), you cannot do a complete dynamic pivoting, even if you use PIVOT/UNPIVOT functions in Oracle, you have to say, which values will be mapped to which columns and vice versa!

Bottom line: We need MORE and DETAILED information on this request/issue!!

Hi please find my below requirement i may not convey my requirement clearly the reason why i am sending excel sheet to understand clearly.Help me how to display data in below format.
Copy-of-Inv-report.xlsx

Actually this doesn't help at all, at least for me to understand...

Alexander, i tried my mind reading helmet.
and my laptop instantly rebooted ...

luckily it's working again ... my laptop, I mean.
I'm not going to use that helmet again.

i was trying to read a sample of his data ... didn't work

hima ?
can you provide a sample of your data
what you have and what you would like to see as the output of the query

just the headers is no good
otherwise we have to invent data, and we aren't any good at coming up with good data

@Geert: just "Alex" ;-) My parents used to to call me by my full name whenever I screw things up *lol*
But yes, I also wanted to put on that helmet, but now I'm scared like hell, so I won't ;-)

just "Alex".
ok

See if this helps get you where you want to be.  I had to make up my own dummy table and guess at columns and results.

/*
drop table tab1 purge;
create table tab1(style char(1), dept char(1), some_date date);

insert into tab1 values('a','a',to_date('09/04/2017','MM/DD/YYYY'));
insert into tab1 values('a','a',to_date('09/24/2017','MM/DD/YYYY'));

insert into tab1 values('b','b',to_date('09/04/2017','MM/DD/YYYY'));
insert into tab1 values('b','b',to_date('10/01/2017','MM/DD/YYYY'));
commit;

*/



select style, dept,
	count(case when some_date between date_1 and date_1+6 then 1 end) week1_count,
	count(case when some_date between date_2 and date_2+6 then 1 end) week2_count,
	count(case when some_date between date_3 and date_3+6 then 1 end) week3_count,
	count(case when some_date between date_4 and date_4+6 then 1 end) week4_count,
	count(case when some_date between date_5 and date_5+6 then 1 end) week5_count,
	count(case when some_date between date_6 and date_6+6 then 1 end) week6_count,
	count(case when some_date between date_7 and date_7+6 then 1 end) week7_count,
	count(case when some_date between date_8 and date_8+6 then 1 end) week8_count
from tab1,
(
select
	max(case when rn=1 then the_date end) date_1,
	max(case when rn=2 then the_date end) date_2,
	max(case when rn=3 then the_date end) date_3,
	max(case when rn=4 then the_date end) date_4,
	max(case when rn=5 then the_date end) date_5,
	max(case when rn=6 then the_date end) date_6,
	max(case when rn=7 then the_date end) date_7,
	max(case when rn=8 then the_date end) date_8
from(
select level rn, to_date('09/04/2017', 'MM/DD/YYYY') + (7 * (level - 1)) as the_date
  from dual
connect by level <= 8
)
)
group by style, dept
/

Select allOpen in new window

I'm also guessing you want the column headers to be the actual date.  That is a lot more complicated but can be done if this is a sqlplus report or something that understands sqlplus commands.

Please review this previous question for how I did it:
https://www.experts-exchange.com/questions/28689445/Dynamic-column-names-in-oracle-10g-sql.html?anchorAnswerId=40830902#a40830902


If this doesn't help, please post actual raw sample data, the actual expected results and the programs/tools you will be using to generate the results.

slightwv ... you know he's gonna come back because of your bad sample data ?

It's like looking for a needle in a haystack...

Give them what they ask for.

WITH prov_date 
     AS (SELECT To_date('09042017', 'mmddyyyy') dt 
         FROM   dual) SELECT 
'Filters,,Style,,Diff1,,Diff 2,,Dept,,Class,,Subclass,,Location,,From Date,' 
       ||To_char(prov_date.dt, 'dd-Mon-yy') 
FROM   prov_date 
UNION ALL 
SELECT ',,,,,,,,,,,,,,,,,' 
FROM   dual 
UNION ALL 
SELECT ',,,,,,,,,,,,,,,,,' 
FROM   dual 
UNION ALL 
SELECT 'Style,SKU,Diff 1,Diff 2,Dept,Class,Subclass,' 
       || To_char(prov_date.dt, 'dd-Mon-yy') 
       || ',' 
       || To_char(prov_date.dt + 7, 'dd-Mon-yy') 
       || ',' 
       || To_char(prov_date.dt + 14, 'dd-Mon-yy') 
       || ',' 
       || To_char(prov_date.dt + 21, 'dd-Mon-yy') 
       || ',' 
       || To_char(prov_date.dt + 28, 'dd-Mon-yy') 
       || ',' 
       || To_char(prov_date.dt + 35, 'dd-Mon-yy') 
       || ',' 
       || To_char(prov_date.dt + 42, 'dd-Mon-yy') 
       || ',' 
       || To_char(prov_date.dt + 49, 'dd-Mon-yy') 
       || ',Sold_Qty,,' 
FROM   prov_date 
UNION ALL 
SELECT ',,,,,,,Week total qty,,,,,,,,,,' 
FROM   dual 
UNION ALL 
SELECT ',,,,,,,(From 04 to 10 date qty should display,,,,,,,,,,' 
FROM   dual 
UNION ALL 
SELECT ',,,,,,,,,,,,,,,,,' 
FROM   dual 
UNION ALL 
SELECT ',,,,,,,,,,,,,,,,,' 
FROM   dual 
UNION ALL 
SELECT ',,,,,,,,,,,,,,,,,' 
FROM   dual 
UNION ALL 
SELECT ',,,,,,,,,,,,,,,,,' 
FROM   dual 
UNION ALL 
SELECT 
',,** Report will be showing for the following 8 weeks based on the from date provided.,,,,,,,,,,,,,,,'
FROM   dual; 

Select allOpen in new window

This produces the expected result in the requirement.  The requirement shows no data and no way to get the data.  The requirement is simply for a plain spreadsheet with no data and only headers.

I have given data my main is date columns should display as column names instead of column values in the place of column names
column name 04-sep-2017  11-sep-2017  18-sep-2017
column value  10                            20                   30

but now with the suggested query we are getting like

column name  week1               week2                week3
column value   04-sep-2017    11-sep-2017     18-sep-2017
Copy-of-Inv-report.xlsx

@johnsone: I totally understand your sarcasm here, but if that's gonna be the way things will be dealt with on EE, I'm gonna quit ;-)

So, here's my final try (and yes, I know, one shouldn't post things in uppercase, but I think here, it's more than neccessary):

@hima bindu: FOR HEAVEN'S SAKE, PLEASE CAN YOU PROVIDE MORE INFORMATION ON WHAT'S EXACTLY YOUR PROBLEM / WHAT YOU'RE TRYING TO ACHIEVE!!!!!!!!!

pivotting doesn't work with dynamic column names
that's why you get fixed column names

oracle is a boat ... we have to row it
it doesn't fly ... yet

My posted sample displays the counts as columns.

I also mentioned that if you want the column headers to show as dates, you can do that using sqlplus formatting.

We need to know how you will be producing the report.

It also appears you are looking for a copy and paste 100% accurate solution.  We can probably do that but we need what everyone has been asking for:  We need sample tables with data and the tools and/or products you will be suing to generate the output.

If you want a raw Excel file, that complicates things.

>>pivotting doesn't work with dynamic column names

I've show how you can use sqlplus to generate dynamic column names.  Sure, it isn't easy but it can be done.

that's not really my idea of dynamic column names
formatting in sqlplus ... hm ok, can work

but then you missed this note:
Note:  Have to use only SQL query from Oracle database.

next incoming question: what is sqlplus ?

>>pivotting doesn't work with dynamic column names

I've show how you can use sqlplus to generate dynamic column names.  Sure, it isn't easy but it can be done.

For generating a report with SQLPLUS (and thus going top-down, procedural style), yes, it's possible to generate dynamic columns (or headings), BUT if you want to do this with the help of PIVOT / UNPIVOT functions in a SQL statement, you definitely will fail!!

The OP already mentioned they cannot use PIVOT.  I'm guessing because of the database and/or tool versions involved.

>>Note:  Have to use only SQL query from Oracle database.

We can probably dummy up a very ugly UNION statement that generates the correct data in the correct order.

We can probably dummy up a very ugly UNION statement that generates the correct data in the correct order.

{Irony ON} Oh yeah, that would be so nice ;-) {Irony OFF}

Hima,
you have provided us with a sample of the aggregated data

and now ... the unaggregated data ?
some data can be difficult to pivot/aggregate

or ... don't you have any data yet ?

Hima,

Right now, you have at least 4 of the best people on this site looking at your question.  I highly recommend that you post what you need.  Sample data in the form of create and insert statements as well as expected results.

In the world I work in, a requirement is a document that contains everything that is required to complete the task.  If it isn't in the requirement it isn't needed and you aren't going to get it.

hima,
to clarify
'4 of the best people', that's not including me
i'm just mediocre ...

Geert,

I would include you.

Seriously, the people commenting on this are the who's who of the top 10 people (for the year) in the Oracle database category.  I see slightwv, myself, markgeer, Geert and Alex.  markgeer hasn't commented yet, but I have seen him on the viewing list a few times.  All top 10 contributors.  Every single one I would kill to have looking at a question I posted and all of them are looking at it.  I believe that all would fall into a top 20 overall in the Oracle database category.

@johnsone: this will be my last (hopefully) off-topic post on this thread, but damn, I am fu**ing honored to hear that from you. I could never imagine, people really thinking or seeing my as a true expert. I am speechless. Thank you so much!!

Back to work: @hima: If I were you, I would come up with some more than you did. I wouldn't want to get my question deleted just because of lacking information...

To try to help answer the original (and very incomplete) question here, after we got a bit more information in an Excel file, it looks like the business problem is: how to create a report that includes a matrix or cross-tab section by using a SQL query only.  

This is certainly not simple!  That may be why reporting tools for Oracle are available.  Some older reporting tools (Oracle Reports and Crystal Reports for example) included this capability.  It is likely that some newer reporting tools (Oracle BI and others) also include this capability, but I'm not an expert on the newer reporting tools.

To do this in SQL without using the PIVOT operator, you will have to use something like what slightwv suggested:
select style, dept,
      count(case when some_date between date_1 and date_1+6 then 1 end) week1_count,
      count(case when some_date between date_2 and date_2+6 then 1 end) week2_count,
...

This is my data

ITEM               GROUP_NAME        DEPT_NAME                    THE_DATE      QTY
100064942      BATH ROOM      WALL MOUNTED ACCS New      25-09-2017      10
100064942      BATH ROOM      WALL MOUNTED ACCS New      02-10-2017      20
100064942      BATH ROOM      WALL MOUNTED ACCS New      09-10-2017      30
100064942      BATH ROOM      WALL MOUNTED ACCS New      16-10-2017      40
100064942      BATH ROOM      WALL MOUNTED ACCS New      23-10-2017      50
100064942      BATH ROOM      WALL MOUNTED ACCS New      30-10-2017      60
100064942      BATH ROOM      WALL MOUNTED ACCS New      06-11-2017      70
100064942      BATH ROOM      WALL MOUNTED ACCS New      13-11-2017      80

Now my requirement is

my selection_date is  25-09-2017 should display 8 weeks prior and its qty

Item            Group_name   dept_name          25-09-2017 02-10-2017 09-10-2017 16-10-2017 23-10-2017 30-10-2017 06-11-2017 13-11-2017
100064942 BATH ROOM  MOUNTED accs    10                 20                  30                  40                 50             60                 70                   80

I think this sheet will make you understand better sorry all but thanks for your all help
8-weeks-date.xlsx

why can't you use pivot ?

what's your oracle version ?

last option yes i can use even pivot but how can only use for date and qty

anton's pivoting ...
i'll try it without that first

your dates are all trunc(date, 'IW') "Same day of the week as the first day of the ISO year"

you never have dates which fall in between ?
they always fall on the first day of the week ?

Note: when selection date is changed automatically 8 weeks date should get updated and its summation qty value gets updated

well, yeah, i figured as much ...
according to johnsone, you are dealing with some of the best people on the site, and me the mediocre one ...

ok, doesn't work without anton's pivoting
oracle out of the box pivoting doesn't allow dynamic pivoting
i'll add the script for that , it allows some sort of dynamic pivoting

i created a table from your sample data ... easier to work with

create table tab1 as
with 
  s as (
    select '100064942      BATH ROOM      WALL MOUNTED ACCS New      25-09-2017      10' x  from dual union all 
    select '100064942      BATH ROOM      WALL MOUNTED ACCS New      02-10-2017      20' x  from dual union all
    select '100064942      BATH ROOM      WALL MOUNTED ACCS New      09-10-2017      30' x  from dual union all
    select '100064942      BATH ROOM      WALL MOUNTED ACCS New      16-10-2017      40' x  from dual union all
    select '100064942      BATH ROOM      WALL MOUNTED ACCS New      23-10-2017      50' x  from dual union all
    select '100064942      BATH ROOM      WALL MOUNTED ACCS New      30-10-2017      60' x  from dual union all
    select '100064942      BATH ROOM      WALL MOUNTED ACCS New      06-11-2017      70' x  from dual union all
    select '100064942      BATH ROOM      WALL MOUNTED ACCS New      13-11-2017      80' x  from dual),
  ss as (    
    select 
      to_number(trim(substr(x, 1, 15))) item,
      trim(substr(x, 16, 14)) group_name,
      trim(substr(x, 31, 26)) dept_name,
      to_date(trim(substr(x, 57, 17)), 'dd-mm-yyyy') the_date,
      to_number(trim(substr(x, 74, 20))) qty
    from s) 
select * from ss

Select allOpen in new window

and then i used anton's pivoting to get your output:

alter session set nls_date_format='dd-mm-yyyy';

select * from table(superuser.pivot ('
with     
  p as (
    select distinct trunc(the_date, ''IW'') dt, row_number() over (order by the_date) n 
    from tab1 where the_date between trunc(the_date, ''IW'') and trunc(the_date, ''IW'') +8*7)
select t.* 
from tab1 t, p
where p.dt = trunc(t.the_date)
order by p.n'))

Select allOpen in new window

for anton's pivoting sources:
https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

look at the bottom of the page in resources
open the zip, and run pivotFun.sql in your schema

for a more extreme solution ... you could wait on others too
the us, those other great minds, only wake up in another 3 hours ...

ah ... i have a typo, remove the superuser schema name from the query

select * from table(superuser.pivot ('

that's where i store those pivot objects in my db

and sorting out those column names also seems to be a problem.
anton's pivoting sort the data with some odci structures

i got it correct with this sql:

alter session set nls_date_format='yyyy-mm-dd';

select * from table(pivot ('
with     
  p as (
    select distinct trunc(the_date, ''IW'') dt, row_number() over (order by the_date) n 
    from tab1 where the_date between trunc(the_date, ''IW'') and trunc(the_date, ''IW'') +8*7),
  x as (
    select t.item, t.group_name, t.dept_name, t.the_date, t.qty 
    from tab1 t, p
    where p.dt = trunc(t.the_date)
    order by p.n)
select * from x     
'))

Select allOpen in new window

Nice work, Geert :-)
Now, I'm really looking forward to getting the OP's response ;-)

well, i know what the first response will be.
that's not with a query only ...

my personal response to that is: not possible

but i might get contradicted by the non-mediocre minds :)

Thank you for the data and expected results.

You now have one last piece of information to provide us.

>>Now my requirement is

What tool/program/product are you using to generate that report and what tool/program/product will you be using to present the results?

I've posted the link to using sqlplus commands to generate dynamic headers.  I've also shown how to pivot without using the PIVOT SQL command.

Are you saying that you can now use the PIVOT SQL built in to Oracle?

Anton's pivoting is not built in ... ;)

Still expecting an answer on that challenge... giving up is possible too ... grin
Good thing mediocre me wasn't challenged :)

Good thing mediocre me wasn't challenged :)

And lead us not into temptation, ...

Hi,

BI tool BI publisher 10g version and  oracle databese 10g version using only sql we can do bcz bi tool will allow only selection.

In pivot how can I send dynamic dates

In pivot how can I send dynamic dates

You CAN'T (using only SQL)!

it would have helped if you provided this info first.
10g doesn't have pivot function

not sure if anton's pivoting works in 10g.
if it does, just create a view and read from that

the selection is built into the sql of query
you never mentioned upfront you wanted to change the selection

create view vw_pivot_for_bi as 
select * from table(pivot (' ...

Select allOpen in new window

Thank you so much for all comments.Is there any other process that we can use using sql

probably yes, but i reckon you'll have to wait until oracle version 14

sometimes the answer to your question is: that's not possible
alas, that's a correct answer too sometimes

half of the comments in this question was unnecessary
that half was mainly given because of your reluctance in providing the necessary information

Hi,
I am running this statement in sql developer which is working fine giving expected result

select to_date(:from_date,'MM-dd-yyyy') + (7 * (level - 1)) as the_date from dual connect by level <= 8

But when i am using this statement in BI publisher 11g giving error

[03-10-2017 12:37] Himabindu Samanthula:
oracle.xdo.servlet.ReportException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: java.sql.SQLDataException: ORA-01858: a non-numeric character was found where a numeric was expected
        
can anyone please help me out the reason and how can i execute above statement in BI publisher.

BI is probably given a different type of date format
you are providing the string in the correct format in sql developer.

obviously BI is not smart enough to give in the correct format

BI is probably getting it's date settings from the server it's running on
is most likely not 'MM-dd-yyyy'

this is a completely different question
it's custom to create a different question for that

@OP, hima: how's the status here?! If you're not going to respond, we have to auto close this one...