* a value by a fixed number - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

* a value by a fixed number

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-23 : 07:16:08

Hey guys

Sorry to use this forum so much
i have another query, which no doubt you will be able to help me out with,

basically once 2011, and 2012 has been summed up i need it to be multiplied by 0.0003

Any ideas ? My query is

with cte as (
SELECT [hst_merchnum],

Case when year(hst_date_processed) = '2011' then SUM ([Scheme_Fees]) else 0 end as [2011] ,
Case when year(hst_date_processed) = '2012' then SUM ([Scheme_Fees]) else 0 end as [2012]
,[hst_prod_code]
,[hst_plan_code]
,[hst_date_processed]
,[hst_sales_amt]
,[hst_returns_amt]
,[Net_Sales]
,[hst_sales_tran]
,[hst_returns_tran]
,[Net_Trans]
,[hst_sales_icg]
,[hst_returns_icg]
,[Net_Intg]
,[Scheme_Fees]
,[Funding_Amt]
,[FDMS_SRG_KEY]
,[Period_Key]
,[Plan_Key]
,[ID]
FROM [FDMS].[dbo].[Fact_Financial_History]

group by hst_merchnum,
[hst_prod_code]
,[hst_plan_code]
,[hst_date_processed]
,[hst_sales_amt]
,[hst_returns_amt]
,[Net_Sales]
,[hst_sales_tran]
,[hst_returns_tran]
,[Net_Trans]
,[hst_sales_icg]
,[hst_returns_icg]
,[Net_Intg]
,[Scheme_Fees]
,[Funding_Amt]
,[FDMS_SRG_KEY]
,[Period_Key]
,[Plan_Key]
,[ID])

select hst_merchnum,SUM([2011]) as [2011 Scheme_Fees],SUM([2012]) as [2012 Scheme_Fees],SUM([2012]) - SUM([2011]) as [Comparision]
from cte
group by hst_merchnum

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-23 : 07:20:21

Did you mean this, or is there more to it?


....
                	[Plan_Key],
                	[ID]
            )

SELECT
	hst_merchnum,
	SUM([2011])*0.0003 AS [2011 Scheme_Fees],
	SUM([2012])*0.0003 AS [2012 Scheme_Fees],
	(SUM([2012]) - SUM([2011]))*0.0003 AS [Comparision]
FROM
	cte
GROUP BY
	hst_merchnum

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-23 : 07:21:11

hi sunitabeck

i think thats it :)

thank you very much

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-23 : 07:29:41

You are very welcome :)

And, about this:

quote:
Sorry to use this forum so much

Don't even worry about it! Most people on the forum who answer questions enjoy doing so and learn new things themselves from the questions and posted answers - I know I certainly do! So it is mutually beneficial.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources