June 1, 2016 at 12:53 pm
SO I have the following select statement and everything is working correctly except for the last piece (LstYrMthProfit). I need it to do what PriorYrProfit is doing but only from the certain time frame stated.
select territory_desc, '' as name, cust_code as Acct_No, '' as Account,
Sum(ext_price) as PriorYrRev,
Sum(ext_price) - SUM(ext_cost) as PriorYrGross,
(Sum(ext_price) - SUM(ext_cost))/ Sum(ext_price) as PriorYrProfit,
SUM(CASE WHEN date_entered >= dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0))
AND date_entered < dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
THEN ext_price END) as LstYrMthRev,
SUM(CASE WHEN date_entered >= dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0))
AND date_entered < dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
THEN ext_price - ext_cost END) as LstYrMthGross,
SUM(CASE WHEN date_entered >= dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0))
AND date_entered < dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
THEN (ext_price - ext_cost)/ext_price END) as LstYrMthProfit
from ##jonathan
where territory_desc = '083' and date_entered < DATEADD(yy, -1, GETDATE())
group by cust_code, territory_desc
Results:
territory_descnameAcct_NoAccountPriorYrRevPriorYrGrossPriorYrProfitLstYrMthRevLstYrMthGrossLstYrMthProfit
083309997002859.1000000000000996.96540000000000.348699487.2000000000000173.75120000000004.027358
Should be:
territory_descnameAcct_NoAccountPriorYrRevPriorYrGrossPriorYrProfitLstYrMthRevLstYrMthGrossLstYrMthProfit
083309997002859.1000000000000996.96540000000000.348699487.2000000000000173.7512000000000.356****
Can anyone help me???
June 1, 2016 at 1:35 pm
Those two formulas are not equivalent. Without looking at the data or taking into account date ranges this looks like what your query is doing.
SELECT (SUM(COL_ONE) - SUM(COL_TWO)) / SUM(COL_THREE), SUM((COL_ONE - COL_TWO) / COL_THREE) FROM (VALUES (1.0, 2.0, 3.0), (2.0, 3.0, 4.0) ) TESTY(COL_ONE, COL_TWO, COL_THREE)
The two sums do not come out equal, the math that's working it out to would be.
-- Your first column
SELECT (3.0 - 5.0) / 7.0
--Your second column
SELECT ((1.0 -2.0) /3.0) + ((2.0-3.0) / 4.0)
This might be what you're trying to do for your last column.
(SUM(CASE WHEN date_entered >= dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0))
AND date_entered < dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
THEN (ext_price) END)
-
SUM(CASE WHEN date_entered >= dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0))
AND date_entered < dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
THEN (ext_cost) END))
/
SUM(CASE WHEN date_entered >= dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0))
AND date_entered < dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
THEN (ext_price) END)
as LstYrMthProfit
June 1, 2016 at 1:51 pm
So it worked somewhat. I just have to find the error for 0 if encountered as that is the error I am getting:
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.
select territory_desc, '' as name, cust_code as Acct_No, '' as Account,
Sum(ext_price) as PriorYrRev,
Sum(ext_price) - SUM(ext_cost) as PriorYrGross,
(Sum(ext_price) - SUM(ext_cost))/ Sum(ext_price) as PriorYrProfit,
SUM(CASE WHEN date_entered >= dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0))
AND date_entered < dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
THEN ext_price END) as LstYrMthRev,
SUM(CASE WHEN date_entered >= dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0))
AND date_entered < dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
THEN ext_price - ext_cost END) as LstYrMthGross,
(SUM(CASE WHEN date_entered >= dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0))
AND date_entered < dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
THEN (ext_price) END)
-
SUM(CASE WHEN date_entered >= dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0))
AND date_entered < dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
THEN (ext_cost) END))
/
SUM(CASE WHEN date_entered >= dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0))
AND date_entered < dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
THEN (ext_price) END) as LstYrMthProfit
from ##jonathan
--where territory_desc = '083' and date_entered < DATEADD(yy, -1, GETDATE())
where date_entered < DATEADD(yy, -1, GETDATE())
group by cust_code, territory_desc
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply