Combining 2 rows from same table?

[Lftsk]

Hello,

I hope someone can help me.

I have a table. For example's sake, it has 4 columns.
In actuality, it has 15.

The structure is

Date smalldatetime (Primary Key)
Total1 money
Total2 money
Total3 money
(table actually has 14 different "Total" columns)

The Date column will always be the last day of the month
and only 1 record will ever have that date.

For example:

Date Total1 Total2 Total3
3/31/2003 10.00 20.00 30.00
4/30/2003 100.00 200.00 300.00
5/31/2003 400.00 500.00 600.00

My question is how do I retrieve a given month's and the previous month's figures into one record using an SQL statement.

For example what I need for May 2003 is:

Date Tot1 Tot2 Tot3 PTot1 PTot2 PTot3
5/31/2003 400.00 500.00 600.00 100.00 200.00 300.00

The "Tot" columns are from May and the "Ptot" columns are from April.

I'm trying to avoid imbedding a SELECT statement for each previous month's column since I actually have 14 "total" columns and a SELECT statment for each one would be quite cumbersome.

Thank you for any suggestions.

 

[GShen]

you could declare 14 variables(EG. @ptot1..... @ptot14).
then use 1 select statement to load all these columns for the prior month. Once this is done, you can add these fields in your select statement for you current months data.
eg.
delcare @ptot1 as money,
@ptot2 as money (etc, etc)

select @ptot1 = tot1,
@ptot2 = tot2 (etc, etc)
from totaltable
where date = prior month

select @ptot1, @ptot2, currtot1, currtot2 (etc, etc)
from totaltable
where date = current month

Its the best I could do for now. It is pretty straight forward also.

Hope this helps.

 

[sunila7]

Hi,

Try this... Does it help

Declare @month int
declare @year int

set @month = 5
set @year = 2003
SELECT date,total1 Tot1,Total2 Tot2, Total3 Tot3,
(select Total1 from TBLname where
month(date) = @month-1
and
year(date) = @year) PTot1,
(select Total2 from TBLname where
month(date) = @month-1
and
year(date) = @year) PTot2,
(select Total3 from TBLname where
month(date) = @month-1
and
year(date) = @year) PTot3
FROM TBLname
WHERE
month(date) = @month
and
year(date) = @year


Sunil

 

[ClaireHCM]

select test.date,test.Total1 as Tot1,Test.Total2 as Tot2,Test.Total3 as Tot3,
A.total1 as PTot1,A.total2 as PTot2,A.total3 as PTot3 from test
full outer join
(select dateadd(d,-1,cast(year(date) as varchar(4))+'-'+cast(month(date)+2 as varchar(2))+'-1') as date,total1,total2,total3 from test)A
on test.date=A.date

--------------------------------------------------------
My test table is
create table test(date smalldatetime,Total1 money,Total2 money,Total3 money)

insert into test select '3/31/2003', 10.00 , 20.00 , 30.00
insert into test select '4/30/2003', 100.00, 200.00, 300.00
insert into test select '5/31/2003', 400.00, 500.00, 600.00

 

[Lftsk]

Thank you all. The example that worked best for me was ClaireHsu's. I had to modify it a little but it's fine. Now I have to place it in a Stored Proc and pass parameters, so I can get the recordset back.

Thanks again!