Dynamcaly create Columns base on given parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamcaly create Columns base on given parameters

Author

Topic

reachmmadhu
Starting Member

12 Posts

Posted - 2009-01-30 : 09:12:14

If a user gives From and todate then it should fetch data in between the dates and also it should display the data with respective Month.
Following are the fileds user enter the date
@FromMonth int,
@Tomonth int ,
@Year int

Eg: inputs

FromMonth Tomonth Year
2 8 2007

2 Feb
8 Aug

Months should create Dynamicaly base on given inputs

Result should be :

Febraury March April May June July
1212 214 234 23 125 848
23 34 34 34 34 34

required very urgently Base on this i have generate Reports

If Someone respond for this post very quickly its a great help

Madhu

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-01-30 : 09:28:51

[code]
Declare @fromdt datetime, @todt datetime

select @fromdt = cast(cast(@year as varchar(4))+ cast(@from as varchar(2))+ '01' as datetime),@todt = cast(cast(@year as varchar(4))+ cast(@to as varchar(2))+ '01' as datetime)

Select
sum(case when month(datecol) = 1 then somecol else 0 end) as January,
sum(case when month(datecol) = 2 then somecol else 0 end) as February,
..
From Table
where datcol between @fromdt and @todt
[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 09:38:56

quote:

Originally posted by harsh_athalye


Declare @fromdt datetime, @todt datetime

select @fromdt = cast(cast(@year as varchar(4))+ cast(@from  as varchar(2))+ '01' as datetime),@todt = cast(cast(@year as varchar(4))+ cast(@to as varchar(2))+ '01' as datetime)

Select
sum(case when month(datecol) = 1 then somecol else 0 end) as January,
sum(case when month(datecol) = 2 then somecol else 0 end) as February,
..
From Table
where datcol >= DATEADD(mm,@fromdt-1,DATEADD(yy,@Year-1900,0))
and @todt< DATEADD(mm,@todt,DATEADD(yy,@Year-1900,0))

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

as all params are int use above

reachmmadhu
Starting Member

12 Posts

Posted - 2009-02-01 : 23:50:27

thank you very much

Madhu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 00:04:43

welcome

reachmmadhu
Starting Member

12 Posts

Posted - 2009-02-02 : 05:26:59

Hey

variables should not be in dateime datatype
It shud be in int datatype

Inputs are FromMonth, ToMonth and Year
eg:2,8 and 2008 respectivly

Madhu

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-02 : 05:55:47

check like this

Declare @FromMonth int,
@Tomonth int ,
@Year int
select @Tomonth = 8, @FromMonth = 1 , @year = 2008

Select
sum(case when month(datecol) = 1 then somecol else 0 end) as January,
sum(case when month(datecol) = 2 then somecol else 0 end) as February,
..
From Table
where month(datecol)>=@FromMonth
and month(datecol)<@Tomonth
and year(datecol) = @year

or just slight modification to visakh code
where datcol >= DATEADD(mm,@fromdt-1,DATEADD(yy,@Year-1900,0))
and datecol< DATEADD(mm,@todt,DATEADD(yy,@Year-1900,0))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 08:37:36

quote:
Originally posted by reachmmadhu

Hey

variables should not be in dateime datatype
It shud be in int datatype

Inputs are FromMonth, ToMonth and Year
eg:2,8 and 2008 respectivly

Madhu

that exactly what i'm given try the query and see


Select
sum(case when month(datecol) = 1 then somecol else 0 end) as January,
sum(case when month(datecol) = 2 then somecol else 0 end) as February,
..
From Table
where datcol >= DATEADD(mm,@fromdt-1,DATEADD(yy,@Year-1900,0))
and datcol< DATEADD(mm,@todt,DATEADD(yy,@Year-1900,0))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources