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 |
|
dlorenc
Posting Yak Master
172 Posts |
 Posted - 2009-02-10 : 11:57:50
|
| Is there a way to use Value1/Value2 in the calculation of metricvalue instead of repeating the sql code?SELECT SUM(CASE when Resolved_Time - Arrival_Time <= 1./24. then 1 else 0 end) AS Value1 , COUNT(Case_ID_) as Value2 -- Number of Cases,(SUM(CASE when Resolved_Time - Arrival_Time <= (1./24.) then 1 else 0 end))/ cast(count(Case_ID_) as float) AS MetricValue FROM "HPD_HelpDesk" "HPD_HelpDesk" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-02-10 : 12:58:50
|
| SELECT a.val AS Value1 , COUNT(Case_ID_) as Value2 -- Number of Cases,a.val/ cast(count(Case_ID_) as float) AS MetricValue from(select val = sum(CASE when Resolved_Time - Arrival_Time <= 1./24. then 1 else 0 end FROM "HPD_HelpDesk" "HPD_HelpDesk")) aFROM "HPD_HelpDesk" "HPD_HelpDesk";with a()as(select val = sum(CASE when Resolved_Time - Arrival_Time <= 1./24. then 1 else 0 end FROM "HPD_HelpDesk" "HPD_HelpDesk"))SELECT a.val AS Value1 , COUNT(Case_ID_) as Value2 -- Number of Cases,a.val/ cast(count(Case_ID_) as float) AS MetricValue from a==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2009-02-10 : 16:14:59
|
| Thank you for the hints..I took your concepts and re-cast them into the following...do you see any performance issues with my solution?declare @x float,@y float;select @x = SUM(CASE when Resolved_Time - Arrival_Time <= 1./24. then 1 else 0 end) ,@y = COUNT(Case_ID_)FROM HPD_HelpDeskSELECT distinct 17 as MetricID -- Resolve Incidents within one hour,DATEADD(d, - DATEPART(dw, GETDATE()), GETDATE()) AS ReportWeek,.5 as Target,@x as value1 -- Number of Cases Resolved under an hour,@y as value2 -- Number of Cases,1-(@x/@y) as metricvalue,GETDATE() AS Entered FROM HPD_HelpDesk |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-02-11 : 04:36:20
|
| Should be ok.will tablescan but as you are processing the whole table that can't be helped.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
optimize a query by using variables?