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 |
|
thedryden
Starting Member
23 Posts |
 Posted - 2007-12-13 : 03:11:03
|
| I have a summary table with a number of columns that give all the information I need to build a report. What I would like to do is create a view specific to a single report, that organizes the data so that each row represents one metric. The only way I know of to do this would be with a series of Union querries, but that would require querrying what is basicly the same data multiple times. Is there some way to gather the data in one pass and then split it up with multiple Union queries? Sicne I doubt I'm explaining this well I'll just try an exsample.Let say I have a summary table with the following columns: Location, Severity, Date_Day, Number_Dispatch, Dispatch_Duration, Dispatch_Goal, Number_Dispatch_Met_Goal, and Dispatch_Met_Goal. Now I want to turn this into a table with the following columns: Metric, Location, Severity, Goal, Value.The only way I know how to do that is with the following SQL:--Number Dispatched YesterdaySELECT Tickets Dispatched Yesterday AS Metric , Location , Severity , N/A AS Goal , sum( Number_Dispatch ) AS VALUEFROM Summary_TableWhereUNION--Average Dispatch DurationSELECT Average Dispatch Duration AS Metric , Location , Severity , Dispatch_Goal AS Goal , sum( Dispatch_Duration ) / sum( Number_Dispatch ) AS ValueFROM Summary_TableWhere...UNION--Percent Dispatch Duration Met GoalSELECT Percent Dispatch Duration Met Goal AS Metric , Location , Severity , Dispatch_Met_Goal AS Goal , sum(Number_Dispatch_Met_Goal ) / sum( Number_Dispatch ) AS ValueFROM Summary_TableWhere...Now I dont have a problem writing a statement for each metric, but it seems like this would be a rather wasteful query, as each would have the same where statement. What Id like is some way to either do all of the above in one pass (some kind of CASE statement perhaps?) or some way to pull the data for all the UNION queries in one pass. |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2007-12-13 : 03:16:43
|
Are you creating your report with reporting services?...... If so then this can be done using the matrix option as opposed to the table option.Duane.  |
 |
|
|
thedryden
Starting Member
23 Posts |
Posted - 2007-12-13 : 10:58:54
|
| Using reporting services would work, and be easier on the database, but if I do it this way I can create a report where I can dynamicly change the report simply by editing the SQL, as apposed to editing the SQL then editing the report... |
|
|
|
JasonL
Starting Member
35 Posts |
Posted - 2007-12-13 : 14:46:13
|
| I am quite sure you could use CASE to decode the sum and group values. That is merging all the 3 sql to one. However, without knowing your datasets (differences between 1,2 and 3 sqls of your union) and the where clause (that is the where clause and group by). Can't really help with the actual sql. Nonetheless: I believe each of your returned rows should look sometime like:Location,Severity,"T Dispatched Yesterday", "T Dispatched_Yesterday_value_sum","Dispatch_Goal", "Dispatch_Goal_sum_value","Dispatch_met_goal", "Dispatch_Met_goal_sum_value"HTH |
|
|
|
thedryden
Starting Member
23 Posts |
Posted - 2007-12-17 : 15:05:24
|
| Sorry for the delay, I though I could get around this using my reporting tool, but it turns out I can't so I'm back to trying to do it this way. What I really can't wrap my mind around is how to use SQL to create more rows than are in your source data. As to your questions the where and group by clauses are identical for all of the Union Queries, and are as follows:Where DATE_DAY >= cast(convert(varchar(8),getdate()-1,1) as datetime) AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime) AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' ) GROUP BY DATE_DAY , EQ_MARKET_CLUSTER , Severity |
|
|
|
|
|
|
|
|
Turning Columns into Rows