?
Solved

Group by Week show Month and 1st day of week

Posted on 2012-04-03
5
Medium Priority
?
616 Views
Last Modified: 2012-04-04
Hi Experts,

I need some help with this, please.

These two return the same data, but the first one doesn't work in SSRS so we modified it to the second one.   I want to add another column with a more human readable version of the week, like "04/01 - 04/07" or just the first day of each week so that the new column looked like:
04/01
04/08
04/15
SELECT DATEPART(YEAR, DateTime) AS Year, DATEPART(WK, DateTime) AS Week, Office, COUNT(CustomerID) AS Traffic
  FROM [OneStopSignIn].[dbo].[Customers]
  GROUP BY Office,DATEPART(YEAR, DateTime), DATEPART(wk, DateTime)
  ORDER BY  DATEPART(YEAR, DateTime), DATEPART(wk, DateTime), Office

Open in new window

SELECT dYear, dWeek, office, count(customerid) as traffic from
(SELECT DATEPART(YEAR, DateTime) AS dYear,  DATEPART(WK, DateTime) AS dWeek, Office, CustomerID
  FROM [OneStopSignIn].[dbo].[Customers]) x
  GROUP BY dYear, dWeek, office
  ORDER BY dYear,  dWeek, office

Open in new window


This has to work in SSRS. I don't know why the first one complains about DateTime not being a valid column.

Thanks.
0
Comment
Question by:megnin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 19

Expert Comment

by:Rimvis
ID: 37804678
Hello megnin,

DateTime is reserved keyword in MS SQL. If you cave a column in your table named that way (which is BAD idea), enclose it in "[]", e.g. DATEPART(YEAR, [DateTime])
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37804759
The first query aliases the first column as Year, not a good idea either because that's a reserved keyword as well.

See for the full list: http://msdn.microsoft.com/en-us/library/ms189822(v=sql.105).aspx
0
 
LVL 1

Author Comment

by:megnin
ID: 37805640
Oh, I didn't think of that.  Thanks.  

But no one offed any suggestions about adding the "week date" column.  That was my question.
0
 
LVL 19

Accepted Solution

by:
Rimvis earned 2000 total points
ID: 37805712
How about this:
CONVERT(CHAR(5), DATEADD(wk, DATEDIFF(wk, 6, [DateTime]), 6), 1) + ' - ' + CONVERT(CHAR(5), DATEADD(wk, DATEDIFF(wk, 5, [DateTime]), 5), 1)
0
 
LVL 1

Author Closing Comment

by:megnin
ID: 37806099
Rimvis, that worked wonderfully.  Thank you very much!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question