I just can’t figure out how to translate this query into 1.2005.1 Adapter model / SQL 2000 / VS2005.

I'm trying to pick out the appropriate formula from a database table based on the purchase date being the most current one that is not newer than the next effective date (which can be and is entered in adavance).

The following works from Query Analyzer

SELECT TOP 1 [Formula] FROM [TaxFormulas]
WHERE [EffectiveDate] < @EffectiveDate and FuelTypeID = @FuelTypeID
ORDER BY [EffectiveDate] DESC

This is as close as I can get so far...

I can't figure out how to use the top 1 and sort operators with getscalar.

Dim uc As IPredicate = _
HelperClasses.FuelTaxFormulaFields.FuelTypeId = InvoiceItemFuelDelivery.FuelTypeId _
And
HelperClasses.FuelTaxFormulaFields.EffectiveDate < InvoiceItemFuelDelivery.DeliveryDate

InvoiceItemFuelDelivery.TaxFormula =  adapter.GetScalar(HelperClasses.FuelTaxFormulaFields.TaxFormula, AggregateFunction.None, uc).tostring()

Test Results

'1/1/2003'
2002-09-01 00:00:00.000                             2 9 2002
'1/1/2005'
2003-01-01 00:00:00.000                             2 2003
'1/1/2008'
2007-01-01 00:00:00.000                             2 2007
'1/1/2012'
2010-01-01 00:00:00.000                             2 2010

Test script follows: (The actual formulas have been replaced with dummy test data)

/* Create Temp Table for testing */
CREATE TABLE [dbo].[#TaxFormulas] (
    [TaxFormulaID] [int] IDENTITY (1, 1) NOT NULL ,
    [FuelTypeID] [int] NOT NULL ,
    [EffectiveDate] [datetime] NOT NULL ,
    [FormulaDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Formula] [varchar] (7000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[#TaxFormulas] ADD 
    CONSTRAINT [PK_TaxFormulas] PRIMARY KEY  CLUSTERED 
    (
        [TaxFormulaID]
    ) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
    CONSTRAINT [IX_TaxFormulas] UNIQUE  NONCLUSTERED 
    (
        [FuelTypeID],
        [EffectiveDate]
    )  ON [PRIMARY] 

SET NOCOUNT ON
SET IDENTITY_INSERT [#TaxFormulas] ON

PRINT 'Inserting test values into [#TaxFormulas]'
--------------------------------------------------------------------------------------------------
INSERT [#TaxFormulas] ([TaxFormulaID],[FuelTypeID],[EffectiveDate],[FormulaDescription],[Formula])
VALUES(1,2,'Jan  1 2003 12:00:00:000AM','Retail Diesel #2','2 2003')
INSERT [#TaxFormulas] ([TaxFormulaID],[FuelTypeID],[EffectiveDate],[FormulaDescription],[Formula])
VALUES(2,3,'Jan  1 2002 12:00:00:000AM','Retail Reefer','3 2002')
INSERT [#TaxFormulas] ([TaxFormulaID],[FuelTypeID],[EffectiveDate],[FormulaDescription],[Formula])
VALUES(4,2,'Jan  1 2007 12:00:00:000AM','Retail Diesel #2','2 2007')
INSERT [#TaxFormulas] ([TaxFormulaID],[FuelTypeID],[EffectiveDate],[FormulaDescription],[Formula])
VALUES(5,3,'Jan  1 2007 12:00:00:000AM','Retail Reefer','3 2007')
INSERT [#TaxFormulas] ([TaxFormulaID],[FuelTypeID],[EffectiveDate],[FormulaDescription],[Formula])
VALUES(6,2,'Jan  1 2006 12:00:00:000AM','Retail Diesel #2','2 2006')
INSERT [#TaxFormulas] ([TaxFormulaID],[FuelTypeID],[EffectiveDate],[FormulaDescription],[Formula])
VALUES(7,3,'Jan  1 2006 12:00:00:000AM','Retail Reefer','3 2006')
INSERT [#TaxFormulas] ([TaxFormulaID],[FuelTypeID],[EffectiveDate],[FormulaDescription],[Formula])
VALUES(8,2,'Jul  1 2006 12:00:00:000AM','Retail Diesel #2','2 7 2006')
INSERT [#TaxFormulas] ([TaxFormulaID],[FuelTypeID],[EffectiveDate],[FormulaDescription],[Formula])
VALUES(9,3,'Jul  1 2006 12:00:00:000AM','Retail Reefer','3 7 2006')
INSERT [#TaxFormulas] ([TaxFormulaID],[FuelTypeID],[EffectiveDate],[FormulaDescription],[Formula])
VALUES(10,2,'Jan  1 2002 12:00:00:000AM','Retail Diesel #2','2 2002')
INSERT [#TaxFormulas] ([TaxFormulaID],[FuelTypeID],[EffectiveDate],[FormulaDescription],[Formula])
VALUES(11,3,'Jan  7 2002 12:00:00:000AM','Retail Reefer','3 7 2002')
INSERT [#TaxFormulas] ([TaxFormulaID],[FuelTypeID],[EffectiveDate],[FormulaDescription],[Formula])
VALUES(12,2,'Sep  1 2002 12:00:00:000AM','Retail Diesel #2','2 9 2002')
INSERT [#TaxFormulas] ([TaxFormulaID],[FuelTypeID],[EffectiveDate],[FormulaDescription],[Formula])
VALUES(13,3,'Sep  1 2002 12:00:00:000AM','Retail Reefer','9 3 2003')
INSERT [#TaxFormulas] ([TaxFormulaID],[FuelTypeID],[EffectiveDate],[FormulaDescription],[Formula])
VALUES(14,2,'Jan  1 2009 12:00:00:000AM','Retail Diesel #2','2 2009')
INSERT [#TaxFormulas] ([TaxFormulaID],[FuelTypeID],[EffectiveDate],[FormulaDescription],[Formula])
VALUES(15,3,'Jan  1 2009 12:00:00:000AM','Retail Reefer','3 2009')
INSERT [#TaxFormulas] ([TaxFormulaID],[FuelTypeID],[EffectiveDate],[FormulaDescription],[Formula])
VALUES(16,2,'Jan  1 2010 12:00:00:000AM','Retail Diesel #2','2 2010')
INSERT [#TaxFormulas] ([TaxFormulaID],[FuelTypeID],[EffectiveDate],[FormulaDescription],[Formula])
VALUES(17,3,'Jan  1 2010 12:00:00:000AM','Retail Reefer','3 2010')

PRINT 'Done'
SET IDENTITY_INSERT [#TaxFormulas] OFF

/* testing variables */
declare @FuelTypeID int
declare @EffectiveDate datetime
declare @FormulaDate datetime
declare @formula varchar(8000) 
set @FuelTypeID = 2

/* test 1 */
set @EffectiveDate = '1/1/2003'
SELECT TOP 1 @Formula = [Formula], @FormulaDate = EffectiveDate
FROM [#TaxFormulas]
WHERE [EffectiveDate] < @EffectiveDate and FuelTypeID = @FuelTypeID
ORDER BY [EffectiveDate] DESC
select @FormulaDate, @formula

/* test 2 */
set @EffectiveDate = '1/1/2005'
SELECT TOP 1 @Formula = [Formula], @FormulaDate = EffectiveDate
FROM [#TaxFormulas]
WHERE [EffectiveDate] < @EffectiveDate and FuelTypeID = @FuelTypeID
ORDER BY [EffectiveDate] DESC
select @FormulaDate, @formula 

/* test 3 */
set @EffectiveDate = '1/1/2008'
SELECT TOP 1 @Formula = [Formula], @FormulaDate = EffectiveDate
FROM [#TaxFormulas]
WHERE [EffectiveDate] < @EffectiveDate and FuelTypeID = @FuelTypeID
ORDER BY [EffectiveDate] DESC
select @FormulaDate, @formula 

/* test 4 */
set @EffectiveDate = '1/1/2012'
SELECT TOP 1 @Formula = [Formula], @FormulaDate = EffectiveDate
FROM [#TaxFormulas]
WHERE [EffectiveDate] < @EffectiveDate and FuelTypeID = @FuelTypeID
ORDER BY [EffectiveDate] DESC
select @FormulaDate, @formula 

drop table [#TaxFormulas]

I don't believe you can, why not just fill an entity collection with one TaxFormulaEntity?

Thanks for the reply, that is basically what I ended up doing, it just seems like swatting a fly with a hammer...

Dim uc As IPredicate = _
HelperClasses.FuelTaxFormulaFields.FuelTypeId = InvoiceItemFuelDelivery.FuelTypeId _
And HelperClasses.FuelTaxFormulaFields.EffectiveDate < InvoiceItemFuelDelivery.DeliveryDate _
And HelperClasses.FuelTaxFormulaFields.TaxCategory = InvoiceItemFuelDelivery.TaxCategory

Dim ecfueltaxformula As New HelperClasses.EntityCollection(_
New FactoryClasses.FuelTaxFormulaEntityFactory())
adapter.FetchEntityCollection(ecfueltaxformula, New RelationPredicateBucket(uc), 1, _
New SortExpression(HelperClasses.FuelTaxFormulaFields.EffectiveDate Or SortOperator.Descending))

Dim tax As EntityClasses.FuelTaxFormulaEntity = _
CType(ecfueltaxformula.Item(0), EntityClasses.FuelTaxFormulaEntity)

This is the generated SQL:

SELECT TOP 1 [TaxFormulaID] AS [TaxFormulaId], [FuelTypeID] AS [FuelTypeId], 
[TaxCategory], [EffectiveDate], [FormulaDescription], 
[TaxFormula] 
FROM [FuelTaxFormula]  
WHERE ( ( ( 
[FuelTypeID] = @FuelTypeId1 
AND [EffectiveDate] < @EffectiveDate2) 
AND [TaxCategory] = @TaxCategory3)) 
ORDER BY [EffectiveDate] DESC

@FuelTypeId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2.
@EffectiveDate2 : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 2/10/2006 00:00:00.
@TaxCategory3 : AnsiString. Length: 20. Precision: 0. Scale: 0. Direction: Input. Value: PREPAID.