Prev: Diferent times for execution the same code
Next: WIndows Authentication Connection String
From: Andrey Vasylenko on 13 Jan 2010 12:47
Hi All!
Here is an example of table:

USER DATE AMOUNT
Tom 2009-08-11 10
Tom 2008-05-21 15
John 2009-12-02 20
Ben 2009-03-05 5
Ben 2009-08-23 25
Ben 2009-09-15 50

I need to create sql-query to show information about users in the next form
(for each user i should show 2 dates and amounts - last and previous) :

User LastDate LastAmount PrevDate PrevAmount
Tom 2009-08-11 10 2008-05-21 15
John 2009-12-02 20 null null
Ben 2009-09-15 50 2009-08-23 25

What workaround I can use to do this? cursor or just simple query?
Thanks!



From: Tom Cooper on 13 Jan 2010 13:14
Declare @Test Table([USER] varchar(10), DATE datetime, AMOUNT int);
Insert @Test ([USER], [DATE], AMOUNT)
Select 'Tom', '2009-08-11', 10
Union All Select 'Tom', '2008-05-21', 15
Union All Select 'John', '2009-12-02', 20
Union All Select 'Ben', '2009-03-05', 5
Union All Select 'Ben', '2009-08-23', 25
Union All Select 'Ben', '2009-09-15', 50;

With cte As
(Select [USER], DATE, AMOUNT,
Row_Number() Over(Partition By [USER] Order By DATE Desc) As rn
From @Test)
Select c1.[USER], c1.DATE As LastDate, c1.AMOUNT As LastAmount,
c2.DATE As PrevDate, c2.AMOUNT As PrevAmount
From cte c1
Left Outer Join cte c2 On c1.[USER] = c2.[USER]
And c2.rn = 2
Where c1.rn = 1;

Tom

"Andrey Vasylenko" <AndreyVasylenko(a)discussions.microsoft.com> wrote in
message news:F80C4305-6828-41D8-995A-8C7C836BF83C(a)microsoft.com...
> Hi All!
> Here is an example of table:
>
> USER DATE AMOUNT
> Tom 2009-08-11 10
> Tom 2008-05-21 15
> John 2009-12-02 20
> Ben 2009-03-05 5
> Ben 2009-08-23 25
> Ben 2009-09-15 50
>
> I need to create sql-query to show information about users in the next
> form
> (for each user i should show 2 dates and amounts - last and previous) :
>
> User LastDate LastAmount PrevDate PrevAmount
> Tom 2009-08-11 10 2008-05-21 15
> John 2009-12-02 20 null null
> Ben 2009-09-15 50 2009-08-23 25
>
> What workaround I can use to do this? cursor or just simple query?
> Thanks!
>
>
>

From: Andrey Vasylenko on 14 Jan 2010 06:44
Tom,
much appreciated!



"Tom Cooper" wrote:

> Declare @Test Table([USER] varchar(10), DATE datetime, AMOUNT int);
> Insert @Test ([USER], [DATE], AMOUNT)
> Select 'Tom', '2009-08-11', 10
> Union All Select 'Tom', '2008-05-21', 15
> Union All Select 'John', '2009-12-02', 20
> Union All Select 'Ben', '2009-03-05', 5
> Union All Select 'Ben', '2009-08-23', 25
> Union All Select 'Ben', '2009-09-15', 50;
>
> With cte As
> (Select [USER], DATE, AMOUNT,
> Row_Number() Over(Partition By [USER] Order By DATE Desc) As rn
> From @Test)
> Select c1.[USER], c1.DATE As LastDate, c1.AMOUNT As LastAmount,
> c2.DATE As PrevDate, c2.AMOUNT As PrevAmount
> From cte c1
> Left Outer Join cte c2 On c1.[USER] = c2.[USER]
> And c2.rn = 2
> Where c1.rn = 1;
>
> Tom

 | 
Pages: 1
Prev: Diferent times for execution the same code
Next: WIndows Authentication Connection String