Retriving Max date results - 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

Retriving Max date results

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-09-16 : 10:07:35

Hey Guys

Really random one

What is the easiest way to get the premium rate for this particular merchant

I am only interested in knowing what the rate was in july,

SELECT [FDMSAccountNo]
,[fee_sequence]
,[fee_retail_amt]
,[fee_wholesale_date]
,[fee_retail_date]
,max([fee_retail_date])
FROM [FDMS].[dbo].[Audit_FDMS_Billing_Fees_Hist]
Where fee_sequence in ('7ek', '7xc')
and FDMSAccountNo = '878756470885'
and fee_wholesale_amt <> '0.00000000'
group by FDMSAccountNo,fee_sequence,[fee_wholesale_amt]
,[fee_retail_amt]
,[fee_wholesale_date]
,[fee_retail_date]
order by [fee_retail_date] desc

Currently results return

FDMSAccountNo fee_sequence fee_retail_amt fee_wholesale_date fee_retail_date (No column name)
878756470885 7EK 0.00960000 2013-05-28 2013-05-28 2013-05-28
878756470885 7XC 0.00990000 2013-05-28 2013-05-28 2013-05-28
878756470885 7EK 0.00960000 2013-02-27 2013-02-27 2013-02-27
878756470885 7XC 0.00990000 2013-02-27 2013-02-27 2013-02-27

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-16 : 11:27:49

quote:
Originally posted by masond

Hey Guys

Really random one

What is the easiest way to get the premium rate for this particular merchant

I am only interested in knowing what the rate was in july,

SELECT [FDMSAccountNo]
,[fee_sequence]
,[fee_retail_amt]
,[fee_wholesale_date]
,[fee_retail_date]
,max([fee_retail_date])
FROM [FDMS].[dbo].[Audit_FDMS_Billing_Fees_Hist]
Where fee_sequence in ('7ek', '7xc')
and FDMSAccountNo = '878756470885'
and fee_wholesale_amt <> '0.00000000'
group by FDMSAccountNo,fee_sequence,[fee_wholesale_amt]
,[fee_retail_amt]
,[fee_wholesale_date]
,[fee_retail_date]
order by [fee_retail_date] desc

Currently results return

FDMSAccountNo fee_sequence fee_retail_amt fee_wholesale_date fee_retail_date (No column name)
878756470885 7EK 0.00960000 2013-05-28 2013-05-28 2013-05-28
878756470885 7XC 0.00990000 2013-05-28 2013-05-28 2013-05-28
878756470885 7EK 0.00960000 2013-02-27 2013-02-27 2013-02-27
878756470885 7XC 0.00990000 2013-02-27 2013-02-27 2013-02-27

If you have only one merchant that you are querying at one time, then just use a TOP (1) qualifier and the appropriate ordering.

SELECT  TOP (1) [FDMSAccountNo] ,
        [fee_sequence] ,
....
....
        [fee_retail_date]
ORDER BY [fee_retail_date] DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources