December 29, 2014 at 7:53 am
Hi There,
I am trying to work out to do the following.
I have two date parameters a start and end date.
What I want to default in the start date is the FIRST MONDAY of the PREVIOUS month from the day you run the report.
The End date I would want to default the day BEFORE the FIRST MONDAY of the CURRENT month the day you run the report.
So if I was running it today the first Monday of November was 03/11/14. The day before the first Monday for December is 30/11/14, so that would be the end date.
Another example if I run the report sometime in Jan next year - it will default to start date - 1/12/14 (first Monday in December) and the end date will be 4/1/15 (the day before the first monday in Jan)
Hope the above makes sense?
December 29, 2014 at 8:12 am
December 29, 2014 at 8:38 am
Thanks, I've been playing about with these basic date functions but am still no where near close to working out how I do this to the next level, which is in my original post.
December 29, 2014 at 9:46 am
a quick (and dirty) solution using a TALLY table:
set nocount on
-- set a date variable to the start date of the range
declare @date date
set @date = '20130101'
-- create tally table to hold all dates in the desired range
create table tally_date (date_value date, year_number int, month_number int, date_number int, day_number int, occurrence int)
-- loop and fill the tally table (I know: a more efficient way is possible)
while @date < '20170101'
begin
insert into tally_date (date_value, year_number, month_number, date_number, day_number)
values(@date, DATEPART(year, @date), DATEPART(month, @date), DATEPART(day, @date), datepart(weekday, @date))
set @date = dateadd(day, 1, @date)
end
-- add values into the occurrence column
update tally_date
set occurrence = td.occurrence
from (select date_value, ROW_NUMBER() OVER (partition by year_number, month_number, day_number order by date_number) as occurrence
from tally_date) td
inner join tally_date
on tally_date.date_value = td.date_value
-- use the date variable for demo
set @date = '20141217'
-- display the required dates
select
*
from tally_date
where
date_value between dateadd(mm, datediff(mm, 0, @date) - 1, 0)-- beginning of previous month
and dateadd(ms, -3, dateadd(mm, datediff(mm, 0, @date) + 1, 0))-- end of this month
and day_number = 2
and occurrence = 1
December 29, 2014 at 10:14 am
I got a feeling I may have posted in the wrong forum.
These are parameters in Report Builder which uses VBA.
December 29, 2014 at 1:52 pm
TSQL Tryer (12/29/2014)
I got a feeling I may have posted in the wrong forum.These are parameters in Report Builder which uses VBA.
My bad... I didn't notice the SSRS forum because I got to this post from a general overview.
I don't have extensive experience with building reports, but I hope some of the other posters can give you the solution...
December 29, 2014 at 9:58 pm
You can calculate the dates you want directly:
;with TestData_cte as (
select date = convert(date,getdate())union all
select data = '20141201'union all
select data = '20141231'union all
select data = '20150101' )
select
DATE,
FirstMonOfPriorMonth =
dateadd(dd,(datediff(dd,'17530101',
dateadd(mm,-1+datediff(mm,0,a.Date),6)
)/7)*7,'17530101'),
DayBeforeFirstMonOfCurrMonth =
dateadd(dd,((datediff(dd,'17530101',
dateadd(mm,datediff(mm,0,a.Date),6)
)/7)*7)-1,'17530101')
from
TestData_cte a
Results:
DATE FirstMonOfPriorMonth DayBeforeFirstMonOfCurrMonth
---------- ----------------------- ----------------------------
2014-12-29 2014-11-03 00:00:00.000 2014-11-30 00:00:00.000
2014-12-01 2014-11-03 00:00:00.000 2014-11-30 00:00:00.000
2014-12-31 2014-11-03 00:00:00.000 2014-11-30 00:00:00.000
2015-01-01 2014-12-01 00:00:00.000 2015-01-04 00:00:00.000
December 30, 2014 at 1:10 am
Again thank you for your generosity of replying but I don't think the example will help me as it is VB language I need in report builder.
Thanks
December 30, 2014 at 1:38 am
Someone has posted on another site for me the following - which works - so if anyone needs to use it in the future.
I use a long IIF() expression to achieve your goal. Put the expressions below into your parameter default values.
FIRST MONDAY of the PREVIOUS month from today:
=IIF(Weekday(DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1)))=2,
DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1)),
DateAdd("d",9-Weekday(DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))),DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))))
The day BEFORE the FIRST MONDAY of the CURRENT month:
=IIF(Weekday(DateSerial(Year(Now()), Month(Now()), 1))=2,
DateAdd("d",-1,DateSerial(Year(Now()), Month(Now()), 1)),
DateAdd("d",8-Weekday(DateSerial(Year(Now()), Month(Now()), 1)),DateSerial(Year(Now()),
December 30, 2014 at 1:02 pm
Did you try building a dataset to calculate each default date that you want ? You could then use those data sets, one for the from date and the other for to date, as the default value in their respective report parameter.
The code Michael provided for the from and to dates is quite good.
----------------------------------------------------
December 30, 2014 at 2:21 pm
Or build a calendar table and use it in a dataset.
Don Simpson
December 31, 2014 at 10:40 am
DonlSimpson (12/30/2014)
Or build a calendar table and use it in a dataset.
How would this be different? Would you have fields in the table to denote first Tuesday of the month, etc... ?
----------------------------------------------------
December 31, 2014 at 12:10 pm
Doing things like you describe is not uncommon with date dimension/calendar tables in a data warehouse. Then you can do pretty much any kind of date-related query really easily.
December 31, 2014 at 12:19 pm
MMartin1 (12/31/2014)
DonlSimpson (12/30/2014)
Or build a calendar table and use it in a dataset.How would this be different? Would you have fields in the table to denote first Tuesday of the month, etc... ?
Actually, yes. You can include any number of attributes about each date. The calendar table used by my primary client includes things like:
dayOfMonth
daysInMonth
daysInMonthSoFar
firstDayOfMonth
firstDayOfQuarter
firstDayOfYear
isALeapYear
isAPublicHoliday
isAWeekendDay
lastDayOfMonth
lastDayOfQuarter
lastDayOfYear
weekdayNumber
weekdaysInMonth
weekdaysInMonthSoFar
weekOfMonth
So a where clause can include "weekOfMonth = 1 and weekdayNumber = 3" (assuming Sunday = 1).
Don Simpson
December 31, 2014 at 4:30 pm
DonlSimpson (12/31/2014)
MMartin1 (12/31/2014)
DonlSimpson (12/30/2014)
Or build a calendar table and use it in a dataset.How would this be different? Would you have fields in the table to denote first Tuesday of the month, etc... ?
Actually, yes. You can include any number of attributes about each date. The calendar table used by my primary client includes things like:
dayOfMonth
daysInMonth
daysInMonthSoFar
firstDayOfMonth
firstDayOfQuarter
firstDayOfYear
isALeapYear
isAPublicHoliday
isAWeekendDay
lastDayOfMonth
lastDayOfQuarter
lastDayOfYear
weekdayNumber
weekdaysInMonth
weekdaysInMonthSoFar
weekOfMonth
So a where clause can include "weekOfMonth = 1 and weekdayNumber = 3" (assuming Sunday = 1).
The issue I see here is where the first week of the month begins on Saturday the first; thus the first Monday, Sunday, whatever will happen in week 2. So that will require a little bit of coding here as well to capture.
----------------------------------------------------
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply