Advanced Defaulting Data Parameters

I have to date parameters defaulted as follows:
startdate = dateadd("M",-1,Today())
enddate = today()
So it will display data for the past month from today. Now, what I
need is the following. I need a subscription to be sent out at the
start of the month (so June 1st would be the next one). I need it to
show all data for May (that's 31 days, so far this is doing 30 days).
So using this same logic, it will return May 2nd to May 31st. I need
it to return the first and last of the previous month.
ThanksThere is a great article on how to do date stuff in T-SQL that might help
with other ideas, as the functionally is the same in VB
http://www.databasejournal.com/features/mssql/article.php/3076421
Reeves
"Ayman" wrote:
> I have to date parameters defaulted as follows:
> startdate = dateadd("M",-1,Today())
> enddate = today()
> So it will display data for the past month from today. Now, what I
> need is the following. I need a subscription to be sent out at the
> start of the month (so June 1st would be the next one). I need it to
> show all data for May (that's 31 days, so far this is doing 30 days).
> So using this same logic, it will return May 2nd to May 31st. I need
> it to return the first and last of the previous month.
> Thanks
>|||Here's how to do it in T-SQL (you can add this as a little dataset into your
report) -- you'll have to adjust the string parsing in the nested select if
your server's date representation is not MMDDYYY, but it's got three parts
and they are all straightforward. The only exciting bit there is making
sure to account for the first month of the year when you put the date
together.
SELECT CAST(FirstOfLastMonth AS DATETIME) AS FirstOfPreviousMonth,
DATEADD(month,1,CAST(FirstOfLastMonth AS DATETIME))-1 AS LastOfPreviousMonth
FROM (
SELECT FirstOfLastMonth = CASE WHEN MONTH(GETDATE()) = 1 THEN '12'
ELSE CAST(MONTH(GETDATE()) -1 AS VARCHAR) END +
'/1/' +
CASE WHEN MONTH(GETDATE()) = 1 THEN CAST(YEAR(GETDATE())-1 AS VARCHAR)
ELSE CAST(YEAR(GETDATE()) AS VARCHAR) END) X
Here's the trick: get the first day of the last month first (that's the
nested select) because you know its day is always day #1 of that month.
Once you've done that, getting the *last* day of the previous month is
pretty easy, no matter how many days there are in the month! Just use
DATEADD to go forward one month from the first day, which you've already
derived, and then go back one day <g>. You are probably going to slap
yourself on the head when you read that <g>.
>L<
"Ayman" <aymantg@.gmail.com> wrote in message
news:1180453077.604629.38770@.o5g2000hsb.googlegroups.com...
>I have to date parameters defaulted as follows:
> startdate = dateadd("M",-1,Today())
> enddate = today()
> So it will display data for the past month from today. Now, what I
> need is the following. I need a subscription to be sent out at the
> start of the month (so June 1st would be the next one). I need it to
> show all data for May (that's 31 days, so far this is doing 30 days).
> So using this same logic, it will return May 2nd to May 31st. I need
> it to return the first and last of the previous month.
> Thanks
>|||On May 30, 6:52 pm, "Lisa Slater Nicholls" <l...@.spacefold.com> wrote:
> Here's how to do it in T-SQL (you can add this as a little dataset into your
> report) -- you'll have to adjust the string parsing in the nested select if
> your server's date representation is not MMDDYYY, but it's got three parts
> and they are all straightforward. The only exciting bit there is making
> sure to account for the first month of the year when you put the date
> together.
> SELECT CAST(FirstOfLastMonth AS DATETIME) AS FirstOfPreviousMonth,
> DATEADD(month,1,CAST(FirstOfLastMonth AS DATETIME))-1 AS LastOfPreviousMonth
> FROM (
> SELECT FirstOfLastMonth => CASE WHEN MONTH(GETDATE()) = 1 THEN '12'
> ELSE CAST(MONTH(GETDATE()) -1 AS VARCHAR) END +
> '/1/' +
> CASE WHEN MONTH(GETDATE()) = 1 THEN CAST(YEAR(GETDATE())-1 AS VARCHAR)
> ELSE CAST(YEAR(GETDATE()) AS VARCHAR) END) X
> Here's the trick: get the first day of the last month first (that's the
> nested select) because you know its day is always day #1 of that month.
> Once you've done that, getting the *last* day of the previous month is
> pretty easy, no matter how many days there are in the month! Just use
> DATEADD to go forward one month from the first day, which you've already
> derived, and then go back one day <g>. You are probably going to slap
> yourself on the head when you read that <g>.
> >L<
> "Ayman" <ayma...@.gmail.com> wrote in message
> news:1180453077.604629.38770@.o5g2000hsb.googlegroups.com...
> >I have to date parameters defaulted as follows:
> > startdate = dateadd("M",-1,Today())
> > enddate = today()
> > So it will display data for the past month from today. Now, what I
> > need is the following. I need a subscription to be sent out at the
> > start of the month (so June 1st would be the next one). I need it to
> > show all data for May (that's 31 days, so far this is doing 30 days).
> > So using this same logic, it will return May 2nd to May 31st. I need
> > it to return the first and last of the previous month.
> > Thanks
I took a slightly different approach but it worked great. I made two
data sets with the following code.
end of month dataset:
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
start of month dataset:
select DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
Under Report>Report Parameters I just used the query for the default
values. Since both scripts only return one value, there is nothing
else that can be defaulted for either of these parameters. Works
well, although the first day of the month only puts the date and not
the time, it does start from midnight though. Thanks for everyone's
help.|||>>I made two data sets with the following code.
You can definitely do that -- and I'm glad you have something that works out
for you!
FWIW I like to have a single dataset with a whole bunch of parameter values
in it for reference (IOW a dataset that always has one row, with each
parameter or configuration value expressed as a column. That way, if you
need a lot of them, you don't have a lot of little datasets littering
around, and you can add in more columns with (say) formatted versions for
display in the report header, without a lot of work.
For example -- most of what you see here is just used in the report title,
but it's convenient to make it available to the report this way:
SELECT CONVERT(VARCHAR,@.From,101) AS FromDate,
CONVERT(VARCHAR,@.To,101) AS ToDate,
Left(DateName(month,@.From),3) AS FromCMonth,
Left(DateName(month,@.To),3) AS ToCMonth,
DATEPART(quarter,@.From) AS FromQtr,
DATEPART(quarter,@.To) AS ToQtr,
CAST(DATEPART(year,@.From) AS VARCHAR(4)) + '-' +
DateName(week,@.From) AS FromWeek,
CAST(DATEPART(year,@.To) AS VARCHAR(4)) + '-' +
DateName(week,@.To) AS ToWeek
...just a personal style thing, but it works really well...
>L<
"Ayman" <aymantg@.gmail.com> wrote in message
news:1180613135.364413.119400@.q75g2000hsh.googlegroups.com...
> On May 30, 6:52 pm, "Lisa Slater Nicholls" <l...@.spacefold.com> wrote:
>> Here's how to do it in T-SQL (you can add this as a little dataset into
>> your
>> report) -- you'll have to adjust the string parsing in the nested select
>> if
>> your server's date representation is not MMDDYYY, but it's got three
>> parts
>> and they are all straightforward. The only exciting bit there is making
>> sure to account for the first month of the year when you put the date
>> together.
>> SELECT CAST(FirstOfLastMonth AS DATETIME) AS FirstOfPreviousMonth,
>> DATEADD(month,1,CAST(FirstOfLastMonth AS DATETIME))-1 AS
>> LastOfPreviousMonth
>> FROM (
>> SELECT FirstOfLastMonth =>> CASE WHEN MONTH(GETDATE()) = 1 THEN '12'
>> ELSE CAST(MONTH(GETDATE()) -1 AS VARCHAR) END +
>> '/1/' +
>> CASE WHEN MONTH(GETDATE()) = 1 THEN CAST(YEAR(GETDATE())-1 AS
>> VARCHAR)
>> ELSE CAST(YEAR(GETDATE()) AS VARCHAR) END) X
>> Here's the trick: get the first day of the last month first (that's the
>> nested select) because you know its day is always day #1 of that month.
>> Once you've done that, getting the *last* day of the previous month is
>> pretty easy, no matter how many days there are in the month! Just use
>> DATEADD to go forward one month from the first day, which you've already
>> derived, and then go back one day <g>. You are probably going to slap
>> yourself on the head when you read that <g>.
>> >L<
>> "Ayman" <ayma...@.gmail.com> wrote in message
>> news:1180453077.604629.38770@.o5g2000hsb.googlegroups.com...
>> >I have to date parameters defaulted as follows:
>> > startdate = dateadd("M",-1,Today())
>> > enddate = today()
>> > So it will display data for the past month from today. Now, what I
>> > need is the following. I need a subscription to be sent out at the
>> > start of the month (so June 1st would be the next one). I need it to
>> > show all data for May (that's 31 days, so far this is doing 30 days).
>> > So using this same logic, it will return May 2nd to May 31st. I need
>> > it to return the first and last of the previous month.
>> > Thanks
> I took a slightly different approach but it worked great. I made two
> data sets with the following code.
> end of month dataset:
> select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
> start of month dataset:
> select DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
> Under Report>Report Parameters I just used the query for the default
> values. Since both scripts only return one value, there is nothing
> else that can be defaulted for either of these parameters. Works
> well, although the first day of the month only puts the date and not
> the time, it does start from midnight though. Thanks for everyone's
> help.
>