Report Design: Stored Procedure

Print Page | Close Window

Stored Procedure

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20345
Printed Date: 01 May 2024 at 10:30pm

Topic: Stored Procedure

Posted By: Tupacmoche
Subject: Stored Procedure
Date Posted: 15 Jan 2014 at 5:47am

I have a CR that uses a stored procedure to generate its output. This works fine as is but, it needs to do more. It calculates a single value and I need this SPROC to calculate about 20 values. So, now to the question. How do, I implement the other 19 or so calculations ? How do, I call the other 19 SPROC In a stored procedure in CR ?

-------------
Rob

Replies:

Posted By: hilfy
Date Posted: 15 Jan 2014 at 5:53am

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

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Tupacmoche
Date Posted: 15 Jan 2014 at 6:22am

Here is a copy of the existing script. As, I mentioned another 19 or so scripts would be needed to calculate the other values. What is the basic structure to implement your suggestion?

/***** This script generates the ASSIGNED column. */

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

-------------
Rob

Posted By: hilfy
Date Posted: 15 Jan 2014 at 6:49am

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

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Tupacmoche
Date Posted: 15 Jan 2014 at 4:16pm

Thanks for this suggestion. I tried it by taking the same code and putting a UNION between the code but got an error message:

Msg 156, Level 15, State 1, Line 48
Incorrect syntax near the keyword 'UNION'.

If this can be fixed all, I would have to do is add the other sproc after a UNION as you did in your example to make it work. Please see my code below.

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

UNION

/***** This script generates the ASSIGNED column. */
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

-------------
Rob

Posted By: hilfy
Date Posted: 16 Jan 2014 at 2:58am

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

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Tupacmoche
Date Posted: 16 Jan 2014 at 7:57am

Dell thanks for all your help in this matter. I am so accustom to using tools that generate code that I need an sql refresher course. Which leads to two follow up questions. The code correction you suggested worked fine but I expected the expression to be in columns. Instead, I got value 1, then value 2 and I expect if I add the other18 or so expressions they will be rows instead of columns. First, is there a way of making them columns? Second, several of the expressions are actually part of a single expression. What, I mean is ExpA + ExpB = TotalExp instead of adding these up manually after the script is run is there a way to have the script sum the two parts and export the total?

Thanks for all your insights.

-------------
Rob

Posted By: hilfy
Date Posted: 16 Jan 2014 at 8:34am

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

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Tupacmoche
Date Posted: 16 Jan 2014 at 10:25am

Dell, I am not following what you mean. Here is the script that produced the output. Should, I put the first select statement along with the four fields listed below over the attached script? With regard to the second question each block of code that is joined by a Union is doing one of the calculations that belong to the Exp1 + Exp2 = ExpTot so, I don't understand how one of the calculations can be referenced outside of the scope where it is being calculated.

/***** This script generates the ASSIGNED column. */
(
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 = 8


group by ws.P4DistrictNumber, ws.Cycle

UNION

/***** This script generates the ReadRICS column. */
Select ws.P4DistrictNumber as BranchNo, ws.Cycle as Trip, 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

)

order by ws.P4DistrictNumber

-------------
Rob

Posted By: hilfy
Date Posted: 16 Jan 2014 at 11:47am

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

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Tupacmoche
Date Posted: 16 Jan 2014 at 1:32pm

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!

-------------
Rob

Posted By: hilfy
Date Posted: 17 Jan 2014 at 3:01am

Yes. Look at the syntax for doing a "with" clause. It might look something like this:

With baseqry as (
Select
ws.P4DistrictNumber as BranchNo,
ws.Cycle as Trip,
msi.MeterSessionInputKey,
mso.MeterSessionOutputKey,
ws.StartDateTime,
wa.RetiredFlag,
wa.LoadStatusIndicator,
msi.P4UtilityTypeReadIndicator,
wa.workfiltername,
ws.worksetid,
mso.ReadCode,
mso.BestResultIndicator,
cdf.CustomData,
mso.TroubleCode1,
mso.TroubleCode2,
mso.TroubleCode3,
mso.TroubleCode4,
mso.TroubleCode5,
mso.TroubleCode6

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 --Assuming these criteria are standard for all of the scripts
ws.StartDateTime IS NOT NULL and
wa.RetiredFlag = 0 and
wa.LoadStatusIndicator NOT IN (0, 4) and
msi.P4UtilityTypeReadIndicator != 'F'
)

Select
BranchNo, trip,
sum(Assigned) as Assigned,
sum(ReadsTotByRte) as ReadsTotByRte
from
(
Select
BranchNo,
Trip,
COUNT(DISTINCT MeterSessionInputKey) as Assigned,
0 as ReadsTotByRte

from baseqry

where --you don't really need the case statement here
(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))) and
Cycle = 8

group by BranchNo, Trip

UNION

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

from baseqry

where
ReadCode NOT IN ('0','2' ,'99') and
BestResultIndicator = 2 and
(CustomData IS NULL or
lower(substring(customdata, 9, 4)) != 'demo') and
(
TroubleCode1 <> 'CA' and
TroubleCode2 <> 'CA' and
TroubleCode3 <> 'CA' and
TroubleCode4 <> 'CA' and
TroubleCode5 <> 'CA' and
TroubleCode6 <> 'CA'
) and
(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))) and
Cycle = 8
group by BranchNo, Trip
) as Data
group by BranchNo, Trip
order by BranchNo, Trip

You'll need to be sure to include in the "With" query ALL of the fields that you'll need for all of the where clauses. Also, if there are any conditions that are common to ALL of the where clauses in your scripts, put those in the where clause of the "With" query only - that way you'll make that initial data set smaller and the whole query will run faster.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Tupacmoche
Date Posted: 27 Jan 2014 at 3:40am

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) )

(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?

-------------
Rob

Posted By: hilfy
Date Posted: 27 Jan 2014 at 3:44am

There are a couple of challenges with this depending on what data order do you use to determine "previous" in the report. I can probably come up with a way around it once I have that information. Also please post the whole query with along with info about where this complex where clause should go.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Tupacmoche
Date Posted: 27 Jan 2014 at 7:29pm

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 ")
         )
        )
      )
   )
)
;

-------------
Rob

Posted By: hilfy
Date Posted: 29 Jan 2014 at 3:08am

Ok, how are you grouping/ordering your data in the report? I need that information in order to figure out how to get the previous values in SQL.

Thanks!

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Tupacmoche
Date Posted: 29 Jan 2014 at 3:22am

The grouping and ordering is below.

group by ws.P4DistrictNumber, ws.Cycle

order by ws.P4DistrictNumber

-------------
Rob

Posted By: hilfy
Date Posted: 29 Jan 2014 at 4:10am

That's in the query - how about in the report itself?

Based on the query, is ws.Cycle a number or text? Does "previous" look at the previous cycle within a district?

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Tupacmoche
Date Posted: 29 Jan 2014 at 6:12am

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.

-------------
Rob

Posted By: hilfy
Date Posted: 29 Jan 2014 at 6:50am

OK, I'm still a bit confused over how "previous" is determined in the data. When you were using Previous in the original report, how was the data sorted? The issue is that data coming into a query is not necessarily in any sort of sequential order, so you need to provide rules for what makes a record the "previous" record.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Tupacmoche
Date Posted: 29 Jan 2014 at 9:22am

I think, I'm following and understanding you better now. From your comments, I guess you can use PREVIOUS() to look back at not just the prior row (what I assumed) but group or other logical set. With that understanding in mind here is my answer. After, all the tables are joined together and the filter is applied for any given Division lets say Division 45 and cycle 17 you are looking at the privious row of that result set. Since, this filter with the previous() function is used in one script at a time when placed in the where clause in would simple be:

Where

Report level filter items

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

Expression level filter

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
)
;

So, the short & skinny answer is it simple looks at the previous row of the result set. I hope that, I'm clear.


-------------
Rob

Posted By: hilfy
Date Posted: 30 Jan 2014 at 9:49am

No, "Previous()" in Crystal goes to the previous row in the data based on how the report is grouped and sorted. However, there is no "Previous" function in SQL. So, you have to somehow create a sub-query that will provide a single row that contains the data from that previous row. However, in order to do that, you have know how the data is ordered (grouping may or may not give you the order in SQL) to determine what to put in the where clause to get that information.

-Dell

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: kostya1122
Date Posted: 31 Jan 2014 at 3:55am

to get previous you could add
ROW_NUMBER ( )
OVER ( order by somefield ) as link

function to the query then create same query but with
ROW_NUMBER ( )
OVER ( order by somefield )-1 as link
link them together using row_number and it should give you the previous.

Print Page | Close Window