Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lemondash
Posting Yak Master
159 Posts |
 Posted - 2010-03-31 : 08:41:13
|
| Hello,Am trying to put together a query that will return usage with the date across the top but with the date to be end of the week.this is what i have so farSELECT luserid,COUNT(*) , DATEADD(dd, -DATEPART(dw, CONVERT(VARCHAR, dtDateLogged, 101)) + 7, CONVERT(VARCHAR, dtDateLogged, 101))FROM dbo.LogGROUP BY luserid,DATEADD(dd, -DATEPART(dw, CONVERT(VARCHAR, dtDateLogged, 101)) + 7, CONVERT(VARCHAR, dtDateLogged, 101)) ORDER BY DATEADD(dd, -DATEPART(dw, CONVERT(VARCHAR, dtDateLogged, 101)) + 7, CONVERT(VARCHAR, dtDateLogged, 101)) |
|
|
RobertKaucher
Posting Yak Master
169 Posts |
Posted - 2010-03-31 : 10:18:53
|
| I don't quite get your request. Could you provide a sample of what you expect the output to look like?It sounds like you are looking to find a way to hard code the field to be the date of the last day of the week?If that is the case I suggest reading this post. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50624Make sure you understand how SQL Server "understands" a week.Edit: I should say how the function "understands" a week. SQL Server has some funny notions about how many weeks are in the year...===http://www.ElementalSQL.com/ |
 |
|
|
lemondash
Posting Yak Master
159 Posts |
Posted - 2010-03-31 : 10:54:45
|
| i would like it to look like this.UserID [2010-03-13] [2010-03-20] [2010-03-27] [2010-04-03]23345 0 34 23 22 |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2010-03-31 : 10:57:37
|
| If you would like to generate dynamic columns based on date values, you would need pivoting with dynamic sql. Something along this line: [url]http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables[/url]Harsh Athalyehttp://www.letsgeek.net/ |
|
|
|
|
|
|
|
|
End of week query/report