Solved

Group by Week show Month and 1st day of week

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

685 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