Solved

Group by Week show Month and 1st day of week

Posted on 2012-04-03
5
604 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
  • 2
  • 2
5 Comments
 
LVL 19

Expert Comment

by:Rimvis
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
Rimvis, that worked wonderfully.  Thank you very much!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now