Dynamic column in the query using SQL 2005

Hi All,

I am using Micosoft Visual Studio Report Desinger. with MS SQL 2005.

I have a table transac table fields are likely,

location,date,amount values,

USA,01/07/2006,3000

SG,01/07/2006,2500

USA,02/07/2006,6000

SG,02/07/2006,3500

USA,03/07/2006,1000

SG,03/07/2006,6700

USA,04/07/2006,500

SG,04/07/2006,200

Am writing query for date = 04/07/2006

select location,date,amount from transac where date = 04/07/2006

I wanted to add two more column in the query which is

a.two days before what is the amount

b. From 01/07/2006 to 04/07/2006 what is the amount

The result I want to be

Location,date,amount,2daysbefore,uptodate

USA,04/07/2006,500,6000,10500

SG,04/07/2006,200,3500,12900

How to write a query ?.

I am writing this query from DataSet for Report Desinger.

Is there any way to include this two column.

Please Advise,

Regrads Saleem

Here is the query in bold, the rest if for creating a tmp table with approx values like the ones you use. NB date format is MM/DD/YYYY.

create table #x
(
country varchar(10),
Date datetime,
PRICE1 decimal(9,2),
)
insert #x
select 'USA', '1/1/2006', 3000 union all
select 'SG', '1/1/2006', 2500 union all
select 'USA', '1/2/2006', 2500 union all
select 'SG', '1/2/2006', 1500 union all
select 'USA', '1/3/2006', 1000 union all
select 'SG', '1/3/2006', 7550 union all
select 'USA', '1/4/2006', 500 union all
select 'SG', '1/4/2006', 300 union all
select 'USA', '1/5/2006', 350 union all
select 'SG', '1/5/2006', 400

select
country,
date,
price1 as dayAmount,
(select price1 from #x as b where datediff(dd,b.date,a.date) = 2 and a.country = b.country) as prevDayAmount,
(select sum(price1) from #x as b where datediff(mm,b.date,a.date) < 1 and a.country = b.country) as sumMonthAmount
from #x as a
where date = '01/03/2006'

drop table #x

Dynamic Column amount query

I want to write a query that will give me a dynamic amount of columns back.
What I want to do, I want to create a calendar application, in which for
each employee, I want to show if he is in the office or not.

this should look like:
ID, Name, 1,2,3,4,5,6,7,8
88,Leo,0,0,1,1,1,0,0,1

The amount of columns is dynamic, and is a period of time, with a column for
each day.
Any suggestions what the best approach to this could be?

Thanks

LeoIn SQL, a table has a known number of columns. What you are trying to
do is destroy First Normal Form (1NF). In a tiered architecture, such
fomatting is done in the front end and not the database.|||Leo Muller (leo_muller_israel@.hotmaill.com) writes:
> I want to write a query that will give me a dynamic amount of columns
> back. What I want to do, I want to create a calendar application, in
> which for each employee, I want to show if he is in the office or not.
> this should look like:
> ID, Name, 1,2,3,4,5,6,7,8
> 88,Leo,0,0,1,1,1,0,0,1
> The amount of columns is dynamic, and is a period of time, with a column
> for each day.

As Joe Celko says, relational databases are not meant for this kind of
thing. A query returns a table, and a table has a fixed number of columns.

This sort of thing may be best do client-side.

Nevertheless this is possible in SQL Server, but you will have to resort
to dynamic SQL, and it's all quite complicated.

An alternative is to use the third-party tool, RAC, see
http://www.rac4sql.net.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You may find this useful:

http://www.windowsitpro.com/Article...3140/43140.html