Home » SQL & PL/SQL » SQL & PL/SQL » need help writing a query (PL/SQL Developer - Oracle 10g)
need help writing a query [message #391845] Fri, 13 March 2009 21:32 Go to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
Hi All,

I need help to write a query with the following data :

I have many columns in the table (call "CostTable") but my requirement basically involves these 3 columns

Id Cost Date
123 $12 1/1/2008
123 $14 2/1/2008
123 $15 3/1/2008
156 $17 1/1/2008
156 $13 1/3/2007

I want those 3 columns on a new table and add 2 more columns called PrevCost and PrevDate to make a table like this

Id Cost Date PrevCost PrevDate
123 $12 1/1/2008 null null
123 $14 2/12/2008 $12 1/1/2008
123 $15 3/1/2008 $14 2/12/2008
156 $17 1/1/2008 null null
156 $13 1/3/2007 $17 1/1/2008

Then add a 3rd column to calculate whether PrevCost versus Cost has an increase or decrease.


Id Cost Date PrevCost PrevDate Change
123 $12 1/1/2008 null null
123 $14 2/12/2008 $12 1/1/2008 Increase
123 $15 3/1/2008 $14 2/12/2008 Increase
156 $17 1/1/2008 null null
156 $13 1/3/2007 $17 1/1/2008 Decrease


Overall, I want to get a count of the following data:
1. how many ids had a change
2. how many ids had an increase
3. how many ids had a decrease
4. how many ids had a 1st of the month change
5. how many ids had a not 1st of the month change

Any help would be greatly appreciated.

[Updated on: Fri, 13 March 2009 21:47]

Report message to a moderator

Re: need help writing a query [message #391847 is a reply to message #391845] Fri, 13 March 2009 22:17 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

>I want those 3 columns on a new table and add 2 more columns called PrevCost and PrevDate to make a table like this
This is a bad idea for a couple or reasons.
1) It violates Third Normal Form
2) It stores "computed" values.

>Then add a 3rd column to calculate whether PrevCost versus Cost has an increase or decrease.
See above.

Rows in a table have no inherent order.
Rows in a table are like balls in a basket.
After you have selected a single ball, how do you determine which ball is next.

You can find many examples which answer your questions if you SEARCH this forum.
Re: need help writing a query [message #391859 is a reply to message #391845] Sat, 14 March 2009 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a Test case: create table and insert statements along with the result you want with these data.

Have a look at LAG/LEAD functions.

Use SQL*Plus and copy and paste you then found.

Regards
Michel

[Updated on: Sat, 14 March 2009 01:54]

Report message to a moderator

Re: need help writing a query [message #391873 is a reply to message #391845] Sat, 14 March 2009 02:50 Go to previous messageGo to next message
vaibhavk
Messages: 7
Registered: March 2009
Junior Member
jen804 wrote on Fri, 13 March 2009 21:32
Hi All,

I need help to write a query with the following data :

I have many columns in the table (call "CostTable") but my requirement basically involves these 3 columns

Id Cost Date
123 $12 1/1/2008
123 $14 2/1/2008
123 $15 3/1/2008
156 $17 1/1/2008
156 $13 1/3/2007

I want those 3 columns on a new table and add 2 more columns called PrevCost and PrevDate to make a table like this

Id Cost Date PrevCost PrevDate
123 $12 1/1/2008 null null
123 $14 2/12/2008 $12 1/1/2008
123 $15 3/1/2008 $14 2/12/2008
156 $17 1/1/2008 null null
156 $13 1/3/2007 $17 1/1/2008

Then add a 3rd column to calculate whether PrevCost versus Cost has an increase or decrease.


Id Cost Date PrevCost PrevDate Change
123 $12 1/1/2008 null null
123 $14 2/12/2008 $12 1/1/2008 Increase
123 $15 3/1/2008 $14 2/12/2008 Increase
156 $17 1/1/2008 null null
156 $13 1/3/2007 $17 1/1/2008 Decrease


Overall, I want to get a count of the following data:
1. how many ids had a change
2. how many ids had an increase
3. how many ids had a decrease
4. how many ids had a 1st of the month change
5. how many ids had a not 1st of the month change

Any help would be greatly appreciated.



Hi if I may offer my .02 , what you are looking for is very basic. Do you have IDs in your table? something to sequentially identify your rows? Like a previous poster said there is no way to know which row is next (like balls in a basket) unless you specifically mark them with a sequential id.

Once you do that you can simply query to find the change.
Re: need help writing a query [message #391913 is a reply to message #391873] Sat, 14 March 2009 10:00 Go to previous messageGo to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
thank you for the feedback. i'm new to pl/sql developer. your comment makes sense. however, i don't have the ability to make changes to these tables but what i want to do can't be accomplised in ms access. this table doesn't have ids to identify which row is next. the id column is the product number. i'm hoping to use the date as a reference to find the previous max date and cost based on the max date of each product number and then export this data to our reporting team.

maybe this sample might help. (i'm sorry if this is not the post format, i'm confused to what it should look look like). i tried using this, but i keep getting an error in sql plus and if there's a better way to write this in pl/sql developer that would be even better. i'd tried searching, but nothing comes up close to what i want to do.

Thank you so much for your time.

 SELECT   vehiclemiles.carnum, 
         vehiclemiles.dordate, 
         vehiclemiles.speedoend, 
         (SELECT speedoend 
          FROM   vehiclemiles AS alias 
          WHERE  dordate = (SELECT Max(dordate) 
                            FROM   vehiclemiles AS alias2 
                            WHERE  alias2.dordate < vehiclemiles.dordate 
                                   AND alias2.carnum = vehiclemiles.carnum) 
                 AND alias.carnum = vehiclemiles.carnum) AS prevend 
FROM     vehiclemiles 
ORDER BY vehiclemiles.carnum, 
         vehiclemiles.dordate, 
         vehiclemiles.speedoend; 

[Updated on: Sat, 14 March 2009 10:13]

Report message to a moderator

Re: need help writing a query [message #391919 is a reply to message #391913] Sat, 14 March 2009 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i'm sorry if this is not the post format, i'm confused to what it should look look like)

Then you didn't read the links I provided.

Quote:
i tried using this, but i keep getting an error in sql plus.

If you don't post the error we don't know it.

As I said:
Quote:
Use SQL*Plus and copy and paste you then found.

And format it.

You also didn't follow my advice:
Quote:
Have a look at LAG/LEAD functions.


Regards
Michel
Re: need help writing a query - PL/SQL Version 7.1.5.1396 [message #391924 is a reply to message #391913] Sat, 14 March 2009 11:06 Go to previous messageGo to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
I think I understand now. Sorry for the confusion and thank you for your patience. It's a learning curve for me. Smile

PL/SQL Version 7.1.5.1396

 SELECT   CostTable.id, 
         CostTable.date, 
         CostTable.cost, 
         (SELECT cost 
          FROM   CostTable AS alias 
          WHERE  date = (SELECT Max(date) 
                            FROM   CostTable AS alias2 
                            WHERE  alias2.date < CostTable.date 
                                   AND alias2.id = CostTable.id) 
                 AND alias.id = CostTable.id) AS prevcost 
FROM     CostTable 
ORDER BY CostTable.id, 
         CostTable.date, 
         CostTable.cost; 


ID	Date	        Cost	       PrevCost
16	5/1/2008	337374	
16	5/2/2008	337538	        337374
16	5/3/2008	337898	        337538
16	5/4/2008	338212	        337898
16	5/5/2008	338420	        338212
16	5/8/2008	338684	        338420
17	5/1/2008	201371	
17	5/2/2008	201519	        201371
17	5/3/2008	201784	        201519
17	5/6/2008	201818	        201784
18	5/1/2008	196520	
18	5/2/2008	196719	        196520
18	5/3/2008	196774	        196719
18	5/4/2008	196838	        196774


The lead/lag is a complete loss to me. but i describe the table to see the data type and it's either a number(2) for id, date, number (15,5) for the cost.

i ran this query in isql*plus 10.2.0.4.0 production and get:
ERROR at line 1: ORA-00921:unexpected end of SQL command

[Updated on: Sat, 14 March 2009 11:26]

Report message to a moderator

Re: need help writing a query - PL/SQL Version 7.1.5.1396 [message #391926 is a reply to message #391924] Sat, 14 March 2009 11:29 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Quote:
The lead/lag is a complete loss to me.
Then you should persevere with it, because it is the best solution to your problem. here is an example of how the LAG function might be used. Try to adapt it to your needs.

WITH T AS (
SELECT 10 DEPT, 10 NO_OF_EMPS, to_date('1/1/2008','dd/mm/yyyy') read_date FROM DUAL UNION ALL
SELECT 10, 12, to_date('2/1/2008','dd/mm/yyyy') FROM DUAL UNION ALL
SELECT 10, 8, to_date('3/1/2008','dd/mm/yyyy') FROM DUAL UNION ALL
SELECT 20, 20, to_date('1/1/2008','dd/mm/yyyy') FROM DUAL UNION ALL
SELECT 20, 15, to_date('1/3/2007','dd/mm/yyyy') FROM DUAL
)
SELECT  dept
      , no_of_emps
      , read_date
      , LAG(read_date) OVER (PARTITION BY dept ORDER BY read_date)
FROM T
Re: need help writing a query - PL/SQL Version 7.1.5.1396 [message #393151 is a reply to message #391926] Fri, 20 March 2009 09:34 Go to previous messageGo to next message
jen804
Messages: 22
Registered: March 2009
Location: USA
Junior Member
Hi - I need additional assistance.

I have this code:

 SELECT  item
      , date
      , amt
      , LAG(date,1) OVER (PARTITION BY item ORDER BY date)AS PREV_DATE
FROM itemtable
WHERE date between '01-FEB-2009' and '28-FEB-2009'


However the date I want to restrict is not within the the column Prev_date but that's what's pulling back. I want the date range for date column to pull back between feb 1 2009 and feb 28 2009 and the prev_date should not be restricted to that date range.Did I miss something?

Thanks.

[Updated on: Fri, 20 March 2009 09:35]

Report message to a moderator

Re: need help writing a query - PL/SQL Version 7.1.5.1396 [message #393446 is a reply to message #393151] Sun, 22 March 2009 21:45 Go to previous message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Hi,
for a start, I would suggest a few things
1. Post the code that you have actually used rather than a mock up of the code
2. NEVER rely on implicit datatype conversion when comparing dates. your predicate is comparing dates to strings which is fraught with potential problems. Look up the syntax for TO_DATE
3. Post a test case as has already been requested.
4. consider the following statement from the documentation on analytics:
Quote:
... analytic_clause to indicate that the function operates on a query result set. That is, it is computed after the FROM, WHERE, GROUP BY, and HAVING clauses. ...
Previous Topic: how can return value from column value more 3267k
Next Topic: how cath data duplicate in Forall Insert exception dup_val_on_index
Goto Forum:
  


Current Time: Thu Dec 08 16:27:44 CST 2016

Total time taken to generate the page: 0.07910 seconds