Calculating Avg - 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

Calculating Avg

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-05 : 04:59:23

Hey Guys

Sorry to bother you again I have one further question, Which no doubt you will be able to help me on

Aim – to calculate an average, by pence and percent by card type via fdmsaccountno

Column ‘[Pence_Percent]’ identfies whether is a “Percent” or “Pence”
Column ‘Card_Type’ identifes whether its ‘credit’ or ‘debit’

This is my query

SELECT
[FDMSAccountNo]
,[fee_sequence]
,[fee_retail_amt]
,[fee_wholesale_date]
,Card_Type
,[Pence_Percent]
FROM
(
SELECT [FDMSAccountNo]
,[fee_sequence]
,[fee_retail_amt]
,[fee_wholesale_date]
,Dim_Fee_Codes.Card_Type
,[Pence_Percent]
,ROW_NUMBER() OVER (PARTITION BY FDMSaccountno,fee_sequence ORDER BY Fee_Wholesale_date DESC) AS Seq
FROM [FDMS].[dbo].[Audit_FDMS_Billing_Fees_Hist]
INNER JOIN Dim_Fee_Codes ON Audit_FDMS_Billing_Fees_Hist.fee_sequence = Dim_Fee_Codes.Fee_Code
where FDMSAccountNo = '878226943883'
and fee_retail_amt <> '0.00000000'
and Card_Type <> 'N/A'
)t
WHERE Seq=1
order by Card_Type desc

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-05 : 05:03:33

Required outcome

would be something along the lines of

Fdmsaccountno card type avg Pence_Percent
878226943883 Debit 2.3 Pence
878226943883 Debit 1.70% Percent
878226943883 Credit 1.9 Pence
878226943883 Credit 0.8% Percent

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-05 : 05:16:34

sounds like this


SELECT 
[FDMSAccountNo]
,AVG([fee_retail_amt]*1.0) AS AvgVal
,Card_Type
,[Pence_Percent]
FROM
(
SELECT [FDMSAccountNo]
,[fee_sequence]
,[fee_retail_amt]
,[fee_wholesale_date]
,Dim_Fee_Codes.Card_Type
,[Pence_Percent]
,ROW_NUMBER() OVER (PARTITION BY FDMSaccountno,fee_sequence ORDER BY Fee_Wholesale_date DESC) AS Seq
FROM [FDMS].[dbo].[Audit_FDMS_Billing_Fees_Hist]
INNER JOIN Dim_Fee_Codes ON Audit_FDMS_Billing_Fees_Hist.fee_sequence = Dim_Fee_Codes.Fee_Code
where FDMSAccountNo = '878226943883'
and fee_retail_amt <> '0.00000000'
and Card_Type <> 'N/A'
)t
WHERE Seq=1
GROUP BY [FDMSAccountNo]
,Card_Type
,[Pence_Percent]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-05 : 05:23:37

visakh16 you beauty

You have bailed me out so much :)

Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-05 : 07:35:37

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources