SQL & PL/SQL :: How To Get Difference Between Two Rows Of Same Column
Aug 3, 2013
Calculating the difference between the two rows of same columns? Example of my Scenario is as follows
I have a table as shown below, there are checks thats has been issued per week (to tell how its been issued per week ,if you see the week number ,there is a change in week no for period of 7 days i.e. what i have to show on my results tables based on week no and probably we may need to take into account for check no too.)
result table and date range i picked up was 1/1/2012-1/31/2012
wekno ! checkno ! end date ! previous period ! Daysin period
------ ! ------ ! ------ ! ------------- ! ------------
1 ! 1 ! 1/7/2012 ! null (as previous period is 2011 and so prvs year i dont count) ! 7 this because we see change in wkno per 7 days
1 ! 2 ! 1/8/2012 !null becoz this one falls same week and therefore previous peroid would be the same as above ! 7 above reason
1 ! 3 ! 1/9/2012 ! null becoz this one falls same week and therefore previous peroid would be the same as above ! 7 above reason
2 ! 1 ! 1/14/2012 ! 1/7/2012 becoz this a second week as you see the change in wekno and therefore its previosu period is 1/7/2012 ! 7 above reason
2 ! 2 ! 1/15/2012 ! 1/7/2012 becoz this check falls on the same second week so previosu would be 1/7/2012 ! 7 above reason
3 ! 1 ! 1/21/2012 ! 1/14/2012 as a new week no starts this gonn be 1/4/2012 ! 7 above reason
How can I compare two rows from the same table and show the field and the difference?
Example:
select ename, job, mgr, hiredate, sal, deptno from EMP t where t.empno = 7782 or t.empno = 7788
ENAME JOB MGR HIREDATE SAL DEPTNO 1 CLARK MANAGER 7839 6/9/1981 2450.00 10 2 SCOTT ANALYST 7566 6/9/1981 3000.00 20
My report comparing fields should be:
FIELD BASE COMPARE DIFFERENCE ENAME CLARK SCOTT -- JOB MANAGER ANALYST -- MGR 7839 7566 -- SAL 2450 3000 550 --(Show difference only for numbers) DEPTNO 10 20 --
I have two tables tab1 and tab2 with below data Now requirement is to find difference between two table with respect to column "serial_no" which exist only in table tab1 so as per below at serial_no 2 CC4 in tab1 but CC2 in tab2 so this is different how to dump out this data
insert into tab1 values('P1','CC1',101,1); insert into tab1 values('P1','CC4',104,2); insert into tab1 values('P1','CC5',105,3); insert into tab1 values('P1','CC2',102,4); insert into tab1 values('P1','CC3',103,5);
insert into tab2 values('P1','CC1',0,101,102); insert into tab2 values('P1','CC2',101,102,103); insert into tab2 values('P1','CC3',102,103,104); insert into tab2 values('P1','CC4',103,104,105); insert into tab2 values('P1','CC5',104,105,0);
There are millions of DISTINCT ID values in TABLE_1 and corresponding to each ID there are some values for all the days of a month. I need to insert these values in TABLE_2 in the above format.
I have a table with columns emp_i, LOC_C and SUBSID_C. I want to find all emp_i's with LOC_C OR SUBSID_C as always NULL. Please note that the value should be NULL, always, for all dates.
The query --- should return 102 as LOC_C OR SUBSID_C is ALWAYS NULL. should return 103 as LOC_C is ALWAYS NULL. should not return 101, as LOC_C is not ALWAYS NULL. In other words, the query should give list of emp_i who never ever had a non-null value for LOC_C OR SUBSID_C. The purpose is to find the emp_i for which the columns LOC_C and SUBSID_C are never used.
I tried the query: --------------------------------------------------- SELECT DISTINCT ORG_EMP_I FROM tab1 WHERE ORG_GRP_I = 58 AND ORG_EMP_I NOT IN ( SELECT DISTINCT org_emp_i FROM tab1 ap WHERE ap.ORG_GRP_I = 58 AND trim(ap.LOC_C) IS NOT NULL OR ap.ORG_SBSID_C IS NOT NULL ) ---------------------------------------------------
SELECT a.objname, c.property1, c.value1 FROM datatable a, datatabledet c WHERE a.OBID=c.DATAOBID and a.CLASSNAME='Class1'; OBJNAME PROPERTY1 VALUE1 280-419-1994psCls1Attr3Attr1Value3 280-419-1994psCls1Attr4Attr1Value4 280-419-1994psCls1Attr5Attr1Value5
[code]....
After query output we put through front end code to make it in the following way i.e. convert rows into columns but with respective data value:
Employee Number Name Value Rate Type Type Assignment Id ----------------------------------------------------------------- 4 Employee 1 800 N 5 Yr Bonus 64 4 Employee 1 1063 N 6 Months Bouns 64 4 Employee 1 24.04 H Reg Hourly Sal 64 5 Employee 2 6923.08 S Reg Sal 65 6 Employee 3 5961.54 S Reg Sal 66 6 Employee 3 15000 N 6 Months Bouns 66
I want to convert above out to look like following
Emp Num Name Value Rate Type Type Val 1 Rate Type 1 Type 1 Value 2 Rate Type 2 Type 2 Assignt Id ------------------------------------------------------------------------------------------------------------------- 4 Employee 1 24.04 H Reg Hourly Sal 800 N 5 Yr Bon 1063 N 6 Mon Bon 64 5 Employee 2 6923.08 S Reg Sal 65 6 Employee 3 5961.54 S Reg Sal 15000 N 6 MontBon 66
Example of Query Output 2.
Employee Number Name Value Rate Type Type Assignment Id -------------------------------------------------------------------------------- 4 Employee 1 800 N 5 Yr Bonus 64 4 Employee 1 1063 N 6 Months Bouns 64 4 Employee 1 1345 N Patent Bonus 64 4 Employee 1 24.04 H Reg Hourly Sal 64 5 Employee 2 6923.08 S Reg Sal 65 6 Employee 3 5961.54 S Reg Sal 66 6 Employee 3 15000 N 6 Months Bouns 66
Desired Output from Above Query. As you can see column have increased as one more row for same employee has increased
ENu Nm Val RTy Ty Val1 RTy1 Ty1 Val2 RTy2 Ty2 Val3 RTy3 Ty3 AsgId ------------------------------------------------------------------------------------------------------------------- 4 E1 24.04 H Reg Hour Sal 1063 N 6 Mon Bns 1345 N Pat Bon 800 N 5YB 64 5 E2 6923.08 S Reg Sal 65 6 E3 5961.54 S Reg Sal 15000 N 6 Mos Bns 66
Currently I have a requirement where I need return data of different columns in rows.
For example: I have a table that contains monthly data for voice calls, sms count and mms count for each mobile. I will need to get the output as summary of voice calls, sms and mms counts in different rows. For this I am using an approach which I am not totally satisfied even though I am getting the required results (majorly because i am querying the table thrice for getting this data).
I would want to know about any alternate ways of implementation.
insert into UsageData values ('9999','JAN',1,2,3); insert into UsageData values ('9888','JAN',5,20,1); insert into UsageData values ('9777','JAN',4,5,9); insert into UsageData values ('9666','JAN',200,111,8); insert into UsageData values ('9555','JAN',154,1534,3); insert into UsageData values ('9444','JAN',0,2,212);
--Query
select 'VOICE_CALL' AS EVENT,sum(VOICE_CALL) AS UNITS from UsageData where cal_month ='JAN' union all select 'SMS',sum(SMS) from UsageData where cal_month ='JAN' union all select 'MMS',sum(MMS) from UsageData where cal_month ='JAN';
I huge table with million of rows and no of column more than 300.. is it possible to keep this table in some memory where oracle can access it fast as compare to disk memory.
I have one table , with one column having 2,3 or 4 machine codes , i need to display them as each row per machine code will it be possible to do as i have thousands of records similar to the test case and which i had to do it manually in excel and then upload it back.
insert into ow_oper_setup VALUES ('1270','1270001','W165','IR HO BV ') insert into ow_oper_setup VALUES ('1270','1270001','W1332','IR BV ') insert into ow_oper_setup values ('1270','1270001','W1367','RE HO SC BV ') insert into ow_oper_setup values ('1270','1270001','W389','RE HO SC BV')
commit;
SELECT * FROM ow_oper_Setup;
WO_NOMRK_NOPOS_NOMC_CODE 12701270001W165IR HO BV 12701270001W1332IR BV 12701270001W1367RE HO SC BV 12701270001W389RE HO SC BV
--i want the output in the following way or the same table data to be replaced as below
name|address|qty alan,1 the street,5 bert,2 the road,3
what I want to do is create an output that takes the value in the qty column and repeates the number of rows e.g. the alan record 5 times and the bert record 3 times
name|address|qty alan,1 the street,5 alan,1 the street,5 alan,1 the street,5
INSERT INTO TEST VALUES('a','a',1,2); INSERT INTO TEST VALUES('b','b',2,3); INSERT INTO TEST VALUES('c','c',7,6); INSERT INTO TEST VALUES('d','d',2,2);
Actual output. C1C2T1T2 aa12 bb23 cc76 dd22
I need the output in this format C1C2C3C4 aaT11 aaT22 bbT12 bbT23 ccT17 ccT26 ddT12 ddT22
ie the header t1 and t2 has to come in the output along with the corresponding values it has.the number of column as T1,T2 will be dynamic cannot predict before.
column sid format 'a5' column serial# format 'a10' column mins_running format 'a15' column sql_text format 'a100' set linesize 200 set pagesize 30
[Code]..
I am running this code, and the output shows multiple lines.
TRIM(S.SID) TRIM(S.SERIAL#) MINS_RUNNING SUBSTR(Q.SQL_TEXT,1,70) ---------------------------------------- ---------------------------------------- --------------- ---------------------------------------------------------------- 700 46592 242.08 Select count(*) as count, case when count(*)>0 then 'FAIL' else 700 46592 242.08 'PASS' end as result from (SELECT cv.code_value FROM code_valu
[Code]...
Is there a way to wrap up the column for SQL_TEXT VARCHAR2(64) so that I can 1 row for the output?
TABLE_A ------------------------------ ID DEPT CRS ------------------------------ 1 CS CS_100 2 SCIENCE SCI_150 3 MATH MATH_400 4 HISTORY HIS_110
[Code]...
To display CRS from TABLE_A where DEPT = 'MATH' but in the following format.,
-------------------------------------------- NO DEPT CRS -------------------------------------------- 1 MATH MATH_400, MATH_550, MATH_230 --------------------------------------------
instead of., -------------------------- NO DEPT CRS --------------------------- 1 MATH MATH_400 2 MATH MATH_550 3 MATH MATH_230 ---------------------------
I need a single select query which converts all the rows into a single value . Below is my requirement :
Create table email_tbl(emailid varchar2(30)); insert into email_tbl('1@y.com'); insert into email_tbl('2@y.com'); insert into email_tbl('3@y.com'); insert into email_tbl('4@y.com');
Now , I need a single select query which gives me the below results.
1@y.com,2@y.com,3@y.com,4@y.com
I have done the above by using a cursors in the pl/sql objects.But want to achieve this with a single sql/query.
I am trying to write a script where a particular post code from a table is having more than 3 telephone numbers.Both the columns are in the same table. How to fetch.
Table is P_Order Columns are DELIVERY_POSTCODE and TEL_NO... Condition DELIVERY_POSTCODE has more than 3 TEL_NO
