Thanks Dell that works! I would like to ask a side question about the the script and making it less redundant. The only difference between any of the 20 or so scripts is what is in the where clause.

Is there a way to 'call' the code in the where clause x number of times while using the script for the joined tables only once?

Thanks!

Dell, I have encountered code in one of the where clauses that I uses a function that, I don't know how to convert into SQL 2008, I don't think that it exist in mssql 2008 r2. See below:

if (

    {MeterSessionInput.P4UtilityTypeReadIndicator} = "L" and

    PREVIOUS ({MeterSessionInput.P4UtilityTypeReadIndicator}) = "E" and

    {Meter.MeterNumber} = PREVIOUS ({Meter.MeterNumber})

   ) then

(

  if ({MeterSessionOutput.ReadCode} <> 0 and

      {MeterSessionOutput.ReadCode} <> 2 and

      {MeterSessionOutput.ReadCode} <> 99 and

      {MeterSessionOutput.BestResultIndicator} = 2 and

      (isNull({CustomDataField_MeterLink.CustomData}) = true or

       IsDemo({CustomDataField_MeterLink.CustomData}) = false) )

     or

     (PREVIOUS({MeterSessionOutput.ReadCode}) <> 0 and

      PREVIOUS({MeterSessionOutput.ReadCode}) <> 2 and

      PREVIOUS({MeterSessionOutput.ReadCode}) <> 99 and

      PREVIOUS({MeterSessionOutput.BestResultIndicator}) = 2 and

     IsDemo(PREVIOUS({CustomDataField_MeterLink.CustomData})) = false) then

     true

)

;

The strange thing is that the use of PREVIOUS() is a call in a Crystal report. Any suggestions?



 

As you can see by following this tread it is centered around 20 or so scripts used to calculate expressions in an SQL script. As, I mentioned earlier all the sprocs center around the script below and only the where clause changes in them. The code that, I sent earlier is yet another condition to put into the where clause of the sproc. I will place the code of this new where clause after the script below. This report is running against an ms sql 2008 r2 DB and as far as, I know 2008r2 does not implement the PREVIOUS function.

THIS IS THE SCRIPT THAT IS RUN OVER 20 TIMES.

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

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 = 7
           
group by ws.P4DistrictNumber, ws.Cycle

order by ws.P4DistrictNumber

THIS IS THE END OF THE SCRIPT

This is the code for the where clause with the PREVIOUS function

if (
    {MeterSessionInput.P4UtilityTypeReadIndicator} = "L" and
    PREVIOUS ({MeterSessionInput.P4UtilityTypeReadIndicator}) = "E" and
    {Meter.MeterNumber} = PREVIOUS ({Meter.MeterNumber})
   ) then
(
  if ({MeterSessionOutput.ReadCode} <> 0 and
      {MeterSessionOutput.ReadCode} <> 2 and
      {MeterSessionOutput.ReadCode} <> 99 and
      {MeterSessionOutput.BestResultIndicator} = 2 and
      (isNull({CustomDataField_MeterLink.CustomData}) = true or
       IsDemo({CustomDataField_MeterLink.CustomData}) = false) )
     or
     (PREVIOUS({MeterSessionOutput.ReadCode}) <> 0 and
      PREVIOUS({MeterSessionOutput.ReadCode}) <> 2 and
      PREVIOUS({MeterSessionOutput.ReadCode}) <> 99 and
      PREVIOUS({MeterSessionOutput.BestResultIndicator}) = 2 and
      IsDemo(PREVIOUS({CustomDataField_MeterLink.CustomData})) = false) then
     true
)
;


THIS IS another where clause that uses the function:

// Counting all DMD not just CustomData DMD - RLB WR340731
// All DMD Meters will have a MeterSessionInput.P4UtilityTypeReadIndicator = 'L'

if ({MeterSessionInput.P4UtilityTypeReadIndicator} = "L") and
   (PREVIOUS ({MeterSessionInput.P4UtilityTypeReadIndicator}) = "E") and
   ({Meter.MeterNumber} = PREVIOUS ({Meter.MeterNumber})) then
(
    //Check if meter is Read and RIC
 if (
     (
       Previous({MeterSessionOutput.ReadCode}) <> 0 and
       Previous({MeterSessionOutput.ReadCode}) <> 2 and
       Previous({MeterSessionOutput.ReadCode}) <> 99 and
       Previous({MeterSessionOutput.BestResultIndicator}) = 2 and
       IsDemo(Previous({CustomDataField_MeterLink.CustomData})) = false
      )
    //And Current is RIC
    AND
     (
       {MeterSessionOutput.ReadCode} = 2 and
       {MeterSessionOutput.BestResultIndicator} = 2 and
       {MeterSessionOutput.SkipCode} = '5' and
       (isNull({CustomDataField_MeterLink.CustomData}) = true or
         IsDemo({CustomDataField_MeterLink.CustomData}) = false) and
       (
         (Trim({MeterSessionOutput.TroubleCode1}) <> "" and {MeterSessionOutput.TroubleCode1} <> "00" and {MeterSessionOutput.TroubleCode1} <> "CA") or
         (Trim({MeterSessionOutput.TroubleCode2}) <> "" and {MeterSessionOutput.TroubleCode2} <> "00" and {MeterSessionOutput.TroubleCode2} <> "CA") or
         (Trim({MeterSessionOutput.TroubleCode3}) <> "" and {MeterSessionOutput.TroubleCode3} <> "00" and {MeterSessionOutput.TroubleCode3} <> "CA") or
         (Trim({MeterSessionOutput.TroubleCode4}) <> "" and {MeterSessionOutput.TroubleCode4} <> "00" and {MeterSessionOutput.TroubleCode4} <> "CA") or
         (Trim({MeterSessionOutput.TroubleCode5}) <> "" and {MeterSessionOutput.TroubleCode5} <> "00" and {MeterSessionOutput.TroubleCode5} <> "CA") or
         (Trim({MeterSessionOutput.TroubleCode6}) <> "" and {MeterSessionOutput.TroubleCode6} <> "00" and {MeterSessionOutput.TroubleCode6} <> "CA")
       )
     )
   )
 then
   false
 else
   (
     {MeterSessionInput.P4UtilityTypeReadIndicator} = "L" and
     PREVIOUS ({MeterSessionInput.P4UtilityTypeReadIndicator}) = "E" and
     {Meter.MeterNumber} = PREVIOUS ({Meter.MeterNumber}) and 
     {MeterSessionOutput.ReadCode} = 2 and
     {MeterSessionOutput.BestResultIndicator} = 2 and
     {MeterSessionOutput.SkipCode} = '5' and
     (isNull({CustomDataField_MeterLink.CustomData}) = true or
      IsDemo({CustomDataField_MeterLink.CustomData}) = false) and
      (
       (
         (Trim({MeterSessionOutput.TroubleCode1}) <> "" and {MeterSessionOutput.TroubleCode1} <> "00" and {MeterSessionOutput.TroubleCode1} <> "CA") or
         (Trim({MeterSessionOutput.TroubleCode2}) <> "" and {MeterSessionOutput.TroubleCode2} <> "00" and {MeterSessionOutput.TroubleCode2} <> "CA") or
         (Trim({MeterSessionOutput.TroubleCode3}) <> "" and {MeterSessionOutput.TroubleCode3} <> "00" and {MeterSessionOutput.TroubleCode3} <> "CA") or
         (Trim({MeterSessionOutput.TroubleCode4}) <> "" and {MeterSessionOutput.TroubleCode4} <> "00" and {MeterSessionOutput.TroubleCode4} <> "CA") or
         (Trim({MeterSessionOutput.TroubleCode5}) <> "" and {MeterSessionOutput.TroubleCode5} <> "00" and {MeterSessionOutput.TroubleCode5} <> "CA") or
         (Trim({MeterSessionOutput.TroubleCode6}) <> "" and {MeterSessionOutput.TroubleCode6} <> "00" and {MeterSessionOutput.TroubleCode6} <> "CA ")
       )
       or
        ( PREVIOUS({MeterSessionOutput.ReadCode}) = 2 and
          PREVIOUS({MeterSessionOutput.SkipCode}) = '5' and
         (
          (Trim(PREVIOUS({MeterSessionOutput.TroubleCode1})) <> "" and PREVIOUS({MeterSessionOutput.TroubleCode1}) <> "00" and PREVIOUS({MeterSessionOutput.TroubleCode1}) <> "CA") or
          (Trim(PREVIOUS({MeterSessionOutput.TroubleCode2})) <> "" and PREVIOUS({MeterSessionOutput.TroubleCode2}) <> "00" and PREVIOUS({MeterSessionOutput.TroubleCode2}) <> "CA") or
          (Trim(PREVIOUS({MeterSessionOutput.TroubleCode3})) <> "" and PREVIOUS({MeterSessionOutput.TroubleCode3}) <> "00" and PREVIOUS({MeterSessionOutput.TroubleCode3}) <> "CA") or
          (Trim(PREVIOUS({MeterSessionOutput.TroubleCode4})) <> "" and PREVIOUS({MeterSessionOutput.TroubleCode4}) <> "00" and PREVIOUS({MeterSessionOutput.TroubleCode4}) <> "CA") or
          (Trim(PREVIOUS({MeterSessionOutput.TroubleCode5})) <> "" and PREVIOUS({MeterSessionOutput.TroubleCode5}) <> "00" and PREVIOUS({MeterSessionOutput.TroubleCode5}) <> "CA") or
          (Trim(PREVIOUS({MeterSessionOutput.TroubleCode6})) <> "" and PREVIOUS({MeterSessionOutput.TroubleCode6}) <> "00" and PREVIOUS({MeterSessionOutput.TroubleCode6}) <> "CA ")
         )
        )
      )
   )
)
;

The grouping and ordering is below.

group by ws.P4DistrictNumber, ws.Cycle

order by ws.P4DistrictNumber

Here is a copy of the query that run 20 time or more with different where clauses. The group by and order by are as indicated above. For any of the calculations that I'm running. Since, I am running this script in CR as a stored proc the where clause has everything in it there is no separation of report vs expression level conditions. ws.cycle is a string[10]. Previous does not look at a previous cycle within a district every script is actually filtered to a single cycle example ws.cysle = 15. Nevertheless it does it's calculations within the district.

Select ws.P4DistrictNumber as BranchNo, ws.Cycle as Trip, COUNT(DISTINCT msi.MeterSessionInputKey) as Assigned 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	Always the same.
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 = 15	Changes for every script.  The conditions with previous will replace this where clause code.
group by ws.P4DistrictNumber, ws.Cycle order by ws.P4DistrictNumber	Always the same.   Every time the script is run the cycle is the same for all 20 or more times it is run. Ws.P4DistrictNumber as BranchNo always changes there are 24 values for it.