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.
| Author |
Topic |
|
gsogol
Starting Member
1 Post |
 Posted - 2011-06-04 : 15:21:58
|
| I have two tables Campaigns and UserCampaigns with 1:many relationship. I need to display on the chart multiple metrics grouped by month. The chart needs to display data for a specific date range (one year, two, etc). The X-axis simply will contain month 1, month 2, month 3 and so forth. The y-axis will contain 5 metrics. Each metric has its own where clause using the aformentioned tables. Some metrics/counts will not have data for some months and others may but I need to display all months within a specific data range.So the tables do have a datatime column including others which are meaningless for this discussion and simply contain various fact data and dimension data for where clauses (just using BI terms) even though it's a standard sql server db. How would you write a query to accomplish this hopefully in one sql statement? Just to reiterate, there's one common where clause which is a data range. To get each individual metric, it would need an additional where clause for that particular metric. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-04 : 17:29:07
|
One way to do it would be to include the "where clause" in the select part itself. For exampleselect sum(case when condition1=1 and condition2=0 then col1 end) as FirstSum, sum(case when conditionA=1 or conditionB=1 then col2 end) as SecondSumfrom YourTablewhere date between @startDate and @endDate If you can post table DDLs, sample input data and expected results, people on this forum may be able to offer alternate/better suggestions. See here: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
 |
|
|
|
|
|
Querying for multiple metrics in one statement