Re: SQL Question

Or this. The idea is about the same but if you have a big table the execution will be faster (the table will be scanned once instead of 6 times).

ACCEPT SDATE CHAR PROMPT 'Start Date: '

SELECT &SDATE,

	 decode(MONTHS_BETWEEN(start_date,TO_DATE(&SDATE,'DD-MON-YYYY')),
		0,
		month1_data,
		0),
	 decode(MONTHS_BETWEEN(start_date,TO_DATE(&SDATE,'DD-MON-YYYY')),
		0,
		month2_data,
		1,
		month1_data,
		0),
	 decode(MONTHS_BETWEEN(start_date,TO_DATE(&SDATE,'DD-MON-YYYY')),
		0,
		month3_data,
		1,
		month2_data,
		2,
		month1_data,
		0),
	 decode(MONTHS_BETWEEN(start_date,TO_DATE(&SDATE,'DD-MON-YYYY')),
		0,
		month4_data,
		1,
		month3_data,
		2,
		month2_data,
		3,
		month1_data,
		0),
	 decode(MONTHS_BETWEEN(start_date,TO_DATE(&SDATE,'DD-MON-YYYY')),
		0,
		month5_data,
		1,
		month4_data,
		2,
		month3_data,
		3,
		month2_data,
		4,
		month1_data,
		0),
	 decode(MONTHS_BETWEEN(start_date,TO_DATE(&SDATE,'DD-MON-YYYY')),
		0,
		month6_data,
		1,
		month5_data,
		2,
		month4_data,
		3,
		month3_data,
		4,
		month2_data,
		5,
		month1_data,
		0)

pberetta_at_my-deja.com a écrit dans l'article <86365r$6eu$1_at_nnrp1.deja.com>...
> Craig
> If you get desparate enough, you might try this:
>
> ACCEPT SDATE CHAR PROMPT 'Start Date: '
>
> SELECT &SDATE, month1_data, month2_data, month3_data, month4_data,
> month5_data, month6_data
> FROM your_table
> WHERE start_date = TO_DATE(&SDATE,'DD-MON-YYYY')
> UNION
> SELECT &SDATE, ' 0', month2_data, month3_data, month4_data,
> month5_data, month6_data
> FROM your_table
> WHERE start_date = ADD_MONTHS(TO_DATE(&SDATE,'DD-MON-YYYY'),1)
> UNION
> SELECT &SDATE, ' 0',' 0', month3_data, month4_data, month5_data,
> month6_data
> FROM your_table
> WHERE start_date = ADD_MONTHS(TO_DATE(&SDATE,'DD-MON-YYYY'),2)
> UNION
> -- think you get the idea by now, just keep on adding unions.
>
> Assuming MONTHn_DATA is a number, you may want to LPAD(TO_CHAR
> (MONTHn_DATA),4) (or whatever the max number of digits could be) all of
> the MONTHn_DATA columns to maintain alignment.
>
> It's an painfully ugly, brute force solution - but your requirements
> that all output lines display a single date, rather than the record
> date and that months after January display progressively fewer columns
> limit the alternatives.
>
> Hope this helps,
>
> Paul
>
>
>
> In article <3884d930_2_at_news.cadvision.com>,
> "Craig Grell" <craigg_at_thewebmarket.com> wrote:
> > I have a table with the following structure.
> >
> > start date, month1_data, month2_data, month3_data, month4_data,
> month5_data,
> > month6_data
> >
> > with the following information
> >
> > '01-JAN-2000', 1000, 1500, 1200, 1900, 1400, 900
> > '01-FEB-2000', 200, 400, 300, 600, 300, 100
> > '01-MAR-2000', 1100, 1700, 1600, 1200, 1000, 700
> >
> > How can I select this information to make the monthly data align so
> that the
> > result of the select would look like the following when aligned on
> the date
> > '01-JAN-2000'
> >
> > '01-JAN-2000', 1000, 1500, 1200, 1900, 1400, 900
> > '01-JAN-2000', 0, 200, 400, 300, 600, 300
> > '01-JAN-2000', 0, 0, 1100, 1700, 1600, 1200
> >
> > Thank you for any suggestions
> >
> > Craig
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Received on Wed Jan 19 2000 - 11:14:01 CET