How to Calculate a new field based on exist fields in query

Question

Hello All,

i tried alot to get a new calculated column,

my present quert is:

Select MonthNameCol, Region, Agent, sum(Acct_WIG * Dollar_prc) as col1, sum(ACCT_WGP * Dollar_prc)as col2
from CompanyCapital,ForignEx
where
MonthNameCol in ('2009-06', '2009-07', '2009-08', '2009-09')
and Region in ('Canada')
and CompanyCapital.Id = ForignEx.ID.id
and "Companies"."Id" = "CompanyPeriodReport"."CompanyId"
group by MonthNameCol, Region, Agent

output:
MonthNameCol,Region,Agent,  col1,   col2
2009-06, Canada, BBDO, 1401, 1312
2009-07, Canada, BBDO, 1357, 1206
2009-08, Canada, BBDO, 1427, 1032
2009-09, Canada, BBDO, 1363, 1100

 

based on the above result,
how can i get the another column which is col3,formula as   (col1-col2/(previous month col2 value) * 30)

for example: 2009-09    col3= 1363-1100 so 263 then 263/1032 (because previous month 2009-08  col2 value is 1032) * 30 i.e = 7.64

here the trickie thing that i broke my head is how to get previous month col2 value in Calculation

Could you please Help me

i worked a lot , seems to be no use

Thanks in advance
asita

Answer 1

Which version of SQL Server you have?

---

Abdallah, PMP, MCTS

Answer 2

Hi Abdallah,

version is Microsoft SQL Server 2005 - 9.00.3042.00 (X64) 

please help me,


Thanks in advance
aswani

Answer 3

Try the following. Please test it as I didn't test it. Just fix the line where I mentioned that I didn't understand what it does.

;WITH CTE AS
(
SELECT MonthNameCol
, Region
, Agent
, SUM(Acct_WIG * Dollar_prc) AS col1
, SUM(ACCT_WGP * Dollar_prc)AS col2
, ROW_NUMBER() OVER (ORDER BY MonthNameCol) AS row
FROM CompanyCapital
JOIN ForignEx ON CompanyCapital.Id = ForignEx.ID.id 
WHERE 
MonthNameCol IN('2009-06', '2009-07', '2009-08', '2009-09') 
AND Region IN ('Canada') 
AND "Companies"."Id" = "CompanyPeriodReport"."CompanyId" --I didn't really understand this line
GROUP BY MonthNameCol, Region, Agent
) AS X

	SELECT c2.MonthNameCol,c2.Region,c2.Agent,c2.col1,c2.col2
		(c1.col1-c1.col2/( c2.col2) * 30) AS col3
	FROM CTE c1
		JOIN CTE c2 ON c1.row = c2.row + 1

---

Abdallah, PMP, MCTS

Answer 4

Hi abdshall thanks for your reply,

ignore that above commecnted line because i truncated som e query which is (lot of joins in where clause)

but when i ran the query i goit

Incorrect Syntax near the keyword 'as'

then i removed AS X before second query but it says below message

Msg 4121, Level 16, State 1, Line 1

Cannot find either column "c2" or the user-defined function or aggregate "c2.col2", or the name is ambiguous.


could you please suggest any wrong in this

once again thank you abdshall
Best Regards
asita

Answer 5

WITH CTE AS
(
Select MonthNameCol, Region, Agent, sum(Acct_WIG * Dollar_prc) as col1, sum(ACCT_WGP * Dollar_prc)as col2
from CompanyCapital,ForignEx
where
MonthNameCol in ('2009-06', '2009-07', '2009-08', '2009-09')
and Region in ('Canada')
and CompanyCapital.Id = ForignEx.ID.id
and "Companies"."Id" = "CompanyPeriodReport"."CompanyId"
group by MonthNameCol, Region, Agent
)
SELECT A.*,1.*(A.col1-A.col2)/B.col2 * 30
FROM CTE AS A LEFT JOIN CTE AS B
  ON A.MonthName+'-01'=DATEADD(MONTH,1,B.MonthName+'-01')

Answer 6

Yeah, remove AS X because you don't need it. I was writing the query in another method before I decided to use CTE and forgot to remove it. Can you post the query as is please to see where the error is?

---

Abdallah, PMP, MCTS

Answer 7

Yeah, remove AS X because you don't need it. I was writing the query in another method before I decided to use CTE and forgot to remove it. Can you post the query as is please to see where the error is?

---

Abdallah, PMP, MCTS

just edit a bit from Abdhall script

I think you misses ; in the end of the script..

;WITH CTE AS   (   SELECT MonthNameCol   <br/>
, Region   <br/>
, Agent   <br/>
, SUM(Acct_WIG * Dollar_prc) AS col1   <br/>
, SUM(ACCT_WGP * Dollar_prc)AS col2   , <br/>
ROW_NUMBER() OVER (ORDER BY MonthNameCol) AS row   <br/>
FROM CompanyCapital   <br/>
JOIN ForignEx ON CompanyCapital.Id = ForignEx.ID.id    <br/>
WHERE    MonthNameCol IN('2009-06', '2009-07', '2009-08', '2009-09')    <br/>
AND Region IN ('Canada')    <br/>
AND "Companies"."Id" = "CompanyPeriodReport"."CompanyId" --I didn't really understand this line   <br/>
GROUP BY MonthNameCol, Region, Agent   )    	<br/>
SELECT c2.MonthNameCol,c2.Region,c2.Agent,c2.col1,c2.col2   		<br/>
(c1.col1-c1.col2/( c2.col2) * 30) AS col3   	<br/>
FROM CTE c1   		<br/>
JOIN CTE c2 ON c1.row = c2.row + 1 ; 

---

Best Regards,
Melissa Suciadi

If you have found this post helpful, please click the 'Vote as Helpful ' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered ' link below. It helps others who experience the same issue in future to find the solution.

Answer 8

Melissa, there is no need for the semicolon because he is not executing another statement after that. 
I'm sure there is another error in the code and that's why I asked him to repost.

Thank you for looking into it though.

---

Abdallah, PMP, MCTS

Answer 9

;

 

WITH CTE AS

(

select

 

"Period"

."Period" AS "Snapshot_Period",

"Regions"

."RegionName" AS "Region",

"CompanyNetworks"

."NetworkName" AS "Network",

sum

 

("Company_FieldData"."AccountsReceivables" * "FX_Rates"."PerDollar") AS "Accounts_Receivable__USD_",

sum

 

("Company_FieldData"."InputToAR" * "FX_Rates"."PerDollar") AS "Input_to_AR__USD_"

,

 

ROW_NUMBER() OVER (ORDER BY "Period"."Period") AS row

from

 

"WIC"."dbo"."Period_view" "Period", "WIC"."dbo"."Regions" "Regions", "WIC"."dbo"."Networks" "CompanyNetworks", "WIC"."dbo"."Company_FieldData" "Company_FieldData", "WIC"."dbo"."FX_Rates" "FX_Rates", "WIC"."dbo"."Companies_incl_VirGroup" "Companies", "WIC"."dbo"."Countries" "Countries", "WIC"."dbo"."CompanyPeriodReport_view" "CompanyPeriodReport"

where

 

"Period"

."Period" in ('2009-06', '2009-07', '2009-08', '2009-09')

and

 

"CompanyNetworks"."NetworkName" in ('BBDO')

and

 

"Regions"."RegionName" in ('Canada')

and

 

"Period"."Id" = "CompanyPeriodReport"."PeriodId"

and

 

"Companies"."Id" = "CompanyPeriodReport"."CompanyId"

and

 

"CompanyNetworks"."Id" = "Companies"."NetworkId"

and

 

"Countries"."Id" = "Companies"."CountryId"

and

 

"Regions"."Id" = "Countries"."RegionId"

and

 

"CompanyPeriodReport"."Id" = "Company_FieldData"."Id"

and

 

"FX_Rates"."PeriodId" = "CompanyPeriodReport"."PeriodId"

and

 

"FX_Rates"."CurrencyId" = "CompanyPeriodReport"."CurrencyId"

group

 

by "Period"."Period", "CompanyNetworks"."NetworkName", "Regions"."RegionName"

)

 

SELECT

 

c2.Snapshot_Period,c2.Region,c2.Network,c2.Accounts_Receivable__USD_,c2.Input_to_AR__USD_

 

(c1.Accounts_Receivable__USD_ - c1.Input_to_AR__USD_/( c2.Input_to_AR__USD_) * 30) AS col3

 

FROM CTE c1

 

JOIN CTE c2 ON c1.row = c2.row + 1

plase check this

thanks & Regards
asita

Answer 10

You are missing a comma before this line
(c1.Accounts_Receivable__USD_ - c1.Input_to_AR__USD_/( c2.Input_to_AR__USD_) * 30) AS col3
Also, I would put all the column names in brackets, it makes it clear for reading. Check out the following

; WITH CTE AS

(

select "Period"."Period" AS "Snapshot_Period", 
"Regions"."RegionName" AS "Region", 
"CompanyNetworks"."NetworkName" AS "Network", 
sum ("Company_FieldData"."AccountsReceivables" * "FX_Rates"."PerDollar") AS "Accounts_Receivable__USD_",
sum ("Company_FieldData"."InputToAR" * "FX_Rates"."PerDollar") AS "Input_to_AR__USD_"
,ROW_NUMBER() OVER (ORDER BY "Period"."Period") AS row

from "WIC"."dbo"."Period_view" "Period", "WIC"."dbo"."Regions" "Regions", "WIC"."dbo"."Networks" "CompanyNetworks", "WIC"."dbo"."Company_FieldData" "Company_FieldData", "WIC"."dbo"."FX_Rates" "FX_Rates", "WIC"."dbo"."Companies_incl_VirGroup" "Companies", "WIC"."dbo"."Countries" "Countries", "WIC"."dbo"."CompanyPeriodReport_view" "CompanyPeriodReport"

where
"Period"."Period" in ('2009-06', '2009-07', '2009-08', '2009-09') 
and "CompanyNetworks"."NetworkName" in ('BBDO') 
and "Regions"."RegionName" in ('Canada') 
and "Period"."Id" = "CompanyPeriodReport"."PeriodId" 
and "Companies"."Id" = "CompanyPeriodReport"."CompanyId" 
and "CompanyNetworks"."Id" = "Companies"."NetworkId" 
and "Countries"."Id" = "Companies"."CountryId" 
and "Regions"."Id" = "Countries"."RegionId" 
and "CompanyPeriodReport"."Id" = "Company_FieldData"."Id" 
and "FX_Rates"."PeriodId" = "CompanyPeriodReport"."PeriodId" 
and "FX_Rates"."CurrencyId" = "CompanyPeriodReport"."CurrencyId"

group by "Period"."Period", "CompanyNetworks"."NetworkName", "Regions"."RegionName"
)
SELECT

c2.Snapshot_Period
,c2.Region,c2.Network
,c2.[Accounts_Receivable__USD_]
,c2.[Input_to_AR__USD_]
,((c1.[Accounts_Receivable__USD_] - c1.[Input_to_AR__USD_])/( c2.[Input_to_AR__USD_]) * 30) AS col3 
FROM CTE c1 
JOIN CTE c2 ON c1.row = c2.row + 1

---

Abdallah, PMP, MCTS

Answer 11

Side note:

sum(Acct_WIG * Dollar_prc) might be better as sum(Acct_WIG) * Dollar_prc

similarly, sum(ACCT_WGP * Dollar_prc) might be better as sum(ACCT_WGP) * Dollar_prc

Assuming that makes sense. The query is not clear as the COLUMNs are not qualified.