Home » SQL & PL/SQL » SQL & PL/SQL » Query problem
Query problem [message #231124] Sat, 14 April 2007 13:22 Go to next message
syamkumar1250
Messages: 1
Registered: April 2007
Location: hyderabad
Junior Member
I am new to Oracle. my requirement is to get yesterday's sum of values to be today's Opening values and today'sum of values as closing values with in a given period. one row for a day. my query is as follows.

SELECT   cl_trn_date
       , SUM (cl_hopper1) AS cl_hopper1
       , SUM (cl_hopper2) AS cl_hopper2
       , SUM (cl_hopper1) + SUM (cl_hopper2) AS cl_total
       , SUM (op_hopper1) AS op_hopper1
       , SUM (op_hopper2) AS op_hopper2
       , SUM (op_total) AS op_total
FROM     (SELECT   cl.trn_date AS cl_trn_date
                 , SUM (cl.hopper1) AS cl_hopper1
                 , SUM (cl.hopper2) AS cl_hopper2
                 , SUM (cl.hopper1) + SUM (cl.hopper2) AS cl_total
                 , 0 AS op_hopper1
                 , 0 AS op_hopper2
                 , 0 AS op_total
          FROM     (SELECT   h.trn_date
                           , SUM (d.VALUE) AS hopper1
                           , 0 AS hopper2
                    FROM     fur_shift_param_trnh h
                           , fur_shift_param_trnd d
                    WHERE    h.trn_id = d.trn_id
                    AND      d.param_no = 5
                    AND      h.trn_date BETWEEN '09-APR-2007' AND '11-APR-2007'
                    GROUP BY h.trn_date
                    UNION ALL
                    SELECT   h.trn_date
                           , 0 AS hopper1
                           , SUM (d.VALUE) AS hopper2
                    FROM     fur_shift_param_trnh h
                           , fur_shift_param_trnd d
                    WHERE    h.trn_id = d.trn_id
                    AND      d.param_no = 6
                    AND      h.trn_date BETWEEN '09-APR-2007' AND '11-APR-2007'
                    GROUP BY h.trn_date) cl
          GROUP BY trn_date
          UNION ALL
          SELECT   trn_date
                 , 0 AS cl_hopper1
                 , 0 AS cl_hopper2
                 , 0 AS cl_total
                 , SUM (op.hopper1) AS op_hopper1
                 , SUM (op.hopper2) AS op_hopper2
                 , SUM (op.hopper1) + SUM (op.hopper2) AS op_total
          FROM     (SELECT   h.trn_date
                           , SUM (d.VALUE) AS hopper1
                           , 0 AS hopper2
                    FROM     fur_shift_param_trnh h
                           , fur_shift_param_trnd d
                    WHERE    h.trn_id = d.trn_id
                    AND      d.param_no = 5
                    AND      h.trn_date BETWEEN '08-APR-2007' AND '10-APR-2007'
                    GROUP BY h.trn_date
                    UNION ALL
                    SELECT   h.trn_date
                           , 0 AS hopper1
                           , SUM (d.VALUE) AS hopper2
                    FROM     fur_shift_param_trnh h
                           , fur_shift_param_trnd d
                    WHERE    h.trn_id = d.trn_id
                    AND      d.param_no = 6
                    AND      h.trn_date BETWEEN '08-APR-2007' AND '10-APR-2007'
                    GROUP BY h.trn_date) op
          GROUP BY trn_date)
GROUP BY cl_trn_date


ref: cl--> closing
op--> opening

please correct my query.

Thanx in Advance.
Syam

[MOD-EDIT]Applied SQL formatting.

[Updated on: Mon, 16 April 2007 04:31] by Moderator

Report message to a moderator

Re: Query problem [message #231125 is a reply to message #231124] Sat, 14 April 2007 13:26 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Obviously you did NOT read/follow the Sticky Post titled:How to format your posts

http://www.orafaq.com/forum/m/161789/74940/#msg_161789

[Updated on: Sat, 14 April 2007 13:27] by Moderator

Report message to a moderator

Re: Query problem [message #231318 is a reply to message #231124] Mon, 16 April 2007 08:24 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
syamkumar1250 wrote on Sat, 14 April 2007 14:22

AND h.trn_date BETWEEN '09-APR-2007' AND '11-APR-2007'
.
.
.
AND h.trn_date BETWEEN '08-APR-2007' AND '10-APR-2007'
.
.
.
AND h.trn_date BETWEEN '08-APR-2007' AND '10-APR-2007'

please correct my query.




to_date('08-APR-2007','DD-MON-YYYY')

QED.
Previous Topic: comparing data of the table using DBLINK
Next Topic: DBMS_SHARED_POOL.KEEP
Goto Forum:
  


Current Time: Thu Dec 08 08:39:55 CST 2016

Total time taken to generate the page: 0.11290 seconds