March 10, 2014 at 2:36 pm
I need to calculate average # of requests per week. The Year # and Week # both fields are Integer and Request # is Varchar
I understand that for the average calculation I should have : The sum(Requests)/Count(Weeks)
SELECT
count(RequestID) as Request
,Year_Number
,Week_Number
,WeekYear = Cast(Week_Number as Varchar(10)) + '-' + Cast(Year_Number as Varchar(10))
FROM Prod
WHERE Configuration = 'Keyboard'
group By Week_Number ,Year_Number
When I do a count of Week_Number I somehow get the same values as that of RequestCount. I am expecting the count of weeks to be 10 as there are total of 10 weeks . If I add it in a seperate CTE , I get the proper count. But then don't know How I should join the two cte to get the complete picture. Is there a better way to do it that CTE. PS:I need groupwise data per WeekYear
March 10, 2014 at 2:55 pm
sharonsql2013 (3/10/2014)
I need to calculate average # of requests per week. The Year # and Week # both fields are Integer and Request # is VarcharI understand that for the average calculation I should have : The sum(Requests)/Count(Weeks)
SELECT
count(RequestID) as Request
,Year_Number
,Week_Number
,WeekYear = Cast(Week_Number as Varchar(10)) + '-' + Cast(Year_Number as Varchar(10))
FROM Prod
WHERE Configuration = 'Keyboard'
group By Week_Number ,Year_Number
When I do a count of Week_Number I somehow get the same values as that of RequestCount. I am expecting the count of weeks to be 10 as there are total of 10 weeks . If I add it in a seperate CTE , I get the proper count. But then don't know How I should join the two cte to get the complete picture. Is there a better way to do it that CTE. PS:I need groupwise data per WeekYear
How about posting the ddl and some sample data so we are all on the same page?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply