Can you wrap it all into a single stored proc that will return the results as a cursor that contains all 19 calculations?

-Dell

I don't know enough about your database to give you an exact answer, but you might try something like this:

Select
'Assigned' as CalcType,
ws.P4DistrictNumber as BranchNo,
ws.Cycle as Trip,
COUNT(DISTINCT msi.MeterSessionInputKey) as Value
from ...

UNION

Select
'<name of the second value>' as CalcType,
ws.P4DistrictNumber as BranchNo,
ws.Cycle as Trip,
<whatever the summary is for the second calculation> as Value
from ...

UNION

Select
'<name of the third value>' as CalcType,
ws.P4DistrictNumber as BranchNo,
ws.Cycle as Trip,
<whatever the summary is for the third calculation> as Value
from ...

etc. until you have all 19 values

This will give you a result set that has 19 rows, each with a "CalcType" field indicating what type of value it is and a "Value" field that has the actual number in it.

If you need it all in a single row, I can get you the logic for that as well.

-Dell

Take the Order By out of the first part of the query - Order By with a Union will apply to the whole result set, so you can't have it in each query. What I usually do is use parentheses around the query and then use the field number instead of the field name. It looks something like this:

(
Select ....
UNION
Select ....
)
Order By 1

-Dell

Yes, to both parts of your question.

To get them all in one row, do something like the following:

Select

  BranchNo,

  Trip,

  sum(Value1) as Value1,

  sum(Value2) as Value2,

  ...

  --Here's how to get the calc from your second question.

  sum(Value5) + sum(Value6) as TotalValue,

  ...

  sum(Value19) as Value19

from (

  Select 

    ws.P4DistrictNumber as BranchNo, 

    ws.Cycle as Trip, 

    COUNT(DISTINCT msi.MeterSessionInputKey) as Value1,

    0 as Value2,

    0 as Value3,

    ...

    0 as Value19

  from fcs.dbo.WorkSet as ws

  ...

UNION

  Select 

    ws.P4DistrictNumber as BranchNo, 

    ws.Cycle as Trip, 

    0 as Value1,

    COUNT(field2) as Value2,

    0 as Value3,

    ...

    0 as Value19

  from fcs.dbo.WorkSet as ws

  ...

UNION

  Select 

    ws.P4DistrictNumber as BranchNo, 

    ws.Cycle as Trip, 

    0 as Value1,

    0 as Value2,

    COUNT(field3) as Value3,

    ...

    0 as Value19

  from fcs.dbo.WorkSet as ws

  ...

) as Data

Group By BranchNo, Trip

-Dell

Try this:

Select
BranchNo, trip,
sum(Assigned) as Assigned,
sum(ReadsTotByRte) as ReadsTotByRte
from
(
Select
ws.P4DistrictNumber as BranchNo,
ws.Cycle as Trip,
COUNT(DISTINCT msi.MeterSessionInputKey) as Assigned,
0 as ReadsTotByRte

from fcs.dbo.WorkSet as ws

left outer join fcs.dbo.Work as w
on ws.WorkSetKey = w.WorkSetKey

left outer join fcs.dbo.Customer as c
on w.WorkKey = c.WorkKey

left outer join fcs.dbo.WorkAssignment as wa
on ws.WorkSetID = wa.WorkSetID

left outer join fcs.dbo.meter as m
on c.CustomerKey = m.CustomerKey

left outer join fcs.dbo.CustomDatafield as cdf
on c.CustomerKey = cdf.CustomerKey

left outer join fcs.dbo.CustomDataField as cdfl
on m.MeterKey = cdfl.MeterKey

left outer join fcs.dbo.MeterSessionInput as msi
on m.MeterKey = msi.MeterKey

left outer join fcs.dbo.MeterSessionOutput as mso
on msi.MeterSessionInputKey = mso.MeterSessionInputKey

where
ws.StartDateTime IS NOT NULL and
wa.RetiredFlag = 0 and
wa.LoadStatusIndicator NOT IN (0, 4) and
msi.P4UtilityTypeReadIndicator != 'F' and

case
        when
            cast(wa.startdatetime as date) <= cast(ws.scheduledreaddate as date) and
            wa.workfiltername not in ('DNRs','Type 2s/3s') or
            ((ws.worksetid %100 < 50 and
            substring(ws.worksetid, len(ws.worksetid) - 3, 1) = 0))
        then 1
            End = 1 and
            
ws.Cycle = 8

            
group by ws.P4DistrictNumber, ws.Cycle

UNION

/***** This script generates the ReadRICS column. */
Select
ws.P4DistrictNumber as BranchNo,
ws.Cycle as Trip,
0 as Assigned,
COUNT(DISTINCT mso.MeterSessionOutputKey) as ReadsTotByRte

from fcs.dbo.WorkSet as ws

left outer join fcs.dbo.Work as w
on ws.WorkSetKey = w.WorkSetKey

left outer join fcs.dbo.Customer as c
on w.WorkKey = c.WorkKey

left outer join fcs.dbo.WorkAssignment as wa
on ws.WorkSetKey = wa.WorkSetKey

left outer join fcs.dbo.meter as m
on c.CustomerKey = m.CustomerKey

left outer join fcs.dbo.CustomDatafield as cdf
on c.CustomerKey = cdf.CustomerKey

left outer join fcs.dbo.CustomDataField as cdfl
on m.MeterKey = cdfl.MeterKey

left outer join fcs.dbo.MeterSessionInput as msi
on m.MeterKey = msi.MeterKey

left outer join fcs.dbo.MeterSessionOutput as mso
on msi.MeterSessionInputKey = mso.MeterSessionInputKey

where
ws.StartDateTime IS NOT NULL and
wa.RetiredFlag = 0 and
wa.LoadStatusIndicator NOT IN (0, 4) and
msi.P4UtilityTypeReadIndicator != 'F' and
mso.ReadCode NOT IN ('0','2' ,'99') and
mso.BestResultIndicator = 2 and
(cdf.CustomData IS NULL or
lower(substring(cdf.customdata, 9, 4)) != 'demo') and
(
mso.TroubleCode1 <> 'CA' and
mso.TroubleCode2 <> 'CA' and
mso.TroubleCode3 <> 'CA' and
mso.TroubleCode4 <> 'CA' and
mso.TroubleCode5 <> 'CA' and
mso.TroubleCode6 <> 'CA'
) and
case
when cast(wa.startdatetime as date) <= cast(ws.scheduledreaddate as date) and
       wa.workfiltername not in ('DNRs','Type 2s/3s') or
       ((ws.worksetid %100 < 50 and
        substring(ws.worksetid, len(ws.worksetid) - 3, 1) = 0))
      then 1
End = 1 and
ws.Cycle = 8
group by ws.P4DistrictNumber, ws.Cycle
) as Data
group by BranchNo
order by BranchNo

This will get you the branch number, trip, and then two numeric fields - Assigned and ReadsTotByRte. The trick to this technique is that every query in your union statement will have as many "measure" fields as you need for ALL of the totals. However, each query will only set the total for its own field and then set 0 for all of the other "measure" fields. By wrapping that in an outer select statement and then summing each field, you get a single record for each branch/trip that contains all of the summaries (value from each of the "measure" fields) for that branch/trip combination.

-Dell