Doh, I choose 0 because I miscounted the number of partitions, I thought there were 21 instead of 28. Anyways, for those interested in a full executable test of the QOD see the following. I found this to be a good question (despite the GROUP BY missing) because I had not worked with partitions. Thanks!

CREATE DATABASE MyDb

ALTER DATABASE MyDB

ADD FILEGROUP Balances

GO

ALTER DATABASE MyDB

ADD FILE (NAME = N'Balances', FILENAME = 'C:\Balances.mdf') TO FILEGROUP Balances

GO

USE MyDB

GO

CREATE PARTITION FUNCTION AccountBalanceDate (DATETIME)

AS RANGE RIGHT FOR VALUES ('2008-03-31',

'2008-04-30','2008-05-31','2008-06-30','2008-07-31',

'2008-08-31','2008-09-30','2008-10-31','2008-11-30',

'2008-12-31','2009-01-31','2009-02-28','2009-03-31',

'2009-04-30','2009-05-31','2009-06-30','2009-07-31',

'2009-08-31','2009-09-30','2009-10-31','2009-11-30',

'2009-12-31','2010-01-31','2010-02-28','2010-03-31',

'2010-04-30','2010-05-31')

GO

CREATE PARTITION SCHEME schemeAccountBalanceDate

AS PARTITION AccountBalanceDate

ALL TO (Balances)

GO

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MyBalances')

DROP TABLE MyBalances

GO

CREATE TABLE [dbo].MyBalances(

[AccountBalanceRefNo] [uniqueidentifier] NULL,

[AccountRefNo] [uniqueidentifier] NULL,

[AccountBalanceDate] [datetime] NULL,

[CostCentreRefNo] [uniqueidentifier] NULL,

[BalanceCurrencyRefNo] [uniqueidentifier] NULL,

[BalanceAmount] [decimal](18, 3) NULL,

[AccruedInterest] [decimal](18, 3) NULL,

[ExchangeRate] [decimal](15, 7) NULL,

[BaseCurrencyRefNo] [uniqueidentifier] NULL,

[BaseAmount] [decimal](18, 3) NULL,

[BaseAccruedInterest] [decimal](18, 3) NULL)

ON schemeAccountBalanceDate([AccountBalanceDate])

GO

CREATE CLUSTERED INDEX [IX_MyBalances_Date] ON [dbo].[MyBalances]

( [AccountBalanceDate] ASC,

[AccountRefNo] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON schemeAccountBalanceDate(AccountBalanceDate)

GO

-- Simulate the partitions being loaded

INSERT MyBalances(AccountBalanceDate)

VALUES('2008-03-31'),

('2008-04-30'),('2008-05-31'),('2008-06-30'),('2008-07-31'),

('2008-08-31'),('2008-09-30'),('2008-10-31'),('2008-11-30'),

('2008-12-31'),('2009-01-31'),('2009-02-28'),('2009-03-31'),

('2009-04-30'),('2009-05-31'),('2009-06-30'),('2009-07-31'),

('2009-08-31'),('2009-09-30'),('2009-10-31'),('2009-11-30'),

('2009-12-31'),('2010-01-31'),('2010-02-28'),('2010-03-31'),

('2010-04-30'),('2010-05-31')

select $Partition.AccountBalanceDate(AccountBalanceDate) Partition, COUNT(1) RecordsPerPartition

from MyBalances with(nolock)

where $Partition.AccountBalanceDate(AccountBalanceDate) between 22 and 25

GROUP BY $Partition.AccountBalanceDate(AccountBalanceDate)

order by 1

GO

USE Master

DROP Database MyDB