Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Values form select in cycle to a single View

Author  Topic 

Ironic
Starting Member

3 Posts

Posted - 2012-11-25 : 16:32:36
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-26 : 06:44:37
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
   

- Advertisement -