Irveen Irveen - 15 days ago 6
SQL Question

SQL query on columns of a table....(Oracle)

I have two tables say (

FCT_SALES_SUMMARY_A
and
FCT_SALES_SUMMARY_B
).

If we assume that
table A
has be generated on every Monday than
table B
will be generated on next Monday
i.e after 1 week like that there will be data for 104 weeks. But the as the weeks increase the previous data
will be lost in
FCT_SALES_SUMMARY_A
as shown below i.e the
col104
in
table B
will have the data of that week and the
table A
will loose the first
col1
data.

As shown below some times the data for the same week in both the tables will be different.
for example the salary for the week 22nd jan has been changed in
FCT_SALES_SUMMARY_B
.

Now I need to find out the changed data for the same week comparing the columns in both tables.
i.e, the
col2
in
FCT_SALES_SUMMARY_A
should be compared with
col1
in
FCT_SALES_SUMMARY_B
.

For example the data in tables is in columns:

Table A
col:col1........col2........col3........col4........col5...col104
WEEk:1stjan......8thjan......15thjan.....22ndjan.....
sal:100.........200.........300.........400.........


TABLE B

col:col1........col2........col3........col4........col5...col104
WEEk:8thjan......15thjan.....22thjan.....29ndjan.....
sal:200.........300.........450.........500.........





Table is defined as follow.s:

DESC FCT_SALES_SUMMARY


CREATE TABLE FCT_SALES_SUMMARY
(
PROD_SID NUMBER,
CURR_CUST_SID NUMBER,
BIO_ID NUMBER,
CURR_TERR_SID NUMBER,
FRAN_SID NUMBER,
CURR_EMP_SID NUMBER,
ESTMT_FLG VARCHAR2(1 BYTE),
PROD_WAC_PRC NUMBER(15,4),
SALE_RATIO NUMBER,
WK_UNITSCUR NUMBER(15,4),
WK_UNITS1 NUMBER(15,4),
WK_UNITS2 NUMBER(15,4),
WK_UNITS3 NUMBER(15,4),
WK_UNITS4 NUMBER(15,4),
WK_UNITS5 NUMBER(15,4),
WK_UNITS6 NUMBER(15,4),
WK_UNITS7 NUMBER(15,4),
WK_UNITS8 NUMBER(15,4),
WK_UNITS9 NUMBER(15,4),
WK_UNITS10 NUMBER(15,4),
WK_UNITS11 NUMBER(15,4),
WK_UNITS12 NUMBER(15,4),
WK_UNITS13 NUMBER(15,4),
WK_UNITS14 NUMBER(15,4),
WK_UNITS15 NUMBER(15,4),
WK_UNITS16 NUMBER(15,4),
WK_UNITS17 NUMBER(15,4),
WK_UNITS18 NUMBER(15,4),
WK_UNITS19 NUMBER(15,4),
WK_UNITS20 NUMBER(15,4),
WK_UNITS21 NUMBER(15,4),
WK_UNITS22 NUMBER(15,4),
WK_UNITS23 NUMBER(15,4),
WK_UNITS24 NUMBER(15,4),
WK_UNITS25 NUMBER(15,4),
WK_UNITS26 NUMBER(15,4),
WK_UNITS27 NUMBER(15,4),
WK_UNITS28 NUMBER(15,4),
WK_UNITS29 NUMBER(15,4),
WK_UNITS30 NUMBER(15,4),
WK_UNITS31 NUMBER(15,4),
WK_UNITS32 NUMBER(15,4),
WK_UNITS33 NUMBER(15,4),
WK_UNITS34 NUMBER(15,4),
WK_UNITS35 NUMBER(15,4),
WK_UNITS36 NUMBER(15,4),
WK_UNITS37 NUMBER(15,4),
WK_UNITS38 NUMBER(15,4),
WK_UNITS39 NUMBER(15,4),
WK_UNITS40 NUMBER(15,4),
WK_UNITS41 NUMBER(15,4),
WK_UNITS42 NUMBER(15,4),
WK_UNITS43 NUMBER(15,4),
WK_UNITS44 NUMBER(15,4),
WK_UNITS45 NUMBER(15,4),
WK_UNITS46 NUMBER(15,4),
WK_UNITS47 NUMBER(15,4),
WK_UNITS48 NUMBER(15,4),
WK_UNITS49 NUMBER(15,4),
WK_UNITS50 NUMBER(15,4),
WK_UNITS51 NUMBER(15,4),
WK_UNITS52 NUMBER(15,4),
WK_UNITS53 NUMBER(15,4),
WK_UNITS54 NUMBER(15,4),
WK_UNITS55 NUMBER(15,4),
WK_UNITS56 NUMBER(15,4),
WK_UNITS57 NUMBER(15,4),
WK_UNITS58 NUMBER(15,4),
WK_UNITS59 NUMBER(15,4),
WK_UNITS60 NUMBER(15,4),
WK_UNITS61 NUMBER(15,4),
WK_UNITS62 NUMBER(15,4),
WK_UNITS63 NUMBER(15,4),
WK_UNITS64 NUMBER(15,4),
WK_UNITS65 NUMBER(15,4),
WK_UNITS66 NUMBER(15,4),
WK_UNITS67 NUMBER(15,4),
WK_UNITS68 NUMBER(15,4),
WK_UNITS69 NUMBER(15,4),
WK_UNITS70 NUMBER(15,4),
WK_UNITS71 NUMBER(15,4),
WK_UNITS72 NUMBER(15,4),
WK_UNITS73 NUMBER(15,4),
WK_UNITS74 NUMBER(15,4),
WK_UNITS75 NUMBER(15,4),
WK_UNITS76 NUMBER(15,4),
WK_UNITS77 NUMBER(15,4),
WK_UNITS78 NUMBER(15,4),
WK_UNITS79 NUMBER(15,4),
WK_UNITS80 NUMBER(15,4),
WK_UNITS81 NUMBER(15,4),
WK_UNITS82 NUMBER(15,4),
WK_UNITS83 NUMBER(15,4),
WK_UNITS84 NUMBER(15,4),
WK_UNITS85 NUMBER(15,4),
WK_UNITS86 NUMBER(15,4),
WK_UNITS87 NUMBER(15,4),
WK_UNITS88 NUMBER(15,4),
WK_UNITS89 NUMBER(15,4),
WK_UNITS90 NUMBER(15,4),
WK_UNITS91 NUMBER(15,4),
WK_UNITS92 NUMBER(15,4),
WK_UNITS93 NUMBER(15,4),
WK_UNITS94 NUMBER(15,4),
WK_UNITS95 NUMBER(15,4),
WK_UNITS96 NUMBER(15,4),
WK_UNITS97 NUMBER(15,4),
WK_UNITS98 NUMBER(15,4),
WK_UNITS99 NUMBER(15,4),
WK_UNITS100 NUMBER(15,4),
WK_UNITS101 NUMBER(15,4),
WK_UNITS102 NUMBER(15,4),
WK_UNITS103 NUMBER(15,4),
WK_UNITS104 NUMBER(15,4),
WK_UNITS105 NUMBER(15,4)


)

We need to compare data week by week.

Answer

From the information given so far, I'd say that First Normal Form would be your friend here.

So your table would become something more like this:

CREATE TABLE FCT_SALES_SUMMARY
(
  PROD_SID                 NUMBER,
  CURR_CUST_SID            NUMBER,
  BIO_ID                   NUMBER,
  CURR_TERR_SID            NUMBER,
  FRAN_SID                 NUMBER,
  CURR_EMP_SID             NUMBER,
  ESTMT_FLG                VARCHAR2(1 BYTE),
  PROD_WAC_PRC             NUMBER(15,4),
  SALE_RATIO               NUMBER,
  WK_UNITSCUR              NUMBER(15,4),
  WEEK_NUMBER              NUMBER,
  WK_UNITS                 NUMBER(15,4)
)

...with one row per week (as designated by WEEK_NUMBER above - call it whatever is most meaningful for your app).

Now you won't lose any data that you don't want to lose: WEEK_NUMBER (or whatever) just keeps incrementing by one each week.

Will it make the table's storage requirements greater? Yes. Disc space is cheap.

Will it make queries slower? Possibly, but computers are fast... ;-)

Comments