Prev: Deploy script
Next: ssms 2005 temp table issue
From: Rocky20 on 3 Jun 2010 11:46
I have a simple table and I need help finding the first payment date
and the amount payed.

Here is the table
CREATE TABLE [dbo].[Payments](
[MemberID] [nchar](10) NOT NULL,
[PaymentDate] [datetime] NOT NULL,
[Amount] [money] NULL,
CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
(
[MemberID] ASC,
[PaymentDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Here is some data to insert
insert into payments values (101,'01-01-2009',20)
insert into payments values (101,'02-01-2009',30)
insert into payments values (101,'03-01-2009',50)
insert into payments values (101,'04-01-2009',60)
insert into payments values (101,'05-01-2009',10)
insert into payments values (102,'04-20-2010',70)
insert into payments values (102,'04-21-2010',20)
insert into payments values (102,'04-30-2010',80)

What I should get back is
101 - 01-01-2009 - 20
102 - 04-20-2010 - 70

I know I need a group by clause but I can't seem to get it to work.

Thanks
From: Tom Cooper on 3 Jun 2010 12:17
;With PaymentsOrderedByDate As
(Select MemberID, PaymentDate, Amount,
Row_Number() Over (Partition By MemberID Order By PaymentDate) As rn
From Payments)
Select MemberID, PaymentDate, Amount
From PaymentsOrderedByDate
Where rn = 1;

Tom

"Rocky20" <msmith1265(a)hotmail.com> wrote in message
news:6ca4a2f0-fd01-4257-8d88-649e55ac962e(a)d12g2000vbr.googlegroups.com...
>I have a simple table and I need help finding the first payment date
> and the amount payed.
>
> Here is the table
> CREATE TABLE [dbo].[Payments](
> [MemberID] [nchar](10) NOT NULL,
> [PaymentDate] [datetime] NOT NULL,
> [Amount] [money] NULL,
> CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
> (
> [MemberID] ASC,
> [PaymentDate] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
>
>
> Here is some data to insert
> insert into payments values (101,'01-01-2009',20)
> insert into payments values (101,'02-01-2009',30)
> insert into payments values (101,'03-01-2009',50)
> insert into payments values (101,'04-01-2009',60)
> insert into payments values (101,'05-01-2009',10)
> insert into payments values (102,'04-20-2010',70)
> insert into payments values (102,'04-21-2010',20)
> insert into payments values (102,'04-30-2010',80)
>
> What I should get back is
> 101 - 01-01-2009 - 20
> 102 - 04-20-2010 - 70
>
> I know I need a group by clause but I can't seem to get it to work.
>
> Thanks

 | 
Pages: 1
Prev: Deploy script
Next: ssms 2005 temp table issue