leezac
asked on
Subtracting values for two date ranges.
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
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
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
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
>>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.
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.
ASKER
All are great answers - I will look at today and get back with probably more questions and a less vague response also :)
ASKER
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/D D/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)
)
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/D
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)
)
ASKER
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.
will do just for two months at a time.
ASKER
and the date value shows as dd-mmm-yy (01-SEP-14)
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);
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
AND acty_date < ADD_MONTHS(TRUNC(&asofdate
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
AND acty_date < ADD_MONTHS(TRUNC(&asofdate
SELECT mon1, mon2, mon2 - mon1 diff
FROM (SELECT SUM(CASE WHEN mon = ADD_MONTHS(TRUNC(&asofdate
SUM(CASE WHEN mon = ADD_MONTHS(TRUNC(&asofdate
FROM monthlydata);
ASKER
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/D D/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
)
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/D
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
)
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
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
ASKER
Ok - I am getting an error that I am missing right parenthesis on this line " ON part.EMP_id = dfrl.EMP_id'
ASKER
I think I fixed the error. Still checking.
ASKER
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."
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."
ASKER
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/D D/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
)
;
DEFINE AsOfDate = TO_DATE('11/01/2014','MM/D
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
AND DFRL_ACTY_D < ADD_MONTHS(TRUNC(&asofdate
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
)
;
ASKER
I will add the mon part back - I just needed to see the sql run. Not sure why it would not run.
ASKER
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/D D/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);
DEFINE AsOfDate = TO_DATE('11/01/2014','MM/D
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
AND ACTY_Date < ADD_MONTHS(TRUNC(&asofdate
SELECT mon1, mon2, mon2 - mon1 diff
FROM (SELECT SUM(CASE WHEN mon = ADD_MONTHS(TRUNC(&asofdate
SUM(CASE WHEN mon = ADD_MONTHS(TRUNC(&asofdate
FROM monthlydata);
You have an extra closing paran.
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);
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
AND acty_date < ADD_MONTHS(TRUNC(&asofdate
SELECT mon1, mon2, mon2 - mon1 diff
FROM (SELECT SUM(CASE WHEN mon = ADD_MONTHS(TRUNC(&asofdate
SUM(CASE WHEN mon = ADD_MONTHS(TRUNC(&asofdate
FROM monthlydata);
ASKER
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'.
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
and the date field for ACTY_Date is '02-SEP-14'.
ASKER
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
EMP_ID ACT_Date DEF_P
096 22-Sep-14 15
EMP_ID ACT_Date DEF_P
096 22-OCT-14 10
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I understand. I appreciate the help and feedback
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
);