SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Values form select in cycle to a single View
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ironic
Starting Member

Portugal
3 Posts

Posted - 11/25/2012 :  16:32:36  Show Profile  Reply with Quote
Hi

So i have a cycle to go over all the weeks in the year and count number of events in each week. This outputs as if it was 51 different queries.

For readability i would like to join all of this information in a view or table. I have done this with UNION when i have several queries in this case since it's always the same query being repeated i do not know how to do this.

Regards

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/26/2012 :  06:44:37  Show Profile  Reply with Quote
Can you post one of your queries. It is very likely that you can get the results for all 51 weeks by joining to a calendar table. Would be easier to provide more concrete and useful solution if people are able to see the template query. What I have in mind is something like this:
SELECT
	DATEADD(wk,DATEDIFF(wk,0,c.Date),0) AS [Week],
	COUNT(*) AS EventCount
FROM
	CalendarTable c
	LEFT JOIN EventsTable e ON
		CAST(e.EventDate AS Date) = c.Date
GROUP BY
	DATEADD(wk,DATEDIFF(wk,0,c.Date),0);
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000