Solved

Group by Week show Month and 1st day of week

Posted on 2012-04-03
5
610 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
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 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

803 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