Showing posts with label current. Show all posts
Showing posts with label current. Show all posts

Sunday, March 11, 2012

Dynamic From Clause

Hi,
Q: Is there a way to make the table's name in the from clause dynamic?
I have a job (T-SQL statements) that makes copies of current month's
selected tables to our archive DB. All copies will have a time stamp like
TableName_yyyy_mm. What I always do is that I edit the T-SQL statements in
that job to reflect the current year and the current month for all statement
s
(find and replace of course).
This month will be,
Select * into archive.dbo.PEIF_2005_03 from data.dbo.PEIF
Next month will be,
Select * into archive.dbo.PEIF_2005_04 from data.dbo.PEIF
.. and so on
I have tried using variables for the time stamp, like:
Declare @.Year Int
Declare @.Month Int
Select @.Year=(
select
case
when month(getdate()) - 1 = 0 then year(getdate()) - 1
else year(getdate())
end
)
Select @.Month=(
select
case
when month(getdate()) - 1 = 0 then 12
else month(getdate()) - 1
end
)
Select * into archive.dbo.PEIF_@.yyyy_@.mm from data.dbo.PEIF (DOESN'T WORK)
Apparently @.variable only works for where clauses (at least in my case).
What I want (if possible) is that I don't need to update the job every month
and let the job does the work for me.
Please help,
Adrian T> Q: Is there a way to make the table's name in the from clause dynamic?
http://www.sommarskog.se/dynamic_sql.html|||you would want to look into the stored procedure sp_executesql in BOL.
Basically it allows you to say "exec sp_executesql @.mySQL" and whatever
is in the string of @.MySQL you've just built up it will generate an
execution plan for it, then run it.
Cheers
Will|||ur solution is almost there.
try this.. a small change.. hope this helps.
Declare @.Year Int
Declare @.Month Int
Select @.Year=(
select
case
when month(getdate()) - 1 = 0 then year(getdate()) - 1
else year(getdate())
end
)
Select @.Month=(
select
case
when month(getdate()) - 1 = 0 then 12
else month(getdate()) - 1
end
)
exec('Select * into archive.dbo.PEIF_' + @.yyyy + '_' + @.mm + ' from
data.dbo.PEIF')

Wednesday, March 7, 2012

Dynamic Dates in Columns on Matrix Report?

I need to display 12 months dynamically in columns of a matrix report, starting with the current MMM, yy - 3 in the first column, current MMM, yy and incrementing by 1 month in columns 2-12.

For example a report that would run on today's MM, yy (5/2007) would look something like this:

MAR-07, APR-07, MAY-07, JUN-07, JUL-07, AUG-07, AUG-07, SEP-07, OCT-07, NOV-07, DEC-07, JAN-08, FEB-08

(Col 1 - Col 12 separated by commas)

How do I do this in a matrix report column?

1- Filter your datasource to return only those rows that meet the date criteria you describe

If SQL Server...

Code Snippet

Select Item, SalesDate, Sales from YourTable Where SalesDate >= DateAdd(dd, -3, GetDate()) AND SalesDate <= DateAdd(dd, 9, GetDate())

For example:

Item Date Sales

Bicycle Mar-07 $5000

Bicycle Apr-07 $4500

etc...

Then set your date column as the Column Group in the matrix report

Then select that column header in SSRS Layout view, and change the format property to the MMM-yy

(If the granularity of the dates in your source data is not month, if it's by day instead for instance, then you'll need to aggregate that data in your query with the appropriate Group By calls)

Hope this helps,

Geof

Dynamic Dates in Columns on Matrix Report

I need to display 12 months dynamically in columns of a matrix report, starting with the current MMM, yy - 3 in the first column, current MMM, yy and incrementing by 1 month in columns 2-12.

For example a report that would run on today's MM, yy (5/2007) would look something like this:

MAR-07, APR-07, MAY-07, JUN-07, JUL-07, AUG-07, AUG-07, SEP-07, OCT-07, NOV-07, DEC-07, JAN-08, FEB-08

(Col 1 - Col 12 separated by commas)

How do I do this in a matrix report column?

I would do this by created an enumerated type for months and then iterate through them based on current month value(5). when you reach 12(or 13 depending on starting value) then reset to 0 or 1 respectively and increase the year by one.

Eric

|||

I'm still not sure how to actually do this. Can you send me an example of how to do this?

|||

Can you post your code please so I understand what you need.

|||

I ended up declaring a begindate and enddate, based on the current month in the stored procedure and passed that in.

Dynamic Date/Calendar

Hey,
I have a financial query that has to retrieve sales totals for each day of
the month for the current year and prior year. Some days though (current an
d
prior) don't have any sales on those days so I still have to return zero. I
have this working just fine...but to do it I created a month_day_year table
that has all the month/day/year columns in it. I join to this and thus am
always assured to have rows/columns returned regardless if there are sales
for those days. The problem is the maintenance of this month_day table. If I
forget to add new months...things go bad. Surely there has to be a better wa
y
to do this...like some way to dynamically generate the month/day/year idea a
t
query run time...and not use this table to join to to ensure I get something
whether data is there or not.
Thanks in advance,
Paul
--
---
Jackson-Reed, Inc. www.jacksonreed.comActually, a calendar table is a great way to handle financial data like
this. As you know fiscal periods don't always line up to calendar
periods; a calendar table let yous manage this and other problems.
Managing this table shouldn't be a big deal; 10 years of days is only
slightly more than 3600 rows; 20 years is still less than 10,000 rows
of data, which should be nothing to manage. I would just generate the
calendar entries for 10 to 20 years, and leave it alone.
Stu|||> for those days. The problem is the maintenance of this month_day table. If
> I
> forget to add new months...things go bad.
Why do you need to add months and days in an ongoing fashion? Just put the
next 30 years in the table, and you might have to think about it again once
before you retire. 10,000 rows is less stressful than a walk in the park
for SQL Server.
http://www.aspfaq.com/2519

Wednesday, February 15, 2012

Dyanamic date changing to current date when printing PDF

I have a dynamic date in my reporting services report that displays
the previous month and year we are reporting on. Here is the
expression: =MonthName(Month(Now().AddMonths(-1))) & " " &
Year(Now())
When I create a pdf from this it displays correctly, but when I print
it, it always puts the current month and year in this field. Does
anyone know why and how to prevent it from doing this when I print the
pdf?
Thanks for any help you can give.
-CarlyJust to clarify, you create a pdf then print the pdf from within Adobe
Acrobat and while the pdf file you see on the screen has September
2007, for instance, the printed pdf will have October 2007?
Is that correct? You're printing the pdf, not printing the report
directly from Report Manager?
- C
On Oct 5, 10:22 am, caroline.fe...@.sdncommunications.com wrote:
> I have a dynamic date in my reporting services report that displays
> the previous month and year we are reporting on. Here is the
> expression: =MonthName(Month(Now().AddMonths(-1))) & " " &
> Year(Now())
> When I create a pdf from this it displays correctly, but when I print
> it, it always puts the current month and year in this field. Does
> anyone know why and how to prevent it from doing this when I print the
> pdf?
> Thanks for any help you can give.
> -Carly