Solved

Subtracting values for two date ranges.

Posted on 2014-12-04
23
72 Views
Last Modified: 2016-06-16
I need to be able to compare values from one month to values of another month and get a difference.
Select
 DEF_P
from xxx
where date between 10/1/2014 and 10/31/2014


DEF_P for Oct  - DEF_P for Nov  (not sure if world subtract - need to show if there was a change and the change could be plus or minus.

DEF_P for Oct                        DEF_P for Nov                        Diff
$120.00                                    $130.oo                                   $10
0
Comment
Question by:leezac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 5
  • 2
  • +1
23 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40482009
If you restrict the date range to October, where do you expect the November data to come from?

Can you post the table description and sample data to go with your expected results.
Oracle version would help as well.

Off the top of my head maybe something like:

select oct_def_p, nov_def_p, nov_def_p-oct_def_p diff
from (
Select
sum(case when to_char(date,'MM') = '10' then  DEF_P end) oct_def_p,
sum(case when to_char(date,'MM') = '11' then  DEF_P end) nov_def_p
from xxx
where date between 10/1/2014 and 11/30/2014
);
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40482758
will you need to do this over more than one month? that is, look for differences between april and may, may and june, june and july,  etc?

If so, try something like this, extend the date range for as long of a period as you need.  Note the date ranges are >=  on the low end and <  (not equal)  on the high end

SELECT m,
       def_p,
       prev_def_p,
       prev_def_p - def_p diff
  FROM (  SELECT TRUNC(yourdate, 'mm') m,
                 SUM(def_p) def_p,
                 LAG(SUM(def_p)) OVER (ORDER BY TRUNC(yourdate, 'mm')) prev_def_p
            FROM xxx
           WHERE yourdate >= TO_DATE('10/1/2014', 'mm/dd/yyyy')
             AND yourdate < TO_DATE('12/1/2014', 'mm/dd/yyyy')
        GROUP BY TRUNC(yourdate, 'mm'))
 WHERE prev_def_p IS NOT NULL
0
 
LVL 32

Expert Comment

by:awking00
ID: 40482830
>>I need to be able to compare values from one month to values of another month and get a difference.<<
The simple answer is select valuesfromonemonth - valuesofanothermonth
If valuesfromonemonth is greater than valuesofanothermonth, the result will be positive and, if not, the result will be negative. How you retrieve values for a given month is another question. Your question is a little vague. Perhaps you can provide some sample data and your expected results.
0
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 

Author Comment

by:leezac
ID: 40482896
All are great answers - I will look at today and get back with probably more questions and a less vague response also :)
0
 

Author Comment

by:leezac
ID: 40483638
Ok this is the code I have and it shows the percents under column DEF_PCNT for each EMP_Number.  Using the AsofDate that is defined this sql returns values for October.   I need to compare two months.  If November, I need to compare to October.  If December I need to compare to November.  I am comparing what EMP_Numbers had a change in DEF_PCNT and doing a count of those that changed percentages between the months.

Example (if employee 1 changed percents in November then I would count him, if not I would not count him).

So my question remains is there to do this in sql (values from different months and show a count).  Thanks in advance.

Oct
Emp Number       DEF_PCNT                  
1                                       8


Nov
Emp Number       DEF_PCNT                      
1                                       9

_____________________________________________________________________






DEFINE AsOfDate = TO_DATE('11/01/2014','MM/DD/YYYY')

SELECT
Distinct  EMP_Number, DEF_PCNT
FROM(
 SELECT EMP_Number, NVL(DEF_P,0) as DEF_PCNT
FROM yyyy
  LEFT JOIN(
      SELECT PLAN_ID, ACTY_Date,
      n_CUR_CTCH_UP_P + N_CUR_PRE_TAX_SPL_P As DEF_P
        FROM xxx
         ) DFRL ON PART.EMP_ID=DFRL.EMP_ID AND  PART.PLAN_ID=DFRL.PLAN_ID

   AND ACTY_Date Between add_months(&AsOfDate,-1) and (&AsOfDate - 1)
)
0
 

Author Comment

by:leezac
ID: 40483644
will you need to do this over more than one month? that is, look for differences between april and may, may and june, june and july,  etc?

will do just for two months at a time.
0
 

Author Comment

by:leezac
ID: 40483698
and the date value shows as  dd-mmm-yy (01-SEP-14)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40483739
WITH monthlydata
     AS (SELECT DISTINCT TRUNC(acty_date, 'mm') mon, emp_number, def_pcnt
           FROM (SELECT emp_number, NVL(def_p, 0) AS def_pcnt
                   FROM yyyy part
                        LEFT JOIN
                        (SELECT plan_id, acty_date, n_cur_ctch_up_p + n_cur_pre_tax_spl_p AS def_p
                           FROM xxx) dfrl
                            ON part.emp_id = dfrl.emp_id
                           AND part.plan_id = dfrl.plan_id
                           AND acty_date >= ADD_MONTHS(TRUNC(&asofdate, 'mm'), -1)
                           AND acty_date < ADD_MONTHS(TRUNC(&asofdate), 1)))
SELECT TO_CHAR(m, 'yyyy-mon') mon,
       def_pcnt,
       prev_def_pcnt,
       prev_def_pcnt - def_pcnt diff
  FROM (  SELECT mon, def_pcnt, LAG(def_pcnt) OVER (ORDER BY mon) prev_def_pcnt
            FROM monthlydata
        GROUP BY mon)
 WHERE prev_def_pcnt IS NOT NULL;


in this query asofdate is for the 2nd month in your 2month span.

So, enter any date in November and you'll Oct and Nov data compared.

change the mon TO_CHAR to whatever format you want


or using the sum(case) method

WITH monthlydata
     AS (SELECT DISTINCT TRUNC(acty_date, 'mm') mon, emp_number, def_pcnt
           FROM (SELECT emp_number, NVL(def_p, 0) AS def_pcnt
                   FROM yyyy part
                        LEFT JOIN
                        (SELECT plan_id, acty_date, n_cur_ctch_up_p + n_cur_pre_tax_spl_p AS def_p
                           FROM xxx) dfrl
                            ON part.emp_id = dfrl.emp_id
                           AND part.plan_id = dfrl.plan_id
                           AND acty_date >= ADD_MONTHS(TRUNC(&asofdate, 'mm'), -1)
                           AND acty_date < ADD_MONTHS(TRUNC(&asofdate), 1)))
SELECT mon1, mon2, mon2 - mon1 diff
  FROM (SELECT SUM(CASE WHEN mon = ADD_MONTHS(TRUNC(&asofdate, 'mm'), -1) THEN def_pcnt END) mon1,
               SUM(CASE WHEN mon = ADD_MONTHS(TRUNC(&asofdate), 1) THEN def_pcnt END) mon2
          FROM monthlydata);
0
 

Author Comment

by:leezac
ID: 40483767
This is my attempt of adding your code --- need help.
This row is adding the percents (   n_CUR_CTCH_UP_P + n_CUR_PRE_TAX_DFRL_P + N_CUR_PRE_TAX_SPL_P As DEF_P) then is referenced as DEF_PCNT


DEFINE AsOfDate = TO_DATE('11/01/2014','MM/DD/YYYY')

SELECT
Distinct SSN_Number, DEF_PCNT, ACTY_Date,
 TO_CHAR(m, 'yyyy-mon') mon,
       def_pcnt,
       prev_def_pcnt,
       prev_def_pcnt - def_pcnt diff
FROM(
 SELECT mon,
def_pcnt,
LAG(def_pcnt) OVER (ORDER BY mon) prev_def_pcnt,
Emp_Number,
NVL(DEF_P,0) as DEF_PCNT, ACTY_Date
FROM monthlydata

  LEFT JOIN(
      SELECT PLAN_ID, SSN_ID, ACTY_Date,
      n_CUR_CTCH_UP_P + n_CUR_PRE_TAX_DFRL_P + N_CUR_PRE_TAX_SPL_P As DEF_P
     
          FROM YYYY
         ) DFRL ON PART.SSN_ID=DFRL.SSN_ID AND  PART.PLAN_ID=DFRL.PLAN_ID

Where prev_def_pcnt IS NOT NULL
    AND DFRL_ACTY_D Between add_months(&AsOfDate,-1) and (&AsOfDate - 1)

Group by mon
)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40483778
the WITH clause was part of the solution.

it incorporated your original query, don't change it

just run it as is, except for changing the xxxx and yyyy to your real tables
0
 

Author Comment

by:leezac
ID: 40483838
Ok - I am getting an error that I am missing right parenthesis on this line  "    ON part.EMP_id = dfrl.EMP_id'
0
 

Author Comment

by:leezac
ID: 40483863
I think I fixed the error.  Still checking.
0
 

Author Comment

by:leezac
ID: 40483880
I am having problems with the "mon"   and I need to add a where statement

Where Emp_ID = 1234 from the Part table or the first table.  I am not use to the With statement.  I got the sql to run but I had to take out everywhere the was a "mon."
0
 

Author Comment

by:leezac
ID: 40483898
Maybe I need to change the format for the month -  Like I said had to take out any reference to "mon' to run and need to add where plan_id =.  

DEFINE AsOfDate = TO_DATE('11/01/2014','MM/DD/YYYY')

WITH monthlydata
     AS (SELECT DISTINCT EMP_N, def_pcnt
           FROM (SELECT EMP_N, NVL(def_p, 0) AS def_pcnt
                   FROM dbo.EMP_PART  Part ( get error on this line "missing righ paren"
                    Where PLAN_ID = 204
                        LEFT JOIN (
                        (SELECT plan_id, EMP_ID, ACTY_Date,
                        n_cur_ctch_up_p + n_cur_pre_tax_spl_p AS def_p
                           FROM dbo.V_ACTY_DLY) DFRL
                              ON part.EMP_id = dfrl.EMP_id
                              AND part.plan_id = dfrl.plan_id
                              and DFRL_ACTY_D >= ADD_MONTHS(TRUNC(&asofdate, 'mm'), -1)
                           AND DFRL_ACTY_D < ADD_MONTHS(TRUNC(&asofdate), 1)))
SELECT
       def_pcnt,
       prev_def_pcnt,
       prev_def_pcnt - def_pcnt diff
  FROM (  SELECT def_pcnt, LAG(def_pcnt) OVER (ORDER BY def_pcnt) prev_def_pcnt
            FROM monthlydata
        GROUP BY DEf_pcnt)
 WHERE prev_def_pcnt IS NOT NULL
 )
;
0
 

Author Comment

by:leezac
ID: 40483920
I will add the mon part back - I just needed to see the sql run.  Not sure why it would not run.
0
 

Author Comment

by:leezac
ID: 40483938
With this sql _ I get an error "missing Select" on this line " AND DFRL_ACTY_D < ADD_MONTHS(TRUNC(&asofdate), 1)))"

DEFINE AsOfDate = TO_DATE('11/01/2014','MM/DD/YYYY')
WITH monthlydata
     AS (SELECT DISTINCT TRUNC(ACTY_Date, 'mm') mon, emp_number, def_pcnt
           FROM (SELECT EMP_N, NVL(def_p, 0) AS def_pcnt
                   FROM dbo.V_FDPAT_PART) PART
                        LEFT JOIN
                        (SELECT plan_id, DFRL_ACTY_D, n_cur_ctch_up_p + n_cur_pre_tax_spl_p AS def_p
                           FROM dbo.V_ACTY_DLY) DFRL
                            ON part.EMP_id = dfrl.EMP_id
                           AND part.plan_id = dfrl.plan_id
                           --and Part.PLAN_ID = '204'
                           AND ACTY_Date >= ADD_MONTHS(TRUNC(&asofdate, 'mm'), -1)
                           AND ACTY_Date < ADD_MONTHS(TRUNC(&asofdate), 1)))
SELECT mon1, mon2, mon2 - mon1 diff
  FROM (SELECT SUM(CASE WHEN mon = ADD_MONTHS(TRUNC(&asofdate, 'mm'), -1) THEN def_pcnt END) mon1,
               SUM(CASE WHEN mon = ADD_MONTHS(TRUNC(&asofdate), 1) THEN def_pcnt END) mon2
          FROM monthlydata);
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40484007
You have an extra closing paran.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40486611
WITH monthlydata
     AS (SELECT DISTINCT TRUNC(acty_date, 'mm') mon, emp_number, def_pcnt
           FROM (SELECT emp_n, NVL(def_p, 0) AS def_pcnt
                   FROM dbo.v_fdpat_part part
                        LEFT JOIN
                        (SELECT plan_id,
                                dfrl_acty_d,
                                n_cur_ctch_up_p + n_cur_pre_tax_spl_p AS def_p
                           FROM dbo.v_acty_dly) dfrl
                            ON part.emp_id = dfrl.emp_id
                           AND part.plan_id = dfrl.plan_id
                           --and Part.PLAN_ID = '204'
                           AND acty_date >= ADD_MONTHS(TRUNC(&asofdate, 'mm'), -1)
                           AND acty_date < ADD_MONTHS(TRUNC(&asofdate), 1)))
SELECT mon1, mon2, mon2 - mon1 diff
  FROM (SELECT SUM(CASE WHEN mon = ADD_MONTHS(TRUNC(&asofdate, 'mm'), -1) THEN def_pcnt END) mon1,
               SUM(CASE WHEN mon = ADD_MONTHS(TRUNC(&asofdate), 1) THEN def_pcnt END) mon2
          FROM monthlydata);
0
 

Author Comment

by:leezac
ID: 40486953
Ok - appreciate the staying with me as I need to get this done.  I am now getting an error using the above code
ORA-00904: "ACTY_Date": invalid identifier
00904. 00000 -  "%s: invalid identifier"


the ACTY-DATE field is in the DFRL table only.  

and I was getting an error on this line  "AND acty_date < ADD_MONTHS(TRUNC(&asofdate), 1)))"

and the date field for ACTY_Date is '02-SEP-14'.
0
 

Author Comment

by:leezac
ID: 40487148
Example for each EMP_ID for OCT was there a change in DEF_P from SEPtember
 EMP_ID      ACT_Date          DEF_P
096      22-Sep-14              15


EMP_ID      ACT_Date          DEF_P
096      22-OCT-14        10
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40487170
this would be a LOT easier with a full example of inputs and outputs.
You have changed the spellings of  columns and we have no way of knowing what the correct ones are

for example - all three of these are names you have posted but I "think" you meant them to be the same thing:

act_date
acty_date
dfrl_acty_d

So all of the code examples above are just guesses - unless you give us a consistent test case to work with, we're basing them off of what you post, there will likely be many typos and misspellings in our code since we can't actually test.  You'll have to fill in the blanks on these yourself.  Or give us something we can test with on our local systems.


Having said that, I "think" this should help

WITH monthlydata
     AS (SELECT DISTINCT TRUNC(acty_date, 'mm') mon, emp_n, def_pcnt
           FROM (SELECT emp_n, NVL(def_p, 0) AS def_pcnt, acty_date
                   FROM dbo.v_fdpat_part part
                        LEFT JOIN
                        (SELECT plan_id, acty_date, n_cur_ctch_up_p + n_cur_pre_tax_spl_p AS def_p
                           FROM dbo.v_acty_dly) dfrl
                            ON part.emp_id = dfrl.emp_id
                           AND part.plan_id = dfrl.plan_id
                           --and Part.PLAN_ID = '204'
                           AND acty_date >= ADD_MONTHS(TRUNC(&asofdate, 'mm'), -1)
                           AND acty_date < ADD_MONTHS(TRUNC(&asofdate), 1)))
SELECT mon1, mon2, mon2 - mon1 diff
  FROM (SELECT SUM(CASE WHEN mon = ADD_MONTHS(TRUNC(&asofdate, 'mm'), -1) THEN def_pcnt END) mon1,
               SUM(CASE WHEN mon = ADD_MONTHS(TRUNC(&asofdate), 1) THEN def_pcnt END) mon2
          FROM monthlydata);


If you get other "invalid identifier" errors - go through the code and change the column and table names to whatever the real names are and then try again.  If you still have errors after that, post what you used with all of the real names and then we can be sure to use them too and save steps going forward.
0
 

Author Comment

by:leezac
ID: 40487703
I understand.  I appreciate the help and feedback
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle mutateing errors 3 28
inserting value in table in oracle form 2 33
Sorting a SQL script 5 42
oracle query 4 31
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question