How to make date dynamic for a report in ssrs using T-sql?

Friday, February 15, 2013 3:06 PM

0

Hi ,

I have a procedure which is something like this

select

TEDW_D_DATE_TYPE_1.DATE_SHORT,TEDW_F_EMAIL_CAPTURE.ORDERS_BOUNCE

from
EDW.Calendar.TEDW_D_DATE_TYPE_1
full
outer join EDW.Retail.TEDW_F_EMAIL_CAPTURE
on
TEDW_F_EMAIL_CAPTURE.DATE_KEY = TEDW_D_DATE_TYPE_1.DATE_KEY
where
TEDW_D_DATE_TYPE_1.DATE_SHORT between '2012-08-01' and '2012-08-30'
Now i want to use this procedure in a report and if the user picks a particular date then he must be able to see all the values for the whole month not for the day which can be done by above proc but how can i do it dynamic? how can i allow the user to only select 1 date and show all values for whole month? I cannot use start date and end date as i have to use only 1 date.

Can somebody help me with this,

Thanks.
- Reply
- Quote

All Replies

Friday, February 15, 2013 3:16 PM

1
Hi, you can change your query for this one

select TEDW_D_DATE_TYPE_1.DATE_SHORT,TEDW_F_EMAIL_CAPTURE.ORDERS_BOUNCE from EDW.Calendar.TEDW_D_DATE_TYPE_1 full outer join EDW.Retail.TEDW_F_EMAIL_CAPTURE on TEDW_F_EMAIL_CAPTURE.DATE_KEY = TEDW_D_DATE_TYPE_1.DATE_KEY where Month(TEDW_D_DATE_TYPE_1.DATE_SHORT) = Month(@DateSelected) and Year(TEDW_D_DATE_TYPE_1.DATE_SHORT) = Year(@DateSelected)

Regards

Regards | John Bocachica | http://jboca.blogspot.com
- Edited by John Bocachica Friday, February 15, 2013 3:17 PM
- Marked As Answer by sujiththechamp Friday, February 15, 2013 3:20 PM
- Unmarked As Answer by sujiththechamp Friday, February 15, 2013 3:26 PM
- Marked As Answer by sujiththechamp Friday, February 15, 2013 4:39 PM
- Reply
- Quote
Friday, February 15, 2013 3:26 PM

0

Hi Jon,

I can see the values for whole month,but there is a small problem.If i select let's say '2012-08-20' then i can see all the values of august from 01-30 ,but the actual calender starts from july 29 and ends at august 28 or soemthing like that.SO,is there any way where i can get the data for actual month instead of given date month?

I hope you understand my question,If not please let me know.

Thanks
- Reply
- Quote
Friday, February 15, 2013 3:42 PM

0

If i understand, that means that you have a customized calendar? I don't really understand your issue.
Regards | John Bocachica | MVP www.sqlgeek.net
- Reply
- Quote
Friday, February 15, 2013 3:46 PM

0

Hi Joe,

If you look at the calendar for this month i.e. feb- 2013 the calender for this month first day is 27th jan and this month ends on march 2.The solution you gave me brings all the values for feb -2013 if i pick any date in feb but i want to see the values from jan 27-march2.

Hope you got it now?

Thanks
- Reply
- Quote
Friday, February 15, 2013 3:54 PM

1

Oh, ok, you need to get from the sunday of the first week of the current month, to the saturday of the last week of the curren month, right?

If what i'm thinking it's ok, pls let me know to write the query.

Regards
Regards | John Bocachica | MVP www.sqlgeek.net
- Reply
- Quote
Friday, February 15, 2013 3:55 PM

0

Hi John,

Exactly you are correct thats what i need to write the query to.

Thanks.
- Reply
- Quote

Friday, February 15, 2013 4:14 PM

Hi Sujith, If thats the case you would have to create a customized calendar table and use it to pass dates or month number and year to your query.

--Build calendar table
 DECLARE @SDate SMALLDATETIME = '01/01/1900' --First Calendar date to include in table
 DECLARE @EDate SMALLDATETIME = GETDATE() --Last calendar date to include in the table
 
 ;
     WITH --generate numbers table
         E00(N) AS (SELECT 1 UNION ALL SELECT 1),
         E02(N) AS (SELECT 1 FROM E00 a, E00 b),
         E04(N) AS (SELECT 1 FROM E02 a, E02 b),
         E08(N) AS (SELECT 1 FROM E04 a, E04 b),
         E16(N) AS (SELECT 1 FROM E08 a, E08 b),
         E32(N) AS (SELECT 1 FROM E16 a, E16 b),
    cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32),
     --CTE to generate calendar dates
 CalendarCTE AS (
 SELECT
         DateKey = n
       , CalendarDate = DATEADD(DAY, n - 1, @SDate )
    FROM cteTally
   WHERE N <= DATEDIFF(DAY, @SDate , @EDate +1)
 )
  
 --Calendar table
 SELECT DateKey
     , IsoDate             = CONVERT(CHAR(8), CalendarDate, 112)
     , CalendarDate
     , CalendarYear        = YEAR(CalendarDate)
     , CalendarQuarter     = DATEPART(QUARTER,CalendarDate)
     , CalendarMonth       = MONTH(CalendarDate)
     , CalendarDay         = DATEPART(DAY, CalendarDate)
     , [DayofWeek]         = DATEPART(Dw, CalendarDate)
     , CalendarWeekOfMonth = DATEDIFF(WEEK, DATEADD(DAY,1, CalendarDate-DAY(CalendarDate) + 1) -1, CalendarDate) +1
     , WeekofYear          = DATEPART(WEEK, CalendarDate)
     , [DayofYear]         = DATEPART(DAYOFYEAR, CalendarDate)
     , NameofMonth         = DATENAME(MONTH, CalendarDate)
     , NameofDay           = DATENAME(WEEKDAY,CalendarDate)
     , WeekStartDate	   = MIN(CalendarDate) OVER (PARTITION BY YEAR(CalendarDate), CASE WHEN CalendarDate > '12/31/2012' AND DATENAME(WEEKDAY,CalendarDate) = 'Sunday' THEN DATEPART(WEEK, CalendarDate)+1 ELSE DATEPART(WEEK, CalendarDate) END)
 FROM CalendarCTE
 
 
 
 
 
 
 
 
 
 
 
 
 
 GO

Hope this will give you a direction.

Edited by ione721 Friday, February 15, 2013 4:15 PM typo

Friday, February 15, 2013 4:22 PM

Hi, now with a clear way, this will help.

select 
TEDW_D_DATE_TYPE_1.DATE_SHORT,TEDW_F_EMAIL_CAPTURE.ORDERS_BOUNCE 
from EDW.Calendar.TEDW_D_DATE_TYPE_1 
full outer join EDW.Retail.TEDW_F_EMAIL_CAPTURE 
on TEDW_F_EMAIL_CAPTURE.DATE_KEY = TEDW_D_DATE_TYPE_1.DATE_KEY 
where TEDW_D_DATE_TYPE_1.DATE_SHORT >= dateadd(d,(1-datepart(dw, cast(cast(year(@DateSelected) as varchar) + '-' + cast(month(@DateSelected) as varchar) + '-1' as date))),cast(cast(year(@DateSelected) as varchar) + '-' + cast(month(@DateSelected) as varchar) + '-1' as date))
and TEDW_D_DATE_TYPE_1.DATE_SHORT <= dateadd(d, 7-datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)), cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))

http://jboca.blogspot.com

Edited by John Bocachica Friday, February 15, 2013 4:24 PM
Proposed As Answer by John Bocachica Friday, February 15, 2013 4:26 PM
Marked As Answer by sujiththechamp Friday, February 15, 2013 4:39 PM

Friday, February 15, 2013 4:31 PM

0

Did you try with my last solution?
Regards | John Bocachica | MVP www.sqlgeek.net
- Reply
- Quote
Friday, February 15, 2013 4:45 PM

0

Hi Jon,

Sorry but a small correction , The month starts perfectly but it ends with the last date of the month lets say of the 30th of this month falls in next month but it comes under this month and next month too...Hope you understand what i am saying?

Thanks
- Reply
- Quote

Friday, February 15, 2013 5:13 PM

Ok, in that case..

select 
TEDW_D_DATE_TYPE_1.DATE_SHORT,TEDW_F_EMAIL_CAPTURE.ORDERS_BOUNCE 
from EDW.Calendar.TEDW_D_DATE_TYPE_1 
full outer join EDW.Retail.TEDW_F_EMAIL_CAPTURE 
on TEDW_F_EMAIL_CAPTURE.DATE_KEY = TEDW_D_DATE_TYPE_1.DATE_KEY 
where TEDW_D_DATE_TYPE_1.DATE_SHORT >= dateadd(d,(1-datepart(dw, cast(cast(year(@DateSelected) as varchar) + '-' + cast(month(@DateSelected) as varchar) + '-1' as date))),cast(cast(year(@DateSelected) as varchar) + '-' + cast(month(@DateSelected) as varchar) + '-1' as date))
and TEDW_D_DATE_TYPE_1.DATE_SHORT <= cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))

Regards

Regards | John Bocachica | MVP www.sqlgeek.net

Friday, February 15, 2013 5:21 PM

0

Hi John,

No change,still the same result.

Thanks
- Reply
- Quote

Friday, February 15, 2013 5:31 PM

Code snippet for first Sunday and last saturday of each month.

DECLARE @year INT
SET @year = 2013
--1st Sunday of each month
SELECT MIN(dates) AS Sunday 
FROM
(SELECT DATEADD(DAY,number-1,DATEADD(YEAR,@year-1900,0)) AS dates 
	FROM master..spt_values
	WHERE type = 'p' AND number BETWEEN 1 AND DATEDIFF(DAY,DATEADD(YEAR,@year-1900,0),DATEADD(YEAR,@year-1900+1,0))
) AS t 
WHERE DATENAME(WEEKDAY,dates) = 'Sunday'
GROUP BY DATEADD(MONTH,DATEDIFF(MONTH,0,dates),0)
--Last Saturday of each month
SELECT MAX(dates) AS Saturday 
FROM
(SELECT DATEADD(DAY,number-1,DATEADD(YEAR,@year-1900,0)) AS dates 
	FROM master..spt_values
	WHERE type = 'p' AND number BETWEEN 1 AND DATEDIFF(DAY,DATEADD(YEAR,@year-1900,0),DATEADD(YEAR,@year-1900+1,0))
) AS t 
WHERE DATENAME(WEEKDAY,dates) = 'Saturday'
GROUP BY DATEADD(MONTH,DATEDIFF(MONTH,0,dates),0)

Friday, February 15, 2013 5:33 PM

0

Hi ione,

Thanks for your code,but i cannot embed this big code becaue i am using it inside another stored proc,The code sent by john earlier looks small and easy but there is a small error,if you understand that code can you take a look at it?
- Reply
- Quote
Friday, February 15, 2013 5:35 PM

0

Ok, let me clear something, in the case of February, you need to get information from the sunday of the first week of february, that is january 27th, to the saturday of the last week of february, that is March 2nd? Is this true?...

Or

You need to get information from the sunday of the first week of february, that again is January 27th, to the last date of the month, that is February 28th?

Wich one is your requirement?
Regards | John Bocachica | MVP www.sqlgeek.net
- Reply
- Quote

Friday, February 15, 2013 5:39 PM

@IONE721, your codespinet could be simplified to this

declare @dateselected date = getdate()
select
@dateselected DateSelected,
dateadd(d,(1-datepart(dw, cast(cast(year(@DateSelected) as varchar) + '-' + cast(month(@DateSelected) as varchar) + '-1' as date))),cast(cast(year(@DateSelected) as varchar) + '-' + cast(month(@DateSelected) as varchar) + '-1' as date)) FirstSunday,
dateadd(d, 7-datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)), cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)) LastSaturday,
cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date) LastDate

Regards | John Bocachica | MVP www.sqlgeek.net

Friday, February 15, 2013 5:42 PM

0

Hi John,

If you look at the calendar for february ,the month starts at february 3 which is the first sunday of the month and ends at march 2 which is the last saturday of the month.This is my requirement.

Hope you got it now.

Thanks.
- Reply
- Quote

Friday, February 15, 2013 5:51 PM

Well, this is the query that you need...


select 
TEDW_D_DATE_TYPE_1.DATE_SHORT,TEDW_F_EMAIL_CAPTURE.ORDERS_BOUNCE 
from EDW.Calendar.TEDW_D_DATE_TYPE_1 
full outer join EDW.Retail.TEDW_F_EMAIL_CAPTURE 
on TEDW_F_EMAIL_CAPTURE.DATE_KEY = TEDW_D_DATE_TYPE_1.DATE_KEY 
where TEDW_D_DATE_TYPE_1.DATE_SHORT >= dateadd(d,(1-datepart(dw, cast(cast(year(@DateSelected) as varchar) + '-' + cast(month(@DateSelected) as varchar) + '-1' as date))),cast(cast(year(@DateSelected) as varchar) + '-' + cast(month(@DateSelected) as varchar) + '-1' as date))
and TEDW_D_DATE_TYPE_1.DATE_SHORT <= case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
 when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)
 else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))*-1,cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
 end

Regards

Regards | John Bocachica | MVP www.sqlgeek.net

Friday, February 15, 2013 5:58 PM

0

Sujith you could make it a function and use it in your query. Mine may have some redundant code which could be taken out as @John pointed. Its been a while having used them take the content and modify them accordingly.

Regards..........
- Reply
- Quote
Friday, February 15, 2013 6:02 PM

0

As a recomendation, don't use functions if you don't needed, the functions Works with each row, so, if you have thousands or millions of rows, your query will be expensive and and slowly.

Regards
Regards | John Bocachica | MVP www.sqlgeek.net
- Reply
- Quote
Friday, February 15, 2013 6:14 PM

0

Hi John,

It starts correctly but dont know why after 2/23 it goes back to 1/27 and that week is displayed in the end.Can you please look into it?

Thanks
- Reply
- Quote
Friday, February 15, 2013 6:30 PM

0

Sujith try using the date queries I sent inline. I am with @John on the suggestion he made.
- Reply
- Quote

Friday, February 15, 2013 7:25 PM

Well, another query after lunch

select 
TEDW_D_DATE_TYPE_1.DATE_SHORT,TEDW_F_EMAIL_CAPTURE.ORDERS_BOUNCE 
from EDW.Calendar.TEDW_D_DATE_TYPE_1 
full outer join EDW.Retail.TEDW_F_EMAIL_CAPTURE 
on TEDW_F_EMAIL_CAPTURE.DATE_KEY = TEDW_D_DATE_TYPE_1.DATE_KEY 
where 
TEDW_D_DATE_TYPE_1.DATE_SHORT >= dateadd(d,(1-datepart(dw, cast(cast(year(case
 when @dateselected > 
	case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
		when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)
		else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))*-1,cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
	end then dateadd(d,12,@dateselected)
	else 
	case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
		when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)
		else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))*-1,cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
	end
end) as varchar) + '-' + cast(month(case
 when @dateselected > 
	case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
		when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)
		else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))*-1,cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
	end then dateadd(d,12,@dateselected)
	else 
	case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
		when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)
		else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))*-1,cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
	end
end) as varchar) + '-1' as date))),cast(cast(year(case
 when @dateselected > 
	case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
		when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)
		else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))*-1,cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
	end then dateadd(d,12,@dateselected)
	else 
	case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
		when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)
		else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))*-1,cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
	end
end) as varchar) + '-' + cast(month(case
 when @dateselected > 
	case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
		when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)
		else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))*-1,cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
	end then dateadd(d,12,@dateselected)
	else 
	case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
		when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)
		else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))*-1,cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
	end
end) as varchar) + '-1' as date))
AND 
TEDW_D_DATE_TYPE_1.DATE_SHORT <= case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,case
 when @dateselected > 
	case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
		when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)
		else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))*-1,cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
	end then dateadd(d,12,@dateselected)
	else 
	case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
		when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)
		else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))*-1,cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
	end
end )+1,0)) as date))
 when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,case
 when @dateselected > 
	case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
		when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)
		else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))*-1,cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
	end then dateadd(d,12,@dateselected)
	else 
	case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
		when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)
		else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))*-1,cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
	end
end )+1,0)) as date)
 else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,case
 when @dateselected > 
	case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
		when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)
		else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))*-1,cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
	end then dateadd(d,12,@dateselected)
	else 
	case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
		when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)
		else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))*-1,cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
	end
end )+1,0)) as date))*-1,
 cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,case
 when @dateselected > 
	case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
		when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)
		else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))*-1,cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
	end then dateadd(d,12,@dateselected)
	else 
	case datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
		when 7 then cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date)
		else dateadd(d,datepart(dw, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))*-1,cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateSelected)+1,0)) as date))
	end
end )+1,0)) as date))
 end

Regards

John Bocachica | Colombia | http://jboca.blogspot.com http://www.iwco.co

How to make date dynamic for a report in ssrs using T-sql?

All Replies

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support by product

Other support links

Not an IT pro?