dynamic table reporting

Hello,
I need a small logic help on how to go about this. Today I have a table
that made as Current_month (Feb of 2006), Jan, Dec, Nov, Oct,
Sept...till Feb of 2005.
if (@.todate>@.fromdate)
if(month(@.fromdate)=month(todays_date)
select * from current_month where
timegenerated>=@.fromdate and timegenerated<=todate
else if(month(@.fromdate)<month(todays_date)
***********************************
How do I write this statement' say my fromdate is 2005-12-2 and my
todate is 2005-12-15... so my query shld be
select * from Dec where timegenerated>=@.fromdate and
timegenerated<=todate
All my tables are in the name of months. I just need a logic in making
the query.
Thank you so much in advanceONe way to do this is with a dynamic query in the data set ie
="SELECT * FROM " & IIF(Parameters!todate.Value >=Parameters!fromdate.Value and month(Parameters!fromdate.value = yada yada,
Month,...
and build the select string like this...
another way is to build the string in SQL and exec it ie
Declare @.SQL varchar(1000)
SELECT @.SQL = 'Select * from "
If ......
@.SQL = @.SQL + month(......
EXEC(@.SQL)
Hope this helps..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"star" wrote:
> Hello,
> I need a small logic help on how to go about this. Today I have a table
> that made as Current_month (Feb of 2006), Jan, Dec, Nov, Oct,
> Sept...till Feb of 2005.
> if (@.todate>@.fromdate)
> if(month(@.fromdate)=month(todays_date)
> select * from current_month where
> timegenerated>=@.fromdate and timegenerated<=todate
> else if(month(@.fromdate)<month(todays_date)
> ***********************************
>
> How do I write this statement' say my fromdate is 2005-12-2 and my
> todate is 2005-12-15... so my query shld be
> select * from Dec where timegenerated>=@.fromdate and
> timegenerated<=todate
> All my tables are in the name of months. I just need a logic in making
> the query.
> Thank you so much in advance
>|||Hi Star,
Best is to use views. I have worked in a environment where we use millions
of records in each month table. we have created some thing like this.
CREATE view [dbo].[month_data]
with schemabinding
AS
SELECT * FROM month_01 -- (select whatever field you require)
UNION ALL
SELECT * FROM month_02
... and so on for all the month.
and use select * from month_data where fromdate>= ... and todate <= ...
Regards
Amarnath
"star" wrote:
> Hello,
> I need a small logic help on how to go about this. Today I have a table
> that made as Current_month (Feb of 2006), Jan, Dec, Nov, Oct,
> Sept...till Feb of 2005.
> if (@.todate>@.fromdate)
> if(month(@.fromdate)=month(todays_date)
> select * from current_month where
> timegenerated>=@.fromdate and timegenerated<=todate
> else if(month(@.fromdate)<month(todays_date)
> ***********************************
>
> How do I write this statement' say my fromdate is 2005-12-2 and my
> todate is 2005-12-15... so my query shld be
> select * from Dec where timegenerated>=@.fromdate and
> timegenerated<=todate
> All my tables are in the name of months. I just need a logic in making
> the query.
> Thank you so much in advance
>|||Thanks alot for the replies. Now my new question is how about when I
have to find all the records between may 10th till july 5th?
it shld be select * from may where timegenerated>='2005-05-10' and
timegenerated<='2005-05-31' UNION
select * from june UNION select * from july where
timegenerated>='2005-07-1' and timegenerated<='2005-07-5'
So how do I do it dynamically like any dates (come from report
parameters) I give it shld query multiple tables without changing the
code. Iam so confused with this when writing the stored procedure. BTW
does UNION work here'
Thanks|||Well, the viwe should have all the months (12 months) select statement so
when you access the view it picks up all the months data. it means you can
give any date to any date like you said that for may how you dont have to
write a seperate query the view will have from Jan to dec data or Feb 05 to
feb 06 etc... In the reporting server data tab. you can use this view as a
table and query it.
This should solve your problem.
Regards
Amarnath
"star" wrote:
> Thanks alot for the replies. Now my new question is how about when I
> have to find all the records between may 10th till july 5th?
> it shld be select * from may where timegenerated>='2005-05-10' and
> timegenerated<='2005-05-31' UNION
> select * from june UNION select * from july where
> timegenerated>='2005-07-1' and timegenerated<='2005-07-5'
> So how do I do it dynamically like any dates (come from report
> parameters) I give it shld query multiple tables without changing the
> code. Iam so confused with this when writing the stored procedure. BTW
> does UNION work here'
> Thanks
>