January 12, 2010 at 12:19 am
I am using the below sp to generate the weeks from 2009 and 2010, but i did not get the exaact results...Current week is getting, but previous week is not getting..
SP:
select WKRed from (
select distinct ' + @p_Suffix + '+
right(convert(varchar,(DATEADD(dd,-(DATEPART(dw, ' + @p_Column + ') - 1),' + @p_Column + ')) , 111 ),10) + ''-'' +
right(convert(varchar,(DATEADD(dd,-(DATEPART(dw, ' + @p_Column + ') - 7),' + @p_Column + ')) , 111 ),10) WKRed,
right(convert(varchar,(DATEADD(dd,-(DATEPART(dw, ' + @p_Column + ') - 7),' + @p_Column + ')) , 111 ),10) WKRedOrd
from ' + @p_Table + ' ' + @p_Where + '
union
select ' + @p_Suffix + '+
right(convert(varchar,(DATEADD(dd,-(DATEPART(dw, getdate()) - 1),getdate())) , 111 ),10) + ''-'' +
right(convert(varchar,(DATEADD(dd,-(DATEPART(dw, getdate()) - 7),getdate())) , 111 ),10) WKRed,
right(convert(varchar,(DATEADD(dd,-(DATEPART(dw, getdate()) - 7),getdate())) , 111 ),10) WKRedOrd
)TAB
ORDER BY TAB.WKRedOrd desc
The result IS:
[Week:2010/01/10-2010/01/16],[Week:2009/12/27-2010/01/02],[Week:2009/12/20-2009/12/26].
Here 2010/01/03-2010/01/09 week is missing.
Please help me regarding this
January 12, 2010 at 12:56 am
It looks like you're using dynamic query.
The second part seems to work (I'm getting WKRed:2010/01/10-2010/01/16, WKRedOrd: 2010/01/16).
It looks like there are some data missing when you apply your dynamic table selection and WHERE clause (from ' + @p_Table + ' ' + @p_Where).
You should print the statement instead of executing it and check if the resulting query is what you need.
If you'd like us to have a closer look at it we'd need some sample data. Please see the first link in my signature on how to do that.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply